Searching over structured and unstructured data in 10G

In my previous blog post I've explained a nice little feature introduced to Oracle Text Context indexes in 11G which allows you to do efficient searches involving both structured and unstructured data. It is time to talk about what could be done by those of you using previous Oracle versions.

I'll start with the same table and data which I've used in my previous blog post.

Tag it

Literally. Because the best way to search over structured and unstructured data is, well, not to mix it. I'll show you how to use your own datastore procedure to archive this goal.

First of all, we need to declare an author tag, we will use it for our searches later:

SQL> begin
  2   ctx_ddl.create_section_group('books_section_group', 'basic_section_group');
  3   ctx_ddl.add_field_section('books_section_group', 'author', 'author', false);
  4  end;
  5  /
 
PL/SQL procedure successfully completed

All we need after that is a simple custom datastore procedure to merge it all together (this function is what Oracle Text will index as well) and tag the author:

SQL> create or replace procedure books_ds(
  2   p_rid in rowid,
  3     p_clob in out clob
  4  ) is
  5  begin
  6   for cur in (select '<author>'||author||'</author>' author, text from books where rowid=p_rid)
  7   loop
  8    dbms_lob.copy(p_clob, cur.author, dbms_lob.getlength(cur.author));
  9    dbms_lob.append(p_clob, cur.text);
 10   end loop;
 11  end;
 12  /
 
Procedure created

Let's create an index now:

SQL> alter table books add (books_info varchar2(1));
 
Table altered

SQL> begin
  2   ctx_ddl.create_preference('books_ds', 'user_datastore');
  3   ctx_ddl.set_attribute('books_ds', 'procedure', 'books_ds');
  4  end;
  5  /
 
PL/SQL procedure successfully completed

SQL> CREATE INDEX ctx_books_info
  2    ON books (books_info)
  3    INDEXTYPE IS CTXSYS.CONTEXT
  4    PARAMETERS('filter ctxsys.null_filter section group books_section_group lexer books_lexer datastore books_ds memory 64m');
 
Index created

Continuing on the example I've used in my previous post, searching on books which mention procedures and are written by author XDB will look like this:

SQL> select author, title, published
  2   from books
  3   where contains(books_info, 'xdb within author and procedure', 1) > 0
  4   order by score(1) desc;

37 rows selected.


Statistics
----------------------------------------------------------
         13  recursive calls
          0  db block gets
         40  consistent gets
          0  physical reads
          0  redo size
       1379  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         37  rows processed

Maintaining summaries in a highly concurrent fashion

I was involved in designing a highly-concurrent OLTP system last year (more than 300K users during peak hours) which were allowing users to play online and win some prizes. As part of the prize winning logic we had to maintain count of prizes won in order to be able to display it in real-time.

The table contained won prizes looked basically like this:

SQL> create table winners
  2  (
  3   prize_id number primary key,
  4   user_id  number
  5  );
 
Table created

That is -- we were storing prize_id among with the user_id who won it. There were a couple of millions prizes available for winning. You can accomplish won prize counting in a number of ways:

• Count it each time.
  Do this only if you partner up with the same folks who sell you hardware and Oracle CPU licenses.
  
  
• Create on-commit materialized view with count.
  Though much better than previous KIWI'ish approach, it brings a bit of overhead for maintaining a materialized view log plus, if you don't take special cares, may blocks users during commit. Nevertheless, this is usually a valid approach, especially if you want to leverage features like query rewrite. We didn't really need it and, to make things more interesting, someone was winning a prize every so milliseconds. Do simple things (insert into winners, mview log, update summaries table, delete from mview log) a lot of times and wander how quickly it starts to add up.
  
  
• Maintain summaries yourself.
  This is relatively easy to do, especially when application does nothing but calls a set of PL/SQL APIs (which means you can do whatever you want to archive the required output).

However, doing a simple update winners_cnt set cnt=cnt+1 is going to serialize all the winners so we need an easy way to spread stuff out. The simplest, yet very efficient, way to archive this is to do something like this:

