-
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?
Jeff Hunter
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|