ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Улучшить производительность WINDOW SORT
25 сообщений из 38, страница 1 из 2
  1  все
Улучшить производительность WINDOW SORT
    #39259008
Фотография --Eugene--
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
--Eugene--
Участник
Привет

Есть табличка
Код: plsql
1.
2.
3.
4.
create table t1 as
select trunc(level/10) a, dbms_random.value b,
(тут еще куча колонок)
from dual connect by level <= дохрена


И есть запросик
Код: plsql
1.
2.
3.
select *
from (select max(b) over (partition by a) max_b, t1.* from t1) x
where b = max_b


Подскажите, пожалуйста, как ускорить запрос? Например, чтобы как-то маркировать строки с максимальным значением b over partition by a..

Спасибо
...
Рейтинг: 0 / 0
20.06.2016, 16:34
| Ответить | Цитировать | Написать  
Улучшить производительность WINDOW SORT
    #39259026
Relict_35
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Relict_35
Участник
посмотрите на
Код: plsql
1.
max(b) keep (dens_rank...
...
Рейтинг: 0 / 0
20.06.2016, 16:46
| Ответить | Цитировать | Написать  
Улучшить производительность WINDOW SORT
    #39259034
Фотография --Eugene--
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
--Eugene--
Участник
Relict_35,

а как быть с "кучей колонок" в случае keep?
...
Рейтинг: 0 / 0
20.06.2016, 16:50
| Ответить | Цитировать | Написать  
Улучшить производительность WINDOW SORT
    #39259039
Relict_35
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Relict_35
Участник
так же через KEEP
...
Рейтинг: 0 / 0
20.06.2016, 16:54
| Ответить | Цитировать | Написать  
Улучшить производительность WINDOW SORT
    #39259043
Фотография Egoр
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Egoр
Участник
--Eugene--,

Код: plsql
1.
select * from t1 where (a,b) in (select a, max(b) from t1 group by a)
...
Рейтинг: 0 / 0
20.06.2016, 16:59
| Ответить | Цитировать | Написать  
Улучшить производительность WINDOW SORT
    #39259058
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
--Eugene--,

"улучшить производительность WINDOW SORT" есть два способа:

1) пытаться превратить его в WINDOW BUFFER путем создания подходящего индекса.
в твоем случае, вероятно по (a, b), м.б. с учетом доп полей от прочих предикатов в условии запроса.

2) заменить его на aggregation/group by
запрос окажется зависимым от того, какой смысл ты вкладываешь в термин "улучшить производительность".
Например, если тебя интересует в первую очередь скорость выдачи первых строк и есть "подходящий индекс" по (a, b)
в наличии, то попробуй вариант.

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
select t1.b as max_b, t1.*
from t1
Where 
  t1.b = (
     Select Max(b)
     From t1 t2
     Where t2.a = t1.a
)
...
Рейтинг: 0 / 0
20.06.2016, 17:10
| Ответить | Цитировать | Написать  
Улучшить производительность WINDOW SORT
    #39259088
Фотография --Eugene--
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
--Eugene--
Участник
booby,

допустим, есть индекс
Код: plsql
1.
create index t1_i1 on t1(a, b)

но в Вашем запросе он всеравно не задействуется (судя по плану)
 
Код: 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.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
SANDBOX@EE>;create index t1_i1 on t1(a, b);

Index created.

explain plan for
  2  select t1.b as max_b, t1.*
  3  from t1
  4  Where
  5    t1.b = (
  6       Select Max(b)
  7       From t1 t2
  8       Where t2.a = t1.a
  9  );

Explained.

Elapsed: 00:00:00.16
SANDBOX@EE>;select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 2573525648

----------------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |     1 |    65 |       |  8006   (2)| 00:01:37 |
|*  1 |  HASH JOIN           |         |     1 |    65 |    45M|  8006   (2)| 00:01:37 |
|   2 |   VIEW               | VW_SQ_1 |  1259K|    31M|       |  1376   (8)| 00:00:17 |
|   3 |    HASH GROUP BY     |         |  1259K|    31M|       |  1376   (8)| 00:00:17 |
|   4 |     TABLE ACCESS FULL| T1      |  1259K|    31M|       |  1294   (2)| 00:00:16 |
|   5 |   TABLE ACCESS FULL  | T1      |  1259K|    46M|       |  1296   (2)| 00:00:16 |
----------------------------------------------------------------------------------------

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

   1 - access("T1"."B"="MAX(B)" AND "ITEM_1"="T1"."A")

Note
-----
   - dynamic sampling used for this statement (level=2)

21 rows selected.
...
Рейтинг: 0 / 0
20.06.2016, 17:39
| Ответить | Цитировать | Написать  
Улучшить производительность WINDOW SORT
    #39259100
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
--Eugene--,

этот план соответствует запросу, который показал Egoр
т.е. - произошел unnest.
Прибейте его.
...
Рейтинг: 0 / 0
20.06.2016, 17:51
| Ответить | Цитировать | Написать  
Улучшить производительность WINDOW SORT
    #39259114
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
booby--Eugene--,

этот план соответствует запросу, который показал Egoр
т.е. - произошел unnest.
Прибейте его.
По дороге поправьте Max(b) на Max(t2.b)

кроме того, проверьте вариант
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
select t1.b as max_b, t1.*
from t1
Where 
  Exists(
     Select null
     From t1 t2
     Where t2.a = t1.a
     Group By t2.a 
     Having Max(t2.b) = t1.b
)
...
Рейтинг: 0 / 0
20.06.2016, 18:05
| Ответить | Цитировать | Написать  
Улучшить производительность WINDOW SORT
    #39259128
Фотография --Eugene--
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
--Eugene--
Участник
booby,

да, работает быстро (для первых строк)
но почему в плане такая заоблачная стоимость? - это она для полного фетча чтоли?
 
Код: 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.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
SANDBOX@EE>;explain plan for
  2  select /*+ first_rows(100) */ t1.b as max_b, t1.*
  3  from t1
  4  Where
  5    Exists(
  6       Select null
  7       From t1 t2
  8       Where t2.a = t1.a
  9       Group By t2.a
 10       Having Max(t2.b) = t1.b
 11  );

Explained.

Elapsed: 00:00:00.01
SANDBOX@EE>;select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3006482294

--------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |     1 |    29 |  2995K  (1)| 09:59:08 |
|*  1 |  FILTER                |       |       |       |            |          |
|   2 |   TABLE ACCESS FULL    | T1    |   999K|    27M|  1305   (2)| 00:00:16 |
|*  3 |   FILTER               |       |       |       |            |          |
|   4 |    SORT GROUP BY NOSORT|       |     1 |    26 |     3   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN   | T1_I1 |     5 |   130 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT 0 FROM "T1" "T2" WHERE "T2"."A"=:B1 GROUP
              BY "T2"."A" HAVING MAX("T2"."B")=:B2))
   3 - filter(MAX("T2"."B")=:B1)
   5 - access("T2"."A"=:B1)

20 rows selected.
...
Рейтинг: 0 / 0
20.06.2016, 18:24
| Ответить | Цитировать | Написать  
Улучшить производительность WINDOW SORT
    #39259133
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
--Eugene--,

ну да. годится это или нет - зависит того, что на самом деле вас интересует.
а для "полного фетча" без join толку не будет.
просто заставьте его бежать по индексу.


поскольку сейчас вы про вакуум спрашиваете, то, с некоторой вероятностью, за прокидыванием предикатов в тот join
в вашем настоящем запросе, может быть придется глазками следить.
...
Рейтинг: 0 / 0
20.06.2016, 18:32
| Ответить | Цитировать | Написать  
Улучшить производительность WINDOW SORT
    #39259146
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
--Eugene--
Код: plsql
1.
trunc(level/10) a

то есть выбираете минимум 1/10 часть таблицы?
что показывает
Код: plsql
1.
select count(distinct a)/count(*) x from dual

?
...
Рейтинг: 0 / 0
20.06.2016, 18:54
| Ответить | Цитировать | Написать  
Улучшить производительность WINDOW SORT
    #39259159
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
boobyпытаться превратить его в WINDOW BUFFER путем создания подходящего индексаВ WINDOW BUFFER оно превратится путем создания неподходяшего индекса, с подходящим индексом можно добиться WINDOW NOSORT.

2 ТС
Учитывая 10 записей на группу, использование индекса может быть не особо выгодным, так что смотри в сторону keep как было сказано.
...
Рейтинг: 0 / 0
20.06.2016, 19:09
| Ответить | Цитировать | Написать  
Улучшить производительность WINDOW SORT
    #39259185
Фотография --Eugene--
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
--Eugene--
Участник
xtender,

Код: plaintext
1.
2.
3.
SANDBOX@EE>;select count(distinct a)/count(1) from t1;
COUNT(DISTINCTA)/COUNT(1)
-------------------------
                 .1000001
...
Рейтинг: 0 / 0
20.06.2016, 20:30
| Ответить | Цитировать | Написать  
Улучшить производительность WINDOW SORT
    #39259201
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
--Eugene--,

а размеры сегментов таблицы и индекса на (a,b) какие? Параллелить можно?
...
Рейтинг: 0 / 0
20.06.2016, 21:29
| Ответить | Цитировать | Написать  
Улучшить производительность WINDOW SORT
    #39259205
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
--Eugene--,

ну и clustering_factor индекса тоже покажи. впрочем, лучше все статистики покажи
...
Рейтинг: 0 / 0
20.06.2016, 21:35
| Ответить | Цитировать | Написать  
Улучшить производительность WINDOW SORT
    #39259236
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
dbms_photoshopboobyпытаться превратить его в WINDOW BUFFER путем создания подходящего индексаВ WINDOW BUFFER оно превратится путем создания неподходяшего индекса, с подходящим индексом можно добиться WINDOW NOSORT.

это вполне пожалуйста.

dbms_photoshop2 ТС
Учитывая 10 записей на группу, использование индекса может быть не особо выгодным, так что смотри в сторону keep как было сказано.
ппц...
надували бы свой хадуп тщательнЕе.
глядишь, веселее получится.
...
Рейтинг: 0 / 0
20.06.2016, 23:08
| Ответить | Цитировать | Написать  
Улучшить производительность WINDOW SORT
    #39259244
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
booby,

Это случайно не твой клон 10971123 ? :)
...
Рейтинг: 0 / 0
20.06.2016, 23:28
| Ответить | Цитировать | Написать  
Улучшить производительность WINDOW SORT
    #39259265
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
dbms_photoshopbooby,

Это случайно не твой клон 10971123 ? :)

вот чем пытаться кидаться скрытыми обвинениями в хамстве, лучше расскажите:
вы что, правда верите, что в реальной таблице все a рядком друг к другу и по порядку окажутся, прямо как здесь написано:
Код: plsql
1.
2.
3.
4.
create table t1 as
select trunc(level/10) a, dbms_random.value b,
(тут еще куча колонок)
from dual connect by level <= дохрена


?
Как и в то, что пара (a, b) окажется "почти уникальной"?
Имхо, это не из той истории нам картинки пытаются нарисовать.
(А для случая уникальной пары (a, b) так и запросы иначе пишутся.
И keep там как раз вполне годным имеет право быть)

Ладно, возьмем за базу 10 записей на группу.
На сколько-нибудь близком к правдоподобному распределении данных,
вы на какой LIO рассчитываете в среднем на возвращенную строку, предлагая "глядеть" на keep? Блоков 10 на запись? Или поболее?

Креатифф - оно шибко зашибись, но очень скушен, когда представляет собой откровенную глупость.
Сам-то я не берусь предложить что-либо креативнее фильтра по индексу для затравки разговора . Но даже у этого креатива не так много шансов выскочить за 4-6 блоков на возвращенную строку на предполагаемых характеристиках таблицы.
А уж куда, казалось бы, креативнее.

Как-то так про клонов.
...
Рейтинг: 0 / 0
21.06.2016, 02:11
| Ответить | Цитировать | Написать  
Улучшить производительность WINDOW SORT
    #39259268
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
booby,

Основных подхода два - WINDOW NOSORT + INDEX FULL SCAN vs SORT GROUP BY + TABLE ACCESS FULL.
В любом случае целиком сканируется либо сегмент индекса либо сегмент таблицы.
Сегмент индекса чуть больше, но при его сканировании можно избежать сортировки (кроме того больше вероятность что он будет в кеше).
На сортировке в этом примере особо не выиграешь независимо от кол-ва b в рамках a.
Так что скорее всего полное сканирование таблицы с группировкой наиболее предпочтительный варинат (можно еще добавить параллельность).
При других особенностях данных могла бы быть быстрее аналитика. Особенно если есть толстое поле, которое не нужно в результате и в индексе.
Если заранее известны какие-то особенности данных, например, множество значений a, то можно было бы мудрить другие варианты.
Иначе все измышлизмы относительно self join (semi join) мимо кассы.
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
drop table xt;

create table xt(a, b not null,x1,x2,x3) as
select trunc(level/1e5) a, dbms_random.value b,
rownum x1, rownum x2, rownum x3
from dual connect by level <= 2e6-1;

create index xt_i on xt(a,b desc,x1,x2,x3);

select *
  from (select xt.*, rank() over(partition by a order by b desc) rn
          from xt)
 where rn = 1;

select a,
       max(b) b,
       max(x1) keep(dense_rank first order by b desc) x1,
       max(x2) keep(dense_rank first order by b desc) x2,
       max(x3) keep(dense_rank first order by b desc) x3
  from xt
 group by a;

...
Рейтинг: 0 / 0
21.06.2016, 03:28
| Ответить | Цитировать | Написать  
Улучшить производительность WINDOW SORT
    #39259270
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
dbms_photoshopИначе все измышлизмы относительно self join (semi join) мимо кассы.Хотя при индексе по двум полям a, b может дать некоторый профит, да.
...
Рейтинг: 0 / 0
21.06.2016, 03:32
| Ответить | Цитировать | Написать  
Улучшить производительность WINDOW SORT
    #39259271
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
Только джойн все равно должен быть по rowid.
...
Рейтинг: 0 / 0
21.06.2016, 03:41
| Ответить | Цитировать | Написать  
Улучшить производительность WINDOW SORT
    #39259294
Фотография Egoр
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Egoр
Участник
--Eugene--,

Ну, раз пошла такая пьянка, то оптимизация вашей задачи детально разбирается у Кайта Best performance of Top N by X .
Там есть и про нахождение всех уникальных t1.а и про нахождение top N t1.b по каждому t1.a. В вашем случае top N - это top 1.
Сам этим пользуюсь, т.к. совершенно незаменимая операция при анализе динамики.
...
Рейтинг: 0 / 0
21.06.2016, 07:56
| Ответить | Цитировать | Написать  
Улучшить производительность WINDOW SORT
    #39259361
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
dbms_photoshop,

create index xt_i on xt(a,b... шурум-брум);

вот-вот.
такой шурум-бурум как форму борьбы с вредоносным lio встретить-то можно.
но вы же советуете keep с серьезной формой лица, без оговорок и не зная какого размера покрывающий индекс потребуется в конкретном случае.
вы уш как-нибудь поосторожнее с советами.
...
Рейтинг: 0 / 0
21.06.2016, 09:58
| Ответить | Цитировать | Написать  
Улучшить производительность WINDOW SORT
    #39259917
Nikolay Savvinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Nikolay Savvinov
Гость
--Eugene--,

Та задача, которую вы пытаетесь решить, встречается весьма часто, и правильного решения в рамках СУБД Oracle, к сожалению, не имеет.

Как правило она связана с моделями данных, имеющих временной разрез (т.н. темпоральными данными), когда описывается состояние объекта во времени (и часто встречаются уникальные/первичные ключи типа (ID, TSTAMP) или (ID, VERSION). Для таких моделей очень популярен вид запросов "дай мне состояние данных на такой-то момент" или "покажи мне последний срез данных", которые реализуются как раз через подобного рода аналитические функции.

Решение это и само по себе не очень эффективно, а уж если задача усложняется, например, хронологическим секционированием таблицы, или соединением нескольких таблиц подобной структуры, то тогда уж совсем плохо.

Все известные мне решения (а я копал на эту тему достаточно давно и долго, и даже как-то Тома Кайта доставал на какой-то конференции этим вопросом) достаточно кривые, но для каких-то конкретных случаев работают workaroundы, например:

1) поддерживать избыточную таблицу с последним срезом данных (b = max_b в вашем примере)
2) добавить флажок, который идентифицирует эти данные, и добавить логику, которая обновляла бы его значения при DML в триггер на вставку.

По понятным причинам эти workaroundы не столько решают проблему, сколько перекладывают ее с этапа опрашивания данных в этап их сохранения или модификации.

По идее, существуют темпоральные (и даже битемпоральные) СУБД в которых такие запросы должны и кодироваться легко, и выполняться влет. Oracle к их числу, к сожалению, не относится. В 12й версии был сделан некий шаг в этом направлении (см. например "temporal validity"), но это по сути только компактный синтаксис, внутри все осталось примерно то же самое поэтому с производительностью все плохо.

С уважением,
Николай
...
Рейтинг: 0 / 0
21.06.2016, 20:05
| Ответить | Цитировать | Написать  
25 сообщений из 38, страница 1 из 2
  1  все
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Улучшить производительность WINDOW SORT
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=39259244&tid=1887219]:
 0ms
get settings:
 11ms
get forum list:
 21ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 198ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 78ms
get tp. blocked users:
 2ms
others: 246ms
total:  579ms
созд. / загр.: 579ms
x
x
Закрыть


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