Hi! Tom
Well I tried ur solution but the Index still remains in the SYSTEM tablespace. I created "storage" preferences for the tables and this intermedia Index and specified it's creation in some other tablespace(default TBS) but it gets created in SYSTEM.
Is it a bug in Oracle8i?
And this is a very serious issue coz this is happening at our production DB. Need a solution asap.
Thanx

-- I create a table...USERS tablespace is the default TBS for this schema...
Create table news_test(ID NUMBER, BODY CLOB)
/
--Declare the preferences as suggested in Oracle8i docs...
begin
ctx_ddl.create_preference('store_clob', 'BASIC_STORAGE');
ctx_ddl.set_attribute('store_clob', 'I_TABLE_CLAUSE', 'tablespace users storage (initial 1M)');
ctx_ddl.set_attribute('store_clob', 'K_TABLE_CLAUSE', 'tablespace users storage (initial 1M)');
ctx_ddl.set_attribute('store_clob', 'R_TABLE_CLAUSE', 'tablespace users storage (initial 1M)');
ctx_ddl.set_attribute('store_clob', 'N_TABLE_CLAUSE', 'tablespace users storage (initial 1M)');
ctx_ddl.set_attribute('store_clob', 'I_INDEX_CLAUSE', 'tablespace users storage (initial 1M)');
end;
/

--And create the index using the Parameter string...
create index idx_news_body_test on news_test(body) indextype is ctxsys.context parameters('storage store_clob')
/


I agree that the other objects created eg... supporting tables and indexes get created in the USERS tablespace but when u query
the users_indexes table then:

select index_name , tablespace_name from user_indexes
/

INDEX_NAME TABLESPACE_NAME
------------------------------ ----------------------
DR$IDX_NEWS_BODY_TEST$X USERS
IDX_NEWS_BODY_TEST SYSTEM

So here's what all I did maybe I missed out on some important step...
Need a quick solution...maybe an alter statement or some other attribute that needs to be added...
coz I can't see any attribute being defined for the main INDEX "IDX_NEWS_BODY_TEST"...

Thanx in advance...
Palash

As I said, that is a "fake" index, it's a domain index -- it is NOT REAL.  Look in user_extents or user_segments instead:

ops$tkyte@ORA817DEV.US.ORACLE.COM> Create table news_test(ID NUMBER, BODY CLOB)
  2  /

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> begin
  2  ctx_ddl.create_preference('store_clob', 'BASIC_STORAGE');
  3  ctx_ddl.set_attribute('store_clob', 'I_TABLE_CLAUSE', 'tablespace users storage (initial 1M)');
  4  ctx_ddl.set_attribute('store_clob', 'K_TABLE_CLAUSE', 'tablespace users storage (initial 1M)');
  5  ctx_ddl.set_attribute('store_clob', 'R_TABLE_CLAUSE', 'tablespace users storage (initial 1M)');
  6  ctx_ddl.set_attribute('store_clob', 'N_TABLE_CLAUSE', 'tablespace users storage (initial 1M)');
  7  ctx_ddl.set_attribute('store_clob', 'I_INDEX_CLAUSE', 'tablespace users storage (initial 1M)');
  8  end;
  9  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> create index idx_news_body_test on news_test(body) indextype is ctxsys.context
  2  parameters('storage store_clob')
  3  /

Index created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select segment_name, segment_type, tablespace_name, blocks
  2    from user_extents
  3   order by segment_type, segment_name
  4  /


SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE     BLOCKS
------------------------------ ------------------ ---------- ----------
DR$IDX_NEWS_BODY_TEST$X        INDEX              USERS              64
DR$IDX_NEWS_BODY_TEST$X        INDEX              USERS              64
SYS_IOT_TOP_28452              INDEX              USERS              64
SYS_IOT_TOP_28452              INDEX              USERS              64
SYS_IOT_TOP_28457              INDEX              USERS              64
SYS_IOT_TOP_28457              INDEX              USERS              64
SYS_IL0000028445C00002$$       LOBINDEX           USERS              64
SYS_IL0000028449C00006$$       LOBINDEX           USERS              64
SYS_IL0000028454C00002$$       LOBINDEX           USERS              64
SYS_LOB0000028445C00002$$      LOBSEGMENT         USERS              64
SYS_LOB0000028449C00006$$      LOBSEGMENT         USERS              64
SYS_LOB0000028454C00002$$      LOBSEGMENT         USERS              64
DR$IDX_NEWS_BODY_TEST$I        TABLE              USERS              64
DR$IDX_NEWS_BODY_TEST$I        TABLE              USERS              64
DR$IDX_NEWS_BODY_TEST$R        TABLE              USERS              64
DR$IDX_NEWS_BODY_TEST$R        TABLE              USERS              64
NEWS_TEST                      TABLE              USERS              64

17 rows selected.

There is NOTHING there!  Don't worry about it. 

hi tom
i totally agree with you.. but why is it creating an object in system tablespace. or is it just a pointer to the real object...what is going on behind the scene here

p!

hi tom,
i know all you said is true but i am more interested in whu there is this false index in system tablespace. There has to be some use of it internally. what is oracle is doing with this index?
i appreciate if you could explain what is going on under the hood and why this false index?
thank you!
P!

PS-i know that this is nor taking any space but i am wondering what is the reason that its in system tablespace

thanks tom!

Master Tom,

I' m in charge of a sizing estimation of a db. I' m able to find sizing formulas or derive some myself for table data as well for btree indexes. We don' t have bitmap indexes in place. I was looking for estimation formulas in order to estimate the size of context domain indexes and I did not find a lot. Up to me to try to derive something myself.
In a test db which hasn' t anyhting to do with the db concerned I ' ve created a set of domain indexes (type context) I used the ctx_report.index_size and ctx_report.index_stats for those indexes all that in the way described here
</code> http://download-uk.oracle.com/docs/cd/B19306_01/text.102/b14218/crptpkg.htm#i996912 <code>
Very interesting output of ctx_report. I found back on the same table, same schema, same index type , same context attributes (filter,stopword) the size of the domain indexes can vary a lot. On both not null columns the size of an index on a varchar2(20) was 2,5 times the size from a varchar2(100) !

Questions
1. Am I right that apart from the number of indexed table rows the number of tokens is the most important parameter with regard to the space of a context domain index ?
2. If no , what are some other important parameters influencing the size.

Thanks in advance for another valuable answer of you.

Regards
Guy

Hi Tom,
I want to merge the rows in a table with domain index. Since merge feature is not supported with domain index table what is the alternative other than just delete and insert.

Thanks

Hi Tom,

I am running into a different version of this same problem, basically i want to place all my Oracle Text index objects in a one tablespace (I am planning to distribute the I/O load). I am having it on a partitioned table and I want to local partition the text index and place them on the relevant tablespace.My Oracle version is 10.2.0.2.

Issue I am runnig into is that the text objects does not utilize the tablespace that I am explicitly allocating it to via preferece BASIC_STORAGE options.Oracle text objects are created on the same tablespace of the table it self (in my case its the default tablespace for the user data for that schema).

I have setup the BASIC_STORAGE as follows,

begin
ctx_ddl.create_preference('my_default_st', 'BASIC_STORAGE');
ctx_ddl.set_attribute('my_default_st', 'I_TABLE_CLAUSE','tablespace ot_index storage (initial 256K)');
ctx_ddl.set_attribute('my_default_st', 'K_TABLE_CLAUSE','tablespace ot_index storage (initial 256K)');
ctx_ddl.set_attribute('my_default_st', 'R_TABLE_CLAUSE','tablespace ot_index storage (initial 256K) lob (data) store as (disable storage in row cache)');
ctx_ddl.set_attribute('my_default_st', 'N_TABLE_CLAUSE','tablespace ot_index storage (initial 256K)');
ctx_ddl.set_attribute('my_default_st', 'I_INDEX_CLAUSE','tablespace ot_index storage storage (initial 256K) compress 2');
ctx_ddl.set_attribute('my_default_st', 'P_TABLE_CLAUSE','tablespace ot_index storage (initial 256K)');
end;
/

begin
ctx_ddl.drop_preference('part_tbs1');
end;
/

begin
ctx_ddl.create_preference('part_tbs1', 'BASIC_STORAGE');
ctx_ddl.set_attribute('part_tbs1', 'I_TABLE_CLAUSE','tablespace ot_index storage (initial 256K)');
ctx_ddl.set_attribute('part_tbs1', 'K_TABLE_CLAUSE','tablespace ot_index storage (initial 256K)');
ctx_ddl.set_attribute('part_tbs1', 'R_TABLE_CLAUSE','tablespace ot_index storage (initial 256K) lob (data) store as (disable storage in row cache)');
ctx_ddl.set_attribute('part_tbs1', 'N_TABLE_CLAUSE','tablespace ot_index storage (initial 256K)');
ctx_ddl.set_attribute('part_tbs1', 'I_INDEX_CLAUSE','tablespace ot_index storage (initial 256K) compress 2');
ctx_ddl.set_attribute('part_tbs1', 'P_TABLE_CLAUSE','tablespace ot_index storage (initial 256K)');
end;
/

