Lol! perhaps you need to familiarize them with the term GIGO.
On Fri, Jun 12, 2009 at 9:45 AM, Rob Wolfe <wolfe....@gmail.com> wrote:
>
>
>
> 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
-~----------~----~----~----~------~----~------~--~---
