You want to know how much pain you are going to inflict on the server and network before you run that ad hoc query? An explain plan can give you an idea of how long it might take to run, and tell you how much data it thinks will be involved.
Cardinality – Fancy Word, Simple Concept
From Wikipedia: “In mathematics, the cardinality of a set is a measure of the “number of elements of the set”.
Relational databases, as described by God…err, I mean Codd, are based on mathematics. Learning to think in sets allows you to excel at SQL. But before you fall asleep, all you need to know is that the cardinality of a plan tells you how many ROWS the database THINKS will be returned or processed by your query!
1: Write Query
B: Explain Query
III: Look at cardinality.
select * from scott.emp;
Is it Right?
Maybe it is, maybe it isn’t. The cardinality is dependent on the statistics available for the underlying tables in the query. If your statistics are stale or missing, then all bets are off. Of course in 11g, the database optimizer is apparently intelligent enough to detect this and automatically fix it, but “insert big butt clause here” _________.
In my case, I run the query, count the rows, and it matches! 244 Cardinality = 244 rows returned.
Statistics Can Lie, Lie, Lie
AskTom sums it up perfectly right here!
“If the cardinality is way off — it can be disasterous,…”
Read the full question and answer here.
If you have not bookmarked ‘AskTom’ yet, then please turn in your license to drive the Internet.
Oracle Can Show You Estimated vs Actual Cardinality for a Query While It’s Running
Yeah, I talked about that in an earlier post. Here’s the picture summary:
You can learn more about this Diagnostic/Tuning pack feature here.