begin
ctx_ddl.drop_preference('part_tbs2');
end;
/

begin
ctx_ddl.create_preference('part_tbs2', 'BASIC_STORAGE');
ctx_ddl.set_attribute('part_tbs2', 'I_TABLE_CLAUSE','tablespace ot_index storage (initial 256K)');
ctx_ddl.set_attribute('part_tbs2', 'K_TABLE_CLAUSE','tablespace ot_index storage (initial 256K)');
ctx_ddl.set_attribute('part_tbs2', 'R_TABLE_CLAUSE','tablespace ot_index storage (initial 256K) lob (data) store as (disable storage in row cache)');
ctx_ddl.set_attribute('part_tbs2', 'N_TABLE_CLAUSE','tablespace ot_index storage (initial 256K)');
ctx_ddl.set_attribute('part_tbs2', 'I_INDEX_CLAUSE','tablespace ot_index storage (initial 256K) compress 2');
ctx_ddl.set_attribute('part_tbs2', 'P_TABLE_CLAUSE','tablespace ot_index storage (initial 256K)');
end;
/


begin
ctx_ddl.drop_preference('part_tbs3');
end;
/

begin
ctx_ddl.create_preference('part_tbs3', 'BASIC_STORAGE');
ctx_ddl.set_attribute('part_tbs3', 'I_TABLE_CLAUSE','tablespace ot_index storage (initial 256K)');
ctx_ddl.set_attribute('part_tbs3', 'K_TABLE_CLAUSE','tablespace ot_index storage (initial 256K)');
ctx_ddl.set_attribute('part_tbs3', 'R_TABLE_CLAUSE','tablespace ot_index storage (initial 256K) lob (data) store as (disable storage in row cache)');
ctx_ddl.set_attribute('part_tbs3', 'N_TABLE_CLAUSE','tablespace ot_index storage (initial 256K)');
ctx_ddl.set_attribute('part_tbs3', 'I_INDEX_CLAUSE','tablespace ot_index storage (initial 256K) compress 2');
ctx_ddl.set_attribute('part_tbs3', 'P_TABLE_CLAUSE','tablespace ot_index storage (initial 256K)');
end;
/


begin
ctx_ddl.drop_preference('part_tbs4');
end;
/

begin
ctx_ddl.create_preference('part_tbs4', 'BASIC_STORAGE');
ctx_ddl.set_attribute('part_tbs4', 'I_TABLE_CLAUSE','tablespace ot_index storage (initial 256K)');
ctx_ddl.set_attribute('part_tbs4', 'K_TABLE_CLAUSE','tablespace ot_index storage (initial 256K)');
ctx_ddl.set_attribute('part_tbs4', 'R_TABLE_CLAUSE','tablespace ot_index storage (initial 256K) lob (data) store as (disable storage in row cache)');
ctx_ddl.set_attribute('part_tbs4', 'N_TABLE_CLAUSE','tablespace ot_index storage (initial 256K)');
ctx_ddl.set_attribute('part_tbs4', 'I_INDEX_CLAUSE','tablespace ot_index storage (initial 256K) compress 2');
ctx_ddl.set_attribute('part_tbs4', 'P_TABLE_CLAUSE','tablespace ot_index storage (initial 256K)');
end;
/

-- create the user data store prior to proceeding to next section


-- Create the OT index preferences
BEGIN
CTX_DDL.CREATE_PREFERENCE('my_asg_uds', 'user_datastore');
CTX_DDL.SET_ATTRIBUTE ('my_asg_uds', 'procedure','ot_user_datastore_pr');
CTX_DDL.SET_ATTRIBUTE ('my_asg_uds', 'output_type', 'CLOB');
END;
/

PROMPT Create the section group
-- Create the section group
BEGIN
CTX_DDL.CREATE_SECTION_GROUP(group_name=>'my_asg', group_type=>'AUTO_SECTION_GROUP');
END;
/


set timing on
-- Create the Oracle Text Index

CREATE INDEX my_asg_text_nuidx ON my_search_tab (ot_col)
INDEXTYPE IS CTXSYS.CONTEXT
LOCAL
(PARTITION P1 parameters ('storage part_tbs1'),
PARTITION P2 parameters ('storage part_tbs2'),
PARTITION P3 parameters ('storage part_tbs3'),
PARTITION P4 parameters ('storage part_tbs4')
)
PARAMETERS('STORAGE my_default_st DATASTORE my_asg_uds SECTION GROUP my_asg')
PARALLEL;
/

Please let me know what I am doing wrong here ?

Thanks,
THRJG

Please disregard the previous issue, I managed to figure it out. Its to do with windows XP , sqlplus & bad network connection.

Sorry for the inconvenience.

following statements works fine and it creates the Oracle text index


CREATE INDEX my_asg_text_nuidx ON my_search_tab (ot_col)
INDEXTYPE IS CTXSYS.CONTEXT
LOCAL
(PARTITION P1 parameters ('storage part_tbs1'),
PARTITION P2 parameters ('storage part_tbs2'),
PARTITION P3 parameters ('storage part_tbs3'),
PARTITION P4 parameters ('storage part_tbs4')
)
PARAMETERS('STORAGE my_default_st DATASTORE my_asg_uds SECTION GROUP my_asg')
PARALLEL;

but it return this error,

Creating Oracle Text index

Index created.

Elapsed: 00:00:23.15
CREATE INDEX my_asg_text_nuidx ON my_search_tab (ot_col)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object


Elapsed: 00:00:00.07

its returned from the above statement but i could not figure out why, because there is no other object by that name and i do have a drop statement before the create statement. Please let me know if you know any information on this issue. I am using oracle 10.2.0.2.

Thanks,
THRJG

A datapump export of domain indexes causes error. This has started after applying CPUJan2007 to 10.2.0.2 APEX databases. Support says they don't know what causes it but it has been fixed in 10.2.0.3. Is there a way of knowing if it has really been fixed since I do not have 10.2.0.3 yet and not planning to have it at least for 3 more months?

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39127: unexpected error from call to local_str := SYS.DBMS_EXPORT_EXTENSION.GET_DOMAIN_INDEX_METADATA('WWV_FLOW_OH_IDX','FLOWS_020000','TEXTINDEXMETHODS','CTXSYS',10.02.00.01.00,newblock,0)
ORA-20000: Invalid indextype version specified
ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 256
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 4770
ORA-39127: unexpected error from call to local_str := SYS.DBMS_EXPORT_EXTENSION.GET_DOMAIN_INDEX_METADATA('WWV_FLOW_OH_PT_IDX','FLOWS_020000','TEXTINDEXMETHODS','CTXSYS',10.02.00.01.00,newblock,0)
ORA-20000: Invalid indextype version specified
ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 256
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 4770
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/DOMAIN_INDEX/INDEX

Thanks

Hi Tom,

I need one help from your side,I want to select the domain index section group source code.How can I get that

Hi Tom,

As always posting this question to get your most valuable opinion.

We have a table which has a column which stores comma separated string. the comma separated values are nothing but keys of a table. Pasting below few sample rows of both the tables.

Table A (contains 3-4 million rows)
-------

ID CSV_COL
--- --------
1 a,b,c,d,e,f,g,h,i,j,k

Table B (contains only 1000 rows)
-------

KEY_COL
-------
a
b
c
d
e
f
g
h
i
j
k
l
m

The requirement is to find all the rows in table A for a particular key value of table B.

table A sees heavy insert and delete operations.

In this case normal b-tree index on A(CSV_COL) is not giving any performance benefit as we need to look for a particular substring of that column.

Here are few approaches we are evaluating.

1> Create a mapping table (C) by storing A(ID) and B(KEY_COL) and create an index (b-tree) on both the columns.

2> Use Oracle Text to search and get the rows.

3> Use Lucene search to achieve the same.

The developers are more inclined towards Lucene.

Just need your expert opinion on this. What would be the best approach among these? Is there any other approach which can help better?

Please help on this.

Hi Tom,

Thanks a lot for your ever valuable suggestion. Just want a small clarification for my better understanding.

When you say ... " would be silly since it would only increase the total work done - "

do you mean that the work done would increase because of maintenance(insert/update/delete) of the mapping table?

********************************************************************************

select comment_key
from ir_comment_test
where comment_desc like '%AUDITRECON%'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.12       0.71       1614       5671          0          32
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.12       0.71       1614       5671          0          32

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55  

Rows     Row Source Operation
-------  ---------------------------------------------------
     32  TABLE ACCESS FULL IR_COMMENT_TEST (cr=5671 pr=1614 pw=0 time=635693 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file sequential read                       131        0.01          0.10
  db file scattered read                        571        0.01          0.43
  SQL*Net message from client                     2        0.00          0.00
********************************************************************************

The Physical IO(1614) and Logical IO(5671) in Plan matches with Tkprof results.

********************************************************************************

select comment_key
from ir_comment_test
where contains(comment_desc,'%AUDITRECON%') > 0

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.03          0        207          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         26          0          32
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.03          0        233          0          32

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55  

Rows     Row Source Operation
-------  ---------------------------------------------------
     32  TABLE ACCESS BY INDEX ROWID IR_COMMENT_TEST (cr=4824 pr=4582 pw=0 time=1817186 us)
     32   DOMAIN INDEX  SEARCH_IDX (cr=4803 pr=4582 pw=0 time=1817297 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.06          0.07
********************************************************************************

After Creating DOMAIN Index, PIO in plan (4582) and LIO in plan (4824) doesn't matches with Tkprof Results (Where PIO=0 LIO=233)

1) Why this is happening in Domain Index ?
2) With Domain Index in place, what is the LIO of this query is that 4824 or 233? Please Explain **Why** ?

ops$tkyte%ORA11GR2> create or replace function foo return number
  2  as
  3          l_n number;
  4  begin
  5          select count(*) into l_n from all_objects;
  6          return l_n;
  7  end;
  8  /

Function created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select foo from dual;

       FOO
----------
     55809

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec dbms_monitor.session_trace_enable;

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> set autotrace on statistics
ops$tkyte%ORA11GR2> select foo from dual;

       FOO
----------
     55809


Statistics
----------------------------------------------------------
        113  recursive calls
          0  db block gets
      60639  consistent gets
          0  physical reads
          0  redo size
        419  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
       1496  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte%ORA11GR2> set autotrace off

select foo
from
 dual
  
  
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          0          0           1

SELECT COUNT(*)
FROM
 ALL_OBJECTS


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      1.80       3.99          0      57994          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      1.80       3.99          0      57994          0           1

select comment_key
from ir_comment_test
where comment_desc like '%AUDITRECON%'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.12       0.71       1614       5671          0          32
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.12       0.71       1614       5671          0          32

Now PIO=1614, LIO=5671 Matches with Real Explain plan below.

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55  

Rows     Row Source Operation
-------  ---------------------------------------------------
     32  TABLE ACCESS FULL IR_COMMENT_TEST (cr=5671 pr=1614 pw=0 time=635693 us)

select comment_key
from ir_comment_test
where contains(comment_desc,'%AUDITRECON%') > 0

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.03          0        207          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         26          0          32
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.03          0        233          0          32

PIO=0, LIO=233, doesn't matches with Real plan below

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55  

Rows     Row Source Operation
-------  ---------------------------------------------------
     32  TABLE ACCESS BY INDEX ROWID IR_COMMENT_TEST (cr=4824 pr=4582 pw=0 time=1817186 us)
     32   DOMAIN INDEX  SEARCH_IDX (cr=4803 pr=4582 pw=0 time=1817297 us)

Tom:

Is this Mismatch is due to recursive sql that my domain index is doing?

Thanks for Teaching me Tom !

Tom:

Sorry to bother you again. for this query I can say number of rows returned is 32 and elapsed Time is 0.03 sec.
How about PIO & LIO is that 0 & 233 or 4582 & 4824 (Basically how i need to focus on Tkprof results or Relality plan portion)

select comment_key
from ir_comment_test
where contains(comment_desc,'%AUDITRECON%') > 0

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.03          0        207          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         26          0          32
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.03          0        233          0          32


Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55  

Rows     Row Source Operation
-------  ---------------------------------------------------
     32  TABLE ACCESS BY INDEX ROWID IR_COMMENT_TEST (cr=4824 pr=4582 pw=0 time=1817186 us)
     32   DOMAIN INDEX  SEARCH_IDX (cr=4803 pr=4582 pw=0 time=1817297 us)