Oracle mechanics

22.01.2009

Неправильный результат при использовании условия IN с «аналитическим подзапросом»

Filed under: CBO,Oracle — Игорь Усольцев @ 13:40
Tags: ,

Замечен ошибочный результат в запросах типа

select * from t1, t2
 where t1.col1 = t2.col2
 and t1.col1 = n
 and t2.col3
IN
   (select decode(row_number()
    over(order by decode(col3,'YY',0,1),col4 desc),1,col3,'ZZ')
    from t2 where col2 = t1.col1)

Эффект замечен на версиях Oracle 10.2.0.4 и 11.1.0.7, подтверждён специалистами Oracle support на версиях с 10.2.0.3 по 11.1.0.7 для функций ROW_NUMBER() и RANK(), заведён баг 7830462.

Получить правильный результат можно, если

  1. заменить условие «IN» на условие «=»
  2. исключить аналитическую функцию из подзапроса

Оба варианта решения трудно назвать удовлетворительным workaround’ом ;)

Интересно, что в случае неверного результата, для доступа к строкам таблицы T2  CBO использует INDEX RANGE SCAN по индексу с  необычным предикатом filter( EXISTS (<not feasible>) после операции FILTER  результатов подзапроса, и никаких аналитических операций типа WINDOWS

--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                    |     1 |   223 |
|   1 |  NESTED LOOPS                   |                    |     1 |   223 |
|   2 |   TABLE ACCESS BY INDEX ROWID   | T1                 |     1 |   118 |
|*  3 |    INDEX UNIQUE SCAN            | PK_T1              |     1 |       |
|   4 |   TABLE ACCESS BY INDEX ROWID   | T2                 |     1 |   105 |
|*  5 |    INDEX RANGE SCAN             | IX_T2              |     1 |       |
|*  6 |     FILTER                      |                    |       |       |
|   7 |      TABLE ACCESS BY INDEX ROWID| T2                 |     1 |    19 |
|*  8 |       INDEX RANGE SCAN          | IX_T2              |     1 |       |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T1"."COL1"=n)
   5 - access("T2"."COL2"=n)
       filter( EXISTS (<not feasible>)
   6 - filter(DECODE(ROW_NUMBER() OVER ( ORDER BY DECODE("COL3",'YY',0,1),INTERNAL_FUNCTION("COL4") DESC ),1,"COL3",'ZZ')=:B1)
   8 - access("COL2"=:B1)

В случае «правильного результата» (при замене «IN» на «=») используется INDEX UNIQUE SCAN с нормальным предикатом и ожидаемая для аналитичесой функции операция WINDOW SORT.

--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                    |     1 |   223 |
|   1 |  NESTED LOOPS                   |                    |     1 |   223 |
|   2 |   TABLE ACCESS BY INDEX ROWID   | T1                 |     1 |   118 |
|*  3 |    INDEX UNIQUE SCAN            | PK_T1              |     1 |       |
|   4 |   TABLE ACCESS BY INDEX ROWID   | T2                 |     1 |   105 |
|*  5 |    INDEX UNIQUE SCAN            | IX_T2              |     1 |       |
|   6 |     WINDOW SORT                 |                    |     1 |    19 |
|   7 |      TABLE ACCESS BY INDEX ROWID| T2                 |     1 |    19 |
|*  8 |       INDEX RANGE SCAN          | IX_T2              |     1 |       |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T1"."COL1"=n)
   5 - access("T2"."COL2"=n AND "T2"."COL3"= (SELECT DECODE(ROW_NUMBER() OVER
( ORDER BY DECODE("COL3",'YY',0,1),INTERNAL_FUNCTION("COL4") DESC ),1,"COl3", 'ZZ')
FROM "T2" WHERE "COL2"=:B1))
   8 - access("COL2"=:B1)

2 комментария »

  1. Специалисты Oracle Support присоединили «мой» bug 7830462 к существующему 7132362, исправления включены в 11.2 и последующие релизы.
    Для нашей системы (Oracle 10.2.0.4 Linux x86_64) выпущен специальный патч 7132362. Приятно :)

    комментарий от iusoltsev — 04.03.2009 @ 18:01 | Ответить

  2. Тестовый пример на реальных таблицах SCOTT:
    SELECT *
    FROM EMP t1, DEPT t2
    WHERE t1.DEPTNO = t2.DEPTNO
    AND t1.DEPTNO = 10
    AND t2.LOC
    IN
    —=
    (SELECT DECODE(row_number()
    OVER(ORDER BY DECODE(LOC, ‘NEW YORK’, 0, 1),
    DNAME DESC),
    1,
    ‘NEW YORK’,
    ‘MOSCOW’)
    FROM DEPT t2
    WHERE DEPTNO = t1.DEPTNO)

    комментарий от iusoltsev — 04.03.2009 @ 18:04 | Ответить


RSS feed for comments on this post. TrackBack URI

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

Создайте бесплатный сайт или блог на WordPress.com.

%d такие блоггеры, как: