Правильный ли я использую запрос?

poc
Дата: 21.03.2011 23:02:25
Необходимо выбрать из массива последние 30 строк, а из них еще первые 10 строк. Использую запрос:
SELECT ID,D FROM(SELECT ID,D FROM(SELECT ID,D FROM (SELECT ID,D FROM DATA ORDER BY ID DESC) WHERE ROWNUM <= 30)ORDER BY ID ASC)WHERE ROWNUM <= 10
Правильный лия использую запрос? Может есть варианты более быстрые и надежные? И в чем недостатки моего запроса?
-2-
Дата: 21.03.2011 23:57:06
poc,

row_number between 21 and 30
AmKad
Дата: 21.03.2011 23:57:39
poc,

+ Есть еще такой вариант, но гарантировать что он будет работать на ваших данных быстрее я не могу.
select *
from
   (select d.*, row_number() over (order by id desc) rn
    from data d
   ) 
where rn between 21 and 30;
andreymx
Дата: 22.03.2011 00:15:58
такой запрос отработал за 25 сек
SELECT   ID, D
  FROM   (  SELECT   ID, D
              FROM   (SELECT   ID, D
                        FROM   (  SELECT   dk_idd id, nkniga D
                                    FROM   debet_kredit
                                ORDER BY   ID DESC)
                       WHERE   ROWNUM <= 30)
          ORDER BY   ID ASC)
 WHERE   ROWNUM <= 10

такой за 5 сек
SELECT   ID, dk.nkniga d
  FROM   (  SELECT   ID
              FROM   (SELECT   ID
                        FROM   (  SELECT   dk_idd id
                                    FROM   debet_kredit
                                ORDER BY   ID DESC)
                       WHERE   ROWNUM <= 30)
          ORDER BY   ID ASC) t, debet_kredit dk
 WHERE   ROWNUM <= 10
    and dk.dk_idd = t.id

а такой за 0.001 сек
SELECT   ID, D
  FROM   (  SELECT   ID, D
              FROM   (SELECT   ID, D
                        FROM   (  SELECT   dk_idd id, nkniga D
                                    FROM   debet_kredit
                                  where dk_idd >= (SELECT   max(dk_idd) id FROM   debet_kredit)-100
                                ORDER BY   ID DESC)
                       WHERE   ROWNUM <= 30)
          ORDER BY   ID ASC)
 WHERE   ROWNUM <= 10
результаты идентичны
в таблице debet_kredit 3.5 млн строк, партицирована, dk_idd - PK


Выделенная желтым строка является довольно спорной
AmKad
Дата: 22.03.2011 00:40:47
andreymx,

+ И все-таки стоит отметить что Ваш второй запрос - планозависимый
drop table data purge;

Таблица удалена.

Затрач.время: 00:00:00.04

create table data as
with s as
(select level id, rpad('x', 100, 'x') d from dual connect by level <= 1e6
)
select * from s;

Таблица создана.

Затрач.время: 00:00:03.35

SELECT ID,D FROM(SELECT ID,D FROM(SELECT ID,D FROM (SELECT ID,D FROM DATA ORDER BY ID DESC) WHERE ROWNUM <= 30)ORDER BY ID ASC)WHERE ROWNUM <= 10;

        ID D
---------- ----------------------------------------------------------------------------------------------------
    999971 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    999972 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    999973 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    999974 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    999975 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    999976 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    999977 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    999978 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    999979 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    999980 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

10 строк выбрано.

Затрач.время: 00:00:03.75

План выполнения
----------------------------------------------------------
Plan hash value: 1706098915

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |    10 |   650 |       | 17718   (1)| 00:03:33 |
|*  1 |  COUNT STOPKEY              |      |       |       |       |            |          |
|   2 |   VIEW                      |      |    30 |  1950 |       | 17718   (1)| 00:03:33 |
|*  3 |    SORT ORDER BY STOPKEY    |      |    30 |  1950 |       | 17718   (1)| 00:03:33 |
|   4 |     VIEW                    |      |    30 |  1950 |       | 17717   (1)| 00:03:33 |
|*  5 |      COUNT STOPKEY          |      |       |       |       |            |          |
|   6 |       VIEW                  |      |   866K|    53M|       | 17717   (1)| 00:03:33 |
|*  7 |        SORT ORDER BY STOPKEY|      |   866K|    53M|    63M| 17717   (1)| 00:03:33 |
|   8 |         TABLE ACCESS FULL   | DATA |   866K|    53M|       |  4232   (1)| 00:00:51 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=10)
   3 - filter(ROWNUM<=10)
   5 - filter(ROWNUM<=30)
   7 - filter(ROWNUM<=30)

Note
-----
   - dynamic sampling used for this statement (level=2)


SELECT t.ID,  d
  FROM   (  SELECT   ID
              FROM   (SELECT   ID
                        FROM   (  SELECT   id
                                    FROM   data
                                ORDER BY   ID DESC)
                       WHERE   ROWNUM <= 30)
          ORDER BY   ID ASC) t, data dk
 WHERE   ROWNUM <= 10
    and dk.id = t.id;

        ID D
