ThatJeffSmith

How Many Rows will my Query Return?

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!

So:
1: Write Query
B: Explain Query
III: Look at cardinality.

The Code

select * from scott.emp;

The Plan

Cardinality tells us how many rows are involved

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:

Here's what a query that has already finished looks like in SQL Developer

You can learn more about this Diagnostic/Tuning pack feature here.