How Many Rows will my Query Return?

thatjeffsmith Database Stuff 3 Comments

Tell Others About This Story:

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.

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.

Tell Others About This Story:

Comments 3

  1. Hi Jeff,
    I need some clarification regarding Estimated rows vs Actual rows.

    Case 1: Estimated rows =1000. Actual rows =100000
    Case 2: Estimated rows=100000 Actual rows =1000

    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.

    1. thatjeffsmith Post
      Author

      look at the plans – if the optimizer is seeing there 1000 rows vs 100000 and it goes a nested loops vs a hash join – AND you’re not happy with the performance of your query, update your statistics

      if you’re using AutoTrace in SQL Developer – make sure you have preferences enabled to fetch all rows so the actual number is accurate

  2. Pingback: Getting Your Execution Plan, The Hard Way

Leave a Reply

Your email address will not be published. Required fields are marked *