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

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.

Anyway, as you mentioned, there might be other situations as well.

Besides looking at the plans themselves, I also used information from Steve Adams' site, http://www.ixora.com.au/home.htm, for info on setting event 10053 and interpreting it's output so that I could "look" into the CBO's head and see how it evaluated and made it's decisions.

Regards,

Larry G. Elkins
elkinsl_at_flash.net

-----Original Message-----
Roberts (ZADCO ITIS)
Sent: Friday, February 02, 2001 11:45 PM To: Multiple recipients of list ORACLE-L non-in

I missed out on original e-mail regarding this subject so I hope I'm on the right track, but I assume that the CBO will use non-indexed columns in its algorithms. I know DB2 would use non-indexed columns, maybe because DB2 is a more advanced optimizer(only because IBM have been doing it a lot longer than Oracle).

I can see one use for the optimizer to use column stats - when a non leading column of a composite index is used in a where.okay yes it is still part of the index but it would probably use the info from analyze in tab_column. There is probably plenty more with the new features like star joins where the optimizer builds tables on the fly.

Sam

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: larry elkins
  INET: elkinsl_at_flash.net

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).