SQL & PL/SQL

Hello Everyone,

Since we deployed linguistic indexes in our application we are facing many performance problems.

RANT:
To me, the way Oracle implemented linguistic indexes is like a patch:
- I assume It is something added on the top of what was existing at the time (function-based indexes).
- It is not something that is deeply integrated into the Oracle core as it deserves to be, since globalization is a must for apllications these days.
END OF RANT

I will come back later with a more documented post on this "opinion" but for now, here is the main reason for this thread:

I have this QUERY :

SELECT *
FROM mytable
WHERE mycol1 = :1 AND (mycol2 = :2 OR mycol2 = :3 OR mycol2 IS NULL );

With the columns : mycol being a varchar2 column and mycol2 being an integer.

And the session is set to:

NLS_SORT = PUNCTUATION
NLS_COMP = ANSI
OPTIMIZER_MODE = FIRST_ROWS
QUERY_REWRITE_ENABLED = TRUE

When I have this index (function-based, linguistic index):

CREATE idx2 ON mytable
(NLSSORT(mycol, 'NLS_SORT = PUNCTUATION'), mycol2 );


I have this trace output (tkproofed):

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 1 0.14 12.24 1904 10431 0 13
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.15 12.25 1904 10431 0 13

When I add this index (binary index):

CREATE idxtest ON mytable
(mycol, mycol2);

And the session is altered to:

NLS_SORT = BINARY
NLS_COMP = ANSI
OPTIMIZER_MODE = FIRST_ROWS
QUERY_REWRITE_ENABLED = TRUE

I have this trace output (tkproofed):

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.01 4 19 0 13
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.02 4 19 0 13


It is clear to me that Oracle does not handle this linguistic index correctly.

I looked in user_indexes to see if something was wrong and found nothing.

In SQLPLUS when I use SET AUTOT TRACEONLY EXPLAIN everything seems fine with both indexes but when we run our application (using OCI9.2), running the traces I obtain the above output.

SQLPLUS and AUTOT TRACEONLY does not give the correct information: I cannot believe that when the execution plan involve the use of the linguistic indexe it can take more than 12 secondes to process...

What should I try now in order to pinpoint the problem?

Best regards.

Carl