Быстрее 'выбирают отличный thing_id, thing_name от table1' в оракуле

[ПОСЛЕДНИЙ РЕДАКТИРОВАНИЕ]

Мой ОРИГИНАЛЬНЫЙ ОТВЕТ относительно создания соответствующего индекса на (имя, идентификатор) для замены индекса на (имя) приведен ниже. (Это не было ответом на исходный вопрос, который запрещал любые изменения базы данных.)

Вот утверждения, которые я не еще проверял. Вероятно, есть очевидная причина, по которой они не работают. Я бы никогда на самом деле не предлагал писать подобные утверждения (рискуя быть подвергнутым тщательной ругани за такое нелепое предложение).

Если эти запросы даже возвращают наборы результатов, набор результатов будет только напоминать набор результатов из запроса OP, почти случайно , воспользовавшись причудливой гарантией данных, которые нам предоставил Дон. Этот оператор НЕ эквивалентен исходному SQL, эти операторы разработаны для особого случая , как описано Доном.

 select m1.id
      , m2.name
   from (select min(t1.rowid) as min_rowid
              , t1.id
           from table1 t1
          where t1.id is not null
          group by t1.id
        ) m1
      , (select min(t2.rowid) as min_rowid
             , t2.name from table1 t2
         where t2.name is not null
         group by t2.name
        ) m2
  where m1.min_rowid = m2.min_rowid
  order
     by m1.id

Давайте распакуем это:

• m1 - это встроенное представление, которое дает нам список различных значений идентификаторов.
• m2 - это встроенное представление, которое предоставляет нам список различных значений имен.
• материализует представления m1 и m2
• соответствуют ROWID из m1 и m2 для сопоставления id с name

Кто-то еще предложил идею слияния индексов. Я ранее отвергал эту идею, план оптимизатора сопоставить 10 миллионов идентификаторов строк, не удаляя ни одного из них.

С достаточно низкой мощностью для идентификатора и имени и с правильным планом оптимизатора:

 select m1.id
      , ( select m2.name
            from table1 m2
           where m2.id = m1.id
             and rownum = 1
        ) as name
   from (select t1.id
           from table1 t1
          where t1.id is not null
          group by t1.id
        ) m1
  order
     by m1.id

Пусть ' s распаковать, что

• m1 - это встроенное представление, которое дает нам список различных значений id.
• материализовать представление m1
• для каждой строки в m1 , запросить table1, чтобы получить значение имени из одной строки (стоп-ключ)

ВАЖНОЕ ПРИМЕЧАНИЕ

Эти операторы являются ФУНДАМЕНТАЛЬНО отличается от запроса OP. Они предназначены для возврата ОТЛИЧНОГО набора результатов от запроса OP. происходит для возврата желаемого набора результатов из-за причудливой гарантии данных. Дон сказал нам, что имя определяется id . (Верно ли обратное? Определяется ли id name ? Есть ли у нас ЗАЯВЛЕННАЯ ГАРАНТИЯ, не обязательно обеспечиваемая базой данных, но гарантия, которой мы можем воспользоваться?) любое значение ID , каждая строка с этим значением ID будет иметь одинаковое значение NAME . (Мы также гарантируем, что верно обратное, что для любого значения NAME каждая строка с этим значением NAME будет иметь одинаковое значение ID ?)

Если так, то, возможно, мы сможем использовать эту информацию. Если ID и NAME появляются в разных парах, нам нужно найти только одну конкретную строку. У «пары» будет соответствующий ROWID, который, как правило, доступен для каждого из существующих индексов. Что, если мы получим минимальный ROWID для каждого ID и получим минимальный ROWID для каждого NAME . Не мог t затем мы сопоставляем ID с NAME на основе ROWID, содержащего пару? Я думаю, что это могло бы сработать, учитывая достаточно низкую мощность. (То есть, если мы имеем дело только с сотнями ROWID, а не с десятками миллионов.)

[/ ПОСЛЕДНЕЕ ИЗМЕНЕНИЕ]

[РЕДАКТИРОВАТЬ]

Теперь вопрос обновлен информацией, касающейся таблицы, он показывает, что столбец ID и столбец ИМЯ допускают значения NULL. Если Дон может жить без каких-либо значений NULL, возвращаемых в результирующем наборе, то добавление предиката IS NOT NULL в оба этих столбца может позволить использовать индекс. (ПРИМЕЧАНИЕ: в индексе Oracle (B-Tree) значения NULL НЕ появляются в индексе.)

[/ EDIT]

ОРИГИНАЛЬНЫЙ ОТВЕТ:

создайте соответствующий индекс

create index table1_ix3 on table_1 (name,id) ... ;

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

Обратите внимание, что если у вас есть индекс, определенный на (name, id) , то вы ( очень вероятно) не нужен индекс для (name) , так как оптимизатор будет учитывать ведущий столбец name в другом индексе.

(ОБНОВЛЕНИЕ: как кто-то более проницательный чем я указал, я даже не рассматривал возможность того, что существующие индексы были индексами растровых изображений, а не индексами B-дерева ...)

Пересмотрите свою потребность в наборе результатов ... вам нужно вернуть id , или будет достаточно возврата name .

select distinct name from table1 order by name;

Для конкретного имени вы можете отправить второй запрос, чтобы получить связанный id , если и когда вам это нужно ...

select id from table1 where name = :b1 and rownum = 1;

Если вам действительно нужен указанный набор результатов, вы можете попробовать несколько альтернатив, чтобы увидеть, улучшилась ли производительность. Я не питаю особой надежды ни на что из этого:

select /*+ FIRST_ROWS */ DISTINCT id, name from table1 order by id;

,

select /*+ FIRST_ROWS */ id, name from table1 group by id, name order by name;

или

select /*+ INDEX(table1) */ id, min(name) from table1 group by id order by id;

ОБНОВЛЕНИЕ: как проницательно отметили другие, с помощью этого подхода мы тестируем и сравниваем производительность альтернативных запросов, что является своего рода подход ударил или промахнулся. (Я не согласен с тем, что он случайный, но я согласен, что это удачно или нет.)

ОБНОВЛЕНИЕ: Том предлагает подсказку ALL_ROWS. Я не подумал об этом, потому что я действительно был сосредоточен на получении плана запроса с использованием ИНДЕКСА. Я подозреваю, что запрос OP выполняет полное сканирование таблицы, и, вероятно, время занимает не сканирование, а уникальная операция сортировки (<10g) или операция хеширования (10gR2 +), которая требует времени. (Отсутствие хронометрированной статистики и трассировки события 10046, я просто предполагаю.) Но опять же, может быть, это сканирование, кто знает, отметка максимума на столе может быть выходом из огромного пространства пустых блоков.

Само собой разумеется, что статистика в таблице должна быть актуальной, и мы должны использовать SQL * Plus AUTOTRACE или хотя бы EXPLAIN PLAN для просмотра планов запросов.

Но ни один из предлагаемые альтернативные запросы действительно решают проблему производительности.

Возможно, подсказки повлияют на оптимизатор, чтобы выбрать другой план, в основном удовлетворяющий ORDER BY из индекса, но я ' Я не питаю на это особой надежды. (Я не думаю, что подсказка FIRST_ROWS работает с GROUP BY, подсказка INDEX может.) Я вижу потенциал для такого подхода в сценарии, где есть кучи пустых и редко заполненных блоков данных, и нет доступа к данным блоков через индекс, на самом деле может быть значительно меньше блоков данных, загруженных в память ... но этот сценарий был бы скорее исключением, чем нормой.

ОБНОВЛЕНИЕ: Как указывает Роб ван Вейк, с использованием средства трассировки Oracle - наиболее эффективный подход к выявлению и решению проблем с производительностью.

Без вывода EXPLAIN PLAN или SQL * Plus AUTOTRACE я просто предполагаю здесь.

Я подозреваю, что проблема с производительностью, которая у вас есть прямо сейчас, заключается в том, что для получения указанного набора результатов необходимо указать блоки данных таблицы.

Этого не избежать, запрос не может быть удовлетворен только с помощью индекса, поскольку нет индекса, который содержит столбцы NAME и ID , либо столбец ID или NAME в качестве ведущего столбца. Два других «быстрых» запроса OP могут быть удовлетворены из индекса без необходимости ссылаться на строку (блоки данных).

Даже если план оптимизатора для запроса заключался в использовании одного из индексов, он все равно должен получить связанную строку из блока данных, чтобы получить значение для другого столбца. А без предиката (без предложения WHERE) оптимизатор, скорее всего, выберет полное сканирование таблицы и, вероятно, выполнит операцию сортировки (<10g). (Опять же, EXPLAIN PLAN покажет план оптимизатора, как и AUTOTRACE.)

I ' m также здесь предполагается (большое предположение), что оба столбца определены как NOT NULL.

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

Вы можете попробовать переписать запрос (если это не изменение базы данных, которое также запрещено). В наших средах баз данных мы рассматриваем запрос должен быть такой же частью базы данных, как таблицы и индексы.)

Опять же, без предиката оптимизатор, скорее всего, не будет использовать индекс. Есть шанс, что вы можете заставить план запроса использовать один из существующих индексов для быстрого возврата первых строк, добавив подсказку, протестируйте комбинацию:

select /*+ INDEX(table1) */ ...
select /*+ FIRST_ROWS */ ...
select /*+ ALL_ROWS */ ...

  distinct id, name from table1;
  distinct id, name from table1 order by id;
  distinct id, name from table1 order by name;
  id, name from table1 group by id, name order by id;
  id, min(name) from table1 group by id order by id;
  min(id), name from table1 group by name order by name;

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

(ОБНОВЛЕНИЕ: кто-то еще указал, что оптимизатор может выбрать объединение двух индексов на основе ROWID. Это возможно, но без предиката для удаления некоторых строк это, вероятно, будет гораздо более дорогим подходом (сопоставление десятков миллионов ROWID) из двух индексов, особенно когда ни одна из строк не будет исключена на основе совпадения.)

Но все эти теоретические рассуждения не сводятся к приседанию без некоторой статистики производительности.

Если не изменять что-либо еще в базе данных, единственная надежда (я могу придумать) на то, что вы ускорите запрос, - это убедиться, операция настроена таким образом, что (обязательная) операция сортировки может выполняться в памяти, а не на диске. Но это' s не совсем правильный ответ. Оптимизатор может вообще не выполнять операцию сортировки, вместо этого он может выполнять операцию хеширования (10gR2 +), и в этом случае ее следует настроить. Операция сортировки - это всего лишь предположение с моей стороны, основанное на прошлом опыте работы с Oracle 7.3, 8, 8i, 9i.)

У серьезного администратора баз данных будет больше проблем, если вы возьмете SORT_AREA_SIZE и / или HASH_AREA_SIZE параметров для ваших сеансов, чем он будет при создании правильных индексов. (И эти параметры сеанса являются «старой школой» для версий, предшествующих магии автоматического управления памятью 10g.)

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