David makes good points. I personally don't like SELECT count(1) from
MYTABLE; because it makes the meaning unclear.
What does "1" have to do with what you are trying to achieve? Nothing. Are
you trying to count how many 1's are in your table? Of course not.
If "SELECT count(1)" means something, what does "SELECT count(2)" mean? How
about SELECT count ('x')?
By using 1 instead of *, all you have done is taken a well defined operator
'*' and replaced it with something that is obscure and confusing.
Regards,
Mike
On Fri, Jun 12, 2009 at 6:10 AM, ddf <orat...@msn.com> wrote:
>
>
>
> 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
-~----------~----~----~----~------~----~------~--~---
