Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 12.2 Запрос возвращает разные значения! в плане CURSOR DURATION MEMORY  [new]
Sergey_Korolev
Member

Откуда:
Сообщений: 141
Версия 12.2.0.1.0 Enterprise Edition

Запрос
WITH /*+ MONITOR */ bb
     AS (  SELECT node_id
             FROM krm.r_prodtree_badd
            WHERE reptree_id = 1 AND node_id BETWEEN 5000 AND 7999
         ORDER BY node_id),
     bb1 AS (SELECT ROWNUM + 4999 rn, bb.* FROM bb),
     tt_1
     AS (  SELECT bb1.*
             FROM bb1
            WHERE rn <> node_id
         ORDER BY rn),
     tt_2 AS (SELECT ROWNUM rr, tt_1.* FROM tt_1),
     ss
     AS (SELECT rn newnode, (SELECT MAX (node_id) mx FROM bb) mx, 7999 cap
           FROM tt_2
          WHERE rr = 1)
SELECT CASE
          WHEN newnode IS NOT NULL THEN newnode
          WHEN mx = cap THEN 9999
          WHEN mx < cap THEN mx + 1
          WHEN newnode IS NULL THEN 5000
       END
          newnode
  FROM ss;


План
Plan hash value: 3898179686
 
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                        | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                             |        |       |     7 (100)|          |       |       |          |
|   1 |  SORT AGGREGATE                          |                             |      1 |    13 |            |          |       |       |          |
|   2 |   VIEW                                   |                             |     33 |   429 |     2   (0)| 00:00:01 |       |       |          |
|   3 |    TABLE ACCESS FULL                     | SYS_TEMP_0FD9D7E3A_76EC0EEE |     33 |   132 |     2   (0)| 00:00:01 |       |       |          |
|   4 |  TEMP TABLE TRANSFORMATION               |                             |        |       |            |          |       |       |          |
|   5 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D7E3A_76EC0EEE |        |       |            |          |  1024 |  1024 |          |
|*  6 |    INDEX RANGE SCAN                      | R_PRODTREE_UIDX1_BADD       |     33 |   231 |     2   (0)| 00:00:01 |       |       |          |
|*  7 |   VIEW                                   |                             |     33 |   858 |     3  (34)| 00:00:01 |       |       |          |
|   8 |    COUNT                                 |                             |        |       |            |          |       |       |          |
|   9 |     VIEW                                 |                             |     33 |   429 |     3  (34)| 00:00:01 |       |       |          |
|  10 |      SORT ORDER BY                       |                             |     33 |   858 |     3  (34)| 00:00:01 |  2048 |  2048 | 2048  (0)|
|* 11 |       VIEW                               |                             |     33 |   858 |     2   (0)| 00:00:01 |       |       |          |
|  12 |        COUNT                             |                             |        |       |            |          |       |       |          |
|  13 |         VIEW                             |                             |     33 |   429 |     2   (0)| 00:00:01 |       |       |          |
|  14 |          TABLE ACCESS FULL               | SYS_TEMP_0FD9D7E3A_76EC0EEE |     33 |   132 |     2   (0)| 00:00:01 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------
 


Возвращает результат

5046
5342
5046
5342
5342
...
5342

Правильно 5342

Как посмотреть почему так ?
4 окт 18, 13:59    [21695202]     Ответить | Цитировать Сообщить модератору
 Re: 12.2 Запрос возвращает разные значения! в плане CURSOR DURATION MEMORY  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4440
На order by внутри with рассчитывать нельзя. Oracle просто поменял алгоритм в 12.2, пользуя PGA вместо temp и случайная сортировка потерялась.
4 окт 18, 14:24    [21695238]     Ответить | Цитировать Сообщить модератору
 Re: 12.2 Запрос возвращает разные значения! в плане CURSOR DURATION MEMORY  [new]
Sergey_Korolev
Member

Откуда:
Сообщений: 141
wurdu,

Убрал сортировки - результат не изменился
WITH /*+ MONITOR_000  */ bb
     AS (  SELECT node_id
             FROM krm.r_prodtree_badd
            WHERE reptree_id = 1 AND node_id BETWEEN 5000 AND 7999
    --     ORDER BY node_id
         ),
     bb1 AS (SELECT ROWNUM + 4999 rn, bb.* FROM bb),
     tt_1
     AS (  SELECT bb1.*
             FROM bb1
            WHERE rn <> node_id
  --       ORDER BY rn
         ),
     tt_2 AS (SELECT ROWNUM rr, tt_1.* FROM tt_1),
     ss
     AS (SELECT rn newnode, (SELECT MAX (node_id) mx FROM bb) mx, 7999 cap
           FROM tt_2
          WHERE rr = 1)
SELECT CASE
          WHEN newnode IS NOT NULL THEN newnode
          WHEN mx = cap THEN 9999
          WHEN mx < cap THEN mx + 1
          WHEN newnode IS NULL THEN 5000
       END
          newnode
  FROM ss;
4 окт 18, 14:42    [21695267]     Ответить | Цитировать Сообщить модератору
 Re: 12.2 Запрос возвращает разные значения! в плане CURSOR DURATION MEMORY  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4440
Sergey_Korolev, так тут вся логика рассчитана на порядок строк, который ничем не гарантируется. Тут наверное логично использовать ROW_NUMBER() OVER (ORDER BY node_id)
4 окт 18, 15:00    [21695305]     Ответить | Цитировать Сообщить модератору
 Re: 12.2 Запрос возвращает разные значения! в плане CURSOR DURATION MEMORY  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1319
Sergey_Korolev
wurdu,

Убрал сортировки - результат не изменился
WITH /*+ MONITOR_000  */ bb
     AS (  SELECT node_id
             FROM krm.r_prodtree_badd
            WHERE reptree_id = 1 AND node_id BETWEEN 5000 AND 7999
    --     ORDER BY node_id
         ),
     bb1 AS (SELECT ROWNUM + 4999 rn, bb.* FROM bb),
     tt_1
     AS (  SELECT bb1.*
             FROM bb1
            WHERE rn <> node_id
  --       ORDER BY rn
         ),
     tt_2 AS (SELECT ROWNUM rr, tt_1.* FROM tt_1),
     ss
     AS (SELECT rn newnode, (SELECT MAX (node_id) mx FROM bb) mx, 7999 cap
           FROM tt_2
          WHERE rr = 1)
SELECT CASE
          WHEN newnode IS NOT NULL THEN newnode
          WHEN mx = cap THEN 9999
          WHEN mx < cap THEN mx + 1
          WHEN newnode IS NULL THEN 5000
       END
          newnode
  FROM ss;


убрав сортировки Вы разрушите логику запроса

імхо, какой-то странный алгоритм (CASE)

.....
stax
4 окт 18, 15:05    [21695314]     Ответить | Цитировать Сообщить модератору
 Re: 12.2 Запрос возвращает разные значения! в плане CURSOR DURATION MEMORY  [new]
Sergey_Korolev
Member

Откуда:
Сообщений: 141
Stax,

Данные берутся из индекса см план выше
CREATE UNIQUE INDEX KRM.R_PRODTREE_UIDX1_BADD ON KRM.R_PRODTREE_BADD
(REPTREE_ID, NODE_ID)
LOGGING
TABLESPACE KRMT
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


Можно без ORDER BY node_id
4 окт 18, 15:17    [21695332]     Ответить | Цитировать Сообщить модератору
 Re: 12.2 Запрос возвращает разные значения! в плане CURSOR DURATION MEMORY  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1319
wurdu,

по любому что-то не так
WHERE rr = 1 должен вернуть одну строку
імхо так замысловато они ищут "дырку" (сбой нумерации node_id)

....
stax
4 окт 18, 15:22    [21695343]     Ответить | Цитировать Сообщить модератору
 Re: 12.2 Запрос возвращает разные значения! в плане CURSOR DURATION MEMORY  [new]
Sergey_Korolev
Member

Откуда:
Сообщений: 141
wurdu,

С (ROW_NUMBER() OVER (ORDER BY node_id) работает верно
WITH bb
     AS (  SELECT ((ROW_NUMBER() OVER (ORDER BY node_id)) + 4999) rn, node_id
             FROM krm.r_prodtree_badd
            WHERE reptree_id = 1 AND node_id BETWEEN 5000 AND 7999
    --     ORDER BY node_id
         ),
     bb1 AS (SELECT ROWNUM + 4999 rn1, bb.* FROM bb),
     tt_1
     AS (  SELECT bb1.*
             FROM bb1
            WHERE rn <> node_id
         ORDER BY rn1
         ),
     tt_2 AS (SELECT ROWNUM rr, tt_1.* FROM tt_1),
     ss
     AS (SELECT rn newnode, (SELECT MAX (node_id) mx FROM bb) mx, 7999 cap
           FROM tt_2
          WHERE rr = 1)
SELECT CASE
          WHEN newnode IS NOT NULL THEN newnode
          WHEN mx = cap THEN 9999
          WHEN mx < cap THEN mx + 1
          WHEN newnode IS NULL THEN 5000
       END
          newnode
  FROM ss;


Есть описание этой ошибки ?
Появилась в 12.2
4 окт 18, 15:29    [21695362]     Ответить | Цитировать Сообщить модератору
 Re: 12.2 Запрос возвращает разные значения! в плане CURSOR DURATION MEMORY  [new]
123йй
Member

Откуда:
Сообщений: 1420
Sergey_Korolev,

на 18с подойдет ?
4 окт 18, 15:31    [21695374]     Ответить | Цитировать Сообщить модератору
 Re: 12.2 Запрос возвращает разные значения! в плане CURSOR DURATION MEMORY  [new]
-2-
Member

Откуда:
Сообщений: 13868
Sergey_Korolev
ошибки
Не стоит чрезмерно усложнять решение примитивной задачи и соберешь меньше граблей.
4 окт 18, 15:46    [21695401]     Ответить | Цитировать Сообщить модератору
 Re: 12.2 Запрос возвращает разные значения! в плане CURSOR DURATION MEMORY  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1319
Sergey_Korolev
Stax,

Данные берутся из индекса см план выше
Можно без ORDER BY node_id


імхо
так нельзя делать, оракля поменяет план, возьмет не из индекса, тогда беда

меня смещает другое, WHERE rr = 1 запрос должен вернуть 1строку, а возвращает несколько
или я что-то не понимаю?

ps
Order by in WITH clause is not preserved
считаю багой и должны исправить

....
stax
4 окт 18, 15:47    [21695404]     Ответить | Цитировать Сообщить модератору
 Re: 12.2 Запрос возвращает разные значения! в плане CURSOR DURATION MEMORY  [new]
Sergey_Korolev
Member

Откуда:
Сообщений: 141
123йй,

Да, Спасибо!
4 окт 18, 15:47    [21695405]     Ответить | Цитировать Сообщить модератору
 Re: 12.2 Запрос возвращает разные значения! в плане CURSOR DURATION MEMORY  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1319
-2-,

возможно решить можно и по другому (напр lag/lead)

но фича (with order by) может выстрелить в других задачах

.....
stax
4 окт 18, 15:51    [21695415]     Ответить | Цитировать Сообщить модератору
 Re: 12.2 Запрос возвращает разные значения! в плане CURSOR DURATION MEMORY  [new]
SkilledJunior
Member

Откуда:
Сообщений: 150
Sergey_Korolev,

Один запрос:
bb 
AS (SELECT node_id
    FROM krm.r_prodtree_badd
    WHERE reptree_id = 1 AND node_id BETWEEN 5000 AND 7999
    ORDER BY node_id),


Второй запрос:
bb1 AS (SELECT ROWNUM + 4999 rn, bb.* FROM bb)


Для второго запроса bb является структурой организованной в виде кучи,
никаких обязательств по сохранению сортировки bb второй запрос не несет,
поэтому применять rownum для нумерации отсортированной выборки так нельзя.

Можно так:
bb1 AS (SELECT ROWNUM + 4999 rn, node_id FROM (select node_id from bb order by node_id))
5 окт 18, 13:37    [21696345]     Ответить | Цитировать Сообщить модератору
 Re: 12.2 Запрос возвращает разные значения! в плане CURSOR DURATION MEMORY  [new]
-2-
Member

Откуда:
Сообщений: 13868
SkilledJunior
Для второго запроса bb является структурой организованной в виде кучи
with декларируется как имя подзапроса и по сути является динамическим create view. "Структурой, организованной в виде" подзапрос становится или не становится по хотению оптимизатора. Но даже, если предположить, что with весь из себя кучерявый, это не объясняет несколько строк результата.
5 окт 18, 14:18    [21696403]     Ответить | Цитировать Сообщить модератору
 Re: 12.2 Запрос возвращает разные значения! в плане CURSOR DURATION MEMORY  [new]
SkilledJunior
Member

Откуда:
Сообщений: 150
[quot -2-]
SkilledJunior
это не объясняет несколько строк результата.


node_id BETWEEN 5000 AND 7999


ROWNUM + 4999 rn


rn <> node_id


rownum полученный на неотсортированной по node_id выборке превращает данный алгоритм в морской бой - попал/не попал, сколько раз попал ...
5 окт 18, 15:35    [21696508]     Ответить | Цитировать Сообщить модератору
 Re: 12.2 Запрос возвращает разные значения! в плане CURSOR DURATION MEMORY  [new]
-2-
Member

Откуда:
Сообщений: 13868
SkilledJunior
rownum полученный на неотсортированной ...
Наложено условие rownum = 1 и поверх этого нет джоинов и других размножателей, результат должен быть одна строка.
5 окт 18, 15:42    [21696516]     Ответить | Цитировать Сообщить модератору
 Re: 12.2 Запрос возвращает разные значения! в плане CURSOR DURATION MEMORY  [new]
SkilledJunior
Member

Откуда:
Сообщений: 150
-2-
Наложено условие rownum = 1 и поверх этого нет джоинов и других размножателей, результат должен быть одна строка.

У тс-а зачем то .* в запросах, полагаю перед тем как выложить запрос он удалял лишнее по его мнению, исходный запрос скорее всего выглядит несколько иначе, понять что происходит можно при наличии полного "неправильно" работающего запроса и данных на которых он "сломался".
5 окт 18, 15:47    [21696527]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить