Hi,
We have a a materialized view containing about 7 million records at
the moment with song artist and album information. We hava a column in
the materialized wiev which contains the song name concateneted with
the artistname concateneted with the album name. We have a textindex
on this column. Searching on 'Elvis' or 'Elvis Presley' and similiar
is very fast. However doing a search on 'in the ghettto' or 'on in
the'. will produce a slow result (more than 10 seconds). I understand
that there are a lot of hits with words like 'in' and 'the' but we
need to be able to search on these words as well. I have provided the
autotrace information as well as the tkprof. Does anybody have any
suggestions to speed this up.

Thanks,

Sql:
SELECT sub2.*
FROM (
SELECT ROWNUM rad, sub1.*
FROM (
SELECT songId, title, songArtist, albumId,
popularity, genreId, genre, explicit, offerId,
priceCategoryId,
offerStartDate, offerEndDate, mountPointId, drmProtected,
releaseDate
FROM MV_Song_batman
WHERE CATSEARCH(searchText, :vSearchString, :vCriterion) > 0)
sub1
WHERE ROWNUM <= :vEnd) sub2
WHERE sub2.rad >= :vStart;

Autotrace:
SQL> @catsearch

PL/SQL procedure successfully completed.

30 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2829723679

--------------------------------------------------------------------------------
-----------------------


--------------------------------------------------------------------------------
-----------------------



25900 (1)| 00:05:11 |

--------------------------------------------------------------------------------
-----------------------


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

1 - filter("SUB2"."RAD">=TO_NUMBER(:VSTART))
2 - filter(ROWNUM<=TO_NUMBER(:VEND))
4 -
access("CTXSYS"."CATSEARCH"("SEARCHTEXT",:VSEARCHS TRING,:VCRITERION)>0)


Statistics
----------------------------------------------------------
872 recursive calls
0 db block gets
86385 consistent gets
12 physical reads
0 redo size
3839 bytes sent via SQL*Net to client
392 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
158 sorts (memory)
0 sorts (disk)
30 rows processed


TKProf:

TKPROF: Release 10.2.0.1.0 - Production on Tue Oct 28 08:30:25 2008

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

Trace file: orcl_ora_4668.trc
Sort options: default

************************************************** ******************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for
update)
rows = number of rows processed by the fetch or execute call
************************************************** ******************************

alter session set events '10046 trace name context forever, level 12'


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 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 1 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 244

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

begin
:vend := 30;
:vStart := 1;
:vSearchstring := 'in the ghetto';
:vCriterion := 'countrycode = ''SE'' order by sortorder';
end;

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

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 244

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

SELECT sub2.*
FROM (
SELECT ROWNUM rad, sub1.*
FROM (
SELECT songId, title, songArtist, albumId,
popularity, genreId, genre, explicit, offerId,
priceCategoryId,
offerStartDate, offerEndDate, mountPointId, drmProtected,
releaseDate
FROM MV_Song_batman
WHERE CATSEARCH(searchText, :vSearchString, :vCriterion) > 0)
sub1
WHERE ROWNUM <= :vEnd) sub2
WHERE sub2.rad >= :vStart

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.25 0 18 0 0
Fetch 3 0.09 0.16 0 18 0 30
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 5 0.10 0.41 0 36 0 30

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 244

Rows Row Source Operation
------- ---------------------------------------------------
30 VIEW (cr=86072 pr=0 pw=0 time=14336154 us)
30 COUNT STOPKEY (cr=86072 pr=0 pw=0 time=14336124 us)
30 MAT_VIEW ACCESS BY INDEX ROWID MV_SONG_BATMAN (cr=86072 pr=0
pw=0 time=14336103 us)
30 DOMAIN INDEX MV_SONG_BATMAN_IND (cr=86054 pr=0 pw=0
time=14336280 us)


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

select metadata
from
kopm$ where name='DB_FDO'


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 1 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 3 0.00 0.00 0 2 0 1

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID KOPMicrosoft (cr=2 pr=0 pw=0 time=47 us)
1 INDEX UNIQUE SCAN I_KOPM1 (cr=1 pr=0 pw=0 time=20 us)(object id
365)

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

select /*+ NO_EXPAND INDEX_ASC(i "DR$MV_SONG_BATMAN_IND03") */ dr
$rowid ,
SORTORDER
from
"MEDIA_CORE"."DR$MV_SONG_BATMAN_IND$I" i where dr$token = :token and
dr$token_type = :ttype and COUNTRYCODE = 'SE' order by dr$token ASC,
dr$token_type ASC, SORTORDER ASC, dr$rowid ASC


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.04 0 0 0 0
Fetch 4 0.35 0.25 0 2485 0 396
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 6 0.35 0.30 0 2485 0 396

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

Rows Row Source Operation
------- ---------------------------------------------------
396 SORT ORDER BY (cr=2485 pr=0 pw=0 time=257922 us)
65277 TABLE ACCESS BY INDEX ROWID DR$MV_SONG_BATMAN_IND$I (cr=2485
pr=0 pw=0 time=486792 us)
261491 INDEX RANGE SCAN DR$MV_SONG_BATMAN_IND03 (cr=978 pr=0 pw=0
time=1054100 us)(object id 63590)

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

select /*+ NO_EXPAND INDEX_ASC(i "DR$MV_SONG_BATMAN_IND03") */ dr
$rowid ,
SORTORDER
from
"MEDIA_CORE"."DR$MV_SONG_BATMAN_IND$I" i where dr$token = :token and
dr$token_type = :ttype and COUNTRYCODE = 'SE' and SORTORDER >= :r01
and
(SORTORDER > :r01 or DR$ROWID >= :r02) order by dr$token ASC,
dr$token_type ASC, SORTORDER ASC, dr$rowid ASC


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 157 0.00 0.00 0 0 0 0
Execute 157 0.01 0.21 0 0 0 0
Fetch 439 16.48 13.60 0 83549 0 43460
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 753 16.50 13.82 0 83549 0 43460

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

Rows Row Source Operation
------- ---------------------------------------------------
198 SORT ORDER BY (cr=2058 pr=0 pw=0 time=331363 us)
210971 TABLE ACCESS BY INDEX ROWID DR$MV_SONG_BATMAN_IND$I (cr=2058
pr=0 pw=0 time=3375647 us)
210971 INDEX RANGE SCAN DR$MV_SONG_BATMAN_IND03 (cr=799 pr=0 pw=0
time=850588 us)(object id 63590)




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

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 2 0.00 0.00 0 0 0 0
Execute 3 0.01 0.65 0 18 0 1
Fetch 3 0.09 0.16 0 18 0 30
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 8 0.10 0.81 0 36 0 31

Misses in library cache during parse: 0

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


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 159 0.00 0.00 0 0 0 0
Execute 159 0.01 0.26 0 0 0 0
Fetch 444 16.84 13.86 0 86036 0 43857
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 762 16.85 14.13 0 86036 0 43857

Misses in library cache during parse: 0

161 user SQL statements in session.
1 internal SQL statements in session.
162 SQL statements in session.
************************************************** ******************************
Trace file: orcl_ora_4668.trc
Trace file compatibility: 10.01.00
Sort options: default

1 session in tracefile.
161 user SQL statements in trace file.
1 internal SQL statements in trace file.
162 SQL statements in trace file.
6 unique SQL statements in trace file.
5777 lines in trace file.
23 elapsed seconds in trace file.