Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Улучшить производительность WINDOW SORT / 25 сообщений из 38, страница 1 из 2
  1  все
20.06.2016, 16:34
    #39259008
--Eugene--
--Eugene--
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Улучшить производительность WINDOW SORT
Привет

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

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

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

этот план соответствует запросу, который показал Egoр
т.е. - произошел unnest.
Прибейте его.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.06.2016, 18:05
    #39259114
booby
booby
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Улучшить производительность WINDOW SORT
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:24
    #39259128
--Eugene--
--Eugene--
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Улучшить производительность WINDOW SORT
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:32
    #39259133
booby
booby
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Улучшить производительность WINDOW SORT
--Eugene--,

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


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

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

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

2 ТС
Учитывая 10 записей на группу, использование индекса может быть не особо выгодным, так что смотри в сторону keep как было сказано.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.06.2016, 20:30
    #39259185
--Eugene--
--Eugene--
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Улучшить производительность WINDOW SORT
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, 21:29
    #39259201
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Улучшить производительность WINDOW SORT
--Eugene--,

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

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

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

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

Это случайно не твой клон 10971123 ? :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.06.2016, 02:11
    #39259265
booby
booby
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Улучшить производительность WINDOW SORT
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, 03:28
    #39259268
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Улучшить производительность WINDOW SORT
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:32
    #39259270
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Улучшить производительность WINDOW SORT
dbms_photoshopИначе все измышлизмы относительно self join (semi join) мимо кассы.Хотя при индексе по двум полям a, b может дать некоторый профит, да.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.06.2016, 03:41
    #39259271
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Улучшить производительность WINDOW SORT
Только джойн все равно должен быть по rowid.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.06.2016, 07:56
    #39259294
Egoр
Egoр
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Улучшить производительность WINDOW SORT
--Eugene--,

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

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

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

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

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

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

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

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

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

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

С уважением,
Николай
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
  1  все
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Улучшить производительность WINDOW SORT / 25 сообщений из 38, страница 1 из 2
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 532ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1887219]:
 0ms
get settings:
 5ms
get forum list:
 13ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 169ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 67ms
get tp. blocked users:
 1ms
others: 263ms
total:  532ms
x
x
Закрыть


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