SQL> create table winners_cnt_t
  2  (
  3   slot_id number  primary key,
  4   cnt  number
  5  ) organization index;
 
Table created

SQL> insert into winners_cnt_t
  2   select level-1, 0
  3    from dual
  4    connect by level <= 11;
 
11 rows inserted
 
SQL> commit;
 
Commit complete

Make sure your slots count is a prime number. Then you can hide it behind the view to make things transparent:

create view winners_cnt as
 select sum(cnt) cnt
  from winners_cnt_t;

After that, all you have to do is maintain number of winners the following way...

update winners_cnt_t
 set cnt=cnt+1
 where slot_id=mod(p_prize_id, 11);

...which will distribute updates evenly among all rows in the table. You can vary number of slots to match your degree of concurrency.

11G adaptive direct path reads -- what is the cached/dirty blocks threshold?

11G's ability to do direct path reads during full table scans without utilizing PQ was covered in a number of places already (see this post by Doug Burns for example).

When direct path reads starts to happen?

It is known that somewhat reliable figure is your _small_table_threshold multiplied by 5 (mentioned by Tanel Poder on oracle-l recently). You can discover it using quick and dirty test case similar to this:

SQL> create tablespace adr_test datafile size 64m segment space management manual;
 
Tablespace created

SQL> create table t (v varchar2(100)) pctused 1 pctfree 99 tablespace adr_test;
 
Table created

SQL> create or replace function get_adr_trsh(
  2   p_step  in number,
  3   p_start  in number default 0,
  4   p_stop  in number default null
  5  ) return number is
  6   l_prd  number;
  7   l_blocks number:=0;
  8   l_start  number:=p_start;
  9  begin
 10   execute immediate 'truncate table t';
 11  
 12   loop
 13    insert /*+ append */ into t
 14     select rpad('*', 100, '*')
 15      from dual
 16      connect by level <= p_step + l_start;
 17    commit;
 18  
 19    l_blocks:=l_blocks + p_step + l_start;
 20    l_start:=0;
 21  
 22    execute immediate 'alter system flush buffer_cache';
 23  
 24    select /*+ full(t) */ count(*) into l_cnt from t;
 25  
 26    select value into l_prd
 27     from v$segment_statistics
 28     where owner=user
 29      and object_name='T'
 30      and statistic_name='physical reads direct';
 31  
 32    exit when (l_prd > 0 or l_blocks > nvl(p_stop, l_blocks));
 33  
 34   end loop;
 35  
 36   return l_blocks - p_step;
 37  end;
 38  /
 
Function created

My _small_table_threshold is:

SQL> select ksppstvl
  2   from x$ksppi x, x$ksppcv y
  3   where (x.indx = y.indx)
  4    and ksppinm='_small_table_threshold';
 
KSPPSTVL
--------------------------------------------------------------------------------
314

which is about 2% of my buffer cache (128MB) so you may expect 11G switch to direct path reads once table goes beyond 1570 blocks. Let's check it:

SQL> declare
  2   l_trsh number;
  3  begin
  4   l_trsh:=get_adr_trsh(10, 1500, 2000);
  5  
  6   dbms_output.put_line(l_trsh);
  7  end;
  8  /
 
1570
 
PL/SQL procedure successfully completed

Note that that number is somewhat "about" and you can get different results depending on stuff like using ASSM/MSSM.

What is the cached blocks threshold?

Direct path reads stops happening after certain amount of your table's blocks are in the buffer cache already. Discovering it is fairly easy as well:

SQL> --need this so we can do irs and cache table blocks
SQL> create index i_t on t (1);
 
Index created

