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

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
 ------------------------------------------------------------------------ 
-------- -------------
 EMP_ID                                                                   
NOT NULL NUMBER
 NAME                                                                           
   
VARCHAR2(50)
 JOB_TITLE                                                                      
   
VARCHAR2(30)
 DEPT_ID                                                                        
   
NUMBER
 MANAGER_ID                                                                     
   
NUMBER
 SALARY                                                                         
   
NUMBER(8,2)
 HIREDATE                                                                       
   
DATE

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
 ------------------------------------------------------------------------ 
-------- -------------------------------
 NAME                                                                           
   
VARCHAR2(100)
 VENDOR_ID                                                                      
   
NUMBER(5)
 SLEEPS                                                                         
   
VARCHAR2(5)
 SEASONS                                                                        
   
VARCHAR2(5)
 DOORS                                                                          
   
VARCHAR2(5)
 WEIGHT                                                                         
   
VARCHAR2(20)
 FLOOR_AREA                                                                     
   
VARCHAR2(20)
 VESTIBULE                                                                      
   
VARCHAR2(20)
 HEIGHT                                                                         
   
VARCHAR2(20)
 POLES                                                                          
   
VARCHAR2(20)
 RETAIL_PRICE                                                                   
   
NUMBER(7,2)

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@xxxxxxxxxxxxx&subject=unsubscribe 
>To read recent messages - http://freelists.org/archives/oracle-l/09-2004
>  
>


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

Other related posts: