Re: Better way to select a block of rows

A copy of this was sent to "Sbaiz" <pierluigi.sbaiz_at_elsag.it> (if that email address didn't require changing) On Tue, 22 Jun 1999 14:18:28 +0200, you wrote:

>Hi anybody,
>
>I want to read the rows inside a generic table T
>between j and k (with j<k).
>For this operation I'm using the select statement:
>
>SELECT * FROM T WHERE rownum <=k
>MINUS
>SELECT * FROM T WHERE rownum <=j;
>
>My question is: there is a faster way to
>perform this operation?
>

yes and no. yes there is -- but it'll give you the same rows but in a different order then you get them now. below are 2 queries. the minus forces a binary sort on the first set, the lack of a minus skips the sort. the third query is a further optimization the second query...

>Thanks in advance
>SbaizP.
>
>
>

SQL> select * from x where rownum <= 1000   2 minus
  3 select * from x where rownum <= 990;

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
WEB$CCOULSON                         1888 01-SEP-97
WEB$CKANDERS                         1891 01-SEP-97
WEB$EWISEMAN                         1893 01-SEP-97
WEB$JBARKER                          1890 01-SEP-97
WEB$JHOFFMAN                         1887 01-SEP-97
WEB$NRICCIO                          1892 01-SEP-97
WEB$PSOEHL                           1889 01-SEP-97
WEB$RKAMINER                         1886 01-SEP-97
WEB$TEST_AS_RAJ_AUG4                 1895 01-SEP-97
WEB$TUNTRECH                         1894 01-SEP-97

10 rows selected.

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 MINUS

   2    1     SORT (UNIQUE)
   3    2       COUNT (STOPKEY)
   4    3         TABLE ACCESS (FULL) OF 'X'
   5    1     SORT (UNIQUE)
   6    5       COUNT (STOPKEY)
   7    6         TABLE ACCESS (FULL) OF 'X'

SQL> select * from ( select rownum rnum, x.* from x )   2 where rnum between 991 and 1000;

      RNUM USERNAME                          USER_ID CREATED
---------- ------------------------------ ---------- ---------
       991 WEB$RKAMINER                         1886 01-SEP-97
       992 WEB$JHOFFMAN                         1887 01-SEP-97
       993 WEB$CCOULSON                         1888 01-SEP-97
       994 WEB$PSOEHL                           1889 01-SEP-97
       995 WEB$JBARKER                          1890 01-SEP-97
       996 WEB$CKANDERS                         1891 01-SEP-97
       997 WEB$NRICCIO                          1892 01-SEP-97
       998 WEB$EWISEMAN                         1893 01-SEP-97
       999 WEB$TUNTRECH                         1894 01-SEP-97
      1000 WEB$TEST_AS_RAJ_AUG4                 1895 01-SEP-97

10 rows selected.

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 VIEW

   2    1     COUNT
   3    2       TABLE ACCESS (FULL) OF 'X'

  1 select * from ( select rownum rnum, x.* from x where rownum < 1001 )   2* where rnum between 991 and 1000
SQL> /

      RNUM USERNAME                          USER_ID CREATED
---------- ------------------------------ ---------- ---------
       991 WEB$RKAMINER                         1886 01-SEP-97
       992 WEB$JHOFFMAN                         1887 01-SEP-97
       993 WEB$CCOULSON                         1888 01-SEP-97
       994 WEB$PSOEHL                           1889 01-SEP-97
       995 WEB$JBARKER                          1890 01-SEP-97
       996 WEB$CKANDERS                         1891 01-SEP-97
       997 WEB$NRICCIO                          1892 01-SEP-97
       998 WEB$EWISEMAN                         1893 01-SEP-97
       999 WEB$TUNTRECH                         1894 01-SEP-97
      1000 WEB$TEST_AS_RAJ_AUG4                 1895 01-SEP-97

10 rows selected.

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 VIEW

   2    1     COUNT (STOPKEY)
   3    2       TABLE ACCESS (FULL) OF 'X'

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Jun 25 1999 - 13:10:05 CDT