10 Replies Latest reply: Dec 27, 2012 5:51 AM by hm RSS

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

    947771
      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
          It does not matter.
          • 2. Re: rownum <=1 or  rownum < 2  or  rownum =1
            sb92075
            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
              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
                As I told you: it does not matter.
                • 5. Re: rownum <=1 or  rownum < 2  or  rownum =1
                  jeneesh
                  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
                    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
                      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
                        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
                          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