Блог

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

 SELECT /*  index(T_DIRECTORY X_DIR) */ 
                 DIRx.dir_id ,                   
                 base.id       
                 FROM T_DIRECTORY DIRx,  T_PERSON base 
                 WHERE 
                   DIRx.id = 26463
                   and DIRx.PERSONID=  base.PERSONID

'| Id  | Operation                    | Name           |'
'-------------------------------------------------------'
'|   0 | SELECT STATEMENT             |                |'
'|   1 |  NESTED LOOPS                |                |'
'|   2 |   TABLE ACCESS BY INDEX ROWID| T_DIRECTORY |'
'|   3 |    INDEX RANGE SCAN          | X_DIRECTORY |'
'|   4 |   TABLE ACCESS BY INDEX ROWID| T_PERSON      |'
'|   5 |    INDEX UNIQUE SCAN         | I_PERSON      |'
  

Во-первых, убедитесь, что у вас есть подходящие индексы для столбцов в предложении where (DIRx.id ) и о присоединяемой таблице (base.personid), и что эти индексы анализируются таким образом, чтобы они представляли данные в таблице — если они не анализируются, Oracle может выполнить полное сканирование таблиц, когда он может использоватьвместо этого индексируйте.

 SELECT INDEX_NAME, 
       NUM_ROWS, 
       LAST_ANALYZED 
  FROM DBA_INDEXES 
 WHERE TABLE_NAME IN ('T_DIRECTORY','T_PERSON');
  

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

Параллельный запрос

У вас есть несколько процессоров, и при запуске этого SQL больше ничего не работает, т.Е. Является ли это частью пакетного процесса или частью онлайн-процесса, который может вызываться несколько раз одновременно. Если выполняется пакетный процесс и у вас несколько процессоров, попробуйте выполнить параллельный запрос, но не делайте этого, если это интерактивная программа (например, отчет при использовании параллельного запроса попытается использовать весь доступный процессор, и производительность может ухудшиться, если он выполняется несколько раз одновременно или если вы запускаете больше параллельных потоков, чем 2на ядро процессора.

На практике параллельные потоки занимают примерно половину времени выполнения на 4 потока.

Кластеризованные таблицы / индексы

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

Контекст

Просмотр запроса в изоляции не всегда дает лучший ответ — выполнение чего-то действительно быстрого, когда это может быть неправильно, не помогает, поэтому посмотрите на контекст, т.Е. Что вы будете делать с 35000 строк, которые были возвращены после их возврата, добавлены ли они только сегодня, есть ли таблица, в которой есть подмножество, которое могло быиспользовать вместо этого?

«мне пришлось денормализовать таблицу»

В базе данных OLTP де-нормализация всегда является плохой новостью. Это ускоряет некоторые запросы, но может замедлить другие. В лучшем случае это снижает производительность операций DML, в худшем — может привести к повреждению данных. Это, безусловно, усложняет наши приложения.

@trevorNorth справедливо указывает на контекст. Распределение данных имеет большое значение. Сколько строк в T_DIRECTORY соответствует этому идентификатору? Сколько строк в этом результирующем наборе имеют совпадающие строки в T_PERSON ? Соответствуют ли эти фактические значения мощности в плане объяснения? Если нет, возможно, обновление статистики базы данных позволит базе данных найти лучший план (без подсказки ИНДЕКСА). Но если ваша статистика актуальна, вам нужно другое решение.

Например … что?

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

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

1. Исключите чтение таблицы и, возможно, также получите хэш-соединение, создав составные индексы:
   T_DIRECTORY(ID, PERSONID, DIR_ID)
T_PERSON(PERSONID,ID)
2. Создайте материализованное представление быстрого обновления для инструкции и разрешите переписывать запросы для удовлетворения запросов.

мне пришлось денормализовать таблицу.