ReSQL.ru
     
powered by simpleCommunicator - 2.0.44     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / оптимизация запроса с GROUP BY clause
25 сообщений из 28, страница 1 из 2
  1  все
оптимизация запроса с GROUP BY clause
    #32058745
Saha
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Saha
Гость
Привет всем!

кто-нибудь умеет оптимизировать запросы с GROUP BY clause?

Например заставить их использовать индексы, построенные по группируемым полям, или группировать записи без сортировки (т.е. внутренний селект делает нужный ORDER BY, а внешний тупо группирует то, что выдал внутренний).
может оракловые хинты какие-нить есть?
...
Рейтинг: 0 / 0
16.10.2002, 13:30
| Ответить | Цитировать | Написать  
оптимизация запроса с GROUP BY clause
    #32058755
ora600
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ora600
Участник
А поконкретнее ?
Может быть, нужно select ... from (select ... from ... group by ...) order by ... ?
...
Рейтинг: 0 / 0
16.10.2002, 13:53
| Ответить | Цитировать | Написать  
оптимизация запроса с GROUP BY clause
    #32058759
.dba
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
.dba
Участник
неправильная постановка вопроса - любой запрос надо (или можно) оптимизировать независимо от наличия group by. На этапе группировки данные уже выбраны из таблиц, поэтому оптимизировать план доступа к данным на этом этапе смысла не имеет (возможно там нужен FTS). Поэтому надо оптимизировать либо сортировку (sort_area_size, temporary segment и т.д.) либо FTS, если его нельзя избежать.
...
Рейтинг: 0 / 0
16.10.2002, 13:56
| Ответить | Цитировать | Написать  
оптимизация запроса с GROUP BY clause
    #32058764
Saha
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Saha
Гость
поконкретнее:
есть запрос с group by по нескольким полям.
есть индекс на эти поля. но он почему-то не используется :(


как одно из решений хорошо подошла бы "тупая" группировка (если такая вообще есть)

select /*+ some-hint-to-group-without-sortig */
a, b, c, count(*)
from (
select a, b, c
from ent_table
order by a, b, c
)
group by a, b, c
...
Рейтинг: 0 / 0
16.10.2002, 13:59
| Ответить | Цитировать | Написать  
оптимизация запроса с GROUP BY clause
    #32058766
Saha
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Saha
Гость
что такое FTS?
и как научить sort group by использовать индекс?
...
Рейтинг: 0 / 0
16.10.2002, 14:02
| Ответить | Цитировать | Написать  
оптимизация запроса с GROUP BY clause
    #32058780
.dba
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
.dba
Участник
select a, b, c, count(*)
from ent_table
group by a, b, c

вот и все (зачем тут вообще подзапрос и явная сортировка?). Как раз случай FTS - full table scan. Так что никакие индексы тут не помогут. Как оптимизировать - смотри мой постинг выше.
...
Рейтинг: 0 / 0
16.10.2002, 14:30
| Ответить | Цитировать | Написать  
оптимизация запроса с GROUP BY clause
    #32058784
Saha
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Saha
Гость
select a, b, c, count(*)
from ent_table
group by a, b, c

план запроса:

SELECT STATEMENT, GOAL = CHOOSE
+--SORT GROUP BY
+--+--TABLE ACCESS FULL ent_table

дело в том, что табилца очень большая и сортировка для group by происходит очень долго. почему эта самая сортировка не умеет использовать индекс на a, b, c ?
...
Рейтинг: 0 / 0
16.10.2002, 14:37
| Ответить | Цитировать | Написать  
оптимизация запроса с GROUP BY clause
    #32058787
.dba
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
.dba
Участник
Подумай сам - сколько строк в таблице тебе надо прочитать, чтобы составить любую группу по этой таблице - очевидно, что ВСЕ строки. Как же тебе в этом помогут индексы?
...
Рейтинг: 0 / 0
16.10.2002, 14:45
| Ответить | Цитировать | Написать  
оптимизация запроса с GROUP BY clause
    #32058790
ora600
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ora600
Участник
А колонки - это именно колонки, или какие-то функции от колонок ? А колонки в индексе в том же порядке, как и в груп бае? Вообще-то, должен использоваться index full scan.
Ну или пробуйте
select /*+ rule*/
select /*+ index(<table или alias> <index>)*/
...
Рейтинг: 0 / 0
16.10.2002, 14:53
| Ответить | Цитировать | Написать  
оптимизация запроса с GROUP BY clause
    #32058794
Saha
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Saha
Гость
.dba, если есть индекс по группируемым полям, то в теории всю таблицу прочитывать не надо. Например, что б в телефонной книге "сгруппировать" всех Ивановых (например count(*) для них посчитать) вовсе не обязательно всю книгу прочитать, достаточно открыть ее там откуда начинаются Ивановы и читать пока они не закончатся.

ora600, я тоже всегда думал, что используется! оказывается нет :(
Ваши хинты не помогли...
...
Рейтинг: 0 / 0
16.10.2002, 15:03
| Ответить | Цитировать | Написать  
оптимизация запроса с GROUP BY clause
    #32058796
Saha
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Saha
Гость
ой, забыл.

Ора, да. это действительно реальные колонки. и на таблиице по ним построен индекс (в том же количестве и порядке)
...
Рейтинг: 0 / 0
16.10.2002, 15:05
| Ответить | Цитировать | Написать  
оптимизация запроса с GROUP BY clause
    #32058800
.dba
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
.dba
Участник
>.dba, если есть индекс по группируемым полям, то в
>теории всю таблицу прочитывать не надо. Например, что
>б в телефонной книге "сгруппировать" всех Ивановых
>(например count(*) для них посчитать) вовсе не
>обязательно всю книгу прочитать, достаточно открыть ее
>там откуда начинаются Ивановы и читать пока они не
>закончатся.

Ну вот вы сами себе и ответили :-). Для того чтоб использовался индекс надо сказать
Код: plaintext
where name= 'Иванов'
. А вы все-таки видно хотите посчитать не только Ивановых.
...
Рейтинг: 0 / 0
16.10.2002, 15:09
| Ответить | Цитировать | Написать  
оптимизация запроса с GROUP BY clause
    #32058803
Фотография killed
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
killed
Участник
а где у вас в запросе сказано, что сгруппировать всех Ивановых?

По-моему, скорее всех "Сидоровых, Петровых, Ивановых и прочих кого найдешь".
У вас индекс построен как (a, b, c) ? И если так, то сколько еще есть других полей в таблице, которые не используются в данном запросе?
...
Рейтинг: 0 / 0
16.10.2002, 15:11
| Ответить | Цитировать | Написать  
оптимизация запроса с GROUP BY clause
    #32058806
Saha
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Saha
Гость
дба, ивановы это для примера того, как при группировке можно использовать индекс. то что в моем слечае full scan по таблице пройдет это я прекрасно понимаю. Но full scan и сортировка для группирования по времени довольно сильно отличаются. опять же на примере тел. книги: что б посчитать count(*) для всех фамилий вым нужно будет прочитать всю книгу один раз листая страницы по порядку (фамиилии же упорядочены), а что б посчитать count(*) по той же книге, но для названий улиц у вас уйдет на порядок больше времени...

киллд, да индекс именно на (a, b, c) кроме них в таблице еще 2 поля.

мне все больше начинает казаться, что при группировке индекс не используется в принципе :(
...
Рейтинг: 0 / 0
16.10.2002, 15:23
| Ответить | Цитировать | Написать  
оптимизация запроса с GROUP BY clause
    #32058811
ora600
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ora600
Участник
Эти колонки NOT NULL ?
...
Рейтинг: 0 / 0
16.10.2002, 15:30
| Ответить | Цитировать | Написать  
оптимизация запроса с GROUP BY clause
    #32058812
.dba
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
.dba
Участник
>то что в моем слечае full scan по таблице пройдет это я
>прекрасно понимаю.

слава богу, так о чем тогда спор? Долго выполняется - оптимизируйте сортировки в базе и full сканы.

>киллд, да индекс именно на (a, b, c) кроме них в таблице
>еще 2 поля.

это вообще роли не играет, сколько там еще полей.

>мне все больше начинает казаться, что при группировке
>индекс не используется в принципе :(

наконец-то.
...
Рейтинг: 0 / 0
16.10.2002, 15:32
| Ответить | Цитировать | Написать  
оптимизация запроса с GROUP BY clause
    #32058813
Vlad_P
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vlad_P
Участник
Действительно, если придется считывать все строки из таблицы, то Oracle будет использовать FullScan.
Но, если у вас
1 поля a,b,c включены в индекс,
2 этот индекс начинается именно с них, например с поля a
3 в данной таблице существуют еще несколько доп.полей, которые "раздувают" одну строку,
то, конечно, лучше заставить оптимизатор работать только с индексным файлом.
Для этого можно либо включить хинт на использование этого индекса, либо поставить пустое по смыслу условие,
например, where a>0, при котором будут выбираться все записи, но включается в работу индекс.
...
Рейтинг: 0 / 0
16.10.2002, 15:35
| Ответить | Цитировать | Написать  
оптимизация запроса с GROUP BY clause
    #32058814
Фотография killed
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
killed
Участник
не используется за исключением случая "покрывающего индекса". По сути индекс будет играть роль такого же источника данных как и таблица. Разница может быть за счет меньшего размера и сортировки. Какое нибудь из этих полей (a, b, c) определено как is not null ?
...
Рейтинг: 0 / 0
16.10.2002, 15:35
| Ответить | Цитировать | Написать  
оптимизация запроса с GROUP BY clause
    #32058818
Saha
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Saha
Гость
> оптимизируйте сортировки в базе
именно этого я и хочу!
но как? единственное, что есть для оптимизации сортировки - индекс!
...
Рейтинг: 0 / 0
16.10.2002, 15:37
| Ответить | Цитировать | Написать  
оптимизация запроса с GROUP BY clause
    #32058824
.dba
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
.dba
Участник
>именно этого я и хочу!
>но как? единственное, что есть для оптимизации
>сортировки - индекс!

1. Где происходят сотртировки на диске или в памяти?
2. Если на диске, то нельзя ли их делать в памяти (sort_area_size)?
3. Если нельзя, то оптимизирован ли temporary tablespace (sort_multiblock_read_count и пр.)?
4. Что занимает больше времени FTS или сортировка?
5. Если FTS, то нельзя ли оптимизировать чтение с диска (db_file_multiblock_read_count)?

вот в 2-х словах.
...
Рейтинг: 0 / 0
16.10.2002, 15:44
| Ответить | Цитировать | Написать  
оптимизация запроса с GROUP BY clause
    #32058868
.dba
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
.dba
Участник
>Но, если у вас
>... поля a,b,c включены в индекс,

Хм... по здравому размышлению должен признать, что был не прав, утверждая, что индекс при группировке никогда не будет использоваться.

Вот сделал такой пример:

Код: 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.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
SQL> create table t as select * from all_objects;

Tabelle wurde angelegt.

SQL> create index ti on t(owner, object_type);

Index wurde angelegt.

SQL> analyze table t compute statistics;

Tabelle wurde analysiert.

SQL> analyze index ti compute statistics;

Index wurde analysiert.

SQL> select owner, object_type, count(*) from t group by owner, object_type;

OWNER                          OBJECT_TYPE          COUNT(*)
 ------------------------------ ------------------ ----------
 
PUBLIC                         SYNONYM                    926 
SYS                            CONSUMER GROUP               2 
SYS                            DIRECTORY                    1 
SYS                            FUNCTION                    20 
SYS                            PACKAGE                     75 
SYS                            PROCEDURE                    9 
SYS                            TABLE                        6 
SYS                            TYPE                        30 
SYS                            VIEW                       442 
SYSTEM                         VIEW                         1 
TEKAMON                        TABLE                        1 
TEST                           FUNCTION                     2 
TEST                           PROCEDURE                    1 
TEST                           SEQUENCE                     1 
TEST                           TABLE                        3 
TEST                           VIEW                         1 

 16  Zeilen ausgewõhlt.

Abgelaufen:  00 : 00 : 00 . 01 

Ausf³hrungsplan
 ----------------------------------------------------------
 
    0       SELECT STATEMENT Optimizer=CHOOSE (Cost= 7  Card= 36  Bytes= 2016 
          )

    1      0    SORT (GROUP BY NOSORT) (Cost= 7  Card= 36  Bytes= 2016 )
    2      1      INDEX (FULL SCAN) OF 'TI' (NON-UNIQUE) (Cost= 7  Card= 1521 
           Bytes= 85176 )


Statistiken
 ----------------------------------------------------------
 
           0   recursive calls
           0   db block gets
           8   consistent gets
           0   physical reads
           0   redo size
        1110   bytes sent via SQL*Net to client
         532   bytes received via SQL*Net from client
           3   SQL*Net roundtrips to/from client
           0   sorts (memory)
           0   sorts (disk)
          16   rows processed

SQL> select  /*+ RULE */ owner, object_type, count(*) from t group by owner, object_type;

OWNER                          OBJECT_TYPE          COUNT(*)
 ------------------------------ ------------------ ----------
 
PUBLIC                         SYNONYM                    926 
SYS                            CONSUMER GROUP               2 
SYS                            DIRECTORY                    1 
SYS                            FUNCTION                    20 
SYS                            PACKAGE                     75 
SYS                            PROCEDURE                    9 
SYS                            TABLE                        6 
SYS                            TYPE                        30 
SYS                            VIEW                       442 
SYSTEM                         VIEW                         1 
TEKAMON                        TABLE                        1 
TEST                           FUNCTION                     2 
TEST                           PROCEDURE                    1 
TEST                           SEQUENCE                     1 
TEST                           TABLE                        3 
TEST                           VIEW                         1 

 16  Zeilen ausgewõhlt.

Abgelaufen:  00 : 00 : 00 . 81 

Ausf³hrungsplan
 ----------------------------------------------------------
 
    0       SELECT STATEMENT Optimizer=HINT: RULE
    1      0    SORT (GROUP BY)
    2      1      TABLE ACCESS (FULL) OF 'T'


Statistiken
 ----------------------------------------------------------
 
           0   recursive calls
          12   db block gets
          23   consistent gets
           0   physical reads
           0   redo size
        1110   bytes sent via SQL*Net to client
         532   bytes received via SQL*Net from client
           3   SQL*Net roundtrips to/from client
           1   sorts (memory)
           0   sorts (disk)
          16   rows processed
...
Рейтинг: 0 / 0
16.10.2002, 17:06
| Ответить | Цитировать | Написать  
оптимизация запроса с GROUP BY clause
    #32058871
Фотография killed
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
killed
Участник
Обрати внимание, что для первого случая нет операции сортировки. Кстати документация лукавит, говоря, что при INDEX FULL SCAN будут использоваться только одноблочные операции чтения.
...
Рейтинг: 0 / 0
16.10.2002, 17:20
| Ответить | Цитировать | Написать  
оптимизация запроса с GROUP BY clause
    #32058876
Saha
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Saha
Гость
супер!!!!!!!! спасибо!!!!! получилось!!!!!!

только не понял почему.... видимо analyze помог.
я так понимаю этот analyze собирает статистику по таблице, что б "правильнее" запросы опитмизировать?
Если да, то видимо его нужно вызывать время-от времени?
как часто?
а может быть можно как-нибудь напрямую сказать оптимизатору, что б использовал индекс для группировки?
...
Рейтинг: 0 / 0
16.10.2002, 17:29
| Ответить | Цитировать | Написать  
оптимизация запроса с GROUP BY clause
    #32058895
.dba
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
.dba
Участник
>Кстати документация лукавит, говоря, что при INDEX
>FULL SCAN будут использоваться только одноблочные
>операции чтения.

а как ты это определил?

>Если да, то видимо его нужно вызывать время-от
>времени?
>как часто?
>а может быть можно как-нибудь напрямую сказать
>оптимизатору, что б использовал индекс для группировки?

Я вызываю каждую ночь пакетом dbms_stat. Но вообще зависит от того как сильно изменяются данные в таблицах.

Можно явно сказать хинтом.
Код: 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.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
SQL> analyze table t delete statistics;

Tabelle wurde analysiert.

SQL> analyze index ti delete statistics;

Index wurde analysiert.

SQL> select owner, object_type, count(*) from t group by owner, object_type;

OWNER                          OBJECT_TYPE          COUNT(*)
 ------------------------------ ------------------ ----------
 
PUBLIC                         SYNONYM                    926 
SYS                            CONSUMER GROUP               2 
SYS                            DIRECTORY                    1 
SYS                            FUNCTION                    20 
SYS                            PACKAGE                     75 
SYS                            PROCEDURE                    9 
SYS                            TABLE                        6 
SYS                            TYPE                        30 
SYS                            VIEW                       442 
SYSTEM                         VIEW                         1 
TEKAMON                        TABLE                        1 
TEST                           FUNCTION                     2 
TEST                           PROCEDURE                    1 
TEST                           SEQUENCE                     1 
TEST                           TABLE                        3 
TEST                           VIEW                         1 

 16  Zeilen ausgewõhlt.

Abgelaufen:  00 : 00 : 00 . 41 

Ausf³hrungsplan
 ----------------------------------------------------------
 
    0       SELECT STATEMENT Optimizer=CHOOSE
    1      0    SORT (GROUP BY)
    2      1      TABLE ACCESS (FULL) OF 'T'

Statistiken
 ----------------------------------------------------------
 
           0   recursive calls
          12   db block gets
          23   consistent gets
           0   physical reads
           0   redo size
        1110   bytes sent via SQL*Net to client
         532   bytes received via SQL*Net from client
           3   SQL*Net roundtrips to/from client
           1   sorts (memory)
           0   sorts (disk)
          16   rows processed

SQL> select  /*+ index(t ti) */ owner, object_type, count(*) from t group by owner, object_type;

OWNER                          OBJECT_TYPE          COUNT(*)
 ------------------------------ ------------------ ----------
 
PUBLIC                         SYNONYM                    926 
SYS                            CONSUMER GROUP               2 
SYS                            DIRECTORY                    1 
SYS                            FUNCTION                    20 
SYS                            PACKAGE                     75 
SYS                            PROCEDURE                    9 
SYS                            TABLE                        6 
SYS                            TYPE                        30 
SYS                            VIEW                       442 
SYSTEM                         VIEW                         1 
TEKAMON                        TABLE                        1 
TEST                           FUNCTION                     2 
TEST                           PROCEDURE                    1 
TEST                           SEQUENCE                     1 
TEST                           TABLE                        3 
TEST                           VIEW                         1 

 16  Zeilen ausgewõhlt.

Abgelaufen:  00 : 00 : 00 . 41 

Ausf³hrungsplan
 ----------------------------------------------------------
 
    0       SELECT STATEMENT Optimizer=CHOOSE (Cost= 26  Card= 1552  Bytes= 8 
           6912 )

    1      0    SORT (GROUP BY NOSORT) (Cost= 26  Card= 1552  Bytes= 86912 )
    2      1      INDEX (FULL SCAN) OF 'TI' (NON-UNIQUE) (Cost= 26  Card= 155 
           2  Bytes= 86912 )

Statistiken
 ----------------------------------------------------------
 
           0   recursive calls
           0   db block gets
           8   consistent gets
           0   physical reads
           0   redo size
        1110   bytes sent via SQL*Net to client
         532   bytes received via SQL*Net from client
           3   SQL*Net roundtrips to/from client
           0   sorts (memory)
           0   sorts (disk)
          16   rows processed
...
Рейтинг: 0 / 0
16.10.2002, 17:50
| Ответить | Цитировать | Написать  
оптимизация запроса с GROUP BY clause
    #32058907
Saha
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Saha
Гость
Всем большое спасибо!!!
...
Рейтинг: 0 / 0
16.10.2002, 18:06
| Ответить | Цитировать | Написать  
25 сообщений из 28, страница 1 из 2
  1  все
Форумы / Oracle [игнор отключен] [закрыт для гостей] / оптимизация запроса с GROUP BY clause
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru             Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&fpage=2837&tid=1992915]:
 0ms
get settings:
 10ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 26ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 105ms
get tp. blocked users:
 2ms
others: 13ms
total:  193ms
созд. / загр.: 193ms
x
x
Закрыть


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