-
Use of Index on Partitioned Table
DB version 9.2.0.4.0 on Sun Solaris
I have a table BALANCE which partitioned on a date column and has other local indexes which start with the date column e.g.
Index1:
Date_col
Reason_code
...
I do a simple query such as:
select * from balance where date_col = '31-mar-06'
and the query plan shows it is using Index1. This is extremely inefficient: since the table is partitioned on date_col, it is obviously much more effective just to do a full table scan of the partition than use an index. If I add a FULL hint to query, performance improves dramatically.
To get stats on the tables, I used the dbms_stats package:
Code:
begin
dbms_stats.gather_table_stats(
OWNNAME => 'XXX',
TABNAME =>'BALANCE',
PARTNAME =>'XXX',
METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 1',
ESTIMATE_PERCENT => 20,
GRANULARITY => 'ALL',
CASCADE => TRUE );
end;
It looks as if DBMS_STATS is not really understanding that the table is partitioned and is not coming up with plans which utilize the partitioning properly. Is there any way to get better stats and plans ?
-
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
I thought I already was. Isn't that what the "FOR ALL INDEXES COLUMNS" does ?
-
I just noticed it using SIZE 1 on the FOR ALL COLUMNS. It was a script which I someone else wrote. I'll try it with a larger size and see if that helps.
-
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
How many values of date_col do you have per partition? Not as many as 254, I expect.
How about just dropping the index?
-
If it was up to me, most of the indexes on this table would either go or be radically altered. Unfortunately, it isn't and there is no way I am going to be able to pursuade the powers that be to take the risk of dropping any indexes.
There weren't many values of the date_col on this table, so I retried with a size of 5. But then I discovered something really weird. If I do my query as:
Code:
SELECT * FROM BALANCE
WHERE REASON_CODE='O'
AND DATE_COL='31-mar-06'
it does indeed change the plan to full table scan. But if I have it as:
Code:
SELECT * FROM BALANCE
WHERE REASON_CODE='O'
AND DATE_COL=TO_DATE('2006/03/31', 'YYYY/MM/DD')
it goes back to index range scan ! Unfortunately, the format I have to use is the second one because the query is being issued by a third party product which isn't able to pass a date in Oracle format.
I can only think that having to to_date confuses the optimizer and it can't associate it correctly with a partition.
-
is that a range part or list part?
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
-
Scorby,
Interesting.. I'm guessing you are using or at least the index in this case is a local partitioned index.
How are you getting the "range scan".. Yes a range scan happens, and is reported through sqlplus using "Set autotrace on". However running an "explain plan for" SQL and then querrying the plan table will get you all the details.
In my tests (similar to yours) I found that the explain plan indicated partition elimination was occuring and the index was selected to access the table.
Now if the index has low cardinality, then it may be best to drop it.
To alter the execution plan you could utilize Stored Outlines.
check the docs for "database performance tuning"
Ken
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
|