OraFAQ Forum: Performance Tuning » Function Index, is there a limit to the complexity ?

Home » RDBMS Server » Performance Tuning » Function Index, is there a limit to the complexity ?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Function Index, is there a limit to the complexity ? [message #136677]

Fri, 09 September 2005 08:43

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

Report message to a moderator

Re: Function Index, is there a limit to the complexity ? [message #136701 is a reply to message #136677]

Fri, 09 September 2005 10:27

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.

Report message to a moderator

Previous Topic:	error while taking snapshot..!!
Next Topic:	Resolving "latch free" problems

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 20 02:27:02 CDT 2024