Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Есть ли разница, в каком месте делать группировку? / 15 сообщений из 15, страница 1 из 1
26.01.2017, 16:53
    #39392295
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Есть ли разница, в каком месте делать группировку?
Подскажите, как сделать оптимальнее?
Есть таблица clients со списком клиентов.
Есть таблица services со списком услуг клиентов (services.client_id = clients.client_id).
Есть таблица charges со списком начислений по услугам (charges.service_id = services.service_id).
Первые две таблицы небольшие (до 10к записей), последняя таблица довольно большая (порядка 500кк).
Мне нужно отобрать услуги, по которым не было начислений за последние три месяца.

Есть ли разница, где делать группировку?

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
select ...
from clients
join services on (services.client_id = clients.client_id)
join (
  select service_id, max(moment) as last_date
  from charges
  group by service_id
) c on (c.service_id = services.service_id)
where c.last_date < sysdate-90



Код: plsql
1.
2.
3.
4.
5.
6.
select clients.client_id, services.service_id, max(charges.moment) as last_date
from clients
join services on (services.client_id = clients.client_id)
join charges on (charges.service_id = services.service_id)
group by clients.client_id, services.service_id
having max(charges.moment) < sysdate-90



________________________
Мы смотрим с оптимизмом...
...в оптический прицел.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.01.2017, 17:11
    #39392314
stax..
stax..
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Есть ли разница, в каком месте делать группировку?
Alibek B.,
Alibek B.,

я б делал через not exists (select 1 from charges c where c.service_id = services.service_id and c.c.last_date < sysdate-90 ...)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.01.2017, 17:14
    #39392317
stax..
stax..
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Есть ли разница, в каком месте делать группировку?
stax..,

ой c.c.last_date >= sysdate-90

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.01.2017, 17:20
    #39392332
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Есть ли разница, в каком месте делать группировку?
Alibek B.Есть ли разница, где делать группировку?Гугли oracle query transformations view merging.
Что мешает посмотреть план?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.01.2017, 19:59
    #39392450
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Есть ли разница, в каком месте делать группировку?
Сервер боевой, я перед выполнением потенциально тяжелых запросов стараюсь предварительно совета спросить.

По поводу not exists — мне нужно помимо самого факта начислений получить ссылку на начисление (идентификатор, сумма), поэтому я и рассматриваю варианты с join. А иначе действительно not exists выглядит более предпочтительным.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.01.2017, 21:05
    #39392502
stax..
stax..
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Есть ли разница, в каком месте делать группировку?
Alibek B.Сервер боевой, я перед выполнением потенциально тяжелых запросов стараюсь предварительно совета спросить.

По поводу not exists — мне нужно помимо самого факта начислений получить ссылку на начисление (идентификатор, сумма), поэтому я и рассматриваю варианты с join. А иначе действительно not exists выглядит более предпочтительным.
другое дело (задача)

лично я не знаю что посоветовать, реально попробовать, не положете ж Вы базу
с одной стороны п2 все по индексах, но чуть больше сртировка
с другой п1 подзапрос (без индексов,) обьедениние может быть и подольше
имхо, пробуйте

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.01.2017, 21:32
    #39392520
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Есть ли разница, в каком месте делать группировку?
stax..,

Станислав, ты в курсе что в CBO есть трансформации запросов и not exists может идти вложенными циклами по индексу, а может быть преобразован в anti join в зависимости от стоимости?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.01.2017, 15:29
    #39393158
stax..
stax..
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Есть ли разница, в каком месте делать группировку?
dbms_photoshopstax..,

Станислав, ты в курсе что в CBO есть трансформации запросов и not exists может идти вложенными циклами по индексу, а может быть преобразован в anti join в зависимости от стоимости?
примерно в курсе, я не всегда вгадую во что он трансформирует

пишу [not] exists мне так более понятен смысл запроса


ps
на счет задачки Alibek B., он уточнил что нужно несколько другое
возможжно есть смысл
,max(case when last_date < sysdate-90 then 1 else 0 end) cc --1-не было начислений, 0-были

....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.01.2017, 13:02
    #39393710
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Есть ли разница, в каком месте делать группировку?
Не удалось придумать нормального решения, чтобы заодно получить ссылку на последнее начисление (сумму, дату, идентификатор).
Пришлось отказаться от этого и обойтись проверкой на exists.
Но все же хотелось бы иметь возможность получить хотя бы список идентификаторов записей, которые попадают под такую проверку, поэтому составил такой запрос:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
select SERVICE_ID, count(*) as CNT
from BM_SERVICE_MONEY
where CHARGE_TYPE_ID = 1
and PERIODIC_ACCT_ID is not null
and MOMENT >= trunc(sysdate)-181
and MONEY > 0
group by SERVICE_ID


Данные возвращаются правильные, но запрос выполняется 3-4 минуты.
explain plan такой:
Код: plaintext
1.
2.
3.
4.
SELECT STATEMENT  ALL_ROWSCost: 87 909  Bytes: 567  Cardinality: 27  			
	3 HASH GROUP BY  Cost: 87 909  Bytes: 567  Cardinality: 27  		
		2 TABLE ACCESS BY INDEX ROWID TABLE BM_SERVICE_MONEY Cost: 87 908  Bytes: 588  Cardinality: 28  	
			1 INDEX RANGE SCAN INDEX SVRM_MOMENT_IDX Cost: 15 172  Cardinality: 4 726 275
Можно ли тут что-нибудь ускорить?
На мой взгляд оптимизировать особо нечего, но может быть я что-то упускаю?

Если сделать просто
Код: plsql
1.
2.
3.
4.
5.
6.
select 1
from BM_SERVICE_MONEY
where CHARGE_TYPE_ID = 1
and PERIODIC_ACCT_ID is not null
and MOMENT >= trunc(sysdate)-181
and MONEY > 0


то запрос выполняется гораздо быстрее (почти моментально), но мне его использовать нужно будет в нескольких местах и при использовании с not exists (select ...) суммарно тоже получается долго.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.01.2017, 13:49
    #39393734
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Есть ли разница, в каком месте делать группировку?
Помогите интерпретировать планы выполнения.
Составил три варианта, все три варианта выполняются примерно одинаковое время (около трех секунд).
Какой из вариантов лучше?

Первый вариант:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
select *
from CUSTOMERS C
where C.STATUS > 0
and exists
(
select 1
from BM_SERVICE_MONEY
where CHARGE_TYPE_ID = 1
and PERIODIC_ACCT_ID is not null
and MOMENT >= trunc(sysdate)-91
and MONEY > 0
and exists (select 1 from SERVICES where SERVICE_ID = BM_SERVICE_MONEY.SERVICE_ID and CUSTOMER_ID = C.CUSTOMER_ID)
)


Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
----------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                  |     1 |    49 |     8  (13)| 00:00:01 |
|   1 |  NESTED LOOPS                   |                  |     1 |    49 |     8  (13)| 00:00:01 |
|   2 |   VIEW                          | VW_SQ_1          |     1 |    13 |     6   (0)| 00:00:01 |
|   3 |    HASH UNIQUE                  |                  |     1 |    31 |            |          |
|   4 |     NESTED LOOPS                |                  |     1 |    31 |     6   (0)| 00:00:01 |
|*  5 |      TABLE ACCESS BY INDEX ROWID| BM_SERVICE_MONEY |     1 |    21 |     5   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN          | SVRM_MOMENT_IDX  |    24 |       |     4   (0)| 00:00:01 |
|   7 |      TABLE ACCESS BY INDEX ROWID| SERVICES         |     1 |    10 |     1   (0)| 00:00:01 |
|*  8 |       INDEX UNIQUE SCAN         | SERVICES_PK      |     1 |       |     0   (0)| 00:00:01 |
|*  9 |   TABLE ACCESS BY INDEX ROWID   | CUSTOMERS        |     1 |    36 |     1   (0)| 00:00:01 |
|* 10 |    INDEX UNIQUE SCAN            | CUSTOMERS_PK     |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - filter("PERIODIC_ACCT_ID" IS NOT NULL AND "MONEY">0 AND "CHARGE_TYPE_ID"=1)
   6 - access("MOMENT">=TRUNC(SYSDATE@!)-91)
   8 - access("SERVICE_ID"="BM_SERVICE_MONEY"."SERVICE_ID")
   9 - filter("C"."STATUS">0)
  10 - access("CUSTOMER_ID"="C"."CUSTOMER_ID")

Второй вариант:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
select *
from CUSTOMERS C
where C.STATUS > 0
and exists
(
select 1
from BM_SERVICE_MONEY
join SERVICES using (SERVICE_ID)
where BM_SERVICE_MONEY.CHARGE_TYPE_ID = 1
and BM_SERVICE_MONEY.PERIODIC_ACCT_ID is not null
and BM_SERVICE_MONEY.MOMENT >= trunc(sysdate)-91
and BM_SERVICE_MONEY.MONEY > 0
and SERVICES.CUSTOMER_ID = C.CUSTOMER_ID
)


Код: plaintext
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.
----------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                  |     1 |    49 |     8  (13)| 00:00:01 |
|   1 |  NESTED LOOPS                   |                  |     1 |    49 |     8  (13)| 00:00:01 |
|   2 |   VIEW                          | VW_SQ_1          |     1 |    13 |     6   (0)| 00:00:01 |
|   3 |    HASH UNIQUE                  |                  |     1 |    31 |            |          |
|   4 |     NESTED LOOPS                |                  |     1 |    31 |     6   (0)| 00:00:01 |
|*  5 |      TABLE ACCESS BY INDEX ROWID| BM_SERVICE_MONEY |     1 |    21 |     5   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN          | SVRM_MOMENT_IDX  |    24 |       |     4   (0)| 00:00:01 |
|   7 |      TABLE ACCESS BY INDEX ROWID| SERVICES         |     1 |    10 |     1   (0)| 00:00:01 |
|*  8 |       INDEX UNIQUE SCAN         | SERVICES_PK      |     1 |       |     0   (0)| 00:00:01 |
|*  9 |   TABLE ACCESS BY INDEX ROWID   | CUSTOMERS        |     1 |    36 |     1   (0)| 00:00:01 |
|* 10 |    INDEX UNIQUE SCAN            | CUSTOMERS_PK     |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - filter("BM_SERVICE_MONEY"."PERIODIC_ACCT_ID" IS NOT NULL AND 
              "BM_SERVICE_MONEY"."MONEY">0 AND "BM_SERVICE_MONEY"."CHARGE_TYPE_ID"=1)
   6 - access("BM_SERVICE_MONEY"."MOMENT">=TRUNC(SYSDATE@!)-91)
   8 - access("BM_SERVICE_MONEY"."SERVICE_ID"="SERVICES"."SERVICE_ID")
   9 - filter("C"."STATUS">0)
  10 - access("CUSTOMER_ID"="C"."CUSTOMER_ID")

Третий вариант:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
select *
from CUSTOMERS C
join SERVICES S on (S.CUSTOMER_ID = C.CUSTOMER_ID)
where C.STATUS > 0
and exists
(
select 1
from BM_SERVICE_MONEY
where CHARGE_TYPE_ID = 1
and PERIODIC_ACCT_ID is not null
and MOMENT >= trunc(sysdate)-91
and MONEY > 0
and SERVICE_ID = S.SERVICE_ID
)


Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
---------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                  |     1 |   207 |     8  (13)| 00:00:01 |
|   1 |  NESTED LOOPS                  |                  |     1 |   207 |     8  (13)| 00:00:01 |
|   2 |   NESTED LOOPS                 |                  |     1 |   171 |     7  (15)| 00:00:01 |
|   3 |    SORT UNIQUE                 |                  |     1 |    21 |     5   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| BM_SERVICE_MONEY |     1 |    21 |     5   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | SVRM_MOMENT_IDX  |    24 |       |     4   (0)| 00:00:01 |
|   6 |    TABLE ACCESS BY INDEX ROWID | SERVICES         |     1 |   150 |     1   (0)| 00:00:01 |
|*  7 |     INDEX UNIQUE SCAN          | SERVICES_PK      |     1 |       |     0   (0)| 00:00:01 |
|*  8 |   TABLE ACCESS BY INDEX ROWID  | CUSTOMERS        |     1 |    36 |     1   (0)| 00:00:01 |
|*  9 |    INDEX UNIQUE SCAN           | CUSTOMERS_PK     |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - filter("PERIODIC_ACCT_ID" IS NOT NULL AND "MONEY">0 AND "CHARGE_TYPE_ID"=1)
   5 - access("MOMENT">=TRUNC(SYSDATE@!)-91)
   7 - access("SERVICE_ID"="S"."SERVICE_ID")
   8 - filter("C"."STATUS">0)
   9 - access("S"."CUSTOMER_ID"="C"."CUSTOMER_ID")

Мне второй вариант более удобен для использования, но зато третий вариант чуть короче.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.01.2017, 10:20
    #39394402
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Есть ли разница, в каком месте делать группировку?
В общем остановился на втором варианте.
В третьем варианте мне потом все равно нужно избавляться от дублирования строк (в CUSTOMERS), так что шило на мыло получается.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.01.2017, 12:16
    #39394506
multidron1
multidron1
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Есть ли разница, в каком месте делать группировку?
первый вариант отработает быстрее. И будет меньше потоков сервера жрать. Так как во втором варианте запрос сначала все что есть вытащит, сцепит и потом только всю массу будет группировать.

В первом же варианте сначала все сгруппирует, а потом вытащит и сцепет.

Если сильно не хочешь грузить сервер, ставь хинт - select /* no parallel */... будет в одном потоке выполнятся. Почти не напряжет сервак, но будет долго отрабатываться.
Ну или можешь поставить select /* parallel(30) */...from, где 30 это количество потоков отработки.
Если не ставить, то по умолчанию берется около 1000 потоков, но работают из них 100-250, остальные в резерве.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.01.2017, 12:29
    #39394515
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Есть ли разница, в каком месте делать группировку?
multidron1Если не ставить, то по умолчанию берется около 1000 потоков, но работают из них 100-250, остальные в резерве.И сколько БД ты, обобщающий наш, успел повидать?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
31.01.2017, 06:42
    #39395088
multidron1
multidron1
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Есть ли разница, в каком месте делать группировку?
Elic, я забыл упомянуть что это по умолчанию. Зачастую в 80% так и стоит. Уже несколько лет занимаюсь тем, что ускоряю и оптимизирую процессы в банках. Например ускорил рассмотрение заявок на выдачу кредитов с 15 минут до 11. Там все БД считает по правилам. Ускорил обновления витрин с 8 часов до 5. В общем еще дохрена чего.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
31.01.2017, 07:41
    #39395105
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Есть ли разница, в каком месте делать группировку?
multidron1это по умолчаниюЗаблуждаешься.
multidron1Уже несколько лет занимаюсь тем, что ускоряю и оптимизирую процессы в банках.Своей пиписюлькой ты не туда пришёл меряться.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Есть ли разница, в каком месте делать группировку? / 15 сообщений из 15, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 476ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1886545]:
 0ms
get settings:
 8ms
get forum list:
 17ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 174ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 43ms
get tp. blocked users:
 1ms
others: 214ms
total:  476ms
x
x
Закрыть


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