Sadly, they all seem to know that one but don't always think that it
applies to THEIR systems
On Fri, June 12, 2009 13:52, Michael Moore wrote:
> 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
-~----------~----~----~----~------~----~------~--~---
