ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Есть ли разница, в каком месте делать группировку?
15 сообщений из 15, страница 1 из 1
Есть ли разница, в каком месте делать группировку?
    #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, 16:53
| Ответить | Цитировать | Написать  
Есть ли разница, в каком месте делать группировку?
    #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:11
| Ответить | Цитировать | Написать  
Есть ли разница, в каком месте делать группировку?
    #39392317
stax..
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
stax..
Участник
stax..,

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

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

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

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

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

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

Станислав, ты в курсе что в CBO есть трансформации запросов и not exists может идти вложенными циклами по индексу, а может быть преобразован в anti join в зависимости от стоимости?
...
Рейтинг: 0 / 0
26.01.2017, 21:32
| Ответить | Цитировать | Написать  
Есть ли разница, в каком месте делать группировку?
    #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
27.01.2017, 15:29
| Ответить | Цитировать | Написать  
Есть ли разница, в каком месте делать группировку?
    #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:02
| Ответить | Цитировать | Написать  
Есть ли разница, в каком месте делать группировку?
    #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
28.01.2017, 13:49
| Ответить | Цитировать | Написать  
Есть ли разница, в каком месте делать группировку?
    #39394402
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
В общем остановился на втором варианте.
В третьем варианте мне потом все равно нужно избавляться от дублирования строк (в CUSTOMERS), так что шило на мыло получается.
...
Рейтинг: 0 / 0
30.01.2017, 10:20
| Ответить | Цитировать | Написать  
Есть ли разница, в каком месте делать группировку?
    #39394506
multidron1
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
multidron1
Гость
первый вариант отработает быстрее. И будет меньше потоков сервера жрать. Так как во втором варианте запрос сначала все что есть вытащит, сцепит и потом только всю массу будет группировать.

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

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


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