| Index is not used after running dbms_stats.gather_table_stats [message #442297] |
Sat, 06 February 2010 11:19  |
katripon
Messages: 6
Registered: February 2010
Location: Bangladesh
|
Junior Member |
|
|
I have run dbms_stats.gather_table_stats for a tble.
After that Index is not used.
But, the is used if I drop the index and then create it after running gather_table_stat. Even rebuilding the index after gather_table_stats does not use index.
I have 20 million rows in the table and the indexed column has 4 types of distinct values. The indexed column used in the where clause has about 7000 rows.
What is the cause of not using Index after running gather_table_stat and of not using by rebuilding index but of using Index by droping and creating it. How can I run gather table stat so that it can use index?
Please help me.
BR// Ripon
|
|
|
|
|
|
|
|
|
|
| Re: Index is not used after running dbms_stats.gather_table_stats [message #442404 is a reply to message #442400] |
Sun, 07 February 2010 19:55  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Is your WHERE clause using BIND variables?
e.g. WHERE col = :val
Or is it using constant values?
e.g. WHERE col = 'val'
If there are 4 distinct values out of 20M rows, then at least ONE of those values is not selective (i.e. millions of matching rows). However you claim that at least one of the values IS selective (7000 matching rows).
If you use bind variables, Oracle cannot know whether you are selecting one of the selective values or one of the non-selective values, so it just picks a plan that it thinks will be best for all values. Oracle can also peek at the bind variable value before it creates a plan - but lets not go there just yet.
If you use a constant value, Oracle can optimise accordingly. Selective values can use an index, non-selective values can perform a full scan.
Ross Leishman
|
|
|
|
|
|
|
|
| Re: Index is not used after running dbms_stats.gather_table_stats [message #442616 is a reply to message #442496] |
Tue, 09 February 2010 07:51 |
raselvista
Messages: 1
Registered: February 2010
Location: Dhaka
|
Junior Member |
|
|
entekeralam wrote on Mon, 08 February 2010 09:35Try gather stats with different method_opt value.
If default is 'FOR ALL COLUMNS SIZE AUTO' then gather stats with 'FOR ALL COLUMNS SIZE 1'. ( delete stats first)
To check the default,
select dbms_stats.get_param('METHOD_OPT') from dual;
hi,
can you give me steps to change METHOD_OPT?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