SQL> create or replace function get_cached_trsh(
  2    p_start in number default 0,
  3    p_step in number default 1
  4  ) return number is
  5   cursor l_cur is select /*+ index(t i_t) */ * from t;
  6   l_v varchar2(100);
  7   l_trsh number:=0;
  8   l_prd number:=0;
  9   l_cnt number:=0;
 10   l_start number:=p_start;
 11  begin
 12   execute immediate 'alter system flush buffer_cache';
 13   open l_cur;
 14  
 15   loop
 16    for i in 1 .. p_step+l_start
 17    loop
 18     fetch l_cur into l_v;
 19    end loop;
 20    l_trsh:=l_trsh+p_step+l_start;
 21    l_start:=0;
 22  
 23    select /*+ full(t) */ count(*) into l_cnt from t;
 24  
 25    select value into l_cnt
 26     from v$segment_statistics
 27     where owner=user
 28      and object_name='T'
 29      and statistic_name='physical reads direct';
 30  
 31    exit when l_cnt=l_prd or l_cur%notfound;
 32  
 33    l_prd:=l_cnt;
 34  
 35   end loop;
 36  
 37   close l_cur;
 38   return l_trsh;
 39  end;
 40  /
 
Function created

Now, we can see after how many blocks 11G will stop doing direct path reads:

SQL> declare
  2   l_trsh number;
  3  begin
  4   l_trsh:=get_cached_trsh(500, 1);
  5  
  6   dbms_output.put_line(l_trsh);
  7  end;
  8  /
 
789
 
PL/SQL procedure successfully completed

Which happens to be half of the table's blocks. I've repeated the above test with 256MB buffer cache and got 3140 blocks (number of blocks for direct read to start happening) and 1568 (number of cached blocks) respectively. Please note that cached blocks threshold seems to be not dependent on your buffer cache size (to a degree where it can find space of course).

What is the dirty blocks threshold?

Doing direct path reads requires segment level checkpoint which may not be something you would like to do if you have a lot of these for the sake of direct read alone.

Something we can start with:

SQL> create or replace function get_dirty_trsh(
  2    p_step in number,
  3    p_start in number default 0,
  4    p_stop in number default null
  5  ) return number is
  6   l_trsh number:=0;
  7   l_prd number:=0;
  8   l_cnt number:=0;
  9   l_start number:=p_start;
 10  begin
 11   execute immediate 'alter system flush buffer_cache';
 12  
 13   loop
 14    l_trsh:=l_trsh+p_step+l_start;
 15    update t set v=v where rownum <= l_trsh;
 16    commit;
 17    l_start:=0;
 18  
 19    select /*+ full(t) */ count(*) into l_cnt from t;
 20  
 21    select value into l_cnt
 22     from v$segment_statistics
 23     where owner=user
 24      and object_name='T'
 25      and statistic_name='physical reads direct';
 26  
 27    exit when l_cnt=l_prd or l_trsh > nvl(p_stop, l_trsh);
 28  
 29    l_prd:=l_cnt;
 30  
 31   end loop;
 32  
 33   return l_trsh;
 34  end;
 35  /
 
Function created

SQL> declare
  2   l_trsh number;
  3  begin
  4   l_trsh:=get_dirty_trsh(1, 350, 400);
  5  
  6   dbms_output.put_line(l_trsh);
  7  end;
  8  /
384

PL/SQL procedure successfully completed.

Which turns out to be 1/4 of a table size.

Quick and dirty

Please note that adaptive direct path reads could (and most probably do) have much more variables to make a decision. The above test were done using ad-hoc approach to at least have an idea what could be potential factors there. Things like system statistics, tablespace block sizes, delayed blocks cleanouts, etc. has a potential to interfere over there.

SQL> select * from v$version;
 
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for 64-bit Windows: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

Scaling dynamic SQL

The story

I was working for one of the Russian biggest telcos back then and marketing guys came out with their next idea. One of the most frequent things your customers do is checking their balances. The idea was to attach an advertising to each balance response. However, usually you don't really want to attach some static message that you show to everyone because it is not the way your customers care about it. Think of Google, which became so popular because it prioritizes information by relevance. Doing something useless to your customers not only annoys them but wastes your bandwidth as well.

