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
[EMAIL PROTECTED]
-----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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
