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
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?
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.
The index wont be used if you are concatenating or using any function in where clause.
Use FBI if you on 8i
Click Here to Expand Forum to Full Width