Urgent Help On Index please....

[sanju_dba]

hi,

i have created an index on a table as
create index on table ( C1||C2||C3)

and i fired a query
select /*+indexhint too */.....where (C1||C2||C3)=(X||y||Z)

but the explan plan shows full table scan...

am i missing something???

thanx in adv
sanjay

[pando]

because your index is b-tree and you are putting a function in your predicate, you have to use functin based indexes

[marist89]

Are your tables analyzed?
Are you retrieving < 10% of the data?
How selective is your key?

[sanju_dba]

Created an index on a table as
create index IDX on table ( C1||C2||C3||C4)
when i query as follows , in all cases Oracle Performs
FULL TABLE SCAN.
1 select C10 from table where (C1||C2||C3||C4)='xxxxx' OR
2 select /*+ index hint */ C10 from table where (C1||C2||C3||C4)='xxxxx' OR
3 select C10 from table where C1||C2||C3||C4='xxxxx'
4 select C10 from table where ='xxxxx'
PLNOTE: STATS COLLECTED FOR THE INDEX USING DBMS_STATS pkg. And
GRANTED QUERY REWRITE TO THE USER,
ALTER SESSION QUERY_REWRITE_ENABLED =TRUE ,
is already done before the above query is fired.
Could you please help me why its going for FULL TABLE SCAN.

** If am using query as stated in step 3 , does Function based index concept comes here?


Thanx
Sanjay

[sanju_dba]

Yes , the index is analyzed and table too.
and with the where clause i am trying to retrieve only one record.
Just to update you the Table is of 350M rows.

Thanx
Sanjay

[SANJAY_G]

The index wont be used if you are concatenating or using any function in where clause.
Use FBI if you on 8i

Sanjay