They most definitely non indexed will have influence on the CBO - there is
the ANALYZE FOR ALL COLUMNS option anyhow, that is there for that reason. I
think also likely used in conjunction with histograms on fields - DSS / Data
Warehouse :
Regards
Sam
-----Original Message-----
Sent: Saturday, February 03, 2001 6:47 PM
To: Multiple recipients of list ORACLE-L
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
[EMAIL PROTECTED]
-----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: [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).
