Context Index not used
452659Jul 19 2006 — edited Jul 21 2006Hi 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