Home » RDBMS Server » Performance Tuning » Index is not used after running dbms_stats.gather_table_stats (Oracle Database 10g in Sun Solaris 64bit)
Index is not used after running dbms_stats.gather_table_stats [message #442297] Sat, 06 February 2010 11:19 Go to next message
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 #442298 is a reply to message #442297] Sat, 06 February 2010 11:25 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/84315/136107/

Please actually READ the Performance STICKY thread that you should have read BEFORE posting!
Re: Index is not used after running dbms_stats.gather_table_stats [message #442312 is a reply to message #442298] Sat, 06 February 2010 20:09 Go to previous messageGo to next message
entekeralam
Messages: 19
Registered: January 2010
Junior Member
Is it B-TREE or BITMAP index? As it has only 4 distinct values bitmap index will be better.

What is the elapsed time difference between using index and not using index?
Re: Index is not used after running dbms_stats.gather_table_stats [message #442400 is a reply to message #442312] Sun, 07 February 2010 14:01 Go to previous messageGo to next message
katripon
Messages: 6
Registered: February 2010
Location: Bangladesh
Junior Member
Hello entekeralam,
Thanks for your reply.
It is B-tree Index. I also created bitmap index, but index is not used in that case also.
Using Index elapsed time reduces 5/6 times than full table scan.

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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 #442439 is a reply to message #442297] Mon, 08 February 2010 01:08 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Try using values and gathering histograms for the involved columns.

Re: Index is not used after running dbms_stats.gather_table_stats [message #442496 is a reply to message #442439] Mon, 08 February 2010 09:35 Go to previous messageGo to next message
entekeralam
Messages: 19
Registered: January 2010
Junior Member
Try 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;
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 Go to previous message
raselvista
Messages: 1
Registered: February 2010
Location: Dhaka
Junior Member

entekeralam wrote on Mon, 08 February 2010 09:35
Try 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?
Previous Topic: Long run time of query with Order by clause
Next Topic: Performance Pruning on fact table
Goto Forum:
  


Current Time: Sat Dec 03 16:06:30 CST 2016

Total time taken to generate the page: 0.11606 seconds