This discussion is archived
10 Replies Latest reply: Dec 27, 2012 3:51 AM by hm RSS

rownum <=1 or  rownum < 2  or  rownum =1

947771 Newbie
Currently Being Moderated
Hi,

which one ii should follow.

rownum <=1 to get 1 recored
or rownum < 2
or rownum=1

yours sincerely

Edited by: 944768 on Dec 26, 2012 11:10 PM

Edited by: 944768 on Dec 26, 2012 11:11 PM
  • 1. Re: rownum <=1 or  rownum < 2  or  rownum =1
    hm Expert
    Currently Being Moderated
    It does not matter.
  • 2. Re: rownum <=1 or  rownum < 2  or  rownum =1
    sb92075 Guru
    Currently Being Moderated
    944768 wrote:
    Hi,

    which one ii should follow.

    rownum <=1 to get 1 recored
    or rownum < 2
    or rownum=1

    yours sincerely

    Edited by: 944768 on Dec 26, 2012 11:10 PM

    Edited by: 944768 on Dec 26, 2012 11:11 PM
    post EXPLAIN PLAN for both
  • 3. Re: rownum <=1 or  rownum < 2  or  rownum =1
    ranit B Expert
    Currently Being Moderated
    I tried on a sample table and all 3 plans are same.
    Have a look -
    -- "Plan 1"
    Plan hash value: 95277658
     
    -------------------------------------------------------------------------------
    | Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |          |     1 |    48 |     2   (0)| 00:00:01 |
    |*  1 |  COUNT STOPKEY     |          |       |       |            |          |
    |   2 |   TABLE ACCESS FULL| QUAL_MSL |     1 |    48 |     2   (0)| 00:00:01 |
    -------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       1 - filter(ROWNUM<=1)
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls 
              0  db block gets
              4  consistent gets
              0  physical reads
              0  redo size
            412  bytes sent via SQL*Net to client
            248  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
       
    -- "Plan 2"   
    Plan hash value: 95277658
     
    -------------------------------------------------------------------------------
    | Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |          |     1 |    48 |     2   (0)| 00:00:01 |
    |*  1 |  COUNT STOPKEY     |          |       |       |            |          |
    |   2 |   TABLE ACCESS FULL| QUAL_MSL |     1 |    48 |     2   (0)| 00:00:01 |
    -------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       1 - filter(ROWNUM=1)
      
    Statistics
    ---------------------------------------------------------- 
      0  recursive calls 
      0  db block gets
      4  consistent gets
      0  physical reads
      0  redo size
    412  bytes sent via SQL*Net to client
    248  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed
    
    
       
    -- "Plan 3"
    Plan hash value: 95277658
     
    -------------------------------------------------------------------------------
    | Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |          |     1 |    48 |     2   (0)| 00:00:01 |
    |*  1 |  COUNT STOPKEY     |          |       |       |            |          |
    |   2 |   TABLE ACCESS FULL| QUAL_MSL |     1 |    48 |     2   (0)| 00:00:01 |
    -------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       1 - filter(ROWNUM<2)
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls  
              0  db block gets
              4  consistent gets
              0  physical reads
              0  redo size
            411  bytes sent via SQL*Net to client
            248  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    Everything looks same.

    @hm - Yes, even i agree. Workout was just for the knowledge of OP.


    Edited by: ranit B on Dec 27, 2012 2:30 PM
    -- server stats added

    Edited by: ranit B on Dec 27, 2012 2:35 PM
    -- note for HM
  • 4. Re: rownum <=1 or  rownum < 2  or  rownum =1
    hm Expert
    Currently Being Moderated
    As I told you: it does not matter.
  • 5. Re: rownum <=1 or  rownum < 2  or  rownum =1
    jeneesh Guru
    Currently Being Moderated
    I will use rownum=1

    It is the most stright forward and the simplest to write...

    rownum=1 --- 8 Key strokes without any SHIFT
    rownum<2 -- 8 Key strokes + One press for SHIFT
    rownum<=1 -- 9 Key strokes + One press for SHIFT
  • 6. Re: rownum <=1 or  rownum < 2  or  rownum =1
    ranit B Expert
    Currently Being Moderated
    jeneesh wrote:
    I will use rownum=1

    It is the most stright forward and the simplest to write...

    rownum=1 --- 8 Key strokes without any SHIFT
    rownum<2 -- 8 Key strokes + One press for SHIFT
    rownum<=1 -- 9 Key strokes + One press for SHIFT
    Very well said, Jeneesh. Cool. B-)

    Actually, I read this type of approach once in Tom Kyte's blog, when there was some hot discussion going on between the comparison of Count(1) or Count(*)
  • 7. Re: rownum <=1 or  rownum < 2  or  rownum =1
    hm Expert
    Currently Being Moderated
    I detected that the best choice depends on your keyboard:

    With a german keyboard you would have:

    rownum=1 --- 8 Key strokes + One press for SHIFT
    rownum<2 -- 8 Key strokes without any SHIFT
    rownum<=1 -- 9 Key strokes + One press for SHIFT

    So the best choice for germans would be: rownum<2

    But I think rownum=1 is better for readability, nevertheless.

    :-)
    jeneesh wrote:
    I will use rownum=1

    It is the most stright forward and the simplest to write...

    rownum=1 --- 8 Key strokes without any SHIFT
    rownum<2 -- 8 Key strokes + One press for SHIFT
    rownum<=1 -- 9 Key strokes + One press for SHIFT
  • 8. Re: rownum <=1 or  rownum < 2  or  rownum =1
    jeneesh Guru
    Currently Being Moderated
    hm wrote:
    I detected that the best choice depends on your keyboard:

    So the best choice for germans would be: rownum<2
    Ho, I missed that point.. :(

    now, OP can choose based on his keyboard.. :)
  • 9. Re: rownum <=1 or  rownum < 2  or  rownum =1
    APC Oracle ACE
    Currently Being Moderated
    hm wrote:
    I detected that the best choice depends on your keyboard:

    With a german keyboard you would have:

    rownum=1 --- 8 Key strokes + One press for SHIFT
    rownum<2 -- 8 Key strokes without any SHIFT
    rownum<=1 -- 9 Key strokes + One press for SHIFT
    From which we can deduce that Germans are more interested in hierarchy than equality ;)

    Cheers, APC
  • 10. Re: rownum <=1 or  rownum < 2  or  rownum =1
    hm Expert
    Currently Being Moderated
    :-)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points