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

Larry,

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 )

Jared

On Sat, 3 Feb 2001, larry elkins wrote:

> Sam,
>
> You had the right topic. The original questions was "In what way do
> statistics (or lack thereof) on non-indexed columns influence the CBO?". I
> was having trouble thinking of a scenario where this would make a
> difference, hence my posing the question to the list.
>
> I finally thought of a scenario and threw it out to the list. That's the
> email you responded to. Anyway, I eventually had a chance to test the
> scenario. Created 2 tables, A and B, each containing 50,000 rows. Each have
> a indexed column containing consecutive integer values from 1 to 50,000 and
> a one to one relationship between the tables on this column. Each table also
> has a second *non-indexed* column. For table A, the second column contains
> 25,000 distinct values with each individual value occurring 2 times. For
> table B, the second column contains only 2 distinct values, 0 and 1, with
> each value occurring 25,000 times. So, I have an index on the tables to
> support a join between the 2 tables, and, no indexes on the second column in
> each table. I did a generic analyze compute which would include generating
> stats on the non-indexed columns.
>
> And yes, the CBO would use the stats on the non-indexed column on table A to
> decide whether to join to table B using an indexed NLJ, or, an FTS and HJ
> (and in some cases an FTS and SMJ). After deleting the stats and
> re-analyzing so that stats on the non-indexed columns were not generated,
> the CBO always chose, at least on my test cases, to do an FTS on each and
> use an HJ.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: jkstill_at_cybcon.com

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: ListGuru_at_fatcity.com (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).