Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 2 [3] 4   вперед  Ctrl      все
 Re: Query Cost ниже, а время выполнения выше  [new]
booby
Member

Откуда:
Сообщений: 2257
booby
...
зачем
...


оно, видно, конечно, зачем.
но ради двойного hash затевать эту историю представляется явно преждевременным.
Фактическое объявленное заполнение таблиц не производит настолько безнадежного впечатления.
20 авг 15, 01:45    [18043519]     Ответить | Цитировать Сообщить модератору
 Re: Query Cost ниже, а время выполнения выше  [new]
booby
Member

Откуда:
Сообщений: 2257
MaximFomenko
...

Могу сказать что всего запрос возвращает всего 8 строк. Если наложить просто фильтр по дате на таблицу F_FINANCE_ENTRY не фильтруя по компании или группе счетов, то получается 12,8М строк. Для данных дат и данной компании: 261к строк.


прошу уточнить,
все-таки осталось упущенным -

+
Select count(*)
From F_FINANCE_ENTRY F
Where 
  Exists(Select * 
           From D_ACCOUNT A
           Where A.ACCOUNT_GROUP  =  'Revenue'
             And   A.ACCOUNT_ID = F.ACCOUNT_ID
           ) 


и
+
Select count(*)
From F_FINANCE_ENTRY F
Where 
  Exists(Select * 
           From D_ACCOUNT A, D_COMPANY С
           Where A.ACCOUNT_GROUP  =  'Revenue'
             And   A.ACCOUNT_ID = F.ACCOUNT_ID
             And   C.COMPANY_NUMBER  IN  ('152')
             And   C.COMPANY_ID=F.COMPANY_ID
           ) 


можно ли увидеть эти числа?
20 авг 15, 03:23    [18043557]     Ответить | Цитировать Сообщить модератору
 Re: Query Cost ниже, а время выполнения выше  [new]
SeaGate
Member

Откуда: Новосибирск
Сообщений: 1635
booby
но ради двойного hash затевать эту историю представляется явно преждевременным.

Имеется ли какая-то аргументация, основанная на фактах или только домыслы?

Как видно из моего поста, я делаю предположения.

booby
Прошу прощения за назойливость - так какой же будет рекомендованный битмап-индекс для данного конкретного примера плана

Я дам подсказку: bitmap индексов будет целых три (3).

booby
Фактическое объявленное заполнение таблиц не производит настолько безнадежного впечатления.

Я не мог оставаться в стороне, видя запросы, как в этом посте.
Где предлагается проверить вариант с откладыванием фильтра по дате на наиболее поздний срок.
Распределение записей по account_id/company_id в F_FINANCE_ENTRY, наверняка, хаотичное.
Подобный запрос приведет к просмотру всех строк по паре (ACCOUNT_ID, COMPANY_ID), а фильтр по дате будет проверяться на последнем этапе, внутри NESTED LOOPS с D_DATE.
Если ILM политик по F_FINANCE_ENTRY нет, не организован жизненный цикл данных, то запрос будет деградировать со временем, т.к. кол-во записей по паре (ACCOUNT_ID, COMPANY_ID) будет расти.
Кроме того, могут быть "популярные" пары (ACCOUNT_ID, COMPANY_ID), на которых запрос будет работать всегда значительно дольше.

В своем варианте я применил все фильтры на наиболее ранних этапах, чтобы минимизировать кол-во обращений к F_FINANCE_ENTRY.
В результате, обращения делаются настолько редко, насколько возможно.
Последующие соединения с измерениями не будут вызывать никаких проблем на селективных фильтрах, т.к. resultset будет небольшой.
Ну и до кучи, вероятно, это не единственный пример фильтров.
Оптимизировать все возможные примеры фильтров для важных запросов (производительность которых важна) через concatenated indexes может оказаться слишком дорого.
BITMAP-ы могут дать определенные преимущества в подобных случаях.
20 авг 15, 05:42    [18043572]     Ответить | Цитировать Сообщить модератору
 Re: Query Cost ниже, а время выполнения выше  [new]
booby
Member

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

Вы не "на яве-писатель", часом?

SeaGate
booby
но ради двойного hash затевать эту историю представляется явно преждевременным.

Имеется ли какая-то аргументация, основанная на фактах или только домыслы?


имеется

SeaGate
booby
Прошу прощения за назойливость - так какой же будет рекомендованный битмап-индекс для данного конкретного примера плана

Я дам подсказку: bitmap индексов будет целых три (3).

форму лица подправьте на менее надменную, и просто дайте пример вами персонально рекомендованного индекса.

SeaGate
...
Подобный запрос приведет к просмотру всех строк по паре (ACCOUNT_ID, COMPANY_ID), а фильтр по дате будет проверяться на последнем этапе, внутри NESTED LOOPS с D_DATE.


Прошу прощения - мне не надо пересказывать словами текст запроса - я вполне и совершенно ясно осознаю, что именно я предлагаю.

SeaGate
Если (...па-ра-рам...), то запрос будет деградировать со временем, т.к. кол-во записей по паре (ACCOUNT_ID, COMPANY_ID) будет расти.

открою маленький секрет - при росте объема данных любой запрос будет "деградировать".
Нельзя написать запрос, который не будет деградировать.
Но можно написать запрос, который будет деградировать в будущем предсказуемым образом.
И выбрать вариант записи именно по этому основанию, а не на основе того, что на текущем заполнении есть вариант, работающий быстрее.

SeaGate
Кроме того, могут быть "популярные" пары (ACCOUNT_ID, COMPANY_ID), на которых запрос будет работать всегда значительно дольше.

И что? Универсальные утверждения бесценны (ничего не стоят).

SeaGate
В своем варианте я применил все фильтры на наиболее ранних этапах, чтобы минимизировать кол-во обращений к F_FINANCE_ENTRY.
В результате, обращения делаются настолько редко, насколько возможно.
Последующие соединения с измерениями не будут вызывать никаких проблем на селективных фильтрах, т.к. resultset будет небольшой.
Ну и до кучи, вероятно, это не единственный пример фильтров.
Оптимизировать все возможные примеры фильтров для важных запросов (производительность которых важна) через concatenated indexes может оказаться слишком дорого.
BITMAP-ы могут дать определенные преимущества в подобных случаях.

[/quot]
Прошу прощения, этот поток сознания, без смысла и содержания, я комментировать уже и не в силах и не в желаниях.
20 авг 15, 10:21    [18044137]     Ответить | Цитировать Сообщить модератору
 Re: Query Cost ниже, а время выполнения выше  [new]
SeaGate
Member

Откуда: Новосибирск
Сообщений: 1635
booby,

booby
Вы не "на яве-писатель", часом?

Какое отношение это имеет к теме топика?
booby
имеется

Я слушаю факты.
booby
форму лица подправьте на менее надменную, и просто дайте пример вами персонально рекомендованного индекса.

Все видно по плану приведенного запроса тут.
booby
открою маленький секрет - при росте объема данных любой запрос будет "деградировать".
Нельзя написать запрос, который не будет деградировать.

Не буду пытаться разубеждать.

booby
Прошу прощения, этот поток сознания, без смысла и содержания, я комментировать уже и не в силах и не в желаниях.

Да, сделайте одолжение и себе, и другим, не комментируйте.

По технической части я высказался, ТС сам выберет, что ему лучше подходит, риски я озвучил.
20 авг 15, 11:24    [18044593]     Ответить | Цитировать Сообщить модератору
 Re: Query Cost ниже, а время выполнения выше  [new]
MaximFomenko
Member

Откуда: London, UK
Сообщений: 594
ArtNick
MaximFomenko
Пробовал кстати создавать индекс по (COMPANY_ID, ACCOUNT_ID) на F_FINANCE_ENTRY. Оптимизатор не обращает на него внимания в данном запросе.

Максим, повторяю еще раз, оптимизатор обращает внимание, но считает что стоимость выполнения с использованием этого индекса неоптимальна. Почему? Статистики нет, она невалидная? А может он прав?


Со статистий все в порядке - есть настроеные DBA процедуры ее обновления при устарении более чем на пару процентов да и вручную запустил рефреш - никак не помогло.
20 авг 15, 12:02    [18044900]     Ответить | Цитировать Сообщить модератору
 Re: Query Cost ниже, а время выполнения выше  [new]
MaximFomenko
Member

Откуда: London, UK
Сообщений: 594
booby
MaximFomenko,
надеюсь, мы продолжаем обсуждать "упрощенный запрос"

Конечно.

booby
а) попробуй создать индекс с опцией compute statistics



booby
б) можно построить построить пару иначе - (account_id, company_id)

не обращает в присутствии desc индекса по дате?
или не обращает, сохраняя BITMAP CONVERSION TO ROWIDS на этапе обращения к после соединения d_company и d_account?

DESC индексы по дате я уже убрал, оставил оригинальные ASC индексы по дате.
В присутсвии индекса по (DATE_ID) запрос идет по нему. Так же использует индекс по (COMPANY_ID), делает по ним BITMAP CONVERSION TO ROWIDS и использует.

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

booby
касательно второго плана запроса.
Сначала оптимизатор ошибается, оценивая результат соединения d_company c d_date
в 94 записи, а не в 230.
Далее следующие детали:
a) Он считает, что соединение d_company c d_date стоит на 7 попугаев
дешевле соединения d_company и d_account - это ошибка даже с точки зрения числа строк в задействованных таблицах, наведенная, вероятно,
отсутствием индекса по D_ACCOUNT"."ACCOUNT_GROUP"

Действительно, индекса по D_ACCOUNT"."ACCOUNT_GROUP" нет. Если его создать, его начинает использовать для доступа к таблице D_ACCOUNT, но индексы по DATE_ID и COMPANY_ID на F_FINANCE_ENTRY продолжают применяться.

booby
б)построив саму комбинацию (company_id, date_id) он дальше считает, что т.к. построение комбинированного поиска с задействованием двух индексов стоит слишком дорого, и искать в индексе он будет по date_id, а по company_id - отбрасывать найденное и на выходе получит меньше строк, чем при использовании пары (company_id, account_id).
Ошибка в оценке здесь провоцируется ожиданием, что при desc индексе "средняя глубина поиска" окажется меньше, чем при asc.
(Конкретно эту ошибку, в 12м, судя по другим постам в данном топике, поправили.)

Ок

booby
в присутствии любого варианта комбинированного индекса по паре (company_id, account_id) (или наоборот),
приведи план и время выполнения запроса по отношению к прочим вариантам:
(в этом варианте индекс в D_GENERAL_DATE по DATE_ID предполагается существующим)

SELECT /*+ LEADING(CT, F_FINANCE_ENTRY,  D_GENERAL_DATE) use_nl(D_GENERAL_DATE) */ 
  D_COMPANY.COMPANY_NUMBER, 
  D_COMPANY.C_COMPANY_REMARK_1, 
  F_FINANCE_ENTRY.JOURNAL_TYPE, 
  CT.ACCOUNT_GROUP, 
  CT.ACCOUNT_NUMBER, 
  CT.ACCOUNT_TEXT
FROM
   (Select /*+ no_merge */
     D_ACCOUNT.ACCOUNT_GROUP, 
     D_ACCOUNT.ACCOUNT_NUMBER, 
     D_ACCOUNT.ACCOUNT_TEXT,
     D_COMPANY.C_COMPANY_REMARK_1,      
     D_ACCOUNT.ACCOUNT_ID  
   From D_COMPANY, D_ACCOUNT
   Where 
     D_COMPANY.COMPANY_NUMBER  IN  ('152')
   AND  D_ACCOUNT.ACCOUNT_GROUP  =  'Revenue'   
   ) CT
   , F_FINANCE_ENTRY
   , D_DATE  D_GENERAL_DATE 
WHERE
    (F_FINANCE_ENTRY.ACCOUNT_ID = CT.ACCOUNT_ID)
AND (F_FINANCE_ENTRY.COMPANY_ID = CT.COMPANY_ID)
AND (F_FINANCE_ENTRY.ENTRY_DATE_ID = D_GENERAL_DATE.DATE_ID)
And  (
   D_GENERAL_DATE.THE_DATE  BETWEEN  '01-JAN-14'  AND  '18-AUG-14'
  )
GROUP BY
  D_COMPANY.COMPANY_NUMBER, 
  D_COMPANY.C_COMPANY_REMARK_1, 
  F_FINANCE_ENTRY.JOURNAL_TYPE, 
  CT.ACCOUNT_GROUP, 
  CT.ACCOUNT_NUMBER, 
  CT.ACCOUNT_TEXT


(у меня нет возможности провести натурный эксперимент, так что, хинты может потребоваться подкрутить по месту до SEL-имен)


Привожу в присутсвии индексов F_FINANCE_ENTRY21(DATE_ID ASC), F_FINANCE_ENTRY04(COMPANY_ID ASC), С_F_FINANCE_ENTRY26(COMPANY_ID ASC, ACCOUNT_ID ASC):

Plan hash value: 1010006041
 
------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                   |   517 | 67210 |   878   (1)| 00:00:08 |
|   1 |  HASH GROUP BY                   |                   |   517 | 67210 |   878   (1)| 00:00:08 |
|*  2 |   HASH JOIN                      |                   |   517 | 67210 |   877   (1)| 00:00:08 |
|*  3 |    TABLE ACCESS FULL             | D_ACCOUNT         |    28 |  1848 |    17   (0)| 00:00:01 |
|   4 |    NESTED LOOPS                  |                   |       |       |            |          |
|   5 |     NESTED LOOPS                 |                   | 10996 |   687K|   860   (1)| 00:00:07 |
|   6 |      NESTED LOOPS                |                   |    94 |  3572 |     2   (0)| 00:00:01 |
|   7 |       TABLE ACCESS BY INDEX ROWID| D_COMPANY         |     1 |    22 |     1   (0)| 00:00:01 |
|*  8 |        INDEX RANGE SCAN          | D_COMPANY02       |     1 |       |     1   (0)| 00:00:01 |
|   9 |       TABLE ACCESS BY INDEX ROWID| D_DATE            |    94 |  1504 |     1   (0)| 00:00:01 |
|* 10 |        INDEX RANGE SCAN          | D_DATE03          |    94 |       |     1   (0)| 00:00:01 |
|  11 |      BITMAP CONVERSION TO ROWIDS |                   |       |       |            |          |
|  12 |       BITMAP AND                 |                   |       |       |            |          |
|* 13 |        BITMAP INDEX SINGLE VALUE | F_FINANCE_ENTRY21 |       |       |            |          |
|* 14 |        BITMAP INDEX SINGLE VALUE | F_FINANCE_ENTRY04 |       |       |            |          |
|  15 |     TABLE ACCESS BY INDEX ROWID  | F_FINANCE_ENTRY   |   117 |  3042 |   860   (1)| 00:00:07 |
------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("F_FINANCE_ENTRY"."ACCOUNT_ID"="D_ACCOUNT"."ACCOUNT_ID")
   3 - filter("D_ACCOUNT"."ACCOUNT_GROUP"='Revenue')
   8 - access("D_COMPANY"."COMPANY_NUMBER"='152')
  10 - access("D_GENERAL_DATE"."THE_DATE">=TO_TIMESTAMP('01-JAN-14') AND 
              "D_GENERAL_DATE"."THE_DATE"<=TO_TIMESTAMP('18-AUG-14'))
  13 - access("F_FINANCE_ENTRY"."ENTRY_DATE_ID"="D_GENERAL_DATE"."DATE_ID")
  14 - access("D_COMPANY"."COMPANY_ID"="F_FINANCE_ENTRY"."COMPANY_ID")
20 авг 15, 12:45    [18045135]     Ответить | Цитировать Сообщить модератору
 Re: Query Cost ниже, а время выполнения выше  [new]
MaximFomenko
Member

Откуда: London, UK
Сообщений: 594
SeaGate
В этой связи, оптимальнее может оказаться переход на bitmap индексы и star-transformation, что уже советовали.
Bitmap индексы compressed by design. Меньше размер, больше шансов, что попадут к кэш, а при желании можно и закрепить (keep pool).

Можно подробнее? Хранилище задизайнено в виде звезд, согласно дизайну Кимбала.
Если я захочу попробовать работу на bitmap index'ах, то как это правильно делается?
После прочтения данной статьи:
http://www.oracle.com/technetwork/articles/sharma-indexes-093638.html
можно подумать что в хранилище просто достаточно все фактовые b-tree индексы пересоздать без разбору в качестве bitmap'ов и все станет лучше прежнего.
Я недавно пробовал, так сделать, но улучшения производительности как-то не заметил. Правда я совершенно не трогал индексы на дименшинах. Они остались B-tree.
В любом случае полазив в гугле я так и не нашел внятно написаной стратегии как следует правильно применять bitmap индексы в хранилище. Что заменять ими, что не заменять. Как быть с фактами, как с дименшинами. Если проясните хотя бы в друх словах - буду рад попробовать применить.
20 авг 15, 13:07    [18045331]     Ответить | Цитировать Сообщить модератору
 Re: Query Cost ниже, а время выполнения выше  [new]
MaximFomenko
Member

Откуда: London, UK
Сообщений: 594
booby
MaximFomenko
...

Могу сказать что всего запрос возвращает всего 8 строк. Если наложить просто фильтр по дате на таблицу F_FINANCE_ENTRY не фильтруя по компании или группе счетов, то получается 12,8М строк. Для данных дат и данной компании: 261к строк.


прошу уточнить,
все-таки осталось упущенным -

+
Select count(*)
From F_FINANCE_ENTRY F
Where 
  Exists(Select * 
           From D_ACCOUNT A
           Where A.ACCOUNT_GROUP  =  'Revenue'
             And   A.ACCOUNT_ID = F.ACCOUNT_ID
           ) 



11,3М строк.
booby
+
Select count(*)
From F_FINANCE_ENTRY F
Where 
  Exists(Select * 
           From D_ACCOUNT A, D_COMPANY С
           Where A.ACCOUNT_GROUP  =  'Revenue'
             And   A.ACCOUNT_ID = F.ACCOUNT_ID
             And   C.COMPANY_NUMBER  IN  ('152')
             And   C.COMPANY_ID=F.COMPANY_ID
           ) 


можно ли увидеть эти числа?


161к
20 авг 15, 13:15    [18045398]     Ответить | Цитировать Сообщить модератору
 Re: Query Cost ниже, а время выполнения выше  [new]
ora601
Member

Откуда:
Сообщений: 750
MaximFomenko
Я недавно пробовал, так сделать, но улучшения производительности как-то не заметил.


Наверное потому что у тебя битмапы создаються во время выполнения с бтри?

Плюс битмапы могут эффективно комбинироваться(OR) и если создать 3 индекса по 1 на поле, то они сначало скомбинируються, потом будут доставаться данные с таблицы.
20 авг 15, 13:25    [18045489]     Ответить | Цитировать Сообщить модератору
 Re: Query Cost ниже, а время выполнения выше  [new]
MaximFomenko
Member

Откуда: London, UK
Сообщений: 594
ora601
MaximFomenko
Я недавно пробовал, так сделать, но улучшения производительности как-то не заметил.


Наверное потому что у тебя битмапы создаються во время выполнения с бтри?

Не понял что вы имеете в виду? Я пробовал удалить все B-tree индексы с фактовой таблицы, на их месте пересоздать точно такие же по точно тем же полям, но BITMAP. Как я уже написал - при этом B-tree индексы на дименшинах этого факта я никак не трогал.

ora601
Плюс битмапы могут эффективно комбинироваться(OR) и если создать 3 индекса по 1 на поле, то они сначало скомбинируються, потом будут доставаться данные с таблицы.

Данный бенефит для наших отчетов не сильно интересен - у нас почти не бывает условий OR, XOR. В 95% отчетов используется только AND.
20 авг 15, 13:40    [18045593]     Ответить | Цитировать Сообщить модератору
 Re: Query Cost ниже, а время выполнения выше  [new]
ora601
Member

Откуда:
Сообщений: 750
[quot MaximFomenko]
Не понял что вы имеете в виду? Я пробовал удалить все B-tree индексы с фактовой таблицы, на их месте пересоздать точно такие же по точно тем же полям, но BITMAP. Как я уже написал - при этом B-tree индексы на дименшинах этого факта я никак не трогал.


Если посмотреть первый план, там будет BITMAP CONVERSION, что и есть преобразование бтри в битмап.

MaximFomenko
Данный бенефит для наших отчетов не сильно интересен - у нас почти не бывает условий OR, XOR. В 95% отчетов используется только AND.


Не только для OR но и для AND. Даже в примере Шармы, который ты привел, в предпоследнем плане это видно ...
20 авг 15, 14:52    [18046161]     Ответить | Цитировать Сообщить модератору
 Re: Query Cost ниже, а время выполнения выше  [new]
MaximFomenko
Member

Откуда: London, UK
Сообщений: 594
ora601
Не только для OR но и для AND. Даже в примере Шармы, который ты привел, в предпоследнем плане это видно ...

Ок, так кто расскажет или покажет ссылку где почитать о том как правильно применять bitmap'ы в хранилище. Что меняем на них, а что оставляем как было?
20 авг 15, 15:29    [18046464]     Ответить | Цитировать Сообщить модератору
 Re: Query Cost ниже, а время выполнения выше  [new]
MaximFomenko
Member

Откуда: London, UK
Сообщений: 594
MaximFomenko
ora601
Не только для OR но и для AND. Даже в примере Шармы, который ты привел, в предпоследнем плане это видно ...

Ок, так кто расскажет или покажет ссылку где почитать о том как правильно применять bitmap'ы в хранилище. Что меняем на них, а что оставляем как было?

Самое детальное, что пока нашел это такая инструкция:
http://www.orafaq.com/node/1861

Но не знаю на сколько на нее можно полагаться. Буду пробовать.
20 авг 15, 18:06    [18047744]     Ответить | Цитировать Сообщить модератору
 Re: Query Cost ниже, а время выполнения выше  [new]
ora601
Member

Откуда:
Сообщений: 750
MaximFomenko
ora601
Не только для OR но и для AND. Даже в примере Шармы, который ты привел, в предпоследнем плане это видно ...

Ок, так кто расскажет или покажет ссылку где почитать о том как правильно применять bitmap'ы в хранилище. Что меняем на них, а что оставляем как было?

Обычно достаточно одного битмап индекса на одну колонку. Методика индексирования - такая же как у бтри, тоесть по тем колонкам которые используются при джоинах и в предикатах. Наибольшая эффективность будет на фактовой таблице, за счет сжатия и возможности комбинирования... Я бы еще потестил бы с bitmap join index. Вообщем никаких особенных особенностей индексирования нет.

Читать можно опять же в доке Oracle Data Warehousing guide ....
20 авг 15, 22:43    [18048794]     Ответить | Цитировать Сообщить модератору
 Re: Query Cost ниже, а время выполнения выше  [new]
K790
Member

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

автор
Вообщем никаких особенных особенностей индексирования нет.

в особенности битмапа я бы поспорил :) надеюсь ТС с этим не столкнется.
20 авг 15, 23:04    [18048849]     Ответить | Цитировать Сообщить модератору
 Re: Query Cost ниже, а время выполнения выше  [new]
ora601
Member

Откуда:
Сообщений: 750
K790
ora601,

автор
Вообщем никаких особенных особенностей индексирования нет.

в особенности битмапа я бы поспорил :) надеюсь ТС с этим не столкнется.

Я говорил за особенности индексирования, не битмапа.
21 авг 15, 02:09    [18049140]     Ответить | Цитировать Сообщить модератору
 Re: Query Cost ниже, а время выполнения выше  [new]
booby
Member

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

сорри, не мог отвечать ранее, да и сейчас - на бегу...

хинты надо поправить на имена частей запроса.
для вашего варианта это должно быть SEL1 и SEl2

(создал прототипы таблиц, с пустой таблицей фактов без заполнения так)

+
create table D_DATE
As
select level as DATE_ID, date '1900-01-01' + level -1 as THE_DATE from dual connect by level <= 43832;
alter table D_DATE add constraint pk_d_date primary key (date_id) using index tablespace indexes;
create index ix_d_date_the_date on d_date(the_date) tablespace ts_indexes;
-- drop table D_DATE  purge;
-- select * from d_date;
-----------------------------
 create table D_ACCOUNT as
 select cast(level as number) as ACCOUNT_ID
   , cast(
        case mod(level,35)
          when 0 then 'Revenue'
          when 1 then 'Cost'
          when 2 then 'Expense'
          when 3 then 'Liabilities & Owner''s Equity'  
          when 4 then 'Cash'  
          else 
           case mod(level,17)
             when 0 then 'Receivables'
             when 1 then 'Receivables'  
             when 4 then 'Plant and Equipment'  
             when 5 then 'Natural Resources'  
             when 6 then 'Intangible Assets'  
             when 7 then 'Receivables'
             when 8 then 'Receivables'  
             else 'Inventories'
           end    
        end  
          as Varchar2(30)) as ACCOUNT_GROUP
      , cast(100000+level as number) as ACCOUNT_NUMBER 
      , cast(
          case mod(level,35)
          when 0 then 'Revenue'
          when 1 then 'Cost'
          when 2 then 'Expense'
          when 3 then 'Liabilities & Owner''s Equity'  
          when 4 then 'Cash'  
          else 
           case mod(level,17)
             when 0 then 'Receivables'
             when 1 then 'Receivables'  
             when 4 then 'Plant and Equipment'  
             when 5 then 'Natural Resources'  
             when 6 then 'Intangible Assets'  
             when 7 then 'Receivables'
             when 8 then 'Receivables'  
             else 'Inventories'
           end    
        end ||' - '|| to_char(100000+level,'TM9')  
             as varchar2(250)) as ACCOUNT_TEXT   
 from dual connect by level <= 982;
 alter table D_ACCOUNT add constraint pk_d_account primary key (ACCOUNT_ID)
 using index tablespace ts_indexes;
 create index ix_d_account_type on d_account(ACCOUNT_GROUP) tablespace ts_indexes compute statistics; 
--  select * from d_account;
-- drop table d_account purge; 
-----------
--
create table D_COMPANY
as select cast(level as number ) as COMPANY_ID
, cast('company -'||to_char(level,'TM9') as varchar2(250)) as C_COMPANY_REMARK_1
, cast(to_char(level,'TM9') as varchar2(38)) as COMPANY_NUMBER
from dual 
connect by level <= 163;
alter table d_company add constraint pk_d_company primary key (COMPANY_ID) using index tablespace ts_indexes;
create index ix_d_company_numbere on d_company(company_number) tablespace ts_indexes compute statistics;
-- select * from d_company;
-- drop table D_COMPANY purge;
--  
create table F_FINANCE_ENTRY(
    ACCOUNT_ID number
  , ENTRY_DATE_ID number
  , COMPANY_ID number
  , JOURNAL_TYPE varchar2(2) Default 'A'
  , mfamount number(22,4) Default 0
);
alter table F_FINANCE_ENTRY add constraint fk_F_FINANCE_ENTRY_A foreign key (ACCOUNT_ID)
references D_ACCOUNT(ACCOUNT_ID);
alter table F_FINANCE_ENTRY add constraint F_FINANCE_ENTRY_DID foreign key (ENTRY_DATE_ID)
references D_DATE(date_id);
alter table F_FINANCE_ENTRY add constraint fk_F_FINANCE_ENTRY_CID foreign key (COMPANY_ID)
references D_COMPANY(COMPANY_ID);
create index ix_F_FINANCE_ENTRY_cmb1 on F_FINANCE_ENTRY(COMPANY_ID, ACCOUNT_ID)
tablespace ts_indexes;
create index ix_F_FINANCE_ENTRY_AI on F_FINANCE_ENTRY(ACCOUNT_ID) tablespace ts_indexes;
create index ix_F_FINANCE_ENTRY_CI on F_FINANCE_ENTRY(COMPANY_ID) tablespace ts_indexes;
create index ix_F_FINANCE_ENTRY_DI on F_FINANCE_ENTRY(ENTRY_DATE_ID) tablespace ts_indexes;
-- -- -- -- 
--select * from F_FINANCE_ENTRY;
--drop table F_FINANCE_ENTRY purge;


запрос в присутствии составного индекса по компании и счету (компания ведет, счет ведомый)

+
SELECT /*+ LEADING(@"SEL$1" "CT"@"SEL$1" "F_FINANCE_ENTRY"@"SEL$1" "D_GENERAL_DATE"@"SEL$1") 
         USE_nl(@"SEL$1" "F_FINANCE_ENTRY"@"SEL$1")
         nlj_batching(@"SEL$1" "F_FINANCE_ENTRY"@"SEL$1")
         INDEX(@"SEL$1" "F_FINANCE_ENTRY"@"SEL$1" ("F_FINANCE_ENTRY"."COMPANY_ID" "F_FINANCE_ENTRY"."ACCOUNT_ID"))
         use_nl(SEL$1 D_GENERAL_DATE)  
       */ 
  /*sql-var4*/
  CT.COMPANY_NUMBER, 
  CT.C_COMPANY_REMARK_1, 
  F_FINANCE_ENTRY.JOURNAL_TYPE, 
  CT.ACCOUNT_GROUP, 
  CT.ACCOUNT_NUMBER, 
  CT.ACCOUNT_TEXT
FROM
   (Select /*+ no_merge */
     D_ACCOUNT.ACCOUNT_GROUP, 
     D_ACCOUNT.ACCOUNT_NUMBER, 
     D_ACCOUNT.ACCOUNT_TEXT,
     D_COMPANY.C_COMPANY_REMARK_1,      
     D_COMPANY.COMPANY_NUMBER, 
     D_COMPANY.COMPANY_ID,
     D_ACCOUNT.ACCOUNT_ID  
   From D_COMPANY, D_ACCOUNT
   Where 
     D_COMPANY.COMPANY_NUMBER  IN  ('152')
   AND  D_ACCOUNT.ACCOUNT_GROUP  =  'Revenue'   
   ) CT
   , F_FINANCE_ENTRY
   , D_DATE  D_GENERAL_DATE 
WHERE
    (F_FINANCE_ENTRY.ACCOUNT_ID = CT.ACCOUNT_ID)
AND (F_FINANCE_ENTRY.COMPANY_ID = CT.COMPANY_ID)
AND (F_FINANCE_ENTRY.ENTRY_DATE_ID = D_GENERAL_DATE.DATE_ID)
And  (
   D_GENERAL_DATE.THE_DATE  BETWEEN Date '2014-01-01'  AND Date '2014-08-18'
  )
GROUP BY
  CT.COMPANY_NUMBER, 
  CT.C_COMPANY_REMARK_1, 
  F_FINANCE_ENTRY.JOURNAL_TYPE, 
  CT.ACCOUNT_GROUP, 
  CT.ACCOUNT_NUMBER, 
  CT.ACCOUNT_TEXT


вообще, чтоб посмотреть, чего хочет Oracle, используйте DBMS_XPLAN.DISPLAY_CURSOR

запускаете запрос, (в данном случае с комментарием /*sql-var4*/ )
ищете его sql_id
select * from v$sql T
where T.SQL_TEXT like '%/*sql-var4*/%';

дальше разглядываете
 SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('найденный_sql_id',0,'ADVANCED ALLSTATS LAST'));


для вашего случая, есть смысл посмотреть на план такого запроса.

+
SELECT 
  /*sql-var5*/
  CT.COMPANY_NUMBER, 
  CT.C_COMPANY_REMARK_1,  
  F1.JOURNAL_TYPE, 
  CT.ACCOUNT_GROUP, 
  CT.ACCOUNT_NUMBER, 
  CT.ACCOUNT_TEXT
FROM
   (Select /*+ no_merge */
     D_ACCOUNT.ACCOUNT_GROUP, 
     D_ACCOUNT.ACCOUNT_NUMBER, 
     D_ACCOUNT.ACCOUNT_TEXT,
     D_COMPANY.C_COMPANY_REMARK_1,      
     D_COMPANY.COMPANY_NUMBER, 
     D_COMPANY.COMPANY_ID,
     D_ACCOUNT.ACCOUNT_ID  
   From D_COMPANY, D_ACCOUNT
   Where 
     D_COMPANY.COMPANY_NUMBER  IN  ('152')
   AND  D_ACCOUNT.ACCOUNT_GROUP  =  'Revenue'   
   ) CT
   , F_FINANCE_ENTRY F1
   , F_FINANCE_ENTRY F2
   , D_DATE  D_GENERAL_DATE 
WHERE
    (F1.ACCOUNT_ID = CT.ACCOUNT_ID)
AND (F1.COMPANY_ID = CT.COMPANY_ID)
AND (F2.ENTRY_DATE_ID = D_GENERAL_DATE.DATE_ID)
And  (
   D_GENERAL_DATE.THE_DATE  BETWEEN Date '2014-01-01'  AND Date '2014-08-18'
  )
AND F1.rowid = F2.rowid  
GROUP BY
  CT.COMPANY_NUMBER, 
  CT.C_COMPANY_REMARK_1, 
  F1.JOURNAL_TYPE, 
  CT.ACCOUNT_GROUP, 
  CT.ACCOUNT_NUMBER, 
  CT.ACCOUNT_TEXT


А уже потом со спокойной душой погружаться в битмап-индексы.

Удачи.
21 авг 15, 18:37    [18053146]     Ответить | Цитировать Сообщить модератору
 Re: Query Cost ниже, а время выполнения выше  [new]
MaximFomenko
Member

Откуда: London, UK
Сообщений: 594
Пишу небольшой отчет дабы подвести итоги.

Итак DESC индексы были отброшены как таковые, которые из-за проблем Оракла 11ж приводят к еще более медленному выполнению запросов, пусть и с будь-то бы низким костом. Что как выяснили являлось ошибкой.

Далее некоторые настойчиво рекоммендовали применить BITMAP индексы со включенным хинтом STAR_TRANSFORMATION.
Вооружившись вышеупомянутой статьей я пересоздал все индексы на крупных фактовых таблицах ДВХ (те что от 2-3 млн строк) исходя из того, насколько гранулярны данные в индексируемой колонке. До 2500 DISTINCT значений - BITMAP, свыше - B-tree.
После пересоздания индексов выполнил повторный тест все того же запроса:

+ SQL
SELECT
  D_COMPANY.COMPANY_NUMBER, 
  D_COMPANY.C_COMPANY_REMARK_1, 
  F_FINANCE_ENTRY.JOURNAL_TYPE, 
  D_ACCOUNT.ACCOUNT_GROUP, 
  D_ACCOUNT.ACCOUNT_NUMBER, 
  D_ACCOUNT.ACCOUNT_TEXT
FROM
   F_FINANCE_ENTRY
   INNER JOIN D_ACCOUNT ON (F_FINANCE_ENTRY.ACCOUNT_ID=D_ACCOUNT.ACCOUNT_ID)
   INNER JOIN D_DATE  D_GENERAL_DATE ON (F_FINANCE_ENTRY.ENTRY_DATE_ID=D_GENERAL_DATE.DATE_ID)
   INNER JOIN D_COMPANY ON (D_COMPANY.COMPANY_ID=F_FINANCE_ENTRY.COMPANY_ID)
 
WHERE
  (
   D_ACCOUNT.ACCOUNT_GROUP  =  'Revenue'
   AND
   D_GENERAL_DATE.THE_DATE  BETWEEN  '01-JAN-14'  AND  '18-AUG-14'
   AND
   D_COMPANY.COMPANY_NUMBER  IN  ('152')
  )
GROUP BY
  D_COMPANY.COMPANY_NUMBER, 
  D_COMPANY.C_COMPANY_REMARK_1, 
  F_FINANCE_ENTRY.JOURNAL_TYPE, 
  D_ACCOUNT.ACCOUNT_GROUP, 
  D_ACCOUNT.ACCOUNT_NUMBER, 
  D_ACCOUNT.ACCOUNT_TEXT


с 448 секунд до 45.

При этом кост запроса опустился до смешных 45 единиц.

Итак, сначала привожу план запроса с B-tree ASC индексами:

+ B-TREE ASC
Plan hash value: 2702559326
 
---------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                   |   577 | 74433 |  8415   (2)| 00:01:00 |
|   1 |  HASH GROUP BY                      |                   |   577 | 74433 |  8415   (2)| 00:01:00 |
|*  2 |   HASH JOIN                         |                   |   577 | 74433 |  8414   (2)| 00:01:00 |
|   3 |    TABLE ACCESS BY INDEX ROWID      | D_DATE            |    94 |  1504 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                | D_DATE03          |    94 |       |     1   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID      | F_FINANCE_ENTRY   |   657 | 16425 |  8412   (2)| 00:01:00 |
|   6 |     NESTED LOOPS                    |                   | 18384 |  2028K|  8412   (2)| 00:01:00 |
|   7 |      MERGE JOIN CARTESIAN           |                   |    28 |  2464 |     9   (0)| 00:00:01 |
|   8 |       TABLE ACCESS BY INDEX ROWID   | D_COMPANY         |     1 |    22 |     1   (0)| 00:00:01 |
|*  9 |        INDEX RANGE SCAN             | D_COMPANY02       |     1 |       |     1   (0)| 00:00:01 |
|  10 |       BUFFER SORT                   |                   |    28 |  1848 |     8   (0)| 00:00:01 |
|* 11 |        TABLE ACCESS FULL            | D_ACCOUNT         |    28 |  1848 |     8   (0)| 00:00:01 |
|  12 |      BITMAP CONVERSION TO ROWIDS    |                   |       |       |            |          |
|  13 |       BITMAP AND                    |                   |       |       |            |          |
|  14 |        BITMAP CONVERSION FROM ROWIDS|                   |       |       |            |          |
|* 15 |         INDEX RANGE SCAN            | F_FINANCE_ENTRY02 | 99143 |       |    53   (0)| 00:00:01 |
|  16 |        BITMAP CONVERSION FROM ROWIDS|                   |       |       |            |          |
|* 17 |         INDEX RANGE SCAN            | F_FINANCE_ENTRY04 | 99143 |       |   209   (1)| 00:00:02 |
---------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("F_FINANCE_ENTRY"."ENTRY_DATE_ID"="D_GENERAL_DATE"."DATE_ID")
   4 - access("D_GENERAL_DATE"."THE_DATE">=TO_TIMESTAMP('01-JAN-14') AND 
              "D_GENERAL_DATE"."THE_DATE"<=TO_TIMESTAMP('18-AUG-14'))
   9 - access("D_COMPANY"."COMPANY_NUMBER"='152')
  11 - filter("D_ACCOUNT"."ACCOUNT_GROUP"='Revenue')
  15 - access("F_FINANCE_ENTRY"."ACCOUNT_ID"="D_ACCOUNT"."ACCOUNT_ID")
  17 - access("D_COMPANY"."COMPANY_ID"="F_FINANCE_ENTRY"."COMPANY_ID")


А теперь что случилось после замены части индексов, а именно тех, у которых DISTINCT значений в таблице F_FINANCE_ENTRY не более 2500 на битмапы.
Ах да, конечно же запрос с битмапами тестировался при:

ALTER SESSION SET STAR_TRANSFORMATION_ENABLED=TRUE; 


Итак...
+ BITMAP
Plan hash value: 1613438952
 
---------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                   |    21 |  2394 |    46  (14)| 00:00:01 |
|   1 |  HASH GROUP BY                      |                   |    21 |  2394 |    46  (14)| 00:00:01 |
|*  2 |   HASH JOIN                         |                   |    21 |  2394 |    45  (12)| 00:00:01 |
|*  3 |    HASH JOIN                        |                   |    21 |  1008 |    27  (15)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID     | D_COMPANY         |     1 |    22 |     1   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN               | D_COMPANY02       |     1 |       |     1   (0)| 00:00:01 |
|   6 |     TABLE ACCESS BY INDEX ROWID     | F_FINANCE_ENTRY   |    21 |   546 |    26  (16)| 00:00:01 |
|   7 |      BITMAP CONVERSION TO ROWIDS    |                   |       |       |            |          |
|   8 |       BITMAP AND                    |                   |       |       |            |          |
|   9 |        BITMAP MERGE                 |                   |       |       |            |          |
|  10 |         BITMAP KEY ITERATION        |                   |       |       |            |          |
|  11 |          TABLE ACCESS BY INDEX ROWID| D_DATE            |    94 |  1504 |     1   (0)| 00:00:01 |
|* 12 |           INDEX RANGE SCAN          | D_DATE03          |    94 |       |     1   (0)| 00:00:01 |
|* 13 |          BITMAP INDEX RANGE SCAN    | F_FINANCE_ENTRY21 |       |       |            |          |
|  14 |        BITMAP MERGE                 |                   |       |       |            |          |
|  15 |         BITMAP KEY ITERATION        |                   |       |       |            |          |
|  16 |          TABLE ACCESS BY INDEX ROWID| D_COMPANY         |     1 |    22 |     1   (0)| 00:00:01 |
|* 17 |           INDEX RANGE SCAN          | D_COMPANY02       |     1 |       |     1   (0)| 00:00:01 |
|* 18 |          BITMAP INDEX RANGE SCAN    | F_FINANCE_ENTRY04 |       |       |            |          |
|  19 |        BITMAP MERGE                 |                   |       |       |            |          |
|  20 |         BITMAP KEY ITERATION        |                   |       |       |            |          |
|* 21 |          TABLE ACCESS FULL          | D_ACCOUNT         |    28 |  1848 |    17   (0)| 00:00:01 |
|* 22 |          BITMAP INDEX RANGE SCAN    | F_FINANCE_ENTRY02 |       |       |            |          |
|* 23 |    TABLE ACCESS FULL                | D_ACCOUNT         |    28 |  1848 |    17   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("F_FINANCE_ENTRY"."ACCOUNT_ID"="D_ACCOUNT"."ACCOUNT_ID")
   3 - access("D_COMPANY"."COMPANY_ID"="F_FINANCE_ENTRY"."COMPANY_ID")
   5 - access("D_COMPANY"."COMPANY_NUMBER"='152')
  12 - access("D_GENERAL_DATE"."THE_DATE">=TO_TIMESTAMP('01-JAN-14') AND 
              "D_GENERAL_DATE"."THE_DATE"<=TO_TIMESTAMP('18-AUG-14'))
  13 - access("F_FINANCE_ENTRY"."ENTRY_DATE_ID"="D_GENERAL_DATE"."DATE_ID")
  17 - access("D_COMPANY"."COMPANY_NUMBER"='152')
  18 - access("F_FINANCE_ENTRY"."COMPANY_ID"="D_COMPANY"."COMPANY_ID")
  21 - filter("D_ACCOUNT"."ACCOUNT_GROUP"='Revenue')
  22 - access("F_FINANCE_ENTRY"."ACCOUNT_ID"="D_ACCOUNT"."ACCOUNT_ID")
  23 - filter("D_ACCOUNT"."ACCOUNT_GROUP"='Revenue')
 
Note
-----
   - star transformation used for this statement


Глядя на такие результаты, принял битмап индексы за основу в своих для своих дальнейших попыток усрорить работу отчетов на данном ДВХ. Правда уже успел столкнуться с тем, что данные индексы не особо то помогают ускориться одному своеобразному кластеру отчетов. Но об этом уже напишу в понедельник.
21 авг 15, 21:37    [18053598]     Ответить | Цитировать Сообщить модератору
 Re: Query Cost ниже, а время выполнения выше  [new]
MaximFomenko
Member

Откуда: London, UK
Сообщений: 594
MaximFomenko
с 448 секунд до 45.

Простите, невнятно выразился. После перехода на BITMAP индексы, время выполнения запроса упало с 448 секунд до 45 секунд.
21 авг 15, 21:41    [18053605]     Ответить | Цитировать Сообщить модератору
 Re: Query Cost ниже, а время выполнения выше  [new]
booby
Member

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

Попробуйте заменить в вашем bitmap-ориентированном примере "обычные" bitmap-индексы на
"F_FINANCE_ENTRY"."ENTRY_DATE_ID" и "F_FINANCE_ENTRY"."ACCOUNT_ID"
на bitmap-join индексы.
В целом они для каких-то похожих на вашу историй придумывались.
В вашем конкретном случае выигрыш может не обнаружиться за малостью таблиц "измерений".
21 авг 15, 22:21    [18053709]     Ответить | Цитировать Сообщить модератору
 Re: Query Cost ниже, а время выполнения выше  [new]
MaximFomenko
Member

Откуда: London, UK
Сообщений: 594
booby
MaximFomenko,

Попробуйте заменить в вашем bitmap-ориентированном примере "обычные" bitmap-индексы на
"F_FINANCE_ENTRY"."ENTRY_DATE_ID" и "F_FINANCE_ENTRY"."ACCOUNT_ID"
на bitmap-join индексы.
В целом они для каких-то похожих на вашу историй придумывались.
В вашем конкретном случае выигрыш может не обнаружиться за малостью таблиц "измерений".

Попробовал.

Создал такие констреинты и индексы:
+ BITMAP JOIN INDEXES
ALTER TABLE D_ACCOUNT ADD CONSTRAINT D_ACCOUNT_UNI UNIQUE (ACCOUNT_ID);
ALTER TABLE D_DATE ADD CONSTRAINT D_DATE_UNI UNIQUE (DATE_ID);

CREATE BITMAP INDEX F_FINANCE_ENTRY02 ON F_FINANCE_ENTRY(D_ACCOUNT.ACCOUNT_GROUP)
FROM F_FINANCE_ENTRY, D_ACCOUNT
WHERE F_FINANCE_ENTRY.ACCOUNT_ID = D_ACCOUNT.ACCOUNT_ID; 

CREATE BITMAP INDEX F_FINANCE_ENTRY21 ON F_FINANCE_ENTRY(D_DATE.THE_DATE)
FROM F_FINANCE_ENTRY, D_DATE
WHERE F_FINANCE_ENTRY.ENTRY_DATE_ID = D_DATE.DATE_ID; 


Потом выполнил запрос и получил план с бОльшим костом - 162:
+ EXECUTION PLAN
Plan hash value: 1519090492
 
----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   |    34 |  3876 |   162   (3)| 00:00:02 |
|   1 |  HASH GROUP BY                 |                   |    34 |  3876 |   162   (3)| 00:00:02 |
|*  2 |   HASH JOIN                    |                   |    34 |  3876 |   161   (2)| 00:00:02 |
|   3 |    MERGE JOIN CARTESIAN        |                   |    28 |  2464 |    18   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| D_COMPANY         |     1 |    22 |     1   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | D_COMPANY02       |     1 |       |     1   (0)| 00:00:01 |
|   6 |     BUFFER SORT                |                   |    28 |  1848 |    17   (0)| 00:00:01 |
|*  7 |      TABLE ACCESS FULL         | D_ACCOUNT         |    28 |  1848 |    17   (0)| 00:00:01 |
|   8 |    TABLE ACCESS BY INDEX ROWID | F_FINANCE_ENTRY   |   107K|  2732K|   142   (2)| 00:00:02 |
|   9 |     BITMAP CONVERSION TO ROWIDS|                   |       |       |            |          |
|  10 |      BITMAP AND                |                   |       |       |            |          |
|  11 |       BITMAP MERGE             |                   |       |       |            |          |
|* 12 |        BITMAP INDEX RANGE SCAN | F_FINANCE_ENTRY21 |       |       |            |          |
|* 13 |       BITMAP INDEX SINGLE VALUE| F_FINANCE_ENTRY02 |       |       |            |          |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("D_COMPANY"."COMPANY_ID"="F_FINANCE_ENTRY"."COMPANY_ID" AND 
              "F_FINANCE_ENTRY"."ACCOUNT_ID"="D_ACCOUNT"."ACCOUNT_ID")
   5 - access("D_COMPANY"."COMPANY_NUMBER"='152')
   7 - filter("D_ACCOUNT"."ACCOUNT_GROUP"='Revenue')
  12 - access("F_FINANCE_ENTRY"."SYS_NC00042$">=TO_TIMESTAMP('01-JAN-14') AND 
              "F_FINANCE_ENTRY"."SYS_NC00042$"<=TO_TIMESTAMP('18-AUG-14'))
  13 - access("F_FINANCE_ENTRY"."SYS_NC00041$"='Revenue')


Выборка при этом выполнялась 246 секунд.

Если же я убираю BITMAP JOIN INDEX:

CREATE BITMAP INDEX F_FINANCE_ENTRY21 ON F_FINANCE_ENTRY(D_DATE.THE_DATE)
FROM F_FINANCE_ENTRY, D_DATE
WHERE F_FINANCE_ENTRY.ENTRY_DATE_ID = D_DATE.DATE_ID; 


и возвращаю простой BITMAP INDEX по F_FINANCE_ENTRY.ENTRY_DATE_ID, то получаю план с костом 24:
+
Plan hash value: 1494474463
 
---------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                   |    34 |  3876 |    24   (9)| 00:00:01 |
|   1 |  HASH GROUP BY                      |                   |    34 |  3876 |    24   (9)| 00:00:01 |
|*  2 |   HASH JOIN                         |                   |    34 |  3876 |    23   (5)| 00:00:01 |
|*  3 |    HASH JOIN                        |                   |    34 |  3128 |    22   (5)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL               | D_ACCOUNT         |    28 |  1848 |    17   (0)| 00:00:01 |
|   5 |     TABLE ACCESS BY INDEX ROWID     | F_FINANCE_ENTRY   |   716 | 18616 |     4   (0)| 00:00:01 |
|   6 |      BITMAP CONVERSION TO ROWIDS    |                   |       |       |            |          |
|   7 |       BITMAP AND                    |                   |       |       |            |          |
|   8 |        BITMAP MERGE                 |                   |       |       |            |          |
|   9 |         BITMAP KEY ITERATION        |                   |       |       |            |          |
|  10 |          TABLE ACCESS BY INDEX ROWID| D_DATE            |    94 |  1504 |     1   (0)| 00:00:01 |
|* 11 |           INDEX RANGE SCAN          | D_DATE03          |    94 |       |     1   (0)| 00:00:01 |
|* 12 |          BITMAP INDEX RANGE SCAN    | F_FINANCE_ENTRY21 |       |       |            |          |
|  13 |        BITMAP MERGE                 |                   |       |       |            |          |
|  14 |         BITMAP KEY ITERATION        |                   |       |       |            |          |
|  15 |          TABLE ACCESS BY INDEX ROWID| D_COMPANY         |     1 |    22 |     1   (0)| 00:00:01 |
|* 16 |           INDEX RANGE SCAN          | D_COMPANY02       |     1 |       |     1   (0)| 00:00:01 |
|* 17 |          BITMAP INDEX RANGE SCAN    | F_FINANCE_ENTRY04 |       |       |            |          |
|* 18 |        BITMAP INDEX SINGLE VALUE    | F_FINANCE_ENTRY02 |       |       |            |          |
|  19 |    TABLE ACCESS BY INDEX ROWID      | D_COMPANY         |     1 |    22 |     1   (0)| 00:00:01 |
|* 20 |     INDEX RANGE SCAN                | D_COMPANY02       |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("D_COMPANY"."COMPANY_ID"="F_FINANCE_ENTRY"."COMPANY_ID")
   3 - access("F_FINANCE_ENTRY"."ACCOUNT_ID"="D_ACCOUNT"."ACCOUNT_ID")
   4 - filter("D_ACCOUNT"."ACCOUNT_GROUP"='Revenue')
  11 - access("D_GENERAL_DATE"."THE_DATE">=TO_TIMESTAMP('01-JAN-14') AND 
              "D_GENERAL_DATE"."THE_DATE"<=TO_TIMESTAMP('18-AUG-14'))
  12 - access("F_FINANCE_ENTRY"."ENTRY_DATE_ID"="D_GENERAL_DATE"."DATE_ID")
  16 - access("D_COMPANY"."COMPANY_NUMBER"='152')
  17 - access("F_FINANCE_ENTRY"."COMPANY_ID"="D_COMPANY"."COMPANY_ID")
  18 - access("F_FINANCE_ENTRY"."SYS_NC00041$"='Revenue')
  20 - access("D_COMPANY"."COMPANY_NUMBER"='152')
 
Note
-----
   - star transformation used for this statement


Время выполнения озвучить пока сложно т.к. данные закешировались
22 авг 15, 01:49    [18054106]     Ответить | Цитировать Сообщить модератору
 Re: Query Cost ниже, а время выполнения выше  [new]
booby
Member

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

понятно.
В первом случае напрашивается добавка и bitmap-join индекса по компании тоже.
22 авг 15, 11:34    [18054349]     Ответить | Цитировать Сообщить модератору
 Re: Query Cost ниже, а время выполнения выше  [new]
MaximFomenko
Member

Откуда: London, UK
Сообщений: 594
booby
MaximFomenko,

понятно.
В первом случае напрашивается добавка и bitmap-join индекса по компании тоже.


Попробовал 3 Bitmap join индекса по: ACCOUNT_ID, ENTRY_DATE_ID, COMPANY_ID.

При таком индексе по компании:
CREATE BITMAP INDEX F_FINANCE_ENTRY04
ON F_FINANCE_ENTRY(D_COMPANY.COMPANY_NUMBER)
FROM F_FINANCE_ENTRY, D_COMPANY
WHERE F_FINANCE_ENTRY.COMPANY_ID = D_COMPANY.COMPANY_ID; 


Получился не самый плохой план:

+ COMPANY BITMAP JOIN INDEX v.1
Plan hash value: 3323612024
 
----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   |    34 |  3876 |    38  (11)| 00:00:01 |
|   1 |  HASH GROUP BY                 |                   |    34 |  3876 |    38  (11)| 00:00:01 |
|*  2 |   HASH JOIN                    |                   |    34 |  3876 |    37   (9)| 00:00:01 |
|   3 |    MERGE JOIN CARTESIAN        |                   |    28 |  2464 |    18   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| D_COMPANY         |     1 |    22 |     1   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | D_COMPANY02       |     1 |       |     1   (0)| 00:00:01 |
|   6 |     BUFFER SORT                |                   |    28 |  1848 |    17   (0)| 00:00:01 |
|*  7 |      TABLE ACCESS FULL         | D_ACCOUNT         |    28 |  1848 |    17   (0)| 00:00:01 |
|   8 |    TABLE ACCESS BY INDEX ROWID | F_FINANCE_ENTRY   |   107K|  2732K|    17   (6)| 00:00:01 |
|   9 |     BITMAP CONVERSION TO ROWIDS|                   |       |       |            |          |
|  10 |      BITMAP AND                |                   |       |       |            |          |
|  11 |       BITMAP MERGE             |                   |       |       |            |          |
|* 12 |        BITMAP INDEX RANGE SCAN | F_FINANCE_ENTRY21 |       |       |            |          |
|* 13 |       BITMAP INDEX SINGLE VALUE| F_FINANCE_ENTRY04 |       |       |            |          |
|* 14 |       BITMAP INDEX SINGLE VALUE| F_FINANCE_ENTRY02 |       |       |            |          |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("D_COMPANY"."COMPANY_ID"="F_FINANCE_ENTRY"."COMPANY_ID" AND 
              "F_FINANCE_ENTRY"."ACCOUNT_ID"="D_ACCOUNT"."ACCOUNT_ID")
   5 - access("D_COMPANY"."COMPANY_NUMBER"='152')
   7 - filter("D_ACCOUNT"."ACCOUNT_GROUP"='Revenue')
  12 - access("F_FINANCE_ENTRY"."SYS_NC00043$">=TO_TIMESTAMP('01-JAN-14') AND 
              "F_FINANCE_ENTRY"."SYS_NC00043$"<=TO_TIMESTAMP('18-AUG-14'))
  13 - access("F_FINANCE_ENTRY"."SYS_NC00042$"='152')
  14 - access("F_FINANCE_ENTRY"."SYS_NC00041$"='Revenue')


При слегка доработанном индексе по компании (в ремарке записано сокращенное имя компании, которое все равно всегда используется в отчетах вместе с номером компании):
CREATE BITMAP INDEX F_FINANCE_ENTRY04
ON F_FINANCE_ENTRY(D_COMPANY.COMPANY_NUMBER, D_COMPANY.C_COMPANY_REMARK_1)
FROM F_FINANCE_ENTRY, D_COMPANY
WHERE F_FINANCE_ENTRY.COMPANY_ID = D_COMPANY.COMPANY_ID; 


Получился уже такой план:

+ COMPANY BITMAP JOIN INDEX v.2
Plan hash value: 692846651
 
----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   |    34 |  3876 |    40   (8)| 00:00:01 |
|   1 |  HASH GROUP BY                 |                   |    34 |  3876 |    40   (8)| 00:00:01 |
|*  2 |   HASH JOIN                    |                   |    34 |  3876 |    39   (6)| 00:00:01 |
|   3 |    MERGE JOIN CARTESIAN        |                   |    28 |  2464 |    18   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| D_COMPANY         |     1 |    22 |     1   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | D_COMPANY02       |     1 |       |     1   (0)| 00:00:01 |
|   6 |     BUFFER SORT                |                   |    28 |  1848 |    17   (0)| 00:00:01 |
|*  7 |      TABLE ACCESS FULL         | D_ACCOUNT         |    28 |  1848 |    17   (0)| 00:00:01 |
|   8 |    TABLE ACCESS BY INDEX ROWID | F_FINANCE_ENTRY   |   107K|  2732K|    20   (5)| 00:00:01 |
|   9 |     BITMAP CONVERSION TO ROWIDS|                   |       |       |            |          |
|  10 |      BITMAP AND                |                   |       |       |            |          |
|  11 |       BITMAP MERGE             |                   |       |       |            |          |
|* 12 |        BITMAP INDEX RANGE SCAN | F_FINANCE_ENTRY21 |       |       |            |          |
|  13 |       BITMAP MERGE             |                   |       |       |            |          |
|* 14 |        BITMAP INDEX RANGE SCAN | F_FINANCE_ENTRY04 |       |       |            |          |
|* 15 |       BITMAP INDEX SINGLE VALUE| F_FINANCE_ENTRY02 |       |       |            |          |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("D_COMPANY"."COMPANY_ID"="F_FINANCE_ENTRY"."COMPANY_ID" AND 
              "F_FINANCE_ENTRY"."ACCOUNT_ID"="D_ACCOUNT"."ACCOUNT_ID")
   5 - access("D_COMPANY"."COMPANY_NUMBER"='152')
   7 - filter("D_ACCOUNT"."ACCOUNT_GROUP"='Revenue')
  12 - access("F_FINANCE_ENTRY"."SYS_NC00042$">=TO_TIMESTAMP('01-JAN-14') AND 
              "F_FINANCE_ENTRY"."SYS_NC00042$"<=TO_TIMESTAMP('18-AUG-14'))
  14 - access("F_FINANCE_ENTRY"."SYS_NC00043$"='152')
       filter("F_FINANCE_ENTRY"."SYS_NC00043$"='152')
  15 - access("F_FINANCE_ENTRY"."SYS_NC00041$"='Revenue')


Как видим кост запроса чуть-чуть выше чем было при bitmap join'е только по таблице D_ACCOUNT. Время выполнения тоже на пару секунд выше.
22 авг 15, 13:46    [18054491]     Ответить | Цитировать Сообщить модератору
 Re: Query Cost ниже, а время выполнения выше  [new]
booby
Member

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

а время-то какое? 445, 240, 45?

В обоих случая он продолжает лезть в картезиан от счетов с компаниями, норовя выполнить hash соединение.
т. е. для полного счастья ему все-равно не хватает данных в индексах и нужно доставать поля из компаний и счетов.

За полное счастье в вашем случае может оказаться бороться нерационально.

А вариант отказа от hash join разумно исследовать.

---------------------------------
Наконец добрался до разглядывания сообщенных вами статистик «повнимательнее».
По крайней мере, становится ясно, почему оптимизатор на вашем заполнении настойчиво
предпочитает для поиска образовывать комбинацию (компания, дата),
а не (компания, счет).

Введем алиасы
F_FINANCE_ENTRY – F
D_COMPANY – C
D_ACCOUNT – A
D_DATE – D

Сообщено:
Всего (записей) в F – 61000000
Отбор по диапазону дат (230 значений дат в наборе) – 12800000
Значит, на один ключ в D приходится 12800000/230 ~= 55652 строк в F
Записываем:
F (55652) -> D(1)

Аналогично выводим для F -> A
11300000/28 ~= 403571
(избирательность поиска только по дате примерно на порядок лучше избирательности
поиска только по счету при сопоставимом промежуточном результате)

Поэтому, при последовательном поиске без образования промежуточных комбинаций –
единственная разумная последовательность – начиная с D искать в F, подцепляя затем A, и
затем C через nested loop

Для комбинаций (C, A) и (C,D)
Получаем
F -> (C, D) - оценка- 12800000/261000 ~= 49, 55652/49 ~= 1136
F(1136) -> (C, D)(1)

F – (C, A) – оценка 11300000/161000 ~= 70, 403571/70 ~= 5765
F(5765) -> (C, A)(1)

Таким образом, избирательность комбинации (C, D) оказывается в 5 раз лучше, чем (C, A), что и предопределяет предпочтения oracle.

Если бы (без использования bitmap-индексов) был дешевый способ образовать
комбинацию (дата, счет, компания), то средняя избирательность такого ключа была бы не хуже примерно 25-30 записей в F на одно значение составного ключа.

Несколько слов по поводу того, почему я предлагал именно то, что предлагал.
Идею я подразумевал еще до опубликования Вами статистик заполнения таблиц.

Не было понятно – коэффициент избирательности между между парами (C, D) и (C, A) - 5
или 25. Но из общего представления о том, как вообще могут или не могут вести себя
данные в схожих ситуациях, в качестве рабочей гипотезы представлялось разумным
предположение, что разница будет далека от 230 примерно на порядок или более того.

Далее, деградация избирательности пары (C,A) стабильна и невелика, из вычисленных оценок следует, что не более, чем на 30 записей на каждый новый заведенный в систему день данных.
Если счета доходов не только по количеству штук, но и по количеству хранимых для них фактов составляют меньшинство, не превышающее 5 - 10% от общего объема, то на долгом отрезке времени именно эта пара окажется предпочтительнее для фактических данных. В данном случае – порядка 20% - это уже «многовато будет». Где-то уже на границе равнозначности поиска по любой из обсуждаемых пар.
22 авг 15, 15:15    [18054596]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 2 [3] 4   вперед  Ctrl      все
Все форумы / Oracle Ответить