On Jun 12, 4:37 am, sonty <saurabh.zen...@gmail.com> wrote:
> I would say
>
> select count(1) from yourtablename;
>
> BR,
> Sonty
>
> On Jun 10, 9:15 pm, Michael Moore <michaeljmo...@gmail.com> wrote:
>
>
>
> > select count(*) from yourtablename;
>
> > regards,
> > Mike
>
> > On Wed, Jun 10, 2009 at 8:51 AM, Michael Lam
> > <michael.y....@hotmail.com>wrote:
>
> > > Hi all,
>
> > > I am kinda new to Oracle. So, really appreicate your help!
>
> > > I wonder what's the best way to retrieve the total number of record of
> > > a table. I googled it for a while and someone mentioned that it was
> > > discussed in google group before. Would you mind if you can give me
> > > direction regarding this please?
>
> > > thanks
> > > Michael- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
Using count(1) is no better than using count(*):
SQL> select count(*)
2 from dbaobjs;
COUNT(*)
----------
69226
Execution Plan
----------------------------------------------------------
Plan hash value: 708967174
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 286 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| DBAOBJS | 69226 | 286 (1)| 00:00:04 |
----------------------------------------------------------------------
Statistics
----------------------------------------------------------
287 recursive calls
0 db block gets
1067 consistent gets
1024 physical reads
0 redo size
420 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> alter system flush shared_pool;
System altered.
SQL> select count(1)
2 from dbaobjs
3 /
COUNT(1)
----------
69226
Execution Plan
----------------------------------------------------------
Plan hash value: 708967174
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 286 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| DBAOBJS | 69226 | 286 (1)| 00:00:04 |
----------------------------------------------------------------------
Statistics
----------------------------------------------------------
293 recursive calls
0 db block gets
1067 consistent gets
0 physical reads
0 redo size
420 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
Yet another device, which is no more efficient, is to select the max
(rownum) from the desired table:
SQL> select max(rownum)
2 from dbaobjs;
MAX(ROWNUM)
-----------
69226
Execution Plan
----------------------------------------------------------
Plan hash value: 4046134655
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time
|
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 286 (1)| 00:00:04
|
| 1 | SORT AGGREGATE | | 1 | |
|
| 2 | COUNT | | | |
|
| 3 | TABLE ACCESS FULL| DBAOBJS | 69226 | 286 (1)| 00:00:04
|
-----------------------------------------------------------------------
Statistics
----------------------------------------------------------
293 recursive calls
0 db block gets
1067 consistent gets
0 physical reads
0 redo size
423 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
Of course, if statistics are current and one doesn't mind a possibly
inexact number there is always the option of querying USER_TABLES (but
in terms of recursive calls using count() is more efficient):
SQL> select table_name, num_rows
2 from user_tables
3 where table_name = 'DBAOBJS'
4 /
TABLE_NAME NUM_ROWS
------------------------------ ----------
DBAOBJS 69226
Execution Plan
----------------------------------------------------------
Plan hash value: 2777635855
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1
| 131 | 10 (10)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 1
| 131 | 10 (10)| 00:00:01 |
| 2 | NESTED LOOPS OUTER | | 1
| 127 | 9 (12)| 00:00:01 |
| 3 | NESTED LOOPS OUTER | | 1
| 119 | 8 (13)| 00:00:01 |
| 4 | NESTED LOOPS OUTER | | 1
| 114 | 7 (15)| 00:00:01 |
| 5 | NESTED LOOPS | | 1
| 103 | 6 (17)| 00:00:01 |
| 6 | NESTED LOOPS | | 1
| 100 | 5 (20)| 00:00:01 |
| 7 | MERGE JOIN CARTESIAN | | 1
| 67 | 4 (25)| 00:00:01 |
|* 8 | HASH JOIN | | 1
| 31 | 1 (100)| 00:00:01 |
|* 9 | FIXED TABLE FULL | X$KSPPI | 1
| 27 | 0 (0)| 00:00:01 |
| 10 | FIXED TABLE FULL | X$KSPPCV | 1915
| 7660 | 0 (0)| 00:00:01 |
| 11 | BUFFER SORT | | 1
| 36 | 4 (25)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1
| 36 | 3 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | I_OBJ2 | 1
| | 2 (0)| 00:00:01 |
|* 14 | TABLE ACCESS CLUSTER | TAB$ | 1
| 33 | 1 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | I_OBJ# | 1
| | 0 (0)| 00:00:01 |
| 16 | TABLE ACCESS CLUSTER | TS$ | 1
| 3 | 1 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | I_TS# | 1
| | 0 (0)| 00:00:01 |
| 18 | TABLE ACCESS CLUSTER | SEG$ | 1
| 11 | 1 (0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1
| | 0 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | I_OBJ1 | 1
| 5 | 1 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | I_OBJ1 | 1
| 8 | 1 (0)| 00:00:01 |
|* 22 | INDEX RANGE SCAN | I_USER2 | 1
| 4 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("KSPPI"."INDX"="KSPPCV"."INDX")
9 - filter("KSPPI"."KSPPINM"='_dml_monitoring_enabled')
12 - filter(BITAND("O"."FLAGS",128)=0)
13 - access("O"."OWNER#"=USERENV('SCHEMAID') AND
"O"."NAME"='DBAOBJS')
14 - filter(BITAND("T"."PROPERTY",1)=0)
15 - access("O"."OBJ#"="T"."OBJ#")
17 - access("T"."TS#"="TS"."TS#")
19 - access("T"."TS#"="S"."TS#"(+) AND "T"."FILE#"="S"."FILE#"(+)
AND
"T"."BLOCK#"="S"."BLOCK#"(+))
20 - access("T"."BOBJ#"="CO"."OBJ#"(+))
21 - access("T"."DATAOBJ#"="CX"."OBJ#"(+))
22 - access("CX"."OWNER#"="CU"."USER#"(+))
Statistics
----------------------------------------------------------
721 recursive calls
0 db block gets
220 consistent gets
0 physical reads
0 redo size
488 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
37 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
Of course all of that is moot if someone in another session is
deleting all of the data from the desired table and hasn't yet issued
a commit. There's more to this discussion here:
http://oratips-ddf.blogspot.com/2008/10/is-anybody-there.html
David Fitzjarrell
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
