| Statistics makes it slower [message #39697] |
Fri, 02 August 2002 08:35  |
Nicola Farina
Messages: 63
Registered: October 2001
|
Member |
|
|
Hi you, Oracle gurus!
I just returned from an O.T.T.("Oracle Trace Trip")
I discovered that, computing statistics for a table, forces the optimizer to use the full access scan plan!
The scenario is very simple. A table on which I issue the statement:
SELECT *
FROM ANAGRA
ORDER BY MATRICOLA ASC
WITHOUT statistics Oracle (8.1.7) selects the "Index range scan"
After computing statistics I got the full access scan.
I tried to export the table and import it in another schema. In this schema I got the quick response (index range scan)
I tried to compute statistics and even here I got the slower full access scan.
I have no doubt that the CBO have some good reasons not to choose the index range scan. Are there any pitfalls in deleting statistics to force index range scan?
|
|
|
|
| Re: Statistics makes it slower [message #39703 is a reply to message #39697] |
Fri, 02 August 2002 11:01  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
It could be that the overal cost of doing a FTS is less that using the index scan even though response is slower. Could it be that you are encountering a disk sort? It is recommended to either have stats on all tables or no tables. You could use a hit to force the use of the index. Make sure that you have comprehensive stats including histograms.
-- most comprehensive
analyze table T compute stats;
-- slightly less comprehensive stats (no histograms on un-indexed columns)
analyze table T compute statistics
for table
for all indexes
for all indexed columns;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
