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

From: Sam P. Roberts (ZADCO ITIS) <roberts_at_zadco.co.ae>
Date: Fri, 02 Feb 2001 22:10:04 -0800
Message-ID: <F001.002A9D83.20010202214524@fatcity.com>

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

p.s. if I missed the boat on this topic ,please ignore

-----Original Message-----
Sent: Friday, February 02, 2001 5:51 PM
To: Multiple recipients of list ORACLE-L non-indexed columns

Steve,

Thanks for taking the time to reply.

My initial thought was that the stats would be of no use, but, the thing that still makes me wonder is why would a simple "analyze table compute statistics" gather statistics on *all* columns. For this to be the default behavior, there could be a reason. That's why I threw the question out to the list, trying to see if I was overlooking something obvious. I see your answer below, but, one scenario hit me this morning and I thought I would run it by you and the list and let people take shots at it.

Assume I have an SQL query joining two tables equal in size and the CBO views the cost of an FTS against either as being the same, and, there is an index supporting joining the tables. In addition to the join criteria, criteria against *non-indexed* columns in each of the two tables are specified. No *indexed* columns criteria, other than the join itself, is specified. So, the CBO needs to choose one of the tables as the "driving" table, doing an FTS, and, needs to decide whether to use an NL, MJ, or HJ between the two. Does it make sense, in this case, that the column stats for the non-indexed column could influence the choice of the driving table and/or the join method, that a generalized selectivity would be construed based on the number of distinct values captured for the columns? Sure, an FTS on at least one table would still be required, but, stats on the non-indexed columns could influence which table to drive by and which join method to use?

My "gut" feeling is that in a case such as the one just described, stats on non-indexed columns could influence the access path and join method determined by the CBO. I picked up info on event 10053 from your site. I'll play around with that this weekend.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
-----Original Message-----
Sent: Thursday, February 01, 2001 9:48 PM To: larry elkins; Multiple recipients of list ORACLE-L

Hi Larry,

I think that the answer is that the statistics are of no use to the CBO until an
index is created.

@ Regards,
@ Steve Adams

--

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

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

Author: Sam P. Roberts (ZADCO ITIS)
INET: roberts_at_zadco.co.ae

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------