Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Analyze Question -- How CBO uses column statistics for non-in

RE: Analyze Question -- How CBO uses column statistics for non-in

From: larry elkins <>
Date: Mon, 05 Feb 2001 16:02:12 -0800
Message-ID: <>


Thanks for replying.

You are right about binds and histograms. I didn't mean to imply "real" histograms, where the number of buckets (size) are specified. Instead, in this case, a simple "analyze table foo compute statistics" was used. This would result in "column statistics" for *all* columns, indexed or not, but, with only one bucket, number of distinct values, high, low, density etc in the XXX_TAB_COLUMNS view. By the same token, only two rows (endpoint 0 and endpoint 1) for each column would show up in XXX_Histograms. So, yeah, while there are column stats there, "real" histograms weren't done. But still, that is a very good point to bring up.

With that said, using binds in place of literals, I still get different plans for different combinations of criteria with those basic column "stats" in place. But, if generation of column stats on the non-indexed columns is
*bypassed* (analyze table foo_a compute statistics for table for all indexed
columns for all indexes), I would *always* get FTS's on both tables and an HJ regardless of the combinations of criteria. So, even if the column stats were very basic and not "real" histograms, they still influenced the plan based on criteria specified vs not having them at all.

I don't know that I ever mentioned it during this thread -- I was trying to be as brief as possible (for me). This all started because a DBA I know was looking for a way to speed up his nightly analyze (while still using compute). One thought he had was to use the "for table for all indexed columns for all indexes" instead of "analyze table foo_a compute statistics" so that the collection of column stats would be bypassed for the non-indexed columns and only collected on the indexed columns. His hopes were that this would speed up the analyze a bit (on He called me and wanted to know if the lack of column stats on the non-indexed columns might influence plans the CBO selects. I couldn't immediately think of a scenario where this would have an impact, thus the question to the list. Subsequent to that, I came up with a few scenarios, and, people have mentioned others.

Funny, though, that you mention binds and histograms. I am working with an app where literals are heavily used -- I will see 500 versions of the exact same SQL statement in V$SQLAREA where the only thing that differs is the
*literal* value used for the PK criteria (this is repeated for many other
SQL statements as well). But, on a 17 million row table they have, there is an index on a Y/N column. 7,000 rows contain Y, the remaining few million have N. They created a histogram on the column, and, the only two queries that hit the table by that column look for a value of Y (so we should use the index). In the case of those two queries, though, they decided to use
*binds* (for a change). So, the histogram is worthless and an FTS is
performed (well, not anymore ;-) ).


Larry G. Elkins
-----Original Message-----


You might want to give this a try using PL/SQL and bind variables.

I don't know if this has changed in 8i, but in 8.x, queries using bind variables could not make use of histograms ( column statistics )


Please see the official ORACLE-L FAQ:
Author: larry elkins

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Mon Feb 05 2001 - 18:02:12 CST

Original text of this message