What you have to do is targeted adverts. What that means is that you may substantially increase the value by figuring out what your customers would like to see based on a data about them which you have in your billing system. And you know literally hundreds of things about any single customer. Starting from age, martial status, birthday, active services and ending up with approximate location based on triangulation data. Think of Google's sponsored links.

The system

Upon receiving a balance request, the system had to find out various stuff about requester and then figure out what to show based on the rules from a marketing department. Rules had to offer virtually unlimited flexibility and the system itself had to be flexible to introduce and remove targeting conditions on the fly.

The implementation

What we had is a simple table with a PL/SQL predicates, like this:

SQL> create table p
  2  (
  3   p_id number primary key,
  4   p varchar2(4000)
  5  );
 
Table created

SQL> insert into p values (1, 'to_number(:age) between 16 and 18');
 
1 row inserted
SQL> insert into p values (2, 'to_date(:birthday, ''yyyymmdd'')=trunc(sysdate) or :balance > 1000');
 
1 row inserted
SQL> insert into p values (3, 'to_number(:balance) between 100 and 200 and :age > 18');
 
1 row inserted
 
SQL> commit;
 
Commit complete

Of course, in reality it was a bit more complex than that, allowing for prioritizing, schedules, etc. As you'd probably already guessed, upon receiving a balance request we were binding data about customer into the above predicates to figure out which one evaluates to true which in turn lead to relevant advert. Given that you may be getting around 5000 balance requests each second plus you have to multiply this by a number of predicates you have to check, you already talking about tens of thousands evaluations per second. In other words, you have to make the entire thing pretty darn efficient.

Soft parsing

Soft parsing is something which is deemed by many to be inevitable as soon as you have to deal with stuff like above -- storing dynamically generated PL/SQL predicates in a table and executing these during runtime. And this is something we had to avoid because, even with stuff like session_cached_cursor, parse is still a parse, a scalability inhibitor and CPU waster. Think of not using KIWI.

What to do?

I'll show you a little trick you can use to break the dreaded marriage of dynamic SQL with parsing. Take a look at the following package:

create or replace package eval is

 type t_cached_cursors is table of number index by p.p%type;
 g_cached_cursors t_cached_cursors;

 function evaluate(
  p in p.p%type,
  p_n in dbms_sql.Varchar2_Table,
  p_v in dbms_sql.Varchar2_Table
 ) return boolean;

 function evaluate_nc(
  p in p.p%type,
  p_n in dbms_sql.Varchar2_Table,
  p_v in dbms_sql.Varchar2_Table
 ) return boolean;
end eval;

create or replace package body eval is

function evaluate(
 p in p.p%type,
 p_n in dbms_sql.Varchar2_Table,
 p_v in dbms_sql.Varchar2_Table
) return boolean is
 l_cursor number;
 l_res  number;
begin
 begin
  l_cursor:=g_cached_cursors(p);
 exception when no_data_found then
  l_cursor:=dbms_sql.open_cursor;
  dbms_sql.parse(l_cursor, p, dbms_sql.native);
  g_cached_cursors(p):=l_cursor;
 end;
 dbms_sql.bind_variable(l_cursor, 'l_res', l_res);

 for i in 1 .. p_n.count
 loop
  if (instr(p, ':'||p_n(i)) > 0)
  then
   dbms_sql.bind_variable(l_cursor, p_n(i), p_v(i));
  end if;
 end loop;

 l_res:=dbms_sql.execute(l_cursor);
 dbms_sql.variable_value(l_cursor, 'l_res', l_res);

 return (l_res=1);
end;

function evaluate_nc(
 p in p.p%type,
 p_n in dbms_sql.Varchar2_Table,
 p_v in dbms_sql.Varchar2_Table
) return boolean is
 l_cursor number;
 l_res  number;
