Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Оптимизация запроса - NEED HELP  [new]
Мудрая белка
Guest
Приветствую господа форумчане!
Есть большой запрос, который формируется динамически. В условии обязательно присутствуют все 9 полей таблицы, но где-то '=', а где-то 'IN', в зависимости от условия.
Кратко: нужно выполнить все 12 вариантов (комбинаций) условий, и выбрать один, у которого в результате >= 3 записей, наименьшая дисперсия одного из полей, (+ вторичные поля сортировки).
Делаю это так (объединяю все запросы в один и группирую по 'v' (v - вариант условий), далее сортировка):

SELECT v, n, df FROM
(
  SELECT v, SUM (n) n, SUM (df) df, ROUND (VARIANCE (d), 1) var_d FROM
  (
    SELECT /*+ index (c IDX2$D) */ c.*, 1 v FROM ANY_TABLE c WHERE c.col_1=14 AND c.col_2=80 AND c.col_3=0 AND c.col_4=7 AND c.col_5 IN (2) AND c.col_6=8 AND c.col_7=21 AND c.col_8=32 AND c.col_9=1
      UNION ALL
    SELECT /*+ index (c IDX2$D) */ c.*, 2 v FROM ANY_TABLE c WHERE c.col_1=14 AND c.col_2=80 AND c.col_3=0 AND c.col_4=7 AND c.col_5 IN (c.col_5) AND c.col_6=8 AND c.col_7=21 AND c.col_8=32 AND c.col_9=1
      UNION ALL
    SELECT /*+ index (c IDX2$D) */ c.*, 3 v FROM ANY_TABLE c WHERE c.col_1=14 AND c.col_2=80 AND c.col_3=0 AND c.col_4=7 AND c.col_5=2 AND c.col_6=8 AND c.col_7 IN (21,22,23,24,25,26,27,28,29,30,31,32) AND c.col_8=32 AND c.col_9=1
      UNION ALL
    SELECT /*+ index (c IDX2$D) */ c.*, 4 v FROM ANY_TABLE c WHERE c.col_1=14 AND c.col_2=80 AND c.col_3=0 AND c.col_4=7 AND c.col_5=2 AND c.col_6=8 AND c.col_7=21 AND c.col_8 IN (1,2,4,5,6,7,15,29,30,31,32,33,34,35,38) AND c.col_9=1
      UNION ALL
    SELECT /*+ index (c IDX2$D) */ c.*, 5 v FROM ANY_TABLE c WHERE c.col_1=14 AND c.col_2=80 AND c.col_3 IN (c.col_3) AND c.col_4=7 AND c.col_5=2 AND c.col_6=8 AND c.col_7=21 AND c.col_8=32 AND c.col_9=1
      UNION ALL
    SELECT /*+ index (c IDX2$D) */ c.*, 6 v FROM ANY_TABLE c WHERE c.col_1=14 AND c.col_2 IN (75,80,85) AND c.col_3=0 AND c.col_4=7 AND c.col_5=2 AND c.col_6=8 AND c.col_7=21 AND c.col_8=32 AND c.col_9=1
      UNION ALL
    SELECT /*+ index (c IDX2$D) */ c.*, 7 v FROM ANY_TABLE c WHERE c.col_1=14 AND c.col_2=80 AND c.col_3=0 AND c.col_4=7 AND c.col_5=2 AND c.col_6=8 AND c.col_7=21 AND c.col_8=32 AND c.col_9 IN (1,2)
      UNION ALL
    SELECT /*+ index (c IDX2$D) */ c.*, 8 v FROM ANY_TABLE c WHERE c.col_1 IN (14,24,27,28,38,49,75,87) AND c.col_2=80 AND c.col_3=0 AND c.col_4=7 AND c.col_5=2 AND c.col_6=8 AND c.col_7=21 AND c.col_8=32 AND c.col_9=1
      UNION ALL
    SELECT /*+ index (c IDX2$D) */ c.*, 9 v FROM ANY_TABLE c WHERE c.col_1 IN (c.col_1) AND c.col_2=80 AND c.col_3=0 AND c.col_4=7 AND c.col_5=2 AND c.col_6=8 AND c.col_7=21 AND c.col_8=32 AND c.col_9=1
      UNION ALL
    SELECT /*+ index (c IDX2$D) */ c.*, 10 v FROM ANY_TABLE c WHERE c.col_1=14 AND c.col_2=80 AND c.col_3=0 AND c.col_4=7 AND c.col_5=2 AND c.col_6 IN (8) AND c.col_7=21 AND c.col_8=32 AND c.col_9=1
      UNION ALL
    SELECT /*+ index (c IDX2$D) */ c.*, 11 v FROM ANY_TABLE c WHERE c.col_1=14 AND c.col_2=80 AND c.col_3=0 AND c.col_4=7 AND c.col_5=2 AND c.col_6 IN (c.col_6) AND c.col_7=21 AND c.col_8=32 AND c.col_9=1
      UNION ALL
    SELECT /*+ index (c IDX2$D) */ c.*, 12 v FROM ANY_TABLE c WHERE c.col_1=14 AND c.col_2=80 AND c.col_3=0 AND c.col_4 IN (4,7,10) AND c.col_5=2 AND c.col_6=8 AND c.col_7=21 AND c.col_8=32 AND c.col_9=1
  )
  GROUP BY v
  HAVING COUNT (v) >= 3
  ORDER BY var_d ASC, n DESC, df DESC, v ASC
)
WHERE ROWNUM = 1

Индекс сделал по всем 9 полям, но оракл его берет не для всех 12 вариантов, поэтому ставлю хинт. Но и с хинтом, запросы отрабатывают неоптимально. Проблема в том, что подобный запрос нужно повторить n-миллионорв раз (в зависимости от условий), и время уже идет на сутки.
Прошу помощи, как можно более оптимально решить данную задачу.
P.S. в таблице - около 1 500 000 записей
20 окт 11, 02:38    [11469916]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - NEED HELP  [new]
глупый хомяк
Guest
использовать bitmap index ?
20 окт 11, 02:41    [11469918]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - NEED HELP  [new]
борсч=суп
Guest
Построй не один индекс по всем полям, а несколько индексов.
И не обязательно по всем полям, а по самым селективным.

Можешь начать с того, что сделать 9 индексов, где первым полем будут разные, потом остальные в любом (лучше в порядке убывания селективности) порядке.
20 окт 11, 02:45    [11469920]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - NEED HELP  [new]
борсч=суп
Guest
насколько равномерно распределены данные?
какая селективность у условия col_1=const ? col_2=const? и т.д.
20 окт 11, 02:47    [11469922]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - NEED HELP  [new]
Мудрая белка
Guest
борсч=суп
насколько равномерно распределены данные?
какая селективность у условия col_1=const ? col_2=const? и т.д.


Количество значений (уникальных) в полях:
col_1 = 72
col_2 = 12
col_3 = 2
col_4 = 12
col_5 = 6
col_6 = 8
col_7 = 89
col_8 = 39
col_9 = 6

Комбинации значений в этих полях дают 1 500 000 записей
20 окт 11, 03:10    [11469949]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - NEED HELP  [new]
борсч=суп
Guest
Тогда я бы рекомендовал сделать 9 индексов, где первое поле col_1 ~ col_9, далее n, df, d.
Итого 9 4х столбцовых индексов.
20 окт 11, 03:13    [11469954]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - NEED HELP  [new]
борсч=суп
Guest
извини, друг,
я в предыдущем сообщении чепуху написал.
20 окт 11, 03:20    [11469962]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - NEED HELP  [new]
Мудрая белка
Guest
борсч=суп
Тогда я бы рекомендовал сделать 9 индексов, где первое поле col_1 ~ col_9, далее n, df, d.
Итого 9 4х столбцовых индексов.


А зачем n, df, d?
С ними проблем вообще нет, ведь проблема с 12-ю подзапросами, в которых они не участвуют. Проблема в полях col_1 ... col_9.
20 окт 11, 03:22    [11469965]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - NEED HELP  [new]
борсч=суп
Guest
Кстати, могут ли быть с in первое и второе поля?

Мудрая белка
борсч=суп
Тогда я бы рекомендовал сделать 9 индексов, где первое поле col_1 ~ col_9, далее n, df, d.
Итого 9 4х столбцовых индексов.


А зачем n, df, d?
С ними проблем вообще нет, ведь проблема с 12-ю подзапросами, в которых они не участвуют. Проблема в полях col_1 ... col_9.

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

Насколько я понимаю, сами по себе столбцы не селективные. Селективные их комбинации.
Тогда можно начать со следующего.
Попробуй сделать все таки 9 индексов по всем полям col_1 - col_9 + n, df, d.
Первое поле должно быть разное.
Почему 9, а не 1 ? Потому что первое поле у тебя может быть in (...) и тогда этот твой единственный индекс будет не так хорош, как другой из 9ти.
20 окт 11, 03:26    [11469968]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - NEED HELP  [new]
борсч=суп
Guest
борсч=суп
Попробуй сделать все таки 9 индексов по всем полям col_1 - col_9 + n, df, d.
Первое поле должно быть разное.

Т.е. речь как ты понял об 9ти индексах с 9+3=12 полями в каждом
20 окт 11, 03:27    [11469970]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - NEED HELP  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Сделай 9 bitmap индексов по одному полю каждый.
20 окт 11, 03:36    [11469972]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - NEED HELP  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Если bitmap по каким-то причинам не подойдет (блокировки при транзакциях, например), можно попробовать 9 btree, по одной колонке. Oracle сам сделает из них bitmap.
20 окт 11, 03:39    [11469974]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - NEED HELP  [new]
Мудрая белка
Guest
борсч=суп
Попробуй сделать все таки 9 индексов по всем полям col_1 - col_9 + n, df, d.
Первое поле должно быть разное.
Почему 9, а не 1 ? Потому что первое поле у тебя может быть in (...) и тогда этот твой единственный индекс будет не так хорош, как другой из 9ти.


Здесь не все так просто.
Смотри:
Сам по себе, IN (n1,n2,n3..nn) может присуствовать в каждом поле (запросы формируются динамически).
НО: конструкция "(все поле) IN (все поле)" может быть ТОЛЬКО для 3-х полей: col_1, col_3, col_5, col_6, и в одном запросе может присутствовать например (col_1) IN (col_1) AND (col_3) IN (col_3) AND (col_5) IN (col_5) AND (col_6) IN (col_6) + условия остальных полей.
20 окт 11, 03:40    [11469975]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - NEED HELP  [new]
борсч=суп
Guest
wurdu
Сделай 9 bitmap индексов по одному полю каждый.

А вот я бы попробовал, но уверенно бы не советовал.
Например, одно из полей 2 уник значения, это примерно 750тыс строк. Только в одном запросе автора bitmap scan & bitmap and будет происходит 12 раз. И запрос надо повторить n миллионов раз.
20 окт 11, 03:41    [11469976]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - NEED HELP  [new]
борсч=суп
Guest
борсч=суп
wurdu
Сделай 9 bitmap индексов по одному полю каждый.

А вот я бы попробовал, но уверенно бы не советовал.
Например, одно из полей 2 уник значения, это примерно 750тыс строк. Только в одном запросе автора bitmap scan & bitmap and будет происходит 12 раз. И запрос надо повторить n миллионов раз.

Кажется я опять ахинею какую-то сказал. Прошу простить. Автор, пробуй bitmap индексы.
20 окт 11, 03:44    [11469977]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - NEED HELP  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
борсч=суп
wurdu
Сделай 9 bitmap индексов по одному полю каждый.

А вот я бы попробовал, но уверенно бы не советовал.
Например, одно из полей 2 уник значения, это примерно 750тыс строк. Только в одном запросе автора bitmap scan & bitmap and будет происходит 12 раз. И запрос надо повторить n миллионов раз.
Для двух уникальных значений с равным распределение bitmap будет неэффективен, поэтому его оптимизатор не будет использовать (естественно, возможен вариант, когда для 2-х уникальных значений будет распределение 1:15000000, тогда, с учетом гистограммы, bitmap может быть полезен).
20 окт 11, 03:46    [11469979]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - NEED HELP  [new]
борсч=суп
Guest
wurdu
борсч=суп
пропущено...

А вот я бы попробовал, но уверенно бы не советовал.
Например, одно из полей 2 уник значения, это примерно 750тыс строк. Только в одном запросе автора bitmap scan & bitmap and будет происходит 12 раз. И запрос надо повторить n миллионов раз.
Для двух уникальных значений с равным распределение bitmap будет неэффективен, поэтому его оптимизатор не будет использовать (естественно, возможен вариант, когда для 2-х уникальных значений будет распределение 1:15000000, тогда, с учетом гистограммы, bitmap может быть полезен).

Согласен, bitmap индексы пожалуй самое оно. Зря я увел автора с верного пути.
20 окт 11, 03:48    [11469980]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - NEED HELP  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Также мне непонятно решение написать запрос через union all, а не через or. В этом случае мы получим bitmap or, что будет еще эффективнее.
20 окт 11, 03:51    [11469982]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - NEED HELP  [new]
не такой уж глупый хомяк
Guest
да
20 окт 11, 03:54    [11469985]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - NEED HELP  [new]
Мудрая белка
Guest
Коллеги, спасибо!
С BITMAP-индексам по каждому полю все работает гораздо шустрее.
А вот про OR я не понял, что это за решение такое?
20 окт 11, 04:15    [11469995]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - NEED HELP  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Мудрая белка
Коллеги, спасибо!
С BITMAP-индексам по каждому полю все работает гораздо шустрее.
А вот про OR я не понял, что это за решение такое?
Почему ты делаешь
SELECT /*+ index (c IDX2$D) */ c.*, 1 v FROM ANY_TABLE c WHERE c.col_1=14 AND c.col_2=80 AND c.col_3=0 AND c.col_4=7 AND c.col_5 IN (2) AND c.col_6=8 AND c.col_7=21 AND c.col_8=32 AND c.col_9=1
      UNION ALL
SELECT /*+ index (c IDX2$D) */ c.*, 2 v FROM ANY_TABLE c WHERE c.col_1=14 AND c.col_2=80 AND c.col_3=0 AND c.col_4=7 AND c.col_5 IN (c.col_5) AND c.col_6=8 AND c.col_7=21 AND c.col_8=32 AND c.col_9=1
, а не
SELECT /*+ index (c IDX2$D) */ c.*, 1 v FROM ANY_TABLE c WHERE (c.col_1=14 AND c.col_2=80 AND c.col_3=0 AND c.col_4=7 AND c.col_5 IN (2) AND c.col_6=8 AND c.col_7=21 AND c.col_8=32 AND c.col_9=1) or
(c.col_1=14 AND c.col_2=80 AND c.col_3=0 AND c.col_4=7 AND c.col_5 IN (c.col_5) AND c.col_6=8 AND c.col_7=21 AND c.col_8=32 AND c.col_9=1)
?
20 окт 11, 04:18    [11469997]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - NEED HELP  [new]
Мудрая белка
Guest
wurdu, потому что мне нужен номер варианта условий (в данном случае - "V"), потом по нему идет группировка данных для вычислений. Если делать через OR - то мы не сможем конкретным строкам присвоить конкретный номер условий.
В вашем примере - везде будет 1 - и смысл всего разбиения пропадает.
20 окт 11, 04:24    [11470001]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - NEED HELP  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Мудрая белка
wurdu, потому что мне нужен номер варианта условий (в данном случае - "V"), потом по нему идет группировка данных для вычислений. Если делать через OR - то мы не сможем конкретным строкам присвоить конкретный номер условий.
В вашем примере - везде будет 1 - и смысл всего разбиения пропадает.
Можно использовать case.
20 окт 11, 04:29    [11470005]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - NEED HELP  [new]
Мудрая белка
Guest
wurdu
Можно использовать case.

Вот так вы имеете ввиду?
SELECT c.*,
       CASE
         WHEN c.col_1=14 AND c.col_2=80 AND c.col_3=0 AND c.col_4=7 AND c.col_5 IN (2) AND c.col_6=8 AND c.col_7=21 AND c.col_8=32 AND c.col_9=1 THEN 1
         WHEN c.col_1=14 AND c.col_2=80 AND c.col_3=0 AND c.col_4=7 AND c.col_5 IN (c.col_5) AND c.col_6=8 AND c.col_7=21 AND c.col_8=32 AND c.col_9=1 THEN 2
       END v
FROM ANY_TABLE c
WHERE (c.col_1=14 AND c.col_2=80 AND c.col_3=0 AND c.col_4=7 AND c.col_5 IN (2) AND c.col_6=8 AND c.col_7=21 AND c.col_8=32 AND c.col_9=1)
OR (c.col_1=14 AND c.col_2=80 AND c.col_3=0 AND c.col_4=7 AND c.col_5 IN (c.col_5) AND c.col_6=8 AND c.col_7=21 AND c.col_8=32 AND c.col_9=1)
Действительно можно, но уж слишком большой запрос получится, а у меня лимит 4000 символов :)
20 окт 11, 04:37    [11470009]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - NEED HELP  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54383
Мудрая белка,

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

обычно лучше один проход по таблице, чем 9
20 окт 11, 07:12    [11470039]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Oracle Ответить