Urgent Help On Index please....

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

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

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

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

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

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

Sanjay