---------- ----------------------------------------------------------------------------------------------------
    999971 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    999972 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    999973 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    999974 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    999975 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    999976 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    999977 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    999978 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    999979 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    999980 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

10 строк выбрано.

Затрач.время: 00:00:04.73

План выполнения
----------------------------------------------------------
Plan hash value: 1502966930

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |    10 |  1430 |       |  9945   (1)| 00:02:00 |
|*  1 |  COUNT STOPKEY               |      |       |       |       |            |          |
|*  2 |   HASH JOIN                  |      |    30 |  4290 |       |  9945   (1)| 00:02:00 |
|   3 |    VIEW                      |      |    30 |   390 |       |  8384   (1)| 00:01:41 |
|   4 |     SORT ORDER BY            |      |    30 |   390 |       |  8384   (1)| 00:01:41 |
|   5 |      VIEW                    |      |    30 |   390 |       |  8383   (1)| 00:01:41 |
|*  6 |       COUNT STOPKEY          |      |       |       |       |            |          |
|   7 |        VIEW                  |      |   866K|    10M|       |  8383   (1)| 00:01:41 |
|*  8 |         SORT ORDER BY STOPKEY|      |   866K|    10M|    16M|  8383   (1)| 00:01:41 |
|   9 |          TABLE ACCESS FULL   | DATA |   866K|    10M|       |  4230   (1)| 00:00:51 |
|  10 |    TABLE ACCESS FULL         | DATA |   866K|    53M|       |  1554   (1)| 00:00:19 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=10)
   2 - access("DK"."ID"="T"."ID")
   6 - filter(ROWNUM<=30)
   8 - filter(ROWNUM<=30)

Note
-----
   - dynamic sampling used for this statement (level=2)


SELECT /*+ use_hash(dk, t) leading(dk, t)*/ t.ID,  d
  FROM   (  SELECT   ID
              FROM   (SELECT   ID
                        FROM   (  SELECT   id
                                    FROM   data
                                ORDER BY   ID DESC)
                       WHERE   ROWNUM <= 30)
          ORDER BY   ID ASC) t, data dk
 WHERE   ROWNUM <= 10
    and dk.id = t.id;

        ID D
---------- ----------------------------------------------------------------------------------------------------
    999972 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    999974 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    999975 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    999977 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    999978 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    999979 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    999981 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    999983 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    999984 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    999985 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

10 строк выбрано.

Затрач.время: 00:00:07.14

План выполнения
----------------------------------------------------------
Plan hash value: 1609895996

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |    10 |   780 |       | 15782   (1)| 00:03:10 |
|*  1 |  COUNT STOPKEY               |      |       |       |       |            |          |
|*  2 |   HASH JOIN                  |      |    30 |  2340 |    63M| 15782   (1)| 00:03:10 |
|   3 |    TABLE ACCESS FULL         | DATA |   866K|    53M|       |  4232   (1)| 00:00:51 |
|   4 |    VIEW                      |      |    30 |   390 |       |  8384   (1)| 00:01:41 |
|   5 |     SORT ORDER BY            |      |    30 |   390 |       |  8384   (1)| 00:01:41 |
|   6 |      VIEW                    |      |    30 |   390 |       |  8383   (1)| 00:01:41 |
|*  7 |       COUNT STOPKEY          |      |       |       |       |            |          |
|   8 |        VIEW                  |      |   866K|    10M|       |  8383   (1)| 00:01:41 |
|*  9 |         SORT ORDER BY STOPKEY|      |   866K|    10M|    16M|  8383   (1)| 00:01:41 |
|  10 |          TABLE ACCESS FULL   | DATA |   866K|    10M|       |  4230   (1)| 00:00:51 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=10)
   2 - access("DK"."ID"="T"."ID")
   7 - filter(ROWNUM<=30)
   9 - filter(ROWNUM<=30)

Note
-----
   - dynamic sampling used for this statement (level=2)

-2-
Дата: 22.03.2011 00:44:54
andreymx,

А зачем по два раза сортировать и брать rownum?
andreymx
Дата: 22.03.2011 00:47:43
AmKad
andreymx,

И все-таки стоит отметить что Ваш второй запрос - планозависимый
да я вроде и не отрицал
даже честно написал, что dk_idd - это PK
andreymx
Дата: 22.03.2011 00:49:22
-2-
andreymx,

А зачем по два раза сортировать и брать rownum?
я просто показал минимальное отличие второго запроса от первого
-2-
Дата: 22.03.2011 00:51:44
andreymx
-2-
А зачем по два раза сортировать и брать rownum?
я просто показал минимальное отличие второго запроса от первого
Спросил про каждый запрос в отдельности. Почему не сделать between 21 and 30, на 30 строках копейки, но все же?
Tolka
Дата: 22.03.2011 04:40:12
-2-
Почему не сделать between 21 and 30, на 30 строках копейки, но все же?


наверное, просто по-аналогии.

Для скорости важна самая первая обрезка по rownum <= 30. Когда в плане проскакивает COUNT STOPKEY.
А для того, чтобы написать
between 21 and 30
нужно
ROWNUM AS row_num
вычислить на предыдущем запросе и писать
row_num between 21 and 30