-
COUNT ON index TABLES
Is this the fastest way to get a total count on a table with 30mil records:
select /*+ ROWID */ count(*) as "TOTAL" from
MINIAL.CHARGES
This query took 10 minutes!!!
-
It may be quicker to the count against an indexed column, if you have one e.g.
select count(primary_key_column) from minial.charges
That way, it should be able to get the data from the index without looking at the table.
-
Nonsense. Just "Select count(*) from MINIAL.CHARGES" and let Oracle work out the fastest way. If there's an appropriate index then it will be used.
-
-
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 ).
-
CENSUSDBA ,
Do you have an index on a not null column?
-
Originally Posted by ixion
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...
-
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.
HTH & Regards,
rbaraer
-
Originally Posted by RBARAER
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>
-
Originally Posted by CENSUSDBA
Is this the fastest way to get a total count on a table with 30mil records:
select /*+ ROWID */ count(*) as "TOTAL" from
MINIAL.CHARGES
This query took 10 minutes!!!
whether you do count(*) or count(primary_key_column) the count timing difference is marginal.
"What is past is PROLOGUE"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|