begin
 l_cursor:=dbms_sql.open_cursor;
 dbms_sql.parse(l_cursor, p, dbms_sql.native);
 dbms_sql.bind_variable(l_cursor, 'l_res', l_res);

 for i in 1 .. p_n.count
 loop
  if (instr(p, ':'||p_n(i)) > 0)
  then
   dbms_sql.bind_variable(l_cursor, p_n(i), p_v(i));
  end if;
 end loop;

 l_res:=dbms_sql.execute(l_cursor);
 dbms_sql.variable_value(l_cursor, 'l_res', l_res);
 dbms_sql.close_cursor(l_cursor);

 return (l_res=1);
end;

end eval;

The package has two functions -- evaluate and evaluate_nc, the first one is using a simple caching trick. The idea behind evaluate function is really that simple -- upon opening and parsing a cursor, place it into in-memory table indexed by cursor text for further reuse instead of closing it. Each execution peeks at that in-memory table to see if there is a cursor we can reuse instead of going through the whole parsing exercise.

The usage is simple as well:

SQL> declare
  2   l_n  dbms_sql.varchar2_table;
  3   l_v  dbms_sql.varchar2_table;
  4   l_res boolean;
  5  begin
  6   l_n(1):='age';
  7   l_n(2):='birthday';
  8   l_n(3):='balance';
  9   l_v(1):='16';
 10   l_v(2):='20090101';
 11   l_v(3):='1500';
 12  
 13   for cur in (select p_id, 'declare l_res number; begin :l_res:=case when ('||p||') then 1 else 
0 end; end;' p from p)
 14   loop
 15    l_res:=eval.evaluate(cur.p, l_n, l_v);
 16    dbms_output.put_line('eval p'||to_char(cur.p_id)||': '||case when l_res then 'true' else 'fal
se' end);
 17   end loop;
 18  end;
 19  /
eval p1: true
eval p2: true
eval p3: false

Let's do some tests now. Here are two test procedures:

create or replace procedure test_cached(
 p_i in number
) is
 l_p   dbms_sql.Varchar2_Table;
 l_n   dbms_sql.varchar2_table;
 l_v   dbms_sql.varchar2_table;
 l_res  boolean;
begin
 l_n(1):='age';
 l_n(2):='birthday';
 l_n(3):='balance';

 l_v(1):='100';
 l_v(2):='20090101';
 l_v(3):='1000';

 select 'declare /* cached */ l_res number; begin :l_res:=case when ('||p||') then 1 else 0 end; end;'
  bulk collect into l_p
  from p;

 dbms_monitor.session_trace_enable(waits => true, binds => false);
 for i in 1 .. p_i
 loop
  for j in 1 .. l_p.count
  loop
   l_res:=eval.evaluate(l_p(j), l_n, l_v);
  end loop;
 end loop;
 dbms_monitor.session_trace_disable;
end;

create or replace procedure test_not_cached(
 p_i in number
) is
 l_p   dbms_sql.Varchar2_Table;
 l_n   dbms_sql.varchar2_table;
 l_v   dbms_sql.varchar2_table;
 l_res  boolean;
begin
 l_n(1):='age';
 l_n(2):='birthday';
 l_n(3):='balance';

 l_v(1):='100';
 l_v(2):='20090101';
 l_v(3):='1000';

 select 'declare /* not_cached */ l_res number; begin :l_res:=case when ('||p||') then 1 else 0 end; end;'
  bulk collect into l_p
  from p;

 dbms_monitor.session_trace_enable(waits => true, binds => false);
 for i in 1 .. p_i
 loop
  for j in 1 .. l_p.count
  loop
   l_res:=eval.evaluate_nc(l_p(j), l_n, l_v);
  end loop;
 end loop;
 dbms_monitor.session_trace_disable;
end;

I tested each of these using four parallel jobs with 100000 iterations (p_i parameter) each. Here is what I've got (I'm using one of the predicates as an example):

declare /* cached */ l_res number; begin :l_res:=case when (to_number(:age) 
  between 16 and 18) then 1 else 0 end; end;


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 100000      1.40       1.29          0          0          0      100000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   100001      1.40       1.29          0          0          0      100000

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 40     (recursive depth: 2)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  cursor: pin S wait on X                        35        0.01          0.37
  cursor: pin S                                  91        0.00          0.00

declare /* not_cached */ l_res number; begin :l_res:=case when 
  (to_number(:age) between 16 and 18) then 1 else 0 end; end;


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse   100000      1.00       1.21          0          0          0           0
Execute 100000      2.73       2.74          0          0          0      100000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   200000      3.73       3.95          0          0          0      100000

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 40     (recursive depth: 2)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  library cache: mutex X                        116        0.00          0.00
  cursor: mutex S                                24        0.00          0.00
  cursor: pin S                                  83        0.00          0.00
  cursor: pin S wait on X                        30        0.01          0.28

That's a three times improvement. Note that in first tkprof report we got only one parse, while in second one amount of parses equals executions, as we expected.

Searching over structured and unstructured data in 11G

For those of you who have to deal with searches involving both structured and unstructured data, there is a small neat feature introduced to Oracle Text Context indexes in Oracle 11G which you may find to be very useful.

The example

Let's say you have a books table like this:

SQL> create table books
  2  (
  3     author          varchar2(30),
  4     title           varchar2(30),
  5     published       date,
  6     text            clob
  7  ) lob (text) store as securefile
  8  (
  9     enable storage in row
 10     cache
 11  );

Table created.

I'm going to populate it using some test data:

SQL> declare
  2     l_clob  clob;
  3  begin
  4     for obj_cur in (
  5             select owner, object_name, created
  6                     from dba_objects
  7                     where object_type in ('PROCEDURE','PACKAGE','PACKAGE BOD
Y','FUNCTION','TRIGGER')
  8     ) loop
  9             insert into books values (obj_cur.owner, obj_cur.object_name, ob
j_cur.created, empty_clob())
 10                     returning text into l_clob;
 11
 12             dbms_lob.open(l_clob, dbms_lob.lob_readwrite);
 13
 14             for text_cur in (
 15                     select text
 16                             from dba_source
 17                             where owner=obj_cur.owner
 18                                     and name=obj_cur.object_name
 19                             order by type, line
 20             ) loop
 21                     dbms_lob.writeappend(l_clob, dbms_lob.getlength(text_cur
.text), text_cur.text);
 22             end loop;
 23
 24             dbms_lob.close(l_clob);
 25     end loop;
 26  end;
 27  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

What I have basically done there is dumped the source text of some of my database objects into books table. Object owner goes as an author, object_name represents a title, object creation date goes as published and, of course, the source itself goes as book's text.

The search

Let's say I'm interested in all the books which write about procedures. To do that kind of search using Oracle Text is fairly straightforward.

First of all, we want to make sure that underscore symbol goes as part of a token. It is not really required for our example, however, it is generally a good idea to do since we want stuff like DBMS_OUTPUT or DBMS_SQL appear as a single tokens instead of being split in half due to underscore symbol treated as a special character:

SQL> begin
  2     ctx_ddl.create_preference('books_lexer', 'BASIC_LEXER');
  3     ctx_ddl.set_attribute('books_lexer', 'printjoins', '_');
  4  end;
  5  /

PL/SQL procedure successfully completed.

All I have to do now is to create my index:

SQL> CREATE INDEX ctx_books_text
  2    ON books (text)
  3    INDEXTYPE IS CTXSYS.CONTEXT
  4    PARAMETERS('filter ctxsys.null_filter lexer books_lexer memory 64m');

Index created.

And do the search:

SSQL> select author, title, published
  2   from books
  3   where contains(text, 'procedure', 1) > 0
  4   order by score(1) desc;

622 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2979142934

-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |   785 |  1576K|       |   352   (1)| 00:00:05 |
|   1 |  SORT ORDER BY               |                |   785 |  1576K|  2104K|   352   (1)| 00:00:05 |
|   2 |   TABLE ACCESS BY INDEX ROWID| BOOKS          |   785 |  1576K|       |    12   (0)| 00:00:01 |
|*  3 |    DOMAIN INDEX              | CTX_BOOKS_TEXT |       |       |       |    12   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("CTXSYS"."CONTAINS"("TEXT",'procedure',1)>0)

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         13  recursive calls
          0  db block gets
        486  consistent gets
          0  physical reads
          0  redo size
      19748  bytes sent via SQL*Net to client
        801  bytes received via SQL*Net from client
         43  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        622  rows processed

We got 622 books which took us 486 LIOs. So far so good.

Let's add something else

Things will get a bit interesting (or complicated, depending on the perspective) once you'll try to mix both structured and unstructured data in one search. If I will take the above search, but this time I'm interested only in books written by author XDB, the search will look like this:

SQL> select author, title, published
  2   from books
  3   where contains(text, 'procedure', 1) > 0
  4    and author='XDB'
  5   order by score(1) desc;

37 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2979142934

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |    18 | 37026 |    13   (8)| 00:00:01 |
|   1 |  SORT ORDER BY               |                |    18 | 37026 |    13   (8)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| BOOKS          |    18 | 37026 |    12   (0)| 00:00:01 |
|*  3 |    DOMAIN INDEX              | CTX_BOOKS_TEXT |       |       |    12   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("AUTHOR"='XDB')
   3 - access("CTXSYS"."CONTAINS"("TEXT",'procedure',1)>0)

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         13  recursive calls
          0  db block gets
        486  consistent gets
          0  physical reads
          0  redo size
       1448  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         37  rows processed

Note that I've got only 37 books this time, however, it took me the same amount of resources. It is not a surprise, after all, because condition on an author column went as a filter predicate. In case your table has some indexes on structured columns already:

create index i_books_author on books (author);

Index created.

You can try to search using B*Tree index:

SQL> select /*+ index(books i_books_author */ author, title, published
  2   from books
  3   where contains(text, 'procedure', 1) > 0
  4    and author='XDB'
  5   order by score(1) desc;

37 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 806035686

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |    47 | 96679 |   288   (1)| 00:00:04 |
|   1 |  SORT ORDER BY               |                |    47 | 96679 |   288   (1)| 00:00:04 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| BOOKS          |    47 | 96679 |   287   (0)| 00:00:04 |
|*  3 |    INDEX RANGE SCAN          | I_BOOKS_AUTHOR |    88 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("CTXSYS"."CONTAINS"("TEXT",'procedure',1)>0)
   3 - access("AUTHOR"='XDB')

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
        223  consistent gets
          0  physical reads
          0  redo size
       1454  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         37  rows processed

Though a bit better, it just really went the other way around this time. We are now fetching all books written by XDB and then passing each book to Oracle Text for filtering.

The problem

As long as one of your structured/ustructured predicates have a good selectivity, the search using only either of indexes will produce good results, there is not much to post-filter, after all. However, if both predicates are selective only when applied together, you need something else to speed the things up. Indexes combined through BITMAP AND can produce some improvements:

SQL> select author, title, published
  2   from books
  3   where contains(text, 'procedure', 1) > 0
  4    and author='XDB'
  5   order by score(1) desc;

37 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3589384316

----------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                |    47 | 96679 |    17   (6)| 00:00:01 |
|   1 |  SORT ORDER BY                    |                |    47 | 96679 |    17   (6)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID     | BOOKS          |    47 | 96679 |    16   (0)| 00:00:01 |
|   3 |    BITMAP CONVERSION TO ROWIDS    |                |       |       |            |          |
|   4 |     BITMAP AND                    |                |       |       |            |          |
|   5 |      BITMAP CONVERSION FROM ROWIDS|                |       |       |            |          |
|*  6 |       INDEX RANGE SCAN            | I_BOOKS_AUTHOR |       |       |     1   (0)| 00:00:01 |
|   7 |      BITMAP CONVERSION FROM ROWIDS|                |       |       |            |          |
|   8 |       SORT ORDER BY               |                |       |       |            |          |
|*  9 |        DOMAIN INDEX               | CTX_BOOKS_TEXT |       |       |    12   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("AUTHOR"='XDB')
   9 - access("CTXSYS"."CONTAINS"("TEXT",'procedure',1)>0)

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         13  recursive calls
          0  db block gets
         57  consistent gets
          0  physical reads
          0  redo size
       1454  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         37  rows processed

We are down to only 57 LIOs. However, the entire thing still requires us to fetch all relevant rowids from both indexes followed by a merge. On huge document sets each step may output quite a bit of data and require substantial processing resources.

The new filter option in 11G

11G's Oracle Text allows you to specify structured data columns in a filter section while creating Oracle Text indexes:

SQL> CREATE INDEX ctx_books_text
  2    ON books (text)
  3    INDEXTYPE IS CTXSYS.CONTEXT
  4    FILTER BY author
  5    PARAMETERS('filter ctxsys.null_filter lexer books_lexer memory 64m');

Index created.

Let's see how the search will look like now:

SQL> select author, title, published
  2   from books
  3   where contains(text, 'procedure') > 0
  4    and author='XDB';

37 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2443340341

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |    47 | 96679 |    11   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| BOOKS          |    47 | 96679 |    11   (0)| 00:00:01 |
|*  2 |   DOMAIN INDEX              | CTX_BOOKS_TEXT |       |       |     8   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CTXSYS"."CONTAINS"("TEXT",'procedure')>0)
       filter("AUTHOR"='XDB')

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         13  recursive calls
          0  db block gets
         48  consistent gets
          0  physical reads
          0  redo size
       1357  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         37  rows processed

Note only 48 LIOs. While it may not be a solid improvement over merged indexes example, the important point here is that Oracle was able to get all required data from a context index alone.

What about 10G?

There is no filter option for context indexes in 10G, however, that doesn't mean that there is nothing you could do to help you facilitate your mixed structured and unstructured data searches. I'll blog about what could be done in 10G next time which, by the way, can be applied to 11G as well.

Alter database datafile offline drop

There was a topic on oracle-l recently and that remind me that offline drop provides one useful feature which people usually not leveraging.

This feature not only allows you to open your database without datafile foo...

SQL> startup
ORACLE instance started.

Total System Global Area  700448768 bytes
Fixed Size                  1260844 bytes
Variable Size             310379220 bytes
Database Buffers          385875968 bytes
Redo Buffers                2932736 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/oradata/XE/datafile/foo.dbf'


SQL> alter database datafile 4 offline drop;

Database altered.

SQL> alter database open;

Database altered.

But what's really important about this is that offline drop doesn't really drops anything (it just updates the controlfile to say that file isn't there) and what you can do later is:

[oracle@srm oradata]$ rman

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Dec 30 19:12:22 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

RMAN> connect target;

connected to target database: XE (DBID=2555430687)

RMAN> restore tablespace foo;

Starting restore at 30-DEC-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=73 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /u01/oradata/XE/datafile/foo.dbf
channel ORA_DISK_1: reading from backup piece /u01/oradata/fra/XE/backupset/2008_12_30/o1_mf_nnnd0_TAG20081230T190854_4oog0pt0_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/oradata/fra/XE/backupset/2008_12_30/o1_mf_nnnd0_TAG20081230T190854_4oog0pt0_.bkp tag=TAG20081230T190854
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 30-DEC-08

RMAN> recover tablespace foo;

Starting recover at 30-DEC-08
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 30-DEC-08

RMAN> sql 'alter tablespace foo online';

sql statement: alter tablespace foo online

In other words, it can be used to facilitate your database restore in certain cases. Imagine that your database has transactional and reporting data and that that data is spread across different tablespaces. In case your transactional data volume is small compared to reporting stuff (which is usually the case), you can plan your restore like this:

Restore everything but your analytical tablespaces, then offline drop missing datafiles and open your database => you are back into transactional business and immediate world stops screaming around you.

Restore your analytical tablespaces => they don't have to steer the company blindly anymore.

Depending on the data volumes, the first step might take only a fraction of time compared to restoring everything in one shot.