Database Software

Hi
I have a table which has close to 140 million records. I had been exploring the option of using oracle text for search. So , I created an index(ctxcat) on the column Name by the following query.

begin
ctx_ddl.create_preference('FT_WL', 'BASIC_WORDLIST');
ctx_ddl.set_attribute ('FT_WL', 'prefix-index','TRUE');
end;
/


create index history_namex on history(name) indextype is ctxsys.ctxcat parameters ('WORDLIST FT_WL');

But when I executed the following query , I have found out that catsearch is taking more time. The queries and thier stats are :-

1. select * from history where catsearch(name, 'Jake%', null) > 0 and rownum < 200;

Elapsed : 00 : 00 : 00.13

Statistics :
112 recursive calls
0 db block gets
413 consistent gets
28 physical reads
0 redo size
33168 bytes sent via SQL*Net to client
663 bytes receuved via SQL*Net from client
15 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
199 rows processed

2. select * from history where name like 'Jake%' and rownum < 200;

Elapsed : 00 : 00 : 00.05

Statistics :
1 recursive calls
0 db block gets
220 consistent gets
383 physical reads
0 redo size
26148 bytes sent via SQL*Net to client
663 bytes receuved via SQL*Net from client
15 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
199 rows processed




Can anyone explain why this is happening?


PS : there is no conventional index on the name column.

Edited by: 976934 on Dec 14, 2012 3:32 AM