Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Outer join и оптимизатор  [new]
TudaBlin
Member

Откуда: Екатеринбург
Сообщений: 87
Доброе время, дамы и господа.

Кто-нибудь может сказать или показать где объясняется, почему запрос с left outer join (синтаксис оракловый используется, т.е. (+)) возвращает различные данные в зависимости от того, используется ли режим оптимизации CHOOSE или RULE? Версия базы 9.2.0.5.

Запрос:
select DIS.*
  from GAUGE_SCALES SCL
    , GAUGES G
    , OBJECT_LINKS OL 
    , MANAG_COMPANIES MNG      
    , BUILDINGS BLD
    , V_BUILDINGS_IN_METERING DIS
  where SCL.GAUGE_RN = G.RN
    and G.BUILDING_RN = BLD.RN(+)
    and OL.LEFT_RN = MNG.RN(+)
    and BLD.RN = OL.RIGHT_RN(+)
    and BLD.RN = DIS.BUILDING_RN(+)

говорит:
                RN        GEOGRAFY_RN DISTRICT_NAME                                                                    DISTRICT_TYPE_NAME               BEGIN_DATE  END_DATE           BUILDING_RN        DISTRICT_RN
------------------ ------------------ -------------------------------------------------------------------------------- -------------------------------- ----------- ----------- ------------------ ------------------

8 rows selected

Тот же самый запрос, но с хинтом /*+RULE*/ говорит:
                RN        GEOGRAFY_RN DISTRICT_NAME                                                                    DISTRICT_TYPE_NAME               BEGIN_DATE  END_DATE           BUILDING_RN        DISTRICT_RN
------------------ ------------------ -------------------------------------------------------------------------------- -------------------------------- ----------- ----------- ------------------ ------------------
          62546888           62543994 ПО №2                                                                            Районы снятия показаний приборов 01.01.2005                        62546888           62552185
          62546888           62543994 ПО №2                                                                            Районы снятия показаний приборов 01.01.2005                        62546888           62552185
          62546888           62543994 ПО №2                                                                            Районы снятия показаний приборов 01.01.2005                        62546888           62552185
          62546888           62543994 ПО №2                                                                            Районы снятия показаний приборов 01.01.2005                        62546888           62552185
          62546888           62543994 ПО №2                                                                            Районы снятия показаний приборов 01.01.2005                        62546888           62552185
          62546888           62543994 ПО №2                                                                            Районы снятия показаний приборов 01.01.2005                        62546888           62552185
          62546888           62543994 ПО №2                                                                            Районы снятия показаний приборов 01.01.2005                        62546888           62552185
          62546888           62543994 ПО №2                                                                            Районы снятия показаний приборов 01.01.2005                        62546888           62552185

8 rows selected

Ну ещё для информации все используемые в запросе объекты - это таблицы, за исключением одного. V_BUILDINGS_IN_METERING - это представление. Объявлено так:
create or replace view v_buildings_in_metering as
select  BD.RN
      , BD.GEOGRAFY_RN
      , BD.DISTRICT_NAME
      , BD.DISTRICT_TYPE_NAME
      , BD.BEGIN_DATE 
      , BD.END_DATE
      , BD.BUILDING_RN
      , BD.DISTRICT_RN
  from  V_BUILDINGS_IN_DISTRICTS BD
  where BD.DISTRICT_TYPE_NAME = 'Районы снятия показаний приборов'
create or replace view v_buildings_in_districts as
select  BUI.RN
      , BUI.GEOGRAFY_RN
      , D.DISTRICT_NAME
      , DT.DISTRICT_TYPE_NAME
      , OL.BEGIN_DATE 
      , OL.END_DATE
      , BUI.RN     as BUILDING_RN
      , D.RN       as DISTRICT_RN
  from  BUILDINGS BUI
      , OBJECT_LINKS OL
      , V_DISTRICTS_WITH_TYPES DT
      , DISTRICTS D
  where OL.LINK_TYPE_RN = PKG_OBJECT_LINKS.GET_TYPE_RN(PKG_OBJECT_LINKS.F_sBLD_IN_DISTR)
    and OL.STATE = 1 --предположительно  "Действителен"
    and BUI.RN = OL.LEFT_RN
    and DT.DISTRICT_RN = OL.RIGHT_RN
    and D.RN = DT.DISTRICT_RN
create or replace view v_districts_with_types as
select 'Участки доставки' as DISTRICT_TYPE_NAME
     , DISTRICT_RN
  from DELIVERY_OFFICES
union all
select 'Зоны доставки' as DISTRICT_TYPE_NAME
     , DISTRICT_RN
  from DELIVERY_ZONES
union all
select 'Судебные участки' as DISTRICT_TYPE_NAME
     , DISTRICT_RN
  from JUDICIAL_DISTRICTS
union all
select 'Районы снятия показаний приборов' as DISTRICT_TYPE_NAME
     , DISTRICT_RN
  from METERING_DISTRICTS
union all
select 'Административные районы' as DISTRICT_TYPE_NAME
     , DISTRICT_RN
  from POLITICAL_DISTRICTS
union all
select 'Почтовые отделения' as DISTRICT_TYPE_NAME
     , DISTRICT_RN
  from POST_OFFICES
union all
select 'Зоны обслуживания' as DISTRICT_TYPE_NAME
     , DISTRICT_RN
  from SERV_ZONES
union all
select 'Тарифные зоны' as DISTRICT_TYPE_NAME
     , DISTRICT_RN
  from TARIFF_ZONES
Надеюсь информацией не перегрузил? Заранее большое спасибо.
31 окт 06, 09:27    [3333270]     Ответить | Цитировать Сообщить модератору
 Re: Outer join и оптимизатор  [new]
Jannny
Member

Откуда: Спб
Сообщений: 6424
TudaBlin
                RN        GEOGRAFY_RN DISTRICT_NAME                                                                    DISTRICT_TYPE_NAME               BEGIN_DATE  END_DATE           BUILDING_RN        DISTRICT_RN
------------------ ------------------ -------------------------------------------------------------------------------- -------------------------------- ----------- ----------- ------------------ ------------------

8 rows selected

А можно все-таки полный пример? Потому как непонятно все-таки, сколько строк Вам все-таки вернул первый вариант...
31 окт 06, 10:08    [3333486]     Ответить | Цитировать Сообщить модератору
 Re: Outer join и оптимизатор  [new]
TudaBlin
Member

Откуда: Екатеринбург
Сообщений: 87
Jannny
TudaBlin
                RN        GEOGRAFY_RN DISTRICT_NAME                                                                    DISTRICT_TYPE_NAME               BEGIN_DATE  END_DATE           BUILDING_RN        DISTRICT_RN
------------------ ------------------ -------------------------------------------------------------------------------- -------------------------------- ----------- ----------- ------------------ ------------------

8 rows selected

А можно все-таки полный пример? Потому как непонятно все-таки, сколько строк Вам все-таки вернул первый вариант...

Это и есть полный пример. :)
Т.е. если в Command Window PL/SQL Developer'а выполнять запрос, то он как раз именно это и выдаёт (скопировано без изменений из вкладки Dialog окна Command Window).
Просто Command Window так выдаёт результат: вроде как выбрано 8 строк, но все поля в них пустые, поэтому результат "сворачивается".
31 окт 06, 10:25    [3333664]     Ответить | Цитировать Сообщить модератору
 Re: Outer join и оптимизатор  [new]
TudaBlin
Member

Откуда: Екатеринбург
Сообщений: 87
Jannny
TudaBlin
                RN        GEOGRAFY_RN DISTRICT_NAME                                                                    DISTRICT_TYPE_NAME               BEGIN_DATE  END_DATE           BUILDING_RN        DISTRICT_RN
------------------ ------------------ -------------------------------------------------------------------------------- -------------------------------- ----------- ----------- ------------------ ------------------

8 rows selected

А можно все-таки полный пример? Потому как непонятно все-таки, сколько строк Вам все-таки вернул первый вариант...

Для удобства восприятия могу вот так переформулировать запрос (в фразу select добавил поле, которое не NULL):
select BLD.RN, DIS.*
  from GAUGE_SCALES SCL
    , GAUGES G
    , OBJECT_LINKS OL 
    , MANAG_COMPANIES MNG      
    , BUILDINGS BLD
    , V_BUILDINGS_IN_METERING DIS
  where SCL.GAUGE_RN = G.RN
    and G.BUILDING_RN = BLD.RN(+)
    and OL.LEFT_RN = MNG.RN(+)
    and BLD.RN = OL.RIGHT_RN(+)
    and BLD.RN = DIS.BUILDING_RN(+)
Результат:
                RN                 RN        GEOGRAFY_RN DISTRICT_NAME                                                                    DISTRICT_TYPE_NAME               BEGIN_DATE  END_DATE           BUILDING_RN        DISTRICT_RN
------------------ ------------------ ------------------ -------------------------------------------------------------------------------- -------------------------------- ----------- ----------- ------------------ ------------------
          62546888                                                                                                                                                                                                    
          62546888                                                                                                                                                                                                    
          62546888                                                                                                                                                                                                    
          62546888                                                                                                                                                                                                    
          62546888                                                                                                                                                                                                    
          62546888                                                                                                                                                                                                    
          62546888                                                                                                                                                                                                    
          62546888                                                                                                                                                                                                    

8 rows selected
С хинтом /*+RULE*/ результат такой:
                RN                 RN        GEOGRAFY_RN DISTRICT_NAME                                                                    DISTRICT_TYPE_NAME               BEGIN_DATE  END_DATE           BUILDING_RN        DISTRICT_RN
------------------ ------------------ ------------------ -------------------------------------------------------------------------------- -------------------------------- ----------- ----------- ------------------ ------------------
          62546888           62546888           62543994 ПО №2                                                                            Районы снятия показаний приборов 01.01.2005                        62546888           62552185
          62546888           62546888           62543994 ПО №2                                                                            Районы снятия показаний приборов 01.01.2005                        62546888           62552185
          62546888           62546888           62543994 ПО №2                                                                            Районы снятия показаний приборов 01.01.2005                        62546888           62552185
          62546888           62546888           62543994 ПО №2                                                                            Районы снятия показаний приборов 01.01.2005                        62546888           62552185
          62546888           62546888           62543994 ПО №2                                                                            Районы снятия показаний приборов 01.01.2005                        62546888           62552185
          62546888           62546888           62543994 ПО №2                                                                            Районы снятия показаний приборов 01.01.2005                        62546888           62552185
          62546888           62546888           62543994 ПО №2                                                                            Районы снятия показаний приборов 01.01.2005                        62546888           62552185
          62546888           62546888           62543994 ПО №2                                                                            Районы снятия показаний приборов 01.01.2005                        62546888           62552185

8 rows selected
31 окт 06, 10:30    [3333727]     Ответить | Цитировать Сообщить модератору
 Re: Outer join и оптимизатор  [new]
Jannny
Member

Откуда: Спб
Сообщений: 6424
Не буду говорить, что такого никогда не бывает ;)

Мои предположения: Ну первым делом должна быть полная уверенность, что база одна, окружение запроса одно, ну и запрос один и тот же. И лучше выполнить из SQLPlus-a. Считаем, что это проверено и уверенность есть. Тогда я посоветую проверить корректность индексов. Проще вначале попробовать уменьшить запрос, чтобы таблиц стало меньше, а результат тот же. Ну а потом посмотреть по плану, какие используются в неправильном варианте индексы, и попробовать перестроить их.

PS: правильные то данные возвращаются в котором варианте?
31 окт 06, 10:36    [3333769]     Ответить | Цитировать Сообщить модератору
 Re: Outer join и оптимизатор  [new]
TudaBlin
Member

Откуда: Екатеринбург
Сообщений: 87
Jannny
Не буду говорить, что такого никогда не бывает ;)

Мои предположения: Ну первым делом должна быть полная уверенность, что база одна, окружение запроса одно, ну и запрос один и тот же. И лучше выполнить из SQLPlus-a. Считаем, что это проверено и уверенность есть. Тогда я посоветую проверить корректность индексов. Проще вначале попробовать уменьшить запрос, чтобы таблиц стало меньше, а результат тот же. Ну а потом посмотреть по плану, какие используются в неправильном варианте индексы, и попробовать перестроить их.

PS: правильные то данные возвращаются в котором варианте?

Уверенность в том, что база и окружение запроса одно и тоже 100%-ое. Окно PL/SQL Developer, пишу запрос без хинтов, выполняю, получаю один ответ, потом, в этом же окне, ничего не меняя, ставлю только /*+RULE*/ в запрос и получаю другой ответ. Собственно даже если выяснить, что данные во втором запросе не совсем правильные возвращаются, всё-равно интересен сам факт: поменять хинт в запросе (только хинт, ничего больше!!!) и результаты разные. Я знал, что хинт влияет на выполнение запроса, но считал, что только на его производительность, а не на возвращаемые данные.

Насчёт перестроения индексов - это мысль. Надо попробовать.
Ну и попробую в SQLPlus выполнить запрос. Мало ли.
31 окт 06, 10:49    [3333896]     Ответить | Цитировать Сообщить модератору
 Re: Outer join и оптимизатор  [new]
TudaBlin
Member

Откуда: Екатеринбург
Сообщений: 87
Jannny
PS: правильные то данные возвращаются в котором варианте?

Данные, которые выглядять более правильными, возвращаются во втором варианте.
31 окт 06, 10:52    [3333919]     Ответить | Цитировать Сообщить модератору
 Re: Outer join и оптимизатор  [new]
TudaBlin
Member

Откуда: Екатеринбург
Сообщений: 87
Проверил выполнение запроса в SQLPlus - аналогичное поведение. Сейчас за индексы примусь.
31 окт 06, 10:53    [3333927]     Ответить | Цитировать Сообщить модератору
 Re: Outer join и оптимизатор  [new]
Я и ёжик
Member

Откуда: СПб
Сообщений: 1815
TudaBlin
поменять хинт в запросе (только хинт, ничего больше!!!) и результаты разные.

Ну Вы меняете не "только" хинт, а хинт изменяющий применяемый оптимизатор, т.е. можно сказать , что включающий другую подсистему сервера.
CBO оптимизатор более сложный и ошибки в него вносятся и исправляются регулярно :),подобные вашей чаще связаны с неправильной трансформацией запроса.
Лучший вариант обратится в тех поддержку Oracle, если вы натолкнулись на уже известный BUG вам возможно предложат патчик, если на неизвестный, то есть шанс , что исправят для всех.

В качестве обходного пути можно попробовать выставить "_complex_view_merging"=false; или star_transformation_enabled=false; это помоему фичи которые наиболее часто вызывают проблемы подобные вашей.
Ну или попробовать перейти на более свежую версию, в 9.2.0.6 исправлялось несколько багов с outer join, например, Bug 3188637 - Wrong result with star transformation and OUTER join.
31 окт 06, 12:05    [3334640]     Ответить | Цитировать Сообщить модератору
 Re: Outer join и оптимизатор  [new]
TudaBlin
Member

Откуда: Екатеринбург
Сообщений: 87
Я и ёжик
TudaBlin
поменять хинт в запросе (только хинт, ничего больше!!!) и результаты разные.

Ну Вы меняете не "только" хинт, а хинт изменяющий применяемый оптимизатор, т.е. можно сказать , что включающий другую подсистему сервера.
CBO оптимизатор более сложный и ошибки в него вносятся и исправляются регулярно :),подобные вашей чаще связаны с неправильной трансформацией запроса.
Лучший вариант обратится в тех поддержку Oracle, если вы натолкнулись на уже известный BUG вам возможно предложат патчик, если на неизвестный, то есть шанс , что исправят для всех.

Поддержка - это хорошо. Только её пока что у нас нету. Всё собираются, считают, да как-то не могут высчитать, дороговасто.

Я и ёжик

В качестве обходного пути можно попробовать выставить "_complex_view_merging"=false; или star_transformation_enabled=false; это помоему фичи которые наиболее часто вызывают проблемы подобные вашей.

Спасибо. Попробую. Хотя и не очень надёжно, администрирование сервера плавно переходит в руки другого человека (он не волшебник, он только учится, но парень толковый, а если долго м-учиться, что-нибудь получится ), кто его знает, какие у него будут соображения по поводу этих параметров, а у нас потом программа перестанет работать.

Я и ёжик

Ну или попробовать перейти на более свежую версию, в 9.2.0.6 исправлялось несколько багов с outer join, например, Bug 3188637 - Wrong result with star transformation and OUTER join.

Это тоже вариант. Давно собирались. Может, наконец, соберёмся. Спасибо.
31 окт 06, 12:36    [3334905]     Ответить | Цитировать Сообщить модератору
 Re: Outer join и оптимизатор  [new]
TudaBlin
Member

Откуда: Екатеринбург
Сообщений: 87
TudaBlin
Проверил выполнение запроса в SQLPlus - аналогичное поведение. Сейчас за индексы примусь.

Проверил все индексы, которые используются, пересторил, собрал статистику. Не помогло
31 окт 06, 12:37    [3334911]     Ответить | Цитировать Сообщить модератору
 Re: Outer join и оптимизатор  [new]
TudaBlin
Member

Откуда: Екатеринбург
Сообщений: 87
Кстати, может что-нибудь скажет такой факт. В плане выполнения запроса с CBO (CHOOSE) есть такое действие:
    VIEW PUSHED PREDICATE	Object owner=ERC	Object name=V_BUILDINGS_IN_METERING
Что это он за утверждение пытается толкнуть?
А, ну да, торможу. Вот, собственно, весь план выполнения:

SELECT STATEMENT, GOAL = CHOOSE
 NESTED LOOPS OUTER
  HASH JOIN OUTER
   NESTED LOOPS OUTER
    NESTED LOOPS OUTER
     NESTED LOOPS
      TABLE ACCESS FULL	Object owner=ERC	Object name=GAUGES
      INDEX RANGE SCAN	Object owner=ERC	Object name=I_GAUGE_SCALES_GAUGE_N
     INDEX UNIQUE SCAN	Object owner=ERC	Object name=I_BUILDINGS_PK_U
    VIEW PUSHED PREDICATE	Object owner=ERC	Object name=V_BUILDINGS_IN_METERING
     NESTED LOOPS
      HASH JOIN
       NESTED LOOPS
        TABLE ACCESS BY INDEX ROWID	Object owner=ERC	Object name=BUILDINGS
         INDEX UNIQUE SCAN	Object owner=ERC	Object name=I_BUILDINGS_PK_U
        TABLE ACCESS BY INDEX ROWID	Object owner=ERC	Object name=OBJECT_LINKS
         INDEX RANGE SCAN	Object owner=ERC	Object name=I_OBJECT_LINKS_LINK_TYPE_N
       VIEW	Object owner=ERC	Object name=V_DISTRICTS_WITH_TYPES
        UNION-ALL PARTITION
         FILTER
          INDEX RANGE SCAN	Object owner=ERC	Object name=I_DELIVERY_OFFICES_DISTRICT_N
         FILTER
          INDEX RANGE SCAN	Object owner=ERC	Object name=I_DELIVERY_ZONES_DISTRICT_N
         FILTER
          INDEX RANGE SCAN	Object owner=ERC	Object name=I_JUDICIAL_DISTRICTS_DISTR_N
         INDEX RANGE SCAN	Object owner=ERC	Object name=I_METERING_DISTRICTS_DISTR_N
         FILTER
          INDEX RANGE SCAN	Object owner=ERC	Object name=I_POLITICAL_DISTRICTS_DISTR_N
         FILTER
          INDEX RANGE SCAN	Object owner=ERC	Object name=I_POST_OFFICES_DISTRICT_N
         FILTER
          INDEX RANGE SCAN	Object owner=ERC	Object name=I_SERV_ZONES_DISTRICT_N
         FILTER
          INDEX RANGE SCAN	Object owner=ERC	Object name=I_TARIFF_ZONES_DISTRICT_N
      TABLE ACCESS BY INDEX ROWID	Object owner=ERC	Object name=DISTRICTS
       INDEX UNIQUE SCAN	Object owner=ERC	Object name=I_DISTRICTS_PK_U
   TABLE ACCESS FULL	Object owner=ERC	Object name=OBJECT_LINKS
  INDEX UNIQUE SCAN	Object owner=ERC	Object name=I_MANAG_COMPANIES_PK_U
31 окт 06, 12:42    [3334953]     Ответить | Цитировать Сообщить модератору
 Re: Outer join и оптимизатор  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1489
TudaBlin
Кстати, может что-нибудь скажет такой факт. В плане выполнения запроса с CBO (CHOOSE) есть такое действие:
    VIEW PUSHED PREDICATE	Object owner=ERC	Object name=V_BUILDINGS_IN_METERING
Что это он за утверждение пытается толкнуть?
Можно сказать, что это как раз тот самый случай, который
Я и ёжик
связан с неправильной трансформацией запроса.

Оракл "протиснул" предикаты во внутрь представления. Судя по всему, это и привело к неверному результату.
Попробуйте хинтом запретить ораклу такое поведение...
31 окт 06, 12:49    [3335008]     Ответить | Цитировать Сообщить модератору
 Re: Outer join и оптимизатор  [new]
TudaBlin
Member

Откуда: Екатеринбург
Сообщений: 87
Щукина Анна
TudaBlin
Кстати, может что-нибудь скажет такой факт. В плане выполнения запроса с CBO (CHOOSE) есть такое действие:
    VIEW PUSHED PREDICATE	Object owner=ERC	Object name=V_BUILDINGS_IN_METERING
Что это он за утверждение пытается толкнуть?
Можно сказать, что это как раз тот самый случай, который
Я и ёжик
связан с неправильной трансформацией запроса.

Оракл "протиснул" предикаты во внутрь представления. Судя по всему, это и привело к неверному результату.
Попробуйте хинтом запретить ораклу такое поведение...

Да уж, блин, точно без хинтов не обойтись похоже. Если вот так сказать
select /*+NO_PUSH_PRED(DIS)*/ BLD.RN, DIS.*
  from GAUGE_SCALES SCL
    , GAUGES G
    , OBJECT_LINKS OL 
    , MANAG_COMPANIES MNG      
    , BUILDINGS BLD
    , V_BUILDINGS_IN_METERING DIS
  where SCL.GAUGE_RN = G.RN
    and G.BUILDING_RN = BLD.RN(+)
    and OL.LEFT_RN = MNG.RN(+)
    and BLD.RN = OL.RIGHT_RN(+)
    and BLD.RN = DIS.BUILDING_RN(+)
то из плана VIEW PUSHED PREDICATE исчезает и запрос правильно выполняется. Кстати, если сделать
/*+FIRST_ROWS*/
то план чуточку меняется и результат правильный выдается. Эээх.
Спасибо всем откликнувшимся за помощь.
31 окт 06, 14:39    [3335836]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить