Re: Count(*) not doing a FTS??
- From: "Daniel W. Fink" <Daniel.Fink@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 02 Sep 2004 15:47:16 -0600
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 - //freelists.org/archives/oracle-l/09-2004
>
>
---
To unsubscribe - mailto:oracle-l-request@xxxxxxxxxxxxx&subject=unsubscribe
To read recent messages - //freelists.org/archives/oracle-l/09-2004
Other related posts:
