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!!!
Printable View
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.
If only there were a silver bullet for select count??? :D :D :D :D :D :DQuote:
Originally Posted by slimdave
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?
I can't imagine the contention on that block... :)Quote:
Originally Posted by ixion
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
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.Quote:
Originally Posted by RBARAER
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>
whether you do count(*) or count(primary_key_column) the count timing difference is marginal.Quote:
Originally Posted by CENSUSDBA
I would sat that it doesn't make any difference at all.Quote:
Originally Posted by dbasan
Unless the PK is longer than another index, as might well be the case with a composite PK.Quote:
Originally Posted by dbasan
Oracle would prefer to fast full scan the smallest index, and if it's a choice between an index on a not-null varchar2(1) column and an index on a three-column primary key (or even a single-column date or numeric PK) then the smaller index will perform better.
Furthermore, counting the primary key column is not logically what you want, even if it guaranteed to give the same answer. What you want is the number of rows, and the appropriate syntax for that is count(*). Using count(pk_column) is an attempt to be tricky, just like using count(1), and to my mind that is also bad for easy understanding of the code.
use slimdave's suggestion + alter session to force parallel query.