Function Based Index

[christof13]

Hi,

I have a problem with a query that uses a function based index.

SELECT *
FROM TABLEA,TABLEB
WHERE TABLEA.TABLEA_ID = TABLEB.TABLEA_ID AND UPPER(TABLEB.NAME) = 'MYNAME'

I have 2 index :
- the primary key : TABLEA.TABLEA_ID
- a function based index on UPPER(TABLEB.NAME)

The FBI is well used but Oracle doesn't use the index on TABLEA.TABLEA_ID. So I have a full table scan on TABLEA . I want that Oracle (9i) use both indexes. How can I do that ?

Thanks in advance

Christophe

[OraBis]

Explain plan output for the statement might be able to show some light.

[christof13]

select * from BOOD.OPERATOR OP, BOOD.BENAMING BE where OP.OPERATOR_ID = BE.OPERATOR_ID and UPPER(BE.BENAMING) LIKE 'MYNAME%'

SELECT STATEMENT, GOAL = CHOOSE Cost=111818 Cardinality=73732 Bytes=10469944
MERGE JOIN Cost=111818 Cardinality=73732 Bytes=10469944
TABLE ACCESS BY INDEX ROWID Object owner=BOOD Object name=OPERATOR Cost=102089 Cardinality=2079429 Bytes=168433749
INDEX FULL SCAN Object owner=BOOD Object name=PK_OPERATOR Cost=12961 Cardinality=2079429
SORT JOIN Cost=9729 Cardinality=72963 Bytes=4450743
TABLE ACCESS BY INDEX ROWID Object owner=BOOD Object name=BENAMING Cost=4370 Cardinality=72963 Bytes=4450743
INDEX RANGE SCAN Object owner=BOOD Object name=I1_BENAMING_FBI Cost=105 Cardinality=1

Index Full Scan on PK_OPERATOR... Why ?

[gamyers]

Formatting is hard to read, but it looks like it thinks there will be around 73,000 rows matching the UPPER(BE.BENAMING) LIKE 'MYNAME%' clause and about the same number coming out the end of the selection.

There seem to be about 2 million rows on BOOD.OPERATOR. That's about 3.5% of the main table.

It looks like it has decided the quickest route is to get 73,000 rows from BENAMING and sort them by operator id, sort the OPERATOR table and merge/join the results. To sort the OPERATOR table, it has deicded it is quicker to FULL SCAN the index (which is in PK/operator_id order) and access the table rows individually rather than reading the entire table and sorting that.

If the 73,000 is accurate, this is probably a reasonable plan for targetting generating the entire result set.
If you switch to FIRST_ROWS optimizer goal, then it will probably use a nested loop rather than all this sort/merge/join.

[christof13]

It works with FIRST_ROWS !!

Thanks a lot