Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Context Index not used

452659Jul 19 2006 — edited Jul 21 2006
Hi all,

I have a table with 4 Columns indexed:

begin
ctx_ddl.create_preference('TICKET_LEXER', 'BASIC_LEXER');
ctx_ddl.set_attribute('TICKET_LEXER', 'printjoins', '_-&/()=\[]{}?!§:;.,#+<>@');
ctx_ddl.set_attribute('TICKET_LEXER', 'INDEX_THEMES', 'NO');
end;
/
CREATE INDEX GTIME.I_TH_ARTICLE_TEXT ON GTIME.GNCPM_TICKET_HISTORY
(ARTICLE_TEXT)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('LEXER TICKET_LEXER');

CREATE INDEX GTIME.I_TH_ARTICLE_SUBJECT ON GTIME.GNCPM_TICKET_HISTORY
(ARTICLE_SUBJECT)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('LEXER TICKET_LEXER');

CREATE INDEX GTIME.I_TH_ARTICLE_TO ON GTIME.GNCPM_TICKET_HISTORY
(ARTICLE_TO)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('LEXER TICKET_LEXER');

CREATE INDEX GTIME.I_TH_ARTICLE_FROM ON GTIME.GNCPM_TICKET_HISTORY
(ARTICLE_FROM)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('LEXER TICKET_LEXER');

when i use this query:

SELECT
ov.ticket_id ID, ov.ticket_nr tnr, ov.tickethist_from hfrom,
ov.tickethist_subject subject, ov.tickethist_reply_to replyto,
TO_CHAR (ov.ticket_date_created, 'DD/MM/YYYY HH24:MI:SS') created,
ov.ticket_status status, ov.ticket_priority prio, ov.queue_id qid,
ov.queue_name qname, ov.pen_id penid, ov.user_name usr,
ov.project_id pid, ov.project_name pname, ov.emp_id empid,
ov.person_first_name fname, ov.person_last_name lname,
ov.tickethist_to histto, ov.ticket_status_id SID,
TO_CHAR (ov.ticket_deadline, 'DD/MM/YYYY HH24:MI') dl,
TO_CHAR (ov.ticket_sleep_until, 'DD/MM/YYYY HH24:MI') su,
(CASE
WHEN ov.ticket_deadline < SYSDATE
THEN 1
ELSE 0
END) dlflag,
(CASE
WHEN ov.ticket_sleep_until < SYSDATE
THEN 1
ELSE 0
END) suflag, ov.tickethist_id hid, ov.owner_name uname,
ov.ticket_date_created tcreated, NULL tickethist_text,
ov.last_history_date lfdate,
TO_CHAR (ov.last_history_date, 'DD/MM/YYYY HH24:MI') lfdatestr,
ov.ticket_deadline, ov.ticket_sleep_until
FROM gncpm_v_ticket_search_ov ov
WHERE ov.user_name = :usr
AND ov.queue_id IN (1097783, 461993, -1)
AND ov.ticket_status_id IN (462020, 462021, -1 )
AND ( contains (ov.tickethist_subject, :str, 0) > 0
OR contains (ov.tickethist_from, :str, 1) > 0
OR contains (ov.tickethist_to, :str, 2) > 0
OR contains (ov.tickethist_text, :str, 3) > 0
)
ORDER BY lfdate DESC

i can't see any text index being used in the explain plan:

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=FIRST_ROWS 1 885.543091638734
SORT ORDER BY 1 447 885.543091638734
NESTED LOOPS OUTER 1 447 884.543091638734
NESTED LOOPS 1 371 10.4040322088623
NESTED LOOPS 1 180 7.00494946536037
NESTED LOOPS 1 165 6.00450523884594
NESTED LOOPS OUTER 1 100 5.00227214192501
NESTED LOOPS 1 80 4.00183604488217
NESTED LOOPS 1 58 3.0013877702379
NESTED LOOPS 1 47 2.00113361124244
TABLE ACCESS BY INDEX ROWID GTIME.WSGSEC_USER 1 31 1.00066055904566
INDEX RANGE SCAN GTIME.WSGUSR_UNAME_PK_I 1 1.00218197534137
TABLE ACCESS BY INDEX ROWID GTIME.GNCDB_EMPLOYEE 1 16 1.00047305219679
INDEX RANGE SCAN GTIME.EMPLOYEE_UK 1 1.0012474510241
TABLE ACCESS BY INDEX ROWID GTIME.GNCPM_QUEUE_USER 1 11 1.00025415899546
INDEX RANGE SCAN GTIME.QUEUE_USER_EMP_FKI 9 .000156463353413655
TABLE ACCESS BY INDEX ROWID GTIME.GNCPM_QUEUE 1 22 1.00044827464427
INDEX UNIQUE SCAN GTIME.QUEUE_PK 1 .00111249091120894
TABLE ACCESS BY INDEX ROWID GTIME.GNCPM_PROJECT 1 20 1.00043609704284
INDEX UNIQUE SCAN GTIME.PROJECT_PK 1 .00109098767068273
INLIST ITERATOR
TABLE ACCESS BY INDEX ROWID GTIME.GNCPM_TICKET 2 130 1.00223309692093
INDEX RANGE SCAN GTIME.TICKET_TICKET_STATUS_FKI 61 1.00300580109103
TABLE ACCESS BY INDEX ROWID GTIME.GNCPM_TICKET_STATUS 1 15 1.00044422651443
INDEX UNIQUE SCAN GTIME.TICKET_STATUS_PK 1 .00110982122248252
TABLE ACCESS BY INDEX ROWID GTIME.GNCPM_TICKET_HISTORY 1 191 3.39908274350194
INDEX RANGE SCAN GTIME.TICKET_HISTORY_TICKET_FKI 2 1.00215217279786
VIEW 1 76 874.139059429872
TABLE ACCESS BY INDEX ROWID GTIME.GNCDB_EMPLOYEE 1 11 1.00047305219679
NESTED LOOPS 1 K 95 K 874.139059429872
NESTED LOOPS 1 K 76 K 294.65455290112
VIEW GTIME.index$_join$_014 1 K 43 K 4.58686484174699
HASH JOIN
INDEX FAST FULL SCAN GTIME.WSGUSR_PEN_FK_I 1 K 43 K 1.51237284780455
INDEX FAST FULL SCAN GTIME.WSGUSR_UNAME_PK_I 1 K 43 K 2.51462613467202
TABLE ACCESS BY INDEX ROWID GTIME.GNCDB_PERSON 1 23 1.00046142920482
INDEX UNIQUE SCAN GTIME.PERSON_PK 1 .00121764848058902
INDEX RANGE SCAN GTIME.EMPLOYEE_UK 1 1.0012474510241

Database Version: 10.1.0.3.0

Schema is completely analysed,optimizer mode during query is FIRST_ROWS.
(even tried rule,all_rows,choose)
The query takes ~4s to complete.

On a 9.2.0.5 with exaclty the same data in the schema,the query takes only ~400ms.
And the explain plan shows that all oracle text indexes are used.

Any suggestions ?
If you need more information,please let me know.

Thanks,
gerald
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 18 2006
Added on Jul 19 2006
3 comments
2,601 views