On Jun 12, 11:20 am, Michael Moore <michaeljmo...@gmail.com> wrote:
> 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....
>
> read more »
>
> 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- Hide quoted text -
>
> - Show quoted text -
And this is precisely why I stopped being a DBA and went over to the
dark side (Business Intelligence). While i always found the internals
of the database utterly fascinating (and still do), I would often find
myself in a conversation with a developer that had managed to pick up
some bit of misinformation or an incredibly bad habit that kept
bringing a system to its knees and they would swear that the way they
were doing it was blessed by some well respected (by them) authority
even when the analysis of what the database was actually doing showed
the contrary.
Now I just have to deal with the easy stuff... Like how to convince
VPs that if they let their people put crap in the ERP system then they
should be unsurprised that the dashboards look weird. And no, it isn't
really any easier, just a different kind of frustration and you can't
tell a VP that they have a suboptimal understanding of something
unless you get really lucky and find one that doesn't know what the
word suboptimal means.
Rob
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
