ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Informix [игнор отключен] [закрыт для гостей] / Создание оптимальных индексов
46 сообщений из 46, показаны все 2 страниц
  все  
Создание оптимальных индексов
    #33406437
Сугубый
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сугубый
Гость
Есть вопрос по оптимизации. Как создать индекс для конкретного запроса? Коков алгоритм действий, куда смотреть?

Informix Dynamic Server 2000 Version 9.21.HC3 стоит под вебом http://www.micex.ru/online/stock/archive/
Типичный запрос
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
   SELECT FIRST  20  h.pos, h.id, h.idBoard, TO_CHAR(h.trdate, '%Y-%m-%d'), h.*
    FROM ngcb_hist h, secs s
    WHERE h.id IN
    (SELECT id FROM secsLI WHERE idSecList='INDEXSECS'
      AND idSector='securies' AND sec_publ= 1 )
    AND h.pos>= 6543 
    AND h.publ= 2 
    AND h.id=s.id
    AND h.trdate=mdy( 7 , 13 , 2005 )
-- мигает
    AND h.numtrades> 0 
-- мигает
    AND h.boardGroup IN
    (SELECT boardGroup FROM boardSelect WHERE boardGroupSet='repo|smal|'
       AND idSector='securies')
-- мигает
    AND s.idSector='securies'
    ORDER BY  1
ngcb_hist - сотни тысяч записей, secs - тысячи
то что находится между комментариями "-- мигает" - иногда присутствует в запросе, иногда - нет.

Я делал так - создавал все кучу индексов на ngcb_hist по всем полям, участвующем в запросе (всевозможные перестановки id, trdate, boardGroup, numtrades), делал апдейт статистикс хай, запускал запрос с експлайном и смотрел какие индексы использует информикс. Оставались несколько индексов, их-то и переносил в боевую БД.

Понимаю, что шел экстенсивным путем, но интенсивного ниасилил. Все эти рассуждения о селективности не смог применить в реальной ситуации - как не делаю, информикс не хочет использовать мои индексы :( Чего-то я недопонимаю...

Может многоуважаемое сообщество подскажет _правильный_ путь создания оптимальных индексов для конкретных запросов? Кто как делает?
...
Рейтинг: 0 / 0
29.11.2005, 16:35
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33406538
Фотография Журавлев Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Журавлев Денис
Участник
Сугубый...
Код: plaintext
1.
   SELECT FIRST  20  h.pos, h.id, h.idBoard, TO_CHAR(h.trdate, '%Y-%m-%d'),     ORDER BY  1

мало информации (сколько строк возратится без FIRST 20), планы хотя-бы.

дурацкое предположение:
SELECT /*+ first_rows*/ FIRST 20 h.pos, h.id, h.idBoard, TO_CHAR(h.trdate, '%Y-%m-%d'),
и индекс по h.pos
...
Рейтинг: 0 / 0
29.11.2005, 17:01
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33406632
Сугубый
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сугубый
Гость
Код: 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.
QUERY:
------
SELECT FIRST  20  h.pos, h.id, h.idBoard, TO_CHAR(h.trdate, '%Y-%m-%d'), h.*
    FROM ngcb_hist h, secs s
    WHERE h.id IN
    (SELECT id FROM secsLI WHERE idSecList='INDEXSECS'
      AND idSector='securies' AND sec_publ= 1 )
    AND h.pos>= 6543 
    AND h.publ= 2 
    AND h.id=s.id
    AND h.trdate=mdy( 7 , 13 , 2005 )
    AND h.numtrades> 0 
    AND s.idSector='securies'
    ORDER BY  1 

Estimated Cost:  3 
Estimated # of Rows Returned:  1 
Temporary Files Required For: Order By  

   1 ) denn.h: INDEX PATH

        Filters: (denn.h.publ =  2  AND denn.h.pos >=  6543  ) 

    ( 1 ) Index Keys: trdate id boardgroup numtrades   (Key-First)  (Serial, fragments: ALL)
        Lower Index Filter: (denn.h.trdate =  13 - 07 - 2005  AND denn.h.id = ANY <subquery> ) 
        Key-First Filters:  (denn.h.numtrades >  0  )

   2 ) denn.s: INDEX PATH

    ( 1 ) Index Keys: idsector id   (Serial, fragments: ALL)
        Lower Index Filter: (denn.h.id = denn.s.id AND denn.s.idsector = 'securies' ) 
NESTED LOOP JOIN

    Subquery:
    ---------
    Estimated Cost:  3 
    Estimated # of Rows Returned:  13 

       1 ) informix.secsli: INDEX PATH

            Filters: informix.secsli.sec_publ =  1  

        ( 1 ) Index Keys: idsector idseclist   (Serial, fragments: ALL)
            Lower Index Filter: (informix.secsli.idseclist = 'INDEXSECS' AND informix.secsli.idsector = 'securies' )

без FIRST запрос вернет порядка 1000 записей. Видно что в данном случае планировщик использовал индекс (trdate, id, boardgroup, numtrades)

Я не про этот конкретный запрос. У меня таких штук 20, на разных таблицах, с разным соотношением distinct id, distinct boardgroup, distinct numtrades...

Какой _правильный_ алгоритм построения оптимального индекса? На что обращать внимание, как определять порядок колонок в индексе?
...
Рейтинг: 0 / 0
29.11.2005, 17:31
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33406779
Сугубый
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сугубый
Гость
Журавлев Денис и индекс по h.pos

это для order by? по моему он будет бесполезен при связывании нескольких таблиц. ведь отфильрованный резалт сет пишется в tmpdb, а толко потом упорядочивается. вот если б одна табличка была...
...
Рейтинг: 0 / 0
29.11.2005, 18:16
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33407078
Фотография Журавлев Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Журавлев Денис
Участник
Сугубый Журавлев Денис и индекс по h.pos

это для order by? по моему он будет бесполезен при связывании нескольких таблиц. ведь отфильрованный резалт сет пишется в tmpdb, а толко потом упорядочивается. вот если б одна табличка была...
:)

SELECT /*+ first_rows*/ FIRST 20 h.pos, h.id, h.idBoard, TO_CHAR(h.trdate, '%Y-%m-%d'),
...
Рейтинг: 0 / 0
29.11.2005, 20:56
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33407517
Фотография Журавлев Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Журавлев Денис
Участник
Сугубый
без FIRST запрос вернет порядка 1000 записей. Видно что в данном случае планировщик использовал индекс (trdate, id, boardgroup, numtrades)
Судя по Filters: (denn.h.publ = 2 AND denn.h.pos >= 6543 ) более эффективным был бы trdate, pos.

Сугубыйh.id IN (SELECT id FROM secsLI WHERE idSecList='INDEXSECS'
AND idSector='securies' AND sec_publ=1)Видимо информикс думает что SELECT id FROM secsLI вернет много строк, кстати у меня есть подозрение в этом случае лучше не IN, а EXSIST, и вообще переделать подзапрос в джойн, если он возвращает более одной строки.

СугубыйЯ не про этот конкретный запрос. У меня таких штук 20, на разных таблицах, с разным соотношением distinct id, distinct boardgroup, distinct numtrades...
Возможно надо несколько индексов, надо цель оптимизации сначала определить, а потом ставить задачи.
СугубыйКакой _правильный_ алгоритм построения оптимального индекса? На что обращать внимание, как определять порядок колонок в индексе?Оптимальных индексов не бывает, нужен критерий оптимальности.
Самое важное это правильный порядок соединения таблиц, индексы на втором месте.

Про порядок колонок: индекс может использоваться если его первые поля были в запросе.
в запросе where trdate =?
может использоваться индекс (trdate, id)
а (id, trdate) не может
но (id, trdate) может использоваться при where trdate = ? and id =?
Если id внешний ключ, то возможно нужен индекс где оно первое.
Если h.numtrades>0 редко (отсекаем выходные?), то его можно не включать в индексы.
Если во многих запросах есть h.pos>=? AND h.publ=? AND h.trdate=?, то возможно нужен trdate, publ, pos, но все зависит от селективности. Например если publ всегда =2, то в индекс его можно не включать. Но если селективность высокая или есть исключения, то его можно включить.

Бд на мой вкус криво спроектирована:
FROM secs s WHERE s.idSector='securies'
FROM secsLI WHERE idSector='securies'
FROM boardSelect WHERE idSector='securies'
...
Рейтинг: 0 / 0
30.11.2005, 09:38
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33408495
Сугубый
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сугубый
Гость
Журавлев Денис
:)

SELECT /*+ first_rows*/ FIRST 20 h.pos, h.id, h.idBoard, TO_CHAR(h.trdate, '%Y-%m-%d'),

ой, а как такие большие буквы сделать? ;)))


Код: 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.
QUERY:        (FIRST_ROWS OPTIMIZATION)

------
SELECT /*+ first_rows*/ FIRST  20  h.pos, h.id,
h.idBoard, TO_CHAR(h.trdate, '%Y-%m-%d'), h.*
    FROM ngcb_hist h, secs s
    WHERE h.id IN
    (SELECT id FROM secsLI WHERE idSecList='INDEXSECS'
      AND idSector='securies' AND sec_publ= 1 )
    AND h.pos>= 6543 
    AND h.publ= 2 
    AND h.id=s.id
    AND h.trdate=mdy( 7 , 13 , 2005 )
    AND h.numtrades> 0 
    AND s.idSector='securies'
    ORDER BY  1 

DIRECTIVES FOLLOWED: 
FIRST_ROWS 
DIRECTIVES NOT FOLLOWED: 

Estimated Cost:  3 
Estimated # of Rows Returned:  1 
Temporary Files Required For: Order By  

   1 ) denn.h: INDEX PATH

        Filters: (denn.h.publ =  2  AND denn.h.pos >=  6543  ) 

    ( 1 ) Index Keys: trdate id boardgroup numtrades   (Key-First)  (Serial, fragments: ALL)
        Lower Index Filter: (denn.h.trdate =  13 - 07 - 2005  AND denn.h.id = ANY <subquery> ) 
        Key-First Filters:  (denn.h.numtrades >  0  )

   2 ) denn.s: INDEX PATH

    ( 1 ) Index Keys: idsector id   (Serial, fragments: ALL)
        Lower Index Filter: (denn.h.id = denn.s.id AND denn.s.idsector = 'securies' ) 
NESTED LOOP JOIN

    Subquery:
    ---------
    Estimated Cost:  3 
    Estimated # of Rows Returned:  12 

       1 ) informix.secsli: INDEX PATH

            Filters: informix.secsli.sec_publ =  1  

        ( 1 ) Index Keys: idsector idseclist   (Serial, fragments: ALL)
            Lower Index Filter: (informix.secsli.idseclist = 'INDEXSECS' AND informix.secsli.idsector = 'securies' ) 



QUERY:
------
SELECT FIRST  20  h.pos, h.id,
h.idBoard, TO_CHAR(h.trdate, '%Y-%m-%d'), h.*
    FROM ngcb_hist h, secs s
    WHERE h.id IN
    (SELECT id FROM secsLI WHERE idSecList='INDEXSECS'
      AND idSector='securies' AND sec_publ= 1 )
    AND h.pos>= 6543 
    AND h.publ= 2 
    AND h.id=s.id
    AND h.trdate=mdy( 7 , 13 , 2005 )
    AND h.numtrades> 0 
    AND s.idSector='securies'
    ORDER BY  1 

Estimated Cost:  3 
Estimated # of Rows Returned:  1 
Temporary Files Required For: Order By  

   1 ) denn.h: INDEX PATH

        Filters: (denn.h.publ =  2  AND denn.h.pos >=  6543  ) 

    ( 1 ) Index Keys: trdate id boardgroup numtrades   (Key-First)  (Serial, fragments: ALL)
        Lower Index Filter: (denn.h.trdate =  13 - 07 - 2005  AND denn.h.id = ANY <subquery> ) 
        Key-First Filters:  (denn.h.numtrades >  0  )

   2 ) denn.s: INDEX PATH

    ( 1 ) Index Keys: idsector id   (Serial, fragments: ALL)
        Lower Index Filter: (denn.h.id = denn.s.id AND denn.s.idsector = 'securies' ) 
NESTED LOOP JOIN

    Subquery:
    ---------
    Estimated Cost:  3 
    Estimated # of Rows Returned:  12 

       1 ) informix.secsli: INDEX PATH

            Filters: informix.secsli.sec_publ =  1  

        ( 1 ) Index Keys: idsector idseclist   (Serial, fragments: ALL)
            Lower Index Filter: (informix.secsli.idseclist = 'INDEXSECS' AND informix.secsli.idsector = 'securies' )

Разницы в планах выполнения я не заметил. И забил на директивы оптимизатора...
...
Рейтинг: 0 / 0
30.11.2005, 13:58
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33408514
АнатоЛой
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
АнатоЛой
Участник
Журавлев Денис
Бд на мой вкус криво спроектирована:
FROM secs s WHERE s.idSector='securies'
FROM secsLI WHERE idSector='securies'
FROM boardSelect WHERE idSector='securies'
Ага - особенно если idSector [N]VARCHAR :)
Смотри прочие thread'ы про индексы :))
...
Рейтинг: 0 / 0
30.11.2005, 14:02
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33408523
Фотография Журавлев Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Журавлев Денис
Участник
Сугубый
Разницы в планах выполнения я не заметил. И забил на директивы оптимизатора...
А сколько времени запрос выполняется? А без order by сколько?
На самом деле first_rows хороший хинт, и информикс не будет сортировать, а пойдет по индексу, даже если джойнится много таблиц. Это даже не хинт, это доп.информация оптимизатору, что нам нужны первые строки, ведь у вас first 20
...
Рейтинг: 0 / 0
30.11.2005, 14:04
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33408538
Фотография Журавлев Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Журавлев Денис
Участник
АнатоЛой Журавлев Денис
Бд на мой вкус криво спроектирована:
FROM secs s WHERE s.idSector='securies'
FROM secsLI WHERE idSector='securies'
FROM boardSelect WHERE idSector='securies'
Ага - особенно если idSector [N]VARCHAR :)
Смотри прочие thread'ы про индексы :))
Индекс тут как раз используется, проблемы с VARCHAR остались в прошлом, в 7-ке.
...
Рейтинг: 0 / 0
30.11.2005, 14:07
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33408566
Сугубый
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сугубый
Гость
Денис, не могли бы вы определить понятие селективности? А то никто мне внятно не может обьяснить. Спасибо заранее :)

Журавлев ДенисСудя по Filters: (denn.h.publ = 2 AND denn.h.pos >= 6543 ) более эффективным был бы trdate, pos.


Эффективность индекса как раз и определяется этой селективностью, как я понял. Если первый ключ может существенно сузить облать поиска, то выбирается именно он. trdate - это дата, данные хранятся за несколько лет, за один день порядка 400 записей. Роs - уникальна для каждой строки (не спрашивайте почему) Насколько селективен индекс по trdate?

Журавлев ДенисВидимо информикс думает что SELECT id FROM secsLI вернет много строк, кстати у меня есть подозрение в этом случае лучше не IN, а EXSIST, и вообще переделать подзапрос в джойн, если он возвращает более одной строки.


Джойн мне немного сложнее формировать... Впрочем, где-то прочитал что подселекты оптимизатор все равно раскладывает в джойн.

Журавлев Денис
Возможно надо несколько индексов, надо цель оптимизации сначала определить, а потом ставить задачи.


Цель оптимизации - скорострельность. Параметр - чем больше тем лучше. Текущий уровень, в принципе, удовлетворяет, но потолок уже близок...

Другая цель оптимизации - научится оптимизации :)

Журавлев Денис
Оптимальных индексов не бывает, нужен критерий оптимальности.
Самое важное это правильный порядок соединения таблиц, индексы на втором месте.

Про порядок колонок: индекс может использоваться если его первые поля были в запросе.
в запросе where trdate =?
может использоваться индекс (trdate, id)
а (id, trdate) не может
но (id, trdate) может использоваться при where trdate = ? and id =?
Если id внешний ключ, то возможно нужен индекс где оно первое.
Если h.numtrades>0 редко (отсекаем выходные?), то его можно не включать в индексы.
Если во многих запросах есть h.pos>=? AND h.publ=? AND h.trdate=?, то возможно нужен trdate, publ, pos, но все зависит от селективности. Например если publ всегда =2, то в индекс его можно не включать. Но если селективность высокая или есть исключения, то его можно включить.

Бд на мой вкус криво спроектирована:
FROM secs s WHERE s.idSector='securies'
FROM secsLI WHERE idSector='securies'
FROM boardSelect WHERE idSector='securies'


h.numtrades>0 у 2/3 записей
publ=2 практически всегда (99,9% строк)

Вкусы на структуру БД у всех разные ;)

Насчет порядка соединения - а как бы его проанализировать? Есть 3 таблицы, одна так сказать ведущая, 2 - вспомогательные; вспомогательные вяжутся в главной одним полем; на все таблички наклыдываются разнообразные условия.

И еще раз - что же такое селективность?... (где же у него кнопка? (ц) Урри :)
...
Рейтинг: 0 / 0
30.11.2005, 14:16
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33408579
АнатоЛой
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
АнатоЛой
Участник
Журавлев ДенисИндекс тут как раз используется, проблемы с VARCHAR остались в прошлом, в 7-ке.
ОК, уболтали :)
В ближайшее время возвращаюсь из прошлого...
...
Рейтинг: 0 / 0
30.11.2005, 14:21
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33408584
Сугубый
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сугубый
Гость
Журавлев Денис Сугубый
Разницы в планах выполнения я не заметил. И забил на директивы оптимизатора...
А сколько времени запрос выполняется? А без order by сколько?
На самом деле first_rows хороший хинт, и информикс не будет сортировать, а пойдет по индексу, даже если джойнится много таблиц. Это даже не хинт, это доп.информация оптимизатору, что нам нужны первые строки, ведь у вас first 20

времена не тестировал :( каюсь. Слишком много запросов, надо было бить по площадям. Сейчас - можно и потестировать (кста, как в dbaccess выводить время выпалнения - есть така фича?)

А оптимизатор сам что ли не видит, что у него в запросе first 20? %)
...
Рейтинг: 0 / 0
30.11.2005, 14:23
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33408608
Фотография Журавлев Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Журавлев Денис
Участник
СугубыйДенис, не могли бы вы определить понятие селективности? А то никто мне внятно не может обьяснить. Спасибо заранее :)Ох, тяжело это. Давай, не будем говорить большая и маленькая селективность, а то совсем запутаемся, будем говорить хорошая селективность.

Поле F содержит много разных значений 0,1,2,3,2,1,4 выполняется много запросов where F=? - селективность поля хорошая.

Поле F содержит мало разных значений 0,0,0,0,0,0,1,1,1,1 селективность поля плохая.

Поле F содержит мало разных значений 0,0,0,0,0,0,0,4 выполняется много запросов where F=0 - селективность поля плохая, а для where F=4 - селективность поля хорошая.



СугубыйНасчет порядка соединения - а как бы его проанализировать? Есть 3 таблицы, одна так сказать ведущая, 2 - вспомогательные; вспомогательные вяжутся в главной одним полем; на все таблички наклыдываются разнообразные условия.
Читаем план:
Код: plaintext
1.
2.
3.
  1) denn.h: INDEX PATH
  2) denn.s: INDEX PATH
NESTED LOOP JOIN
Идем в табличку 1 берем запись, идем в табличку 2, находим все соотвествующие h.id=s.id, идем в табличку 1.
...
Рейтинг: 0 / 0
30.11.2005, 14:32
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33408633
Фотография Журавлев Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Журавлев Денис
Участник
Сугубый
выбирается именно он. trdate - это дата, данные хранятся за несколько лет, за один день порядка 400 записей. Роs - уникальна для каждой строки (не спрашивайте почему) Насколько селективен индекс по trdate?
Индекс по trdate эффективен, по Роs намного эффективнее при условии что Роs используется в where.

Сугубый
Цель оптимизации - скорострельность. Параметр - чем больше тем лучше. Текущий уровень, в принципе, удовлетворяет, но потолок уже близок...
Другая цель оптимизации - научится оптимизации :)
Я понял сейчас. Вы этим запросом листаете 1000 строк кусочками по 20 строк с помощью Роs >, вам однозначно надо уйти от сортировки.


Сугубыйh.numtrades>0 у 2/3 записей
Плохая селективность. Индекс не нужен.
Сугубый
publ=2 практически всегда (99,9% строк)Очень плохая селективность. Индекс не нужен.
...
Рейтинг: 0 / 0
30.11.2005, 14:42
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33408644
Фотография Журавлев Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Журавлев Денис
Участник
Сугубыйвремена не тестировал :( каюсь. Слишком много запросов, надо было бить по площадям.Как можно оптимизировать "скорострельность" без времени?

Сугубый Сейчас - можно и потестировать (кста, как в dbaccess выводить время выпалнения - есть така фича?)Не знаю как dbaccess :(. Чаще пользуюсь eSQLEditor , там вроде есть такая фича.
Сугубый
А оптимизатор сам что ли не видит, что у него в запросе first 20? %)По моему не видит, 100% гарантии дать не могу.
...
Рейтинг: 0 / 0
30.11.2005, 14:47
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33408873
Сугубый
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сугубый
Гость
Журавлев ДенисИндекс по trdate эффективен, по Роs намного эффективнее при условии что Роs используется в where.


сначала я создавал такие составные индексы, в которых pos был на первом месте. оптимизатор их _не_использовал_ , наверно потому что у меня стоит pos > NNN и индекс по этому полю отсекает половину таблицы. А это мало. Оптимизатору лучше сразу выкусить из всех значений наименьший кусок. И он ставил на первые места trdate (дату, одну из нескольких лет)

Журавлев Денис
Я понял сейчас. Вы этим запросом листаете 1000 строк кусочками по 20 строк с помощью Роs >, вам однозначно надо уйти от сортировки.


Вот здесь поподробнее, плз. Я знаю, что индекс используется при сортировке, если только все выбираемые поля лежат в индексе. У меня же в ngcb_hist 30 полей. Все их в индекс? нереально.


Журавлев Денис
Сугубыйh.numtrades>0 у 2/3 записей
Плохая селективность. Индекс не нужен.
Сугубый
publ=2 практически всегда (99,9% строк)Очень плохая селективность. Индекс не нужен.

что и было выявлено на первых этапах, когда я просто создал по индексу на каждую колонку из where - оптимизатор их не использовал вообще

Журавлев ДенисКак можно оптимизировать "скорострельность" без времени?

на глазок :) по времени отклика браузера :)

Журавлев Денис
Не знаю как dbaccess :(. Чаще пользуюсь eSQLEditor , там вроде есть такая фича.


пасиб, посмотрим.
...
Рейтинг: 0 / 0
30.11.2005, 15:39
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33408947
Фотография Журавлев Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Журавлев Денис
Участник
Сугубый
Журавлев Денис
Я понял сейчас. Вы этим запросом листаете 1000 строк кусочками по 20 строк с помощью Роs >, вам однозначно надо уйти от сортировки.


Вот здесь поподробнее, плз. Я знаю, что индекс используется при сортировке, если только все выбираемые поля лежат в индексе. У меня же в ngcb_hist 30 полей. Все их в индекс? нереально.
Вы путаете, в индексе должны быть поля по которым сортируется.

Сделай индекс (trdate, pos), собери статистику, и покажите план:
Код: plaintext
1.
2.
3.
4.
5.
 SELECT /*+first_rows*/ FIRST  20  h.pos, h.id, h.idBoard, TO_CHAR(h.trdate, '%Y-%m-%d')
    FROM ngcb_hist h
    AND h.pos>= 6543 
    AND h.publ= 2 
    AND h.trdate=mdy( 7 , 13 , 2005 )
    ORDER BY  1

А зачем надо 30 полей сразу?
...
Рейтинг: 0 / 0
30.11.2005, 15:58
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33408948
Сугубый
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сугубый
Гость
Журавлев Денис
Сугубый Сейчас - можно и потестировать (кста, как в dbaccess выводить время выпалнения - есть така фича?)Не знаю как dbaccess :(. Чаще пользуюсь eSQLEditor , там вроде есть такая фича.
Сугубый
А оптимизатор сам что ли не видит, что у него в запросе first 20? %)По моему не видит, 100% гарантии дать не могу.

А прикольная у вас программка :) Пасиба, буду юзать.

Скорость выполнения с {+First} и без него одинаковы (в пределах точности) - 250 и 280 миллисек, к примеру. Видать оптимизатор сам видит, что у него FIRST 20 в запросе стоит :)
...
Рейтинг: 0 / 0
30.11.2005, 15:59
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33408964
Фотография Журавлев Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Журавлев Денис
Участник
СугубыйСкорость выполнения с {+First} и без него одинаковы (в пределах точности) - 250 и 280 миллисек, к примеру. Видать оптимизатор сам видит, что у него FIRST 20 в запросе стоит :)Это ничего не доказывает. Я не поверю, пока не увижу что из-за FIRST 20 меняется план.
...
Рейтинг: 0 / 0
30.11.2005, 16:02
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33408984
Сугубый
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сугубый
Гость
ой, ошибся, с /*+first_rows*/ - то же саме.
...
Рейтинг: 0 / 0
30.11.2005, 16:08
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33408998
Фотография Журавлев Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Журавлев Денис
Участник
Сугубыйой, ошибся, с /*+first_rows*/ - то же саме.Да я догадался.


Просто смотрим первую строку плана при first_rows и видим:
Код: plaintext
QUERY:        (FIRST_ROWS OPTIMIZATION)

А без хинта, один first 20:
Код: plaintext
QUERY:
Хотя это все фигня конечно.
...
Рейтинг: 0 / 0
30.11.2005, 16:12
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33409022
Сугубый
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сугубый
Гость
Журавлев ДенисВы путаете, в индексе должны быть поля по которым сортируется.

Сделай индекс (trdate, pos), собери статистику, и покажите план:
Код: plaintext
1.
2.
3.
4.
5.
 SELECT /*+first_rows*/ FIRST  20  h.pos, h.id, h.idBoard, TO_CHAR(h.trdate, '%Y-%m-%d')
    FROM ngcb_hist h
    AND h.pos>= 6543 
    AND h.publ= 2 
    AND h.trdate=mdy( 7 , 13 , 2005 )
    ORDER BY  1


Сделал и вот что получилось:

Код: 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.
QUERY:        (FIRST_ROWS OPTIMIZATION)

------
SELECT /*+first_rows*/ FIRST  20  h.pos,
 h.id, h.idBoard, TO_CHAR(h.trdate, '%Y-%m-%d')
    FROM ngcb_hist h
    WHERE h.pos>= 6543 
    AND h.publ= 2 
    AND h.trdate=mdy( 7 , 13 , 2005 )
    ORDER BY  1 

DIRECTIVES FOLLOWED:
FIRST_ROWS
DIRECTIVES NOT FOLLOWED:

Estimated Cost:  132 
Estimated # of Rows Returned:  252 
Temporary Files Required For: Order By

   1 ) denn.h: INDEX PATH

        Filters: (denn.h.publ =  2  AND denn.h.pos >=  6543  )

    ( 1 ) Index Keys: trdate boardgroup id numtrades   (Serial, fragments: ALL)
        Lower Index Filter: denn.h.trdate =  13 - 07 - 2005

он его не использует вообще. Ему больше нравится (trdate boardgroup id numtrades)

счас делаю update statistics HIGH для ngcb_hist - может что поменяется

Журавлев Денис
А зачем надо 30 полей сразу?

ну вот к примеру http://www.micex.ru/online/stock/archive/by_date.html?ssector=securies&doSearch=auto
...
Рейтинг: 0 / 0
30.11.2005, 16:18
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33409074
Фотография Журавлев Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Журавлев Денис
Участник
Попробуй:

SELECT /*+first_rows*/ FIRST 20 h.trdate, h.pos, ....
...
ORDER BY 1, 2

Сугубый
счас делаю update statistics HIGH для ngcb_hist - может что поменяется
HIGH необязательно, в случае добавления нового индекса.
...
Рейтинг: 0 / 0
30.11.2005, 16:32
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33409090
Сугубый
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сугубый
Гость
упс, накладочка, перепутал сервера где выполнялись запросы :(

Код: 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.
QUERY:        (FIRST_ROWS OPTIMIZATION)

------
SELECT /*+first_rows*/ FIRST  20  h.pos,
 h.id, h.idBoard, TO_CHAR(h.trdate, '%Y-%m-%d')
    FROM ngcb_hist h
    WHERE h.pos>= 6543 
    AND h.publ= 2 
    AND h.trdate=mdy( 7 , 13 , 2005 )
    ORDER BY  1 

DIRECTIVES FOLLOWED:
FIRST_ROWS
DIRECTIVES NOT FOLLOWED:

Estimated Cost:  55 
Estimated # of Rows Returned:  243 

   1 ) denn.h: INDEX PATH

        Filters: denn.h.publ =  2 

    ( 1 ) Index Keys: trdate pos   (Serial, fragments: ALL)
        Lower Index Filter: (denn.h.trdate =  13 - 07 - 2005  AND denn.h.pos >=  6543  )

индекс испорльзуется и пропала строчка
Temporary Files Required For: Order By - значит сортировка идет по индексу?

Интересно
...
Рейтинг: 0 / 0
30.11.2005, 16:38
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33409160
Фотография Журавлев Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Журавлев Денис
Участник
Сугубый
индекс испорльзуется и пропала строчка
Temporary Files Required For: Order By - значит сортировка идет по индексу?
Куда он денется с подводной лодки?

Покажи план того же запроса без /*+first_rows*/
SELECT FIRST 20 h.pos,...
...
Рейтинг: 0 / 0
30.11.2005, 16:56
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33409224
Сугубый
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сугубый
Гость
Журавлев Денис Сугубый
индекс испорльзуется и пропала строчка
Temporary Files Required For: Order By - значит сортировка идет по индексу?
Куда он денется с подводной лодки?

Покажи план того же запроса без /*+first_rows*/
SELECT FIRST 20 h.pos,...

Но если не используется Temporary Files, то не загружается tmpdb! А у нас боттлнек по каналу обращения к диску именно для tmpdb - постоянные сортировки идут. И тогда время отклика должно уменьшится... Это хорошая новость :)

план не меняется

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
QUERY:
------
SELECT FIRST  20  h.pos,
 h.id, h.idBoard, TO_CHAR(h.trdate, '%Y-%m-%d')
    FROM ngcb_hist h
    WHERE h.pos>= 6543 
    AND h.publ= 2 
    AND h.trdate=mdy( 7 , 13 , 2005 )
    ORDER BY  1 

Estimated Cost:  55 
Estimated # of Rows Returned:  242 

   1 ) denn.h: INDEX PATH

        Filters: denn.h.publ =  2 

    ( 1 ) Index Keys: trdate pos   (Serial, fragments: ALL)
        Lower Index Filter: (denn.h.trdate =  13 - 07 - 2005  AND denn.h.pos >=  6543  )
...
Рейтинг: 0 / 0
30.11.2005, 17:15
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33409236
Сугубый
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сугубый
Гость
То есть в конец индексов мне бы хорошо еще добавить pos? тогда по нему будет сортировка идти.

к примеру
Код: plaintext
1.
2.
3.
4.
5.
6.
CREATE INDEX XIE2fbbonds_hist2_id_trdate_board ON fbbonds_hist
(
       id                             ASC,
       trdate                         ASC,
       boardGroup                     ASC,
       numtrades                      ASC
);

сделать как

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
CREATE INDEX XIE2fbbonds_hist2_id_trdate_board ON fbbonds_hist
(
       id                             ASC,
       trdate                         ASC,
       boardGroup                     ASC,
       numtrades                      ASC,
       pos                      ASC
);

я все правильно понимаю?
...
Рейтинг: 0 / 0
30.11.2005, 17:18
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33409248
Фотография Журавлев Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Журавлев Денис
Участник
Сугубый
Но если не используется Temporary Files, то не загружается tmpdb! А у нас боттлнек по каналу обращения к диску именно для tmpdb - постоянные сортировки идут. И тогда время отклика должно уменьшится... Это хорошая новость :) Сортировки в файлах можно убрать перенеся их в ОЗУ (если она конечно есть). Используй PDQ. Или переходи на 9.4х4 и настраивай DS_NONPDQ_QUERY_MEM
...
Рейтинг: 0 / 0
30.11.2005, 17:22
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33409265
nkulikov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
nkulikov
Гость
Кстати в 10-ке хинты можно не писать

External Optimizer Directives

The external optimizer directive is a new feature introduced in IBM® Informix® Dynamic Server, Version 10.0. This feature provides a more flexible way of specifying optimizer directives and optimizer hints.


What it Does?
In previous releases of Dynamic Server, optimizer directives existed within DML statements, but were not stored as separate database objects. External optimizer directives are useful when it is not feasible to rewrite a query for a short-term solution to a problem, for example, when a query starts to perform poorly. The database administrator can create external optimizer directives and apply them to subsequent queries, without changing existing application code. This feature is implemented as a new SQL statement, SAVE EXTERNAL DIRECTIVES, to create and register external optimizer directives in a new system catalog table called sysdirectives. Use the new IFX_EXTDIRECTIVES environment variable or the EXT_DIRECTIVES configuration parameter to enable this feature.

The sysdirectives table stores external optimizer directives that can be applied to queries. Whether queries in client applications can use these optimizer directives depends on the setting of the IFX_EXTDIRECTIVES environment variable on the client system, and on the EXT_DIRECTIVES setting in the configuration file of the database server.

Following is a structure of the sysdirectives table:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
Column	       	Type	     	Explanation
id	       	SERIAL		Unique code identifying the optimizer directive
query	       	TEXT	      	Text of the query as it appears in the application
directive	TEXT	      	Text of the optimizer directive, without comments
directivecode	BYTE	    	Blob of the optimizer directive
active	        SMALLINT	Integer code that identifies whether this entry is active ( =  1  ) or test only ( =  2  )
hash_code	INT		For internal use only


How to use it?

1. Enable the external directive using a combination of the EXT_DIRECTIVES configuration parameter, and the IFX_EXTDIRECTIVES client-side environment variable. Queries within a given client application can use external directives if both the EXT_DIRECTIVES parameter in the configuration file and the IFX_EXTDIRECTIVES environment variable setting on the client system are both set to 1 or ON.

The EXT_DIRECTIVE values that you can use are:

Value Explanation
0 Off. The directive cannot be enabled even if IFX_EXTDIRECTIVES is on.
1 On. The directive can be enabled for a session if IFX_EXTDIRECTIVES is on.
2 (default) On. The directive can be used even if IFX_EXTDIRECTIVES is not set.


IFX_EXTDIRECTIVES environment variable specifies whether the query optimizer allows external query optimization directives from the sysdirectives system catalog table to be applied to queries in existing applications. You can specify either ON and OFF or 1 and 0 to set the IFX_EXTDIRECTIVES environment variable.
If IFX_EXTDIRECTIVES is not set, external directives are supported only if the ONCONFIG parameter EXT_DIRECTIVES is set to 2. The following table summarizes the effect of valid IFX_EXTDIRECTIVES and EXT_DIRECTIVES settings on support for external optimizer directives.
Код: plaintext
1.
2.
3.
4.
5.
 
IFX_EXTDIRECTIVES	EXT_DIRECTIVES =  0 	EXT_DIRECTIVES =  1 	EXT_DIRECTIVES =  2 
Not set			OFF			OFF			ON
 0   = OFF		OFF			OFF			OFF
 1  = ON			OFF			ON			ON


2. Use the SAVE EXTERNAL DIRECTIVES statement to create external optimizer directives for a specified query, and save the directives in the database. These directives are applied automatically to subsequent instances of the same query.

Syntax
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
 
                               .---------.
                             V           |
>>-SAVE EXTERNAL DIRECTIVES----directive-+--+-ACTIVE----+------->
                                            +-INACTIVE--+
                                            '-TEST ONLY-'
 
>--FOR--query--------------------------------------------------><


directive
the optimizer directive valid for query

query
Text of a valid SELECT statement. NULL string is not valid

Following is an example of SAVE EXTERNAL DIRECTIVES statement and corresponding entry in sysdirectives table:
Код: plaintext
1.
2.
3.
  	        
  SAVE EXTERNAL DIRECTIVES {+INDEX(tab1, idx1) } ACTIVE FOR
  SELECT {+INDEX(tab1, idx1) } col1 FROM tab1 WHERE col1= 1



Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
 
sysdirectives
Column		Values
id		 1 
query		SELECT {+INDEX(tab1, idx1) } col1 FROM tab1 WHERE col1= 1 
directive	INDEX(tab1, idx1)
directivecode	<BYTE value>
active		 1 
hash_code	 83229839


Notes
External directives are for occasional use only. The number of directives stored in the sysdirectives catalog should not exceed 50. A typical enterprise only needs 0 to 9 directives.
...
Рейтинг: 0 / 0
30.11.2005, 17:24
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33409277
Фотография Журавлев Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Журавлев Денис
Участник
Сугубыйя все правильно понимаю?Наверно неправильно. Мы вроде уже решили что индексировать numtrades не надо. Я думаю индекс по boardGroup тоже лишний. А по id надо отдельный индекс.
Т.о. я вижу три индекса:

CREATE INDEX (id)
CREATE INDEX (trdate,pos)
CREATE Uniq INDEX (pos)

Если boardGroup это внешний ключ то можно на всякий добавить еще один индекс CREATE INDEX (boardGroup)
...
Рейтинг: 0 / 0
30.11.2005, 17:28
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33409354
Сугубый
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сугубый
Гость
Журавлев Денис Сугубыйя все правильно понимаю?Наверно неправильно. Мы вроде уже решили что индексировать numtrades не надо. Я думаю индекс по boardGroup тоже лишний. А по id надо отдельный индекс.
Т.о. я вижу три индекса:

CREATE INDEX (id)
CREATE INDEX (trdate,pos)
CREATE Uniq INDEX (pos)

Если boardGroup это внешний ключ то можно на всякий добавить еще один индекс CREATE INDEX (boardGroup)

Эх, у меня запросы типовые. И в одном из типов есть
Код: plaintext
1.
2.
3.
    AND h.boardGroup IN
    (SELECT boardGroup FROM boardSelect WHERE boardGroupSet='repo|smal|'
       AND idSector='securies')

так что индекс нужен. Каюсь, я взял первый попавшийся под руку индекс. Более правильно былоб обсуждать сл. индекс (оптимизатор берет именно его)
Код: plaintext
1.
2.
3.
4.
CREATE INDEX XIE3ngcb_hist2_trdate_id_board ON ngcb_hist
(
       trdate                         ASC,
       id                             ASC
);
под рассматриваемый запрос
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
SELECT FIRST  20  h.pos, h.id, h.idBoard, TO_CHAR(h.trdate, '%Y-%m-%d'), h.*
    FROM ngcb_hist h, secs s
    WHERE h.id IN
    (SELECT id FROM secsLI WHERE idSecList='INDEXSECS'
      AND idSector='securies' AND sec_publ= 1 )
    AND h.pos>= 6543 
    AND h.publ= 2 
    AND h.id=s.id
    AND h.trdate=mdy( 7 , 13 , 2005 )
    AND h.numtrades> 0 
    AND s.idSector='securies'
    ORDER BY  1

Вопроос такой - стоит ли для этого индекса добавить в конец pos ? Для оптимизации сортировки.

Насчет нескольких одиночных индексов: я считал что один составной индекс гораздо лучше нескольких одинарных. Я не прав?

Завтра (уже) попробую создать эту кучку индексов и посмотреть, что там оптимизатор выберет... :)
...
Рейтинг: 0 / 0
30.11.2005, 17:44
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33410127
Фотография Журавлев Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Журавлев Денис
Участник
Сугубый
Эх, у меня запросы типовые. И в одном из типов есть
Код: plaintext
1.
2.
3.
    AND h.boardGroup IN
    (SELECT boardGroup FROM boardSelect WHERE boardGroupSet='repo|smal|'
       AND idSector='securies')
Включаю телепатические способности на полную мощность:
Осмелюсь утверждать что индекс в который входит boardGroup, использоваться не будет. SELECT boardGroup FROM boardSelect должен вернуть такие значения чтобы их суммарная селективность h.boardGroup IN была лучше чем h.trdate, но какие именно значения вернуться подзапросом оптимизатор не знает, поэтому он возьмет среднестатистическую селективность и помножит на количество ожидаемых строк подзапроса, и получит нехорошую селективность.
Еслиже случится такое несчастье что первым он решит выполнить подзапрос, то будет дисковая сортировка потому что подзапрос вернет несколько значений (по мнению оптимизатора), а в этом случае индекс (boardGroup, pos) принципиально нельзя использовать для order by pos.
Еще раз предлагаю переделать IN на EXSIST.

Сугубый
INDEX (trdate, id);
Вопроос такой - стоит ли для этого индекса добавить в конец pos ? Для оптимизации сортировки.
(trdate, id, pos) Тоже самое -- будет дисковая сортировка.

Сугубый
Насчет нескольких одиночных индексов: я считал что один составной индекс гораздо лучше нескольких одинарных. Я не прав?
Не прав. Индексы не должны быть ни длинными, ни короткими. Они должны быть такими какими нужны. Этими излишне длинными индексами вы жрете место на диске и замедляете операции обновления таблицы.

СугубыйЗавтра (уже) попробую создать эту кучку индексов и посмотреть, что там оптимизатор выберет... :)Статистику после создания нового индекса собирайте LOW, high не НУЖЕН в этом случае.
...
Рейтинг: 0 / 0
01.12.2005, 08:49
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33410265
Фотография Тан
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Тан
Участник
СугубыйДжойн мне немного сложнее формировать... Впрочем, где-то прочитал что подселекты оптимизатор все равно раскладывает в джойн.

Раскладывает, но не всегда, и если разложит, то в плане это будет видно

Query Plans for Subqueries
http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.perf.doc/perf282.htm#sii-10query-32245
...
Рейтинг: 0 / 0
01.12.2005, 09:56
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33410403
Фотография Журавлев Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Журавлев Денис
Участник
ТанРаскладывает, но не всегда, и если разложит, то в плане это будет видно
Вообще интересный вопрос и сложный, оптимизатор FIRST 20 не видит. А в нашем случае может быть и не надо все h.boardGroup = (SELECT boardGroup FROM boardSelect WHERE boardGroupSet='repo|smal|' AND idSector='securies'), нам возможно не надо выполнять весь селект, а для первых "20" boardGroup проверить EXISTS (SELECT 1 FROM boardSelect WHERE boardGroupSet='repo|smal|' AND idSector='securies' and boardGroup=h.boardGroup)
...
Рейтинг: 0 / 0
01.12.2005, 10:47
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33411021
Сугубый
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сугубый
Гость
Журавлев ДенисВообще интересный вопрос и сложный, оптимизатор FIRST 20 не видит. А в нашем случае может быть и не надо все h.boardGroup = (SELECT boardGroup FROM boardSelect WHERE boardGroupSet='repo|smal|' AND idSector='securies'), нам возможно не надо выполнять весь селект, а для первых "20" boardGroup проверить EXISTS (SELECT 1 FROM boardSelect WHERE boardGroupSet='repo|smal|' AND idSector='securies' and boardGroup=h.boardGroup)

Нда, оптимизация - странная штука. Переход на EXISTS выйгрыша не дал:

Код: 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.
116.
117.
QUERY:
------
SELECT FIRST  20  h.pos, h.id, h.idBoard,
TO_CHAR(h.trdate, '%Y-%m-%d'), h.*
    FROM ngcb_hist h, secs s
    WHERE EXISTS
    (SELECT li.id FROM secsLI li WHERE
      h.id=li.id
      AND li.idSecList='INDEXSECS'
      AND li.idSector='securies' AND li.sec_publ= 1 )
    AND h.pos>= 6543 
    AND h.publ= 2 
    AND h.id=s.id
    AND h.trdate=mdy( 7 , 13 , 2005 )
    AND h.numtrades> 0 
    AND s.idSector='securies'
    AND EXISTS
    (SELECT boardGroup FROM boardSelect bs WHERE
       h.boardGroup=bs.boardGroup
       AND bs.boardGroupSet='repo|smal|'
       AND bs.idSector='securies')
    ORDER BY  1 ;

Estimated Cost:  22 
Estimated # of Rows Returned:  1 
Temporary Files Required For: Order By

   1 ) wad.li: INDEX PATH

        Filters: wad.li.sec_publ =  1 

    ( 1 ) Index Keys: idseclist idsector   (Serial, fragments: ALL)
        Lower Index Filter: (wad.li.idseclist = 'INDEXSECS' AND wad.li.idsector = 'securies' )

   2 ) wad.h: INDEX PATH

        Filters: (wad.h.publ =  2  AND wad.h.pos >=  6543  )

    ( 1 ) Index Keys: trdate id boardgroup numtrades   (Key-First)  (Serial, fragments: ALL)
        Lower Index Filter: (wad.h.id = wad.li.id AND wad.h.trdate =  13 - 07 - 2005  )
        Key-First Filters:  (wad.h.numtrades >  0  )
NESTED LOOP JOIN

   3 ) wad.bs: INDEX PATH

    ( 1 ) Index Keys: idsector boardgroupset boardgroup   (Serial, fragments: ALL)
        Lower Index Filter: ((wad.h.boardgroup = wad.bs.boardgroup AND wad.bs.boardgroupset = 'repo|smal|' ) AND wad.bs.idsector = 'securies' )
NESTED LOOP JOIN

   4 ) wad.s: INDEX PATH

    ( 1 ) Index Keys: idsector id   (Serial, fragments: ALL)
        Lower Index Filter: (wad.s.id = wad.li.id AND wad.s.idsector = 'securies' )
NESTED LOOP JOIN


QUERY:
------
SELECT FIRST  20  h.pos, h.id, h.idBoard,
TO_CHAR(h.trdate, '%Y-%m-%d'), h.*
    FROM ngcb_hist h, secs s
    WHERE h.id IN
    (SELECT id FROM secsLI li WHERE
      li.idSecList='INDEXSECS'
      AND li.idSector='securies' AND li.sec_publ= 1 )
    AND h.pos>= 6543 
    AND h.publ= 2 
    AND h.id=s.id
    AND h.trdate=mdy( 7 , 13 , 2005 )
    AND h.numtrades> 0 
    AND s.idSector='securies'
    AND h.boardGroup IN
    (SELECT boardGroup FROM boardSelect WHERE
       boardGroupSet='repo|smal|'
       AND idSector='securies')
    ORDER BY  1 

Estimated Cost:  5 
Estimated # of Rows Returned:  1 
Temporary Files Required For: Order By

   1 ) wad.h: INDEX PATH

        Filters: (wad.h.publ =  2  AND wad.h.pos >=  6543  )

    ( 1 ) Index Keys: trdate id boardgroup numtrades   (Key-First)  (Serial, fragments: ALL)
        Lower Index Filter: (wad.h.trdate =  13 - 07 - 2005  AND wad.h.id = ANY <subquery> )
        Key-First Filters:  (wad.h.boardgroup = ANY <subquery> ) AND
                            (wad.h.numtrades >  0  )

   2 ) wad.s: INDEX PATH

    ( 1 ) Index Keys: idsector id   (Serial, fragments: ALL)
        Lower Index Filter: (wad.h.id = wad.s.id AND wad.s.idsector = 'securies' )
NESTED LOOP JOIN

    Subquery:
    ---------
    Estimated Cost:  3 
    Estimated # of Rows Returned:  21 

       1 ) wad.li: INDEX PATH

            Filters: wad.li.sec_publ =  1 

        ( 1 ) Index Keys: idseclist idsector   (Serial, fragments: ALL)
            Lower Index Filter: (wad.li.idseclist = 'INDEXSECS' AND wad.li.idsector = 'securies' )


    Subquery:
    ---------
    Estimated Cost:  1 
    Estimated # of Rows Returned:  2 

       1 ) wad.boardselect: SEQUENTIAL SCAN

            Filters: (wad.boardselect.boardgroupset = 'repo|smal|' AND wad.boardselect.idsector = 'securies' )

Общая стоимость запроса с IN меньше чем с EXISTS. Согласен, возможно индексы на ngcb_hist неоптимальны и из-за это такой разброс...

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

boardselect - мельчайшая табличка, 22 записи, 4 колонки. Она полностью попадает в память. Другое дело secsLI - 14 тысяч записей (попутно кста на ней индекс оптимизировал)
...
Рейтинг: 0 / 0
01.12.2005, 13:42
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33411042
Сугубый
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сугубый
Гость
Журавлев Денис
Не прав. Индексы не должны быть ни длинными, ни короткими. Они должны быть такими какими нужны. Этими излишне длинными индексами вы жрете место на диске и замедляете операции обновления таблицы.


По базе всегда идут только чтения (веб-сервер), обновление данных раз в сутки, вечером. Так что наличие множества индексов имхо оправдано. Места - пока хватает.

Я заметил, что оптимизатор использует составные индексы охотнее, нежели чем отдельные по колонкам. И именно поэтому бьюсь над составными индексами, порядком колонок в них.
...
Рейтинг: 0 / 0
01.12.2005, 13:47
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33411055
Сугубый
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сугубый
Гость
Тан СугубыйДжойн мне немного сложнее формировать... Впрочем, где-то прочитал что подселекты оптимизатор все равно раскладывает в джойн.

Раскладывает, но не всегда, и если разложит, то в плане это будет видно

Query Plans for Subqueries
http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.perf.doc/perf282.htm#sii-10query-32245

Спасибо за уточнение. Теперь и сам вижу, что не раскладывает :) Но как показал эксперимент - не всегда разложение в джойн полезнее.
...
Рейтинг: 0 / 0
01.12.2005, 13:49
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33411304
Фотография Журавлев Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Журавлев Денис
Участник
СугубыйОбщая стоимость запроса с IN меньше чем с EXISTS. Согласен, возможно индексы на ngcb_hist неоптимальны и из-за это такой разброс...

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

boardselect - мельчайшая табличка, 22 записи, 4 колонки. Она полностью попадает в память. Другое дело secsLI - 14 тысяч записей (попутно кста на ней индекс оптимизировал)а +first_rows
...
Рейтинг: 0 / 0
01.12.2005, 14:45
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33411406
Сугубый
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сугубый
Гость
Журавлев Дениса +first_rows

план не изменился в обоих случаях.

Даже больше, для SELECT /*+first_rows*/ h.pos, h.id, h.idBoard и для просто SELECT h.pos, h.id, h.idBoard (убрал FIRST 20) - тот же самый план выполнения (для IN и EXISTS). Там только иногда появляется "(Key-First)"

но это я не создал всевозможные индексы еще. счас попробуем создать :)
...
Рейтинг: 0 / 0
01.12.2005, 15:09
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33411806
Valentyn Pidburtnyi
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Valentyn Pidburtnyi
Гость
СугубыйНо как показал эксперимент - не всегда разложение в джойн полезнее.
"не всегда полезнее" - это иногда бесполезно? Или именно вредно?:)
ЗЫ
Если второе - хотелось бы посмотреть пример.
...
Рейтинг: 0 / 0
01.12.2005, 16:53
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33411857
Сугубый
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сугубый
Гость
Valentyn Pidburtnyi СугубыйНо как показал эксперимент - не всегда разложение в джойн полезнее.
"не всегда полезнее" - это иногда бесполезно? Или именно вредно?:)
ЗЫ
Если второе - хотелось бы посмотреть пример.

я сужу по Estimated Cost в планах выполнения. Пример был уже дан в этом сообщении - я заменил конструкцию "IN" на "EXISTS" и Estimated Cost увеличилась. Сечас пишу скрипт замера времени выполнения - предварительно время выполнения запроса с EXISTS больше, хотя и не намного, на 1-2%.
...
Рейтинг: 0 / 0
01.12.2005, 17:05
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33412216
Сугубый
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сугубый
Гость
написал скрипт сравнения времен выполнения запросов. Прогоняет N раз рассматриваемую пару, вычисляет среднее время выполнения.

Вот что получилось (первый запрос - с EXIST, второй - с IN):
Код: plaintext
1.
2.
3.
4.
registered  100 -- всего пар запросов было
totals  16.7241873741 16.503223896 -- сумма времен выполнения
avarage  0.167241873741 0.16503223896 -- средние значения
percents  1.33891098782 -- в процентах насколько первый медленнее второго

то есть запрос с EXIST на 1.33 % медленнее чем с с IN :)

(в сторону: мля, и на это я потратил полдня? ;) ничего, зато у меня теперь измерялка времен выполнения :)
...
Рейтинг: 0 / 0
01.12.2005, 18:48
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33414412
Valentyn Pidburtnyi
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Valentyn Pidburtnyi
Гость
Сугубыйя заменил конструкцию "IN" на "EXISTS" и Estimated Cost увеличилась. Сечас пишу скрипт замера времени выполнения - предварительно время выполнения запроса с EXISTS больше, хотя и не намного, на 1-2%.
Да, теперь вижу: на своей базе тоже попробовал и тоже увидел, что иногда при exists оптимизатор выбирает план с бОльшей стоимостью.

ЗЫ
Не пробовал запускать запрос с собранным распределением (по колонкам, по которым есть фильтры и джойны) и без оного?
Я у себя пока не могу потестить: тестовая база пустая, а рабочая.. Ну ты понял:)
...
Рейтинг: 0 / 0
02.12.2005, 17:13
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33416825
Сугубый
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сугубый
Гость
Valentyn Pidburtnyi
Не пробовал запускать запрос с собранным распределением (по колонкам, по которым есть фильтры и джойны) и без оного?


Это как? Я создал всевозможные (ну почти) индексы на всех колонках, участвующих в джойне, а после этого сделал UPDATE STATISTICS.

Что есть "собранным распределением" ?
...
Рейтинг: 0 / 0
05.12.2005, 12:15
| Ответить | Цитировать | Написать  
Создание оптимальных индексов
    #33417506
Valentyn Pidburtnyi
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Valentyn Pidburtnyi
Гость
Сугубый Valentyn Pidburtnyi
Не пробовал запускать запрос с собранным распределением (по колонкам, по которым есть фильтры и джойны) и без оного?


Это как? Я создал всевозможные (ну почти) индексы на всех колонках, участвующих в джойне, а после этого сделал UPDATE STATISTICS.

Что есть "собранным распределением" ?
update statistics high или medium по фильтруемым полям таблиц.
Можно distributions only, чтобы не делалось лишней работы, которая и так у тебя уже сделана после update statistics low.
...
Рейтинг: 0 / 0
05.12.2005, 15:38
| Ответить | Цитировать | Написать  
46 сообщений из 46, показаны все 2 страниц
  все  
Форумы / Informix [игнор отключен] [закрыт для гостей] / Создание оптимальных индексов
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?all=1&fid=44&tid=1608822]:
 0ms
get settings:
 7ms
get forum list:
 11ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 31ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 46ms
get tp. blocked users:
 1ms
others: 201ms
total:  313ms
созд. / загр.: 313ms
x
x
Закрыть


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