Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Выборка из отсортированного подзапроса  [new]
subquery_order
Guest
Добрый день, подскажите пожалуйста, есть два запроса -
select *
 from ( select row_number() over (order by ...) rw , *
         from t)
where rw=1;

и
select *
from ( select *
         from t
          order by ..... )
where rownum=1;


они идентичны, но хотелось бы узнать - гарантирует ли Оракл что записи подзапроса с order by будут выбираться во внешний строго в порядке сортировки?
Т.е. я к тому что подзапрос по идее это такая же таблица в виде кучи где может быть выбрана первой любая запись, но я так понимаю в случае отсортированного подзапроса - это уже не классическая куча, а упорядоченная?
21 мар 12, 12:47    [12287136]     Ответить | Цитировать Сообщить модератору
 Re: Выборка из отсортированного подзапроса  [new]
publexus
Member

Откуда: Москва
Сообщений: 955
Первый гарантирует.
Второй гарантирует при следующей модификации:
select *
from ( select rownum rn, *
         from t
          order by ..... )
where rn=1;
21 мар 12, 13:02    [12287275]     Ответить | Цитировать Сообщить модератору
 Re: Выборка из отсортированного подзапроса  [new]
xxxxnnnn
Guest
publexus,

ага, ничего что rownum присваивается до сортировки...
21 мар 12, 13:20    [12287474]     Ответить | Цитировать Сообщить модератору
 Re: Выборка из отсортированного подзапроса  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
ROWNUM Pseudocolumn
21 мар 12, 13:22    [12287494]     Ответить | Цитировать Сообщить модератору
 Re: Выборка из отсортированного подзапроса  [new]
publexus
Member

Откуда: Москва
Сообщений: 955
xxxxnnnn
publexus,

ага, ничего что rownum присваивается до сортировки...

Тупанул, конечно же.
21 мар 12, 13:22    [12287499]     Ответить | Цитировать Сообщить модератору
 Re: Выборка из отсортированного подзапроса  [new]
stax..
Guest
publexus
Первый гарантирует.
Второй гарантирует при следующей модификации:
select *
from ( select rownum rn, *
         from t
          order by ..... )
where rn=1;


Імхо
2 subquery_order, гарантируют оба

2 publexus
с точностью до наоборот
  1  select * from
  2  (select rownum rn,t.* from dept t order by deptno desc)
  3* where rn=1
SQL> /

        RN     DEPTNO DNAME          LOC
---------- ---------- -------------- -------------
         1         10 ACCOUNTING     NEW YORK

SQL> select rownum rn,t.* from dept t order by deptno desc
  2  /

        RN     DEPTNO DNAME          LOC
---------- ---------- -------------- -------------
         4         40 OPERATIONS     BOSTON
         3         30 SALES          CHICAGO
         2         20 RESEARCH       DALLAS
         1         10 ACCOUNTING     NEW YORK


.....
stax
21 мар 12, 13:29    [12287595]     Ответить | Цитировать Сообщить модератору
 Re: Выборка из отсортированного подзапроса  [new]
stax..
Guest
stax..,
вдогонку
часто задумивался что луче пользовать, но стеснялся спросить
SQL> select * from
  2  (select t.* from dept t order by deptno desc)
  3  where rownum=1
  4  /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON


Execution Plan
----------------------------------------------------------
Plan hash value: 768126664

--------------------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |     1 |    30 |     4  (25)| 00:00:01 |
|*  1 |  COUNT STOPKEY          |      |       |       |            |          |
|   2 |   VIEW                  |      |     4 |   120 |     4  (25)| 00:00:01 |
|*  3 |    SORT ORDER BY STOPKEY|      |     4 |    80 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL   | DEPT |     4 |    80 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   1 - filter(ROWNUM=1)
   3 - filter(ROWNUM=1)

SQL> select * from
  2  (select row_number() over (order by deptno desc) rw,
  3  t.* from dept t order by deptno desc)
  4  where rw=1
  5  /

        RW     DEPTNO DNAME          LOC
---------- ---------- -------------- -------------
         1         40 OPERATIONS     BOSTON


Execution Plan
----------------------------------------------------------
Plan hash value: 3839792721

---------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |     4 |   172 |     3   (0)| 00:00:01 |
|*  1 |  VIEW                    |      |     4 |   172 |     3   (0)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|      |     4 |    80 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | DEPT |     4 |    80 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

   1 - filter("RW"=1)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY
              INTERNAL_FUNCTION("T"."DEPTNO") DESC )<=1)


интересно какой алгоритм
WINDOW SORT PUSHED RANK или SORT ORDER BY STOPKEY
орякля реализовал более оптимально

судя как cost 3 і 4 (и СПУ разные), то как бы WINDOW SORT PUSHED RANK дишевле
и, возможно, более предпочтительнее

никто не исследовал?

зы
в 12-ке наверное уже не актуально будет

......
stax
21 мар 12, 13:49    [12287857]     Ответить | Цитировать Сообщить модератору
 Re: Выборка из отсортированного подзапроса  [new]
subquery_order
Guest
короче так никто и не ответил где собственно гарантия что берется именно первая отсортированная строка во вложенном подзапросе, где это в плане к примеру видно?

То что оба запроса корректны - это я итак знаю, но где это описано в документации например, что подзапрос с order by обрабатывается-соединяется, рассматривается... и т.д. в строгом порядке?
21 мар 12, 15:02    [12288652]     Ответить | Цитировать Сообщить модератору
 Re: Выборка из отсортированного подзапроса  [new]
stax..
Guest
subquery_order
короче так никто и не ответил где собственно гарантия что берется именно первая отсортированная строка во вложенном подзапросе, где это в плане к примеру видно?

То что оба запроса корректны - это я итак знаю, но где это описано в документации например, что подзапрос с order by обрабатывается-соединяется, рассматривается... и т.д. в строгом порядке?

в плане STOPKEY и PUSHED RANK

в доке тоже наверное есть, лень искать
http://docs.oracle.com/cd/E11882_01/server.112/e26088/pseudocolumns009.htm
If you embed the ORDER BY clause in a subquery and place the ROWNUM condition in the top-level query, then you can force the ROWNUM condition to be applied after the ordering of the rows. For example, the following query returns the employees with the 10 smallest employee numbers. This is sometimes referred to as top-N reporting:

SELECT *
FROM (SELECT * FROM employees ORDER BY employee_id)
WHERE ROWNUM < 11;

http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions156.htm
The following example is a join query on the sh.sales table. It finds the sales amounts in 2000 of the five top-selling products in 1999 and compares the difference between 2000 and 1999. The ten top-selling products are calculated within each distribution channel.
...



в древних версиях решалось с помощью индекса и хинта,
из старой доки пример убрали

.....
stax
21 мар 12, 15:17    [12288820]     Ответить | Цитировать Сообщить модератору
 Re: Выборка из отсортированного подзапроса  [new]
subquery_order
Guest
stax..,

да, в принципе согласен, в доке описано конкретно для rownum с order by.
Просто всю жизнь писал так и не задумывался, а тут что-то накатило, а с чего бы это вдруг.. оказывается с того...
Ну что ж, спасибо.
21 мар 12, 15:29    [12288965]     Ответить | Цитировать Сообщить модератору
 Re: Выборка из отсортированного подзапроса  [new]
CBO
Guest
stax..,

по стоимости запросы сравнивать нельзя!
21 мар 12, 15:56    [12289207]     Ответить | Цитировать Сообщить модератору
 Re: Выборка из отсортированного подзапроса  [new]
stax..
Guest
CBO
stax..,

по стоимости запросы сравнивать нельзя!

да я догадиваюсь что нельзя, но ведь СВО часто выбирает план с меньшей стоимостью,
и мож неспроста ROW_NUMBER<x оценивается выше ROWNUM<x

я пользовал rownum, но мож есть смысл (выгода) перейти к аналитике,
поетому и спрашиваю, мож кто проводил исследования

......
stax
21 мар 12, 16:07    [12289325]     Ответить | Цитировать Сообщить модератору
 Re: Выборка из отсортированного подзапроса  [new]
Timur Akhmadeev
Member

Откуда:
Сообщений: 510
stax..
интересно какой алгоритм
WINDOW SORT PUSHED RANK или SORT ORDER BY STOPKEY
орякля реализовал более оптимально

Когда-то давно было непонятно, что лучше.
21 мар 12, 21:14    [12291467]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить