Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Count(*) not doing a FTS??

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

From: Daniel W. Fink <Daniel.Fink_at_Sun.COM>
Date: Thu, 02 Sep 2004 15:47:16 -0600
Message-id: <413794E4.50900@sun.com>


Granted it's been a few since I looked at a segment header, but I don't recall any rowcount being stored. The high watermark is stored there, which controls the number of blocks a FTS reads. A count(*) may not do a FTS if an appropriate index exists. If an index on a not-null column exists (primary keys for example), it will use this index.

Example:

SQL> desc employee
 Name Null? Type


SQL> select count(*) from employee;

Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)    1 0 SORT (AGGREGATE)
   2 1 INDEX (FULL SCAN) OF 'PK_EMPLOYEE' (UNIQUE) (Cost=1 Card=21)

SQL> select count(*) from tent;

Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1)    1 0 SORT (AGGREGATE)
   2 1 TABLE ACCESS (FULL) OF 'TENT' (Cost=2 Card=8)

SQL> desc tent
 Name Null? Type


SQL> create index test_it on tent (name);

Index created.

SQL> select count(*) from tent;

Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1)    1 0 SORT (AGGREGATE)
   2 1 TABLE ACCESS (FULL) OF 'TENT' (Cost=2 Card=8)

SQL> select count(name) from tent;

Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=15)    1 0 SORT (AGGREGATE)
   2 1 TABLE ACCESS (FULL) OF 'TENT' (Cost=2 Card=8 Bytes=120)

rjamya wrote:

>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
>
>

---
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:19:50 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US