RE: Count(*) not doing a FTS??

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Thu, 2 Sep 2004 16:31:05 -0400
Message-ID: <564DE4477544D411AD2C00508BDF0B6A2133DEBC@usahm018.exmi01.exch.eds.com>

I do not believe that Oracle even keeps the rows counts for a table in the header. This would require updating the information for every insert and delete which seems to be unnecessary overhead to me.

Reference the following link
http://www.jlcomp.demon.co.uk/faq/count_rows.html for the article "Is there a good way of counting the number of rows in a table ?" that has explain plans for numerous count situations: PK exists, no PK, is null, etc...

Using 9.2.0.4

  2  -- Insert sql after this line.     WARNING - Do not end sql in ';'
  3  select     count(*) from item_master

  4 /

Explained.

ddc1 > set echo off

QUERY_PLAN

---

      COST CARDINALITY
---------- -----------
 SELECT STATEMENT
        97 1

  2.1 SORT AGGREGATE

                     1

    3.1 INDEX FAST FULL SCAN ITEM_MASTER_PRIME UNIQUE
        97      425471

• Mark D Powell --

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of rjamya Sent: Thursday, September 02, 2004 11:21 AM To: Oracle Discussion List
Subject: Count(*) not doing a FTS??

I am sitting in a class and instructor told us that count(*) reads rowcount from segment header in some (or most?) cases rather than doign the good ol' FTS. He can't remember exact details, so this email ....

is it true? Which version was this introduced? Someone has a reproducible test case?

Thanks in advance
Raj

---
To unsubscribe - mailto:oracle-l-request_at_freelists.org&subject=unsubscribe 
To read recent messages - http://freelists.org/archives/oracle-l/09-2004

Received on Thu Sep 02 2004 - 19:14:37 CDT