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

---

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