| analyze tables [message #59556] |
Wed, 03 December 2003 16:31  |
Kapil
Messages: 145
Registered: May 2002
|
Senior Member |
|
|
had you guys had this problem like, when ever you analyze table, querys stops using indexes, what i am doing is. i created index on a table. and run my query. it uses the index and its fast. but later when i analyze my tables. using
analyze table tabname compute statistics; or
analyze table tabname compute statistics
for table
for all indexes
for indexed columns; or
analyze table tabname compute statistics
for table
for all indexes;
i used all possible combinations, but its not using the indexes. but when i re-create the index and does not analyze the table. it uses the index.
what is the reason ? please any help is appreciated.
|
|
|
|
| Re: analyze tables [message #59561 is a reply to message #59556] |
Wed, 03 December 2003 21:14  |
Daljit Singh
Messages: 290
Registered: October 2003
Location: Texas
|
Senior Member |
|
|
Hi,
Well u r using cost based optimizer in ur query which automatically select the best path to retrieve the data from DB on the basis of available statistics. There can be more than 1 execution plan for a single query but CBO select the best plan means which take less cost to fetch the data.
And in ur case when u collect the statistics for ur table/index CBO think that its better to fetch the records from ur table directly without using indexes this is bcz may be ur table size is small which can be fetch in less I/O than fetching it using indexes.
If u want to use indexes in ur query just simply place "/*+ rule */" in ur select query.
I hope it will clarify u.
|
|
|
|
| Re: analyze tables [message #59572 is a reply to message #59561] |
Thu, 04 December 2003 05:41 |
Kapil
Messages: 145
Registered: May 2002
|
Senior Member |
|
|
|
No tables are not small. one of the tables in query has 10 millions recds, and other tables are also not thats small. then why the optimizer is using indexes when i just create them fresh and does not compute statistics. thanks again
|
|
|
|
| What works for me ... [message #59576 is a reply to message #59572] |
Thu, 04 December 2003 10:37 |
Thomas G.
Messages: 10
Registered: November 2003
|
Junior Member |
|
|
is :
analyze table [[table]] estimate statistics for all indexed columns for all indexes for table
just an "analyze table" had the same effect you described.
|
|
|
|
|
|
| Re: analyze tables [message #60287 is a reply to message #59561] |
Fri, 30 January 2004 09:28 |
Amit Jain
Messages: 33
Registered: August 2001
|
Member |
|
|
Hi Kapil,
We faced this issue earlier. In our case the table had some 90 million records. We did not try out forcing the index but we deleted the statistics for that particular index and then the query took that index.
Hope this helps,
Amit
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
