Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / EXISTS и FULL SCAN / 7 сообщений из 7, страница 1 из 1
01.12.2016, 09:35
    #39358200
Миша78
Миша78
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
EXISTS и FULL SCAN
Доброго времени суток!

Необходимо создать отчет по клиентам, у которых есть начисления за один период и нет за другой.
Оптимизировал, но уперся в FULL SCAN. Хинт по индексу index(b BILL_CLNTBLTP_I) оптимизатор не видит.
Если хинт поставить в начало, план получается вообще ужасный.

Подскажите пожалуйста, как его побороть?

client_histories - 20 497 667 строк
bills - 160 939 855 строк

Запрос:

Код: plsql
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.
select /*+ parallel(b 8) parallel(ch 8) ORDERED (ch b) USE_NL(b) index(b BILL_CLNTBLTP_I)*/
  ch.clnt_clnt_id,
  ch.name,
  b.bill_date,
  b.summa_all_$
FROM
  bills b,client_histories ch
  where 1=1 
  and ch.clnt_clnt_id=b.clnt_clnt_id
  and b.bill_date > =  (to_date('01.09.2012','dd.mm.yyyy')) and b.bill_date < to_date('01.10.2012','dd.mm.yyyy') 
  and b.summa_all_$ > 0 
  and ch.ctyp_ctyp_id = 3 --прямой договор
  and ch.end_date > sysdate 
  
  and EXISTS
(

  SELECT  /*+ parallel(b1 8) parallel(ch1 8) LEADING (ch1 b1) USE_NL(b) index(ch1 CLNH_CLNT_DATE_I)*/ --1
  ch1.clnt_clnt_id,
  ch1.name,
  b1.bill_date,
  b1.summa_all_$
  FROM

  bills b1,client_histories ch1
  where 1=1 
  and b1.bill_date > =  (to_date('01.08.2012','dd.mm.yyyy')) and b1.bill_date < to_date('01.09.2012','dd.mm.yyyy') 
  and b1.summa_all_$ = 0
  and ch1.ctyp_ctyp_id = 3 --прямой договор
  and ch1.clnt_clnt_id=b1.clnt_clnt_id
  and ch1.end_date > sysdate 
  and ch1.clnt_clnt_id = ch.clnt_clnt_id 
   )



План запроса на картинке во вложении.

С этим планом запроса время выполнения 2.5 минуты.

Спасибо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
01.12.2016, 10:08
    #39358232
Ito
Ito
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
EXISTS и FULL SCAN
1. может NOT exists?
2. может в exists все-таки надо смотреть b1.summa_all_$ > 0, но чтобы таких записей не было (т.е. нет оплаты в период)
или действительно надо смотреть, что за прошлый период оплата есть, но с 0 суммой?
3. если вдруг в каждый момент с клиентом есть только один договор, то client_histories из exists можно бы убрать )
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
01.12.2016, 10:16
    #39358249
Ito
Ito
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
EXISTS и FULL SCAN
Имхо хорошо бы привязку делать по id договора, а не по id клиента.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
01.12.2016, 10:36
    #39358264
SeaGate
SeaGate
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
EXISTS и FULL SCAN
Миша78,

Код: plsql
1.
ORDERED (ch b) USE_NL(b) index(b BILL_CLNTBLTP_I)


ORDERED не допускает указание query_block или чего-то еще.
Пусть будет, если это действительно надо
Код: plsql
1.
LEADING(ch b) USE_NL(b) index(b BILL_CLNTBLTP_I)


Что ожидается от сначала parallel по bills-ам, а потом index по bills-ам, не знаю. ПС вроде угрожал bills-партиционировать много лет назад, но тут по плану таблички не партиционированы, а судя по названию, это индекс на BILLS(CLNT_CLNT_ID, + что-то еще). Таким образом,
либо parallel(b 8), либо use_nl(b) index(b), вместе бессмысленно.

BILLS, наверное, по BILL_DATE бы партиционировали помесячно, так что в parallel index range scan по партициям тут особо смысла не имеет, даже если бы она была партиционирована.

Зачем делать ведущей табличкой открытых клиентов с прямым договором, тоже не знаю, т.к. я так понимаю, прямых договоров и открытых клиентов "много". Если много закрытых клиентов, то можно посмотреть в сторону настройки процессов архивации, избавляющие от этого добра, если это основная биллинговая БД, а не отчетная. Если отчетная, то партиционировать по BILL_DATE, это должен быть популярный фильтр для отчетности.

Вообще, если это весь запрос, я бы посмотрел альтернативу и избавился бы от второго обращения к BILLS/CH.

Что-нибудь вроде:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
select ch.clnt_clnt_id,
          max(ch.name), 
          max(b.bill_date),
          max(b.summa_all_$) keep (dense_rank last order by b.bill_date) summa_all_$
  from bills b, client_histories ch
 where 1=1
   and ch.clnt_clnt_id=b.clnt_clnt_id
   and b.bill_date > =  (to_date('01.08.2012','dd.mm.yyyy')) and b.bill_date < to_date('01.10.2012','dd.mm.yyyy')
   --and b.summa_all_$ > 0 
   and ch.ctyp_ctyp_id = 3 --прямой договор
   and ch.end_date > sysdate 
 group by ch.clnt_clnt_id
 having sum(case when b.bill_date between date '2012-09-01' and date '2012-10-01'-1/86400 then b.summa_all_$ end)>0
    and sum(case when b.bill_date between date '2012-08-01' and date '2012-09-01'-1/86400 then b.summa_all_$ end)=0


2 записи в BILLS на клиента не должно быть, как я помню, и отрицательных summa_all_$ не будет, иначе надо подредактировать.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
01.12.2016, 12:55
    #39358391
ora601
ora601
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
EXISTS и FULL SCAN
Миша78,

А есть вообще индекс на bill_date ? Он должен использоваться в плане. Партиционирование bill_date уже сказали. Матвью на EXISTS тоже можно придумать.

Seagate - переписывание в один запрос может быть и дольше, зависит от количество клиент/договор (т.к. EXISTS срабатывает по первому нахождению).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.12.2016, 10:28
    #39361038
Миша78
Миша78
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
EXISTS и FULL SCAN
Спасибо боьлшое.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.12.2016, 10:45
    #39361049
Миша78
Миша78
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
EXISTS и FULL SCAN
SeaGate, спасибо тебе лично за подробный ответ.
авторORDERED не допускает указание query_block или чего-то еще.

Не очень понял, ты не мог бы пояснить, что-такое query_block? Что ORDERED не допускает?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / EXISTS и FULL SCAN / 7 сообщений из 7, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 588ms
Закрыть
start [/forum/topic.php?fid=52&tablet=1&tid=1886884]:
 0ms
get settings:
 11ms
get forum list:
 20ms
check forum access:
 5ms
check topic access:
 5ms
track hit:
 201ms
get topic data:
 10ms
get forum data:
 3ms
get page messages:
 41ms
get tp. blocked users:
 1ms
others: 291ms
total:  588ms
x
x
Закрыть


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