CREATE INDEX.. COMPUTE STATT.. changes table stats

#1

Hello,

I just observed that while creating index with "COMPUTE STATISTICS"
also collect the table/column (for the column in index) level
statistics.

Is this a normal behaviour?

Problem is , it does not create the histograms for the column which
eventually make CBO to choose in-correct execution plan...

Maks.

#2

Depends on what you mean by "normal".

Yes, it happens in 8.1 and 9.2 - specifically,
it's only the histogram on the first column of
the index that is deleted, and the column stats
get updated.

This is (probably) a bug, and does not happen
in 10.1.0.3.

--
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated Jan 21st 2005

"maks" <maks70 (AT) comcast (DOT) net> wrote

Quote:

Hello,

I just observed that while creating index with "COMPUTE STATISTICS"
also collect the table/column (for the column in index) level
statistics.

Is this a normal behaviour?

Problem is , it does not create the histograms for the column which
eventually make CBO to choose in-correct execution plan...

Maks.

#3

Thanks JL.

What I mean't to ask was if this behaviour was a bug or a feature.

Quote:

From your answer it looks like its possibly a bug and I will have to
remove 'compute statistics' clause while recreating indexes and then

collect stats on indexes separately.

Is there any other work around apart from this?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

CREATE INDEX.. COMPUTE STATT.. changes table stats

comp.databases.oracle.misc comp.databases.oracle.misc

CREATE INDEX.. COMPUTE STATT.. changes table stats - 02-18-2005 , 04:56 PM

Re: CREATE INDEX.. COMPUTE STATT.. changes table stats - 02-19-2005 , 11:14 AM

Re: CREATE INDEX.. COMPUTE STATT.. changes table stats - 02-21-2005 , 04:30 PM