Too bad Oracle doesn't keep the count in the root block of the index. It would only need a few bytes, and getting the count would be a trivial matter of reading a field from the index root block.
Take slimdave's advice:
select count(*) is the best approach. (unless your using oracle 7 ).
Too bad Oracle doesn't keep the count in the root block of the index. It would only need a few bytes, and getting the count would be a trivial matter of reading a field from the index root block.
I can't imagine the contention on that block...
Thomas Saviour(royxavier@yahoo.com)
Technical Lead (Databases)
Thomson Reuters (Markets)
Well, if an approximate count matches your needs, then you can just get NUM_ROWS from USER_TABLES or ALL_TABLES : it's the number of rows calculated by the last statistics gathering.
But if you need the exact count, then follow slimdave's advice.
Well, if an approximate count matches your needs, then you can just get NUM_ROWS from USER_TABLES or ALL_TABLES : it's the number of rows calculated by the last statistics gathering.
You might even use the SAMPLE clause to do that -- Oracle will still scan an index if available, but if not you'd only be reading 1% (or your chosen fraction) of the rows or blocks.
You don't want to "overcook" the sample percentage though.
Code:
SQL> select * from v$version
2 /
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Prod
PL/SQL Release 10.1.0.4.0 - Production
CORE 10.1.0.4.0 Production
TNS for 32-bit Windows: Version 10.1.0.4.0 - Production
NLSRTL Version 10.1.0.4.0 - Production
SQL> drop table t
2 /
Table dropped.
SQL>
SQL> create table t (col1 not null, col2)
2 as
3 select rownum, lpad(rownum,4000)
4 from dual connect by level <= 10000
5 /
Table created.
SQL>
SQL> set autotrace on
SQL>
SQL> exec dbms_stats.gather_table_stats(user,'t')
PL/SQL procedure successfully completed.
SQL>
SQL> select count(*) from t;
COUNT(*)
----------
10000
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=16 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=16 Card=10000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
10034 consistent gets
8051 physical reads
0 redo size
420 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> select 100*count(*) from t sample (1);
100*COUNT(*)
------------
9600
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=16 Card=1 Bytes=7)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (SAMPLE) OF 'T' (TABLE) (Cost=16 Card=100 B
ytes=700)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
476 consistent gets
3081 physical reads
0 redo size
424 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> select 100*count(*) from t sample block (1);
100*COUNT(*)
------------
12800
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=7)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (SAMPLE) OF 'T' (TABLE) (Cost=2 Card=100 By
tes=700)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
162 consistent gets
91 physical reads
0 redo size
425 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> create index i on t (col1) compress;
Index created.
SQL>
SQL> exec dbms_stats.gather_index_stats(user,'i')
PL/SQL procedure successfully completed.
SQL>
SQL> select count(*) from t;
COUNT(*)
----------
10000
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'I' (INDEX) (Cost=2 Card=10000
)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
33 consistent gets
30 physical reads
0 redo size
420 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> select 100*count(*) from t sample (1);
100*COUNT(*)
------------
10500
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=7)
1 0 SORT (AGGREGATE)
2 1 INDEX (SAMPLE FAST FULL SCAN) OF 'I' (INDEX) (Cost=2 Car
d=100 Bytes=700)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
33 consistent gets
0 physical reads
0 redo size
425 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> select 100*count(*) from t sample block (1);
100*COUNT(*)
------------
0
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=7)
1 0 SORT (AGGREGATE)
2 1 INDEX (SAMPLE FAST FULL SCAN) OF 'I' (INDEX) (Cost=2 Car
d=100 Bytes=700)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
423 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
Reply With Quote
Originally Posted by slimdave
Bookmarks