Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / оптимизация запроса с GROUP BY clause / 25 сообщений из 28, страница 1 из 2
  1  все
16.10.2002, 13:30
    #32058745
Saha
Saha
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса с GROUP BY clause
Привет всем!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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, 17:06
    #32058868
.dba
.dba
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса с GROUP BY clause
>Но, если у вас
>... поля 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:20
    #32058871
killed
killed
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса с GROUP BY clause
Обрати внимание, что для первого случая нет операции сортировки. Кстати документация лукавит, говоря, что при INDEX FULL SCAN будут использоваться только одноблочные операции чтения.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.10.2002, 17:29
    #32058876
Saha
Saha
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса с GROUP BY clause
супер!!!!!!!! спасибо!!!!! получилось!!!!!!

только не понял почему.... видимо analyze помог.
я так понимаю этот analyze собирает статистику по таблице, что б "правильнее" запросы опитмизировать?
Если да, то видимо его нужно вызывать время-от времени?
как часто?
а может быть можно как-нибудь напрямую сказать оптимизатору, что б использовал индекс для группировки?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.10.2002, 17:50
    #32058895
.dba
.dba
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса с GROUP BY clause
>Кстати документация лукавит, говоря, что при 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, 18:06
    #32058907
Saha
Saha
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса с GROUP BY clause
Всем большое спасибо!!!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
  1  все
Форумы / Oracle [игнор отключен] [закрыт для гостей] / оптимизация запроса с GROUP BY clause / 25 сообщений из 28, страница 1 из 2
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 185ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1992915]:
 0ms
get settings:
 9ms
get forum list:
 12ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 56ms
get topic data:
 8ms
get forum data:
 13ms
get page messages:
 66ms
get tp. blocked users:
 1ms
others: 12ms
total:  185ms
x
x
Закрыть


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