Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / не работает SUBQUERY_PRUNING с NOT EXISTS / 8 сообщений из 8, страница 1 из 1
02.08.2017, 18:46
    #39499229
niv76
niv76
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
не работает SUBQUERY_PRUNING с NOT EXISTS
Всем привет. Помогите добиться нужного плана запроса. Имеем ORACLE 11.2.0.3 (тестировал так же 11.2.0.4 результат тот же) и OEBS апликуха.
Имеется темповая табличка GL_POSTING_INTERIM и табличка GL_BALANCES партиционированная по period_name (много партиций около 10 лет).
В GL_POSTING_INTERIM лежат данные за несколько дней ближе к текущей дате.
Есть запрос который тормозит, поменять его нельзя т.к. встроен в апликуху (но хотелось бы сгенерировать другой план, например с помошью хинтов, и закрепить его с помощью бейслайна за данным запросом).
Вот упрощенная версия запроса (хинт hash_aj мой) :

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
select PI.*
    from GL_POSTING_INTERIM PI
   where
    not exists
   (select /*+ hash_aj qb_name(sub) */  'X'
            from GL_BALANCES B
           where (((((((B.ledger_id = PI.ledger_id and
                 B.code_combination_id = PI.code_combination_id) and
                 B.currency_code = PI.currency_code) and
                 B.period_name = PI.period_name) and
                 B.actual_flag = PI.actual_flag) and
                 nvl(B.encumbrance_type_id, (-1)) =
                 nvl(PI.encumbrance_type_id, (-1))) and
                 nvl(B.budget_version_id, (-1)) =
                 nvl(PI.budget_version_id, (-1))) and
                 decode(B.translated_flag,
                         '',
                         '-1',
                         'Y',
                         '0',
                         'N',
                         '0',
                         'R',
                         '1',
                         b.translated_flag) =
                 decode(PI.translated_flag,
                         '',
                         '-1',
                         'Y',
                         '0',
                         'N',
                         '0',
                         'R',
                         '1',
                         PI.translated_flag)))



