Home » RDBMS Server » Performance Tuning » Function Index, is there a limit to the complexity ?
Function Index, is there a limit to the complexity ? [message #136677] Fri, 09 September 2005 08:43 Go to next message
deadpoet
Messages: 17
Registered: August 2005
Junior Member
Dear friends,

here is my new joke:

select A_field1
from
Table_A A,
Table_B B
where DECODE(TO_CHAR(A.field2),'00000000000000','',DECODE(ASCII(SUBSTR(TO_CHAR(A.field2),1,1)),10,'', TO_CHAR(TO_NUMBER(DECODE(LENGTH(TRIM(TRANSLATE(TO_CHAR(A.field2),'0123456789',' '))),0,TRIM(TO_CHAR(A.field2)),NULL,TRIM(TO_CHAR(A.field2)),'')))))) = to_number(B.field1 (+))

here is the plan:

SELECT STATEMENT Optimizer Mode=CHOOSE 507 M 5795
HASH JOIN OUTER 507 M 8G 5795
TABLE ACCESS FULL TABLE_A 50 K 538 K 3611
TABLE ACCESS FULL Table_B 1 M 6 M 1873

So I've decided to create an index on the function:

CREATE INDEX Table_A_IDX1 ON Table_A
(DECODE(TO_CHAR(field2),'00000000000000','',DECODE(ASCII(SUBSTR(TO_CHAR(field2),1,1)),10,'', TO_CHAR(TO_NUMBER(DECODE(LENGTH(TRIM(TRANSLATE(TO_CHAR(field2),'0123456789',' '))),0,TRIM(TO_CHAR(field2)),NULL,TRIM(TO_CHAR(field2)),''))))))
NOLOGGING
TABLESPACE TBS_ID_128K
NOPARALLEL
Compute Statistics;

I add the hint /*+ index(A) */
but this incredible donkey just want to do an HASH beetween 2 FULL ACCESS for the table A & the table B

I've done the same with "to_number(field1)" on the TABLE_B
i've added the /*+ index (B) */ and it uses the index on "to_number" but still refuse to use the first one ...

So my question is :

Is there a limit for the complexity of an Function for the Oracale optimiser's understanding ?

thank you
Gerald
Re: Function Index, is there a limit to the complexity ? [message #136701 is a reply to message #136677] Fri, 09 September 2005 10:27 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
My understanding is that function based indexes

A) must be deterministic

B) must use the exact text that the query attempting to use them

You haven't posted your session demonstrating this, but your create index statement and your select statement appear to differ in the text used in the function itself.t

Also, if I were testing this, I would first want to remove the complication of the outer join. I would then want to use the index as merely a criteria in the where clause, not as a means of joining two tables together.

I don't know of any limit to the complexity in general, but please continue your test and post your findings for us to find out.
Previous Topic: error while taking snapshot..!!
Next Topic: Resolving "latch free" problems
Goto Forum:
  


Current Time: Tue Dec 06 20:50:25 CST 2022