Блог

Я запускаю следующий запрос.

 SELECT Table_1.Field_1,
           Table_1.Field_2,
           SUM(Table_1.Field_5) BALANCE_AMOUNT
      FROM Table_1, Table_2
     WHERE Table_1.Field_3 NOT IN (1, 3)
       AND Table_2.Field_2 <> 2
       AND Table_2.Field_3 = 'Y'
       AND Table_1.Field_1 = Table_2.Field_1
       AND Table_1.Field_4 = '31-oct-2011'
     GROUP BY Table_1.Field_1, Table_1.Field_2;
  

Я создал индекс для столбцов (Field_1,Field_2,Field_3,Field_4) из Table_1 , но индекс не используется.

Если я удалю SUM(Table_1.Field_5) из предложения select, тогда индекс будет использоваться.

Я в замешательстве, если оптимизатор не использует этот индекс или его из-за SUM() функции, которую я использовал в запросе.

Пожалуйста, поделитесь своим объяснением по тому же вопросу.

Когда вы удаляете сумму, вы также удаляете field_5 из запроса. Затем все данные, необходимые для ответа на запрос, могут быть найдены в индексе, что может быть быстрее, чем сканирование таблицы. Если вы добавили field_5 к индексу, запрос с SUM может использовать индекс.

Если ваш запрос возвращает большой процент строк таблицы, Oracle может решить, что выполнение полного сканирования таблицы дешевле, чем «переход» между индексом и кучей таблицы (для получения значений в Table_1.Field_5 ).

Попробуйте добавить Table_1.Field_5 к индексу (таким образом, охватывая весь запрос индексом) и посмотрите, поможет ли это.

Смотрите Сканирование только по индексу: избегание доступа к таблице в Use The Index Luke для концептуального объяснения того, что происходит.

Как вы упомянули, наличие функции суммирования приводит к тому, что индекс не учитывается.

Существуют индексы на основе функций:

Индекс на основе функций включает столбцы, которые либо преобразованы функцией, такой как функция UPPER, либо включены в выражение, такое как col1 col2.

Определение индекса на основе функции в преобразованном столбце или выражении позволяет возвращать эти данные с использованием индекса, когда эта функция или выражение используются в предложении WHERE или предложении ORDER BY. Следовательно, индекс на основе функций может быть полезен, когда часто выполняемые инструкции SQL включают преобразованные столбцы или столбцы в выражениях в предложении WHERE или ORDER BY.

Однако, как и у всех индексов, основанных на функциях, есть свои ограничения:

Выражения в индексе на основе функций не могут содержать никаких агрегированных функций. Выражения должны ссылаться только на столбцы в строке таблицы.

Хотя я вижу здесь несколько хороших ответов, упускается пара важных моментов —

 SELECT Table_1.Field_1,
           Table_1.Field_2,
           SUM(Table_1.Field_5) BALANCE_AMOUNT
      FROM Table_1, Table_2
     WHERE Table_1.Field_3 NOT IN (1, 3)
       AND Table_2.Field_2 <> 2
       AND Table_2.Field_3 = 'Y'
       AND Table_1.Field_1 = Table_2.Field_1
       AND Table_1.Field_4 = '31-oct-2011'
     GROUP BY Table_1.Field_1, Table_1.Field_2;
  

Говорят, что имея СУММУ(Таблица_1.Field_5) в предложении select приводит к тому, что индекс не используется в неправильном. Ваш индекс на (Field_1,Field_2,Field_3,Field_4) все еще можно использовать. Но есть проблемы с вашим индексом и SQL-запросом.

Поскольку ваш индекс включен только (Field_1,Field_2,Field_3,Field_4) , даже если ваш индекс будет использоваться, DB придется обращаться к фактической строке таблицы, чтобы получить Field_5 для применения фильтра. Теперь это полностью зависит от плана выполнения, составленного из sql optimizer, который является экономически эффективным. Если оптимизатор SQL выяснит, что это full table scan обходится дешевле, чем использование index, он проигнорирует индекс. Говоря так, я сейчас расскажу вам о возможных проблемах с вашим индексом —

1. Поскольку у других есть состояния, вы могли бы просто добавить Field_5 в индекс, чтобы не было необходимости в отдельном доступе к таблице.
2. Ваш порядок индексов очень важен для производительности. Например. в вашем случае, если вы зададите порядок как (Field_4,Field_1,Field_2,Field_3) , это будет быстрее, так как у вас есть равенство в Field_4 — Table_1.Field_4 = '31-oct-2011' . Подумайте об этом, это было —

Table_1.Field_4 = '31-oct-2011' тогда у вас будет меньше возможностей для выбора конечного результата Table_1.Field_3 NOT IN (1, 3) . Ситуация может измениться, поскольку вы выполняете объединение. Всегда лучше ознакомиться с планом выполнения и соответствующим образом спроектировать свой индекс / sql.