RE: ** statistics for zero row table - Yes or No?
- From: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>
- To: <ajoshi977@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 23 Aug 2009 17:03:38 -0500
YES - Always collect stats on a table (and its indexes), zero rows or
millions, and always user DBMS_STATS. (I'm assuming you have a DB that
is 9 or above.)
Without Stats how what will the optimizer do?
a) It will use rule based since rule doesn't use stats (Very unlikely in
9+)
b) It will use default stats for the table (unlikely in 9+, but can
happen)
c) It will do dynamic sampling to figure out the stats for the table "on
the fly" (most likely in 9+)
So if you don't have stats the optimizer will likely spend time
collecting them for each hard parse that touches the table. Maybe that
doesn't seem like a big deal but it is extra processing that it has to
do EACH TIME IT DOES A HARD PARSE. The optimizer doesn't remember the
dynamic stats it collected last time.
So the question really doesn't have much to do with if the table has 0
or a million rows in it, do you want the optimizer to "guess" as the
stats for the table on each hard parse? (I say guess because the dynamic
sample in a large table could actually give different stats each time.)
The effects could be large, IF the optimizer goes with default stats,
the default certainly isn't zero rows. So it will make a decision for
table order in a join and what type of join to use based on what it
"thinks" the table has in it, and if it thinks a table has rows where it
doesn't, well that could make for a "bad" plan.
The escape clause here is that basically if the optimizer over estimates
the cardinality of a table then it normally not to bad (which is at
least possible with a zero row table), it's when it under estimates when
things normally go terribly wrong.
So what is the end of all this giber-gaber? It's likely that if you
don't have stats on zero row tables, you will get query that seem to run
ok. But that it only because it's not doing work that it thought it
would have to do, not because it did the right thing.
Ric Van Dyke
Hotsos Enterprises, Ltd.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of A Joshi
Sent: Sunday, August 23, 2009 5:26 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: ** statistics for zero row table - Yes or No?
Hi,
Is it advisable to analyze (or dbms_stats) for a table that has zero
rows. Going further what if the table size is over 1 MB and zero rows ?
What are the pros and cons. If it is single table select then it is
simpler : however for multi table join select : how would that be
affected by zero row tables : with statistics and without. Thanks.
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
