RE: ** statistics for zero row table - Yes or No?
Date: Sun, 23 Aug 2009 17:03:38 -0500
Message-ID: <C970F08BBE1E164AA8063E01502A71CF015155EB_at_WIN02.hotsos.com>
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?
- It will use rule based since rule doesn't use stats (Very unlikely in 9+)
- It will use default stats for the table (unlikely in 9+, but can happen)
- 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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of A Joshi
Sent: Sunday, August 23, 2009 5:26 PM
To: oracle-l_at_freelists.org
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
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Sun Aug 23 2009 - 17:03:38 CDT