Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

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

From: larry elkins <elkinsl_at_flash.net>
Date: Fri, 02 Feb 2001 06:06:57 -0800
Message-ID: <F001.002A8C6A.20010202055109@fatcity.com>

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). Received on Fri Feb 02 2001 - 08:06:57 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US