хочу добиться плана с HASH ANTI JOIN, фулсканом двух таблиц только чтобы в таблице GL_BALANCES фулсканились только партиции с PERIOD_NAME которые есть в таблице GL_POSTING_INTERIM (сейчас беруться все партиции):


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |                    |    20 |  3160 |  5768K  (3)| 19:13:44 |       |       |
|   1 |  LOAD TABLE CONVENTIONAL | GL_BALANCES        |       |       |            |          |       |       |
|*  2 |   HASH JOIN ANTI         |                    |    20 |  3160 |  5768K  (3)| 19:13:44 |       |       |
|   3 |    TABLE ACCESS FULL     | GL_POSTING_INTERIM |    20 |  2000 |    29   (0)| 00:00:01 |       |       |
|   4 |    PARTITION LIST ALL    |                    |  2051M|   110G|  5743K  (2)| 19:08:45 |     1 |    51 |
|   5 |     TABLE ACCESS FULL    | GL_BALANCES        |  2051M|   110G|  5743K  (2)| 19:08:45 |     1 |    51 |
---------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$0C6FB14C
   3 - SEL$0C6FB14C / PI@SEL$1
   5 - SEL$0C6FB14C / B@SUB

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

   2 - access("B"."LEDGER_ID"="PI"."LEDGER_ID" AND "B"."CODE_COMBINATION_ID"="PI"."CODE_COMBINATION_ID"
               AND "B"."CURRENCY_CODE"="PI"."CURRENCY_CODE" AND "B"."PERIOD_NAME"="PI"."PERIOD_NAME" AND
              "B"."ACTUAL_FLAG"="PI"."ACTUAL_FLAG" AND NVL("B"."ENCUMBRANCE_TYPE_ID",(-1))=NVL("PI"."ENCUMBRANCE_TYPE
              _ID",(-1)) AND NVL("B"."BUDGET_VERSION_ID",(-1))=NVL("PI"."BUDGET_VERSION_ID",(-1)) AND
              DECODE("B"."TRANSLATED_FLAG",'','-1','Y','0','N','0','R','1',"B"."TRANSLATED_FLAG")=DECODE("PI"."TRANSL
              ATED_FLAG",'','-1','Y','0','N','0','R','1',"PI"."TRANSLATED_FLAG"))



Скрипты на создание таблиц прикрепил.

Помогите добиться нужного плана, плиз. может быть можно как-то заставить работать BLOOM FILTER или SUBQUERY_PRUNING ?
Ворк эраунды тоже приветствуются. Пока из идей есть только добавить на таблицу GL_POSTING_INTERIM CHECK constraint со списком последних актуальных периодов (тогда Оракл берет только партии из этого листа) но эта идея не очень нравиться, т.к. разные пользователи могут работать с разными периодами и иногда брать старые. Была идея сделать партиционирование по PERIOD_NAME GL_POSTING_INTERIM и добиться wise join в надежде что оракл будет видеть что партиции в GL_POSTING_INTERIM пустые и не будет читать соответствующие партиции в GL_BALANCES, но она темповая (партиционирование не поддерживается).
Спасибо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.08.2017, 18:51
    #39499231
niv76
niv76
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
не работает SUBQUERY_PRUNING с NOT EXISTS
После публикации не увидел прикрепленный файл, поэтому дубль два.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.08.2017, 19:26
    #39499243
Alpdrucken
Alpdrucken
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
не работает SUBQUERY_PRUNING с NOT EXISTS
а данные из тех партиций, которые ты не хочеш, по-твоему не попадают под условие чтоли
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.08.2017, 22:18
    #39499305
niv76
niv76
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
не работает SUBQUERY_PRUNING с NOT EXISTS
Alpdrucken,

там джойн по B.period_name = PI.period_name. Соответственно читать партиции в "B", которых нет в "PI" нет смысла.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.08.2017, 10:49
    #39499524
Alpdrucken
Alpdrucken
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
не работает SUBQUERY_PRUNING с NOT EXISTS
Показалось что временная таблица в подзапросе.

Попробуй добавить в подзапрос условие
Код: plsql
1.
and B.period_name in (select period_name  from GL_POSTING_INTERIM)


у меня подхватил одну партицию по которой были все данные в темпе
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.08.2017, 11:41
    #39499575
niv76
niv76
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
не работает SUBQUERY_PRUNING с NOT EXISTS
Запрос встроен в апликуху, менять текст нет возможности. Задумка добиться нужно плана и подсунуть его под существующий запрос с помошью бейслайна.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.08.2017, 15:17
    #39499738
pihel
pihel
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
не работает SUBQUERY_PRUNING с NOT EXISTS
niv76,

а NL по уникальному локальному индексу GL_BALANCES_N1 до первого вхождения не быстрей получается?
Судя по плану в GL_POSTING_INTERIM всего 20 строчек.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.08.2017, 22:27
    #39499983
niv76
niv76
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
не работает SUBQUERY_PRUNING с NOT EXISTS
Нет, там статистики нет, работает динамический семплинг. Табличка временная. Делал план на пустой таблице.
В реалии там чуть меньше 1млн строк, и в нужной GL_BALANCES 1...20 млн строк в зависимости от дня месяца.
Думаю побить на ежедневные партиции, тогда 1млн будет сравниваться с 1 миллионом, поэтому быстрее будет фулскан + hash_aj. Только лишние партиции не хочется читать.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / не работает SUBQUERY_PRUNING с NOT EXISTS / 8 сообщений из 8, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 448ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1885493]:
 0ms
get settings:
 6ms
get forum list:
 18ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 166ms
get topic data:
 9ms
get forum data:
 3ms
get page messages:
 38ms
get tp. blocked users:
 1ms
others: 201ms
total:  448ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]