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
[sql]select * from scott.emp;[/sql]

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.

What should be our action in above cases?
In my opinion, case 2 will run fine because we have gathered statistics for 100000 rows so optimizer will do just fine. But in case 1, statistics are stale and we need to gather statistics? Please clarify. Thanks.