Как принудительно использовать индекс в DB2 при использовании скалярного значения, возвращаемого подзапросом?

Запрос выполняется на огромной таблице (1 миллиард записей +)

Выберите Col1 из таблицы A, где identcol > (выберите bigint (identval) из ParamTable)

(identval тип данных — это символ, а identcol — это BIGINT). Это занимает много времени и выполняет полное сканирование таблицы. Когда я заменяю подзапрос и использую числовое значение в предложении where для identcol, он начинает использовать индекс этого столбца.

Есть ли способ заставить DB2 использовать этот индекс без указания буквального значения? (DB2 9.7 luw на AIX)

edit: я обнаружил, что BETWEEN работает с подзапросом и верхним значением, а также использует индекс. Также, если я удалю явное приведение к BIGINT, он начнет использовать индекс. Но все же хотелось бы знать техническое объяснение/указатели. Спасибо!


performance indexing db2 database-performance sql-tuning
person dbza    schedule 09.09.2015    source источник

Ответы (1)


arrow_upward
2
arrow_downward

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

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

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

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

  1. Создайте статистическое представление, что-то вроде create view sv1 as select Col1 from TableA, ParamTable where TableA.identcol > ParamTable.identval (и удалите bigint() из запроса — не нужно еще больше запутывать оптимизатор). Затем вам нужно будет включить оптимизацию представления статистики (alter view sv1 enable query optimization) и собрать статистику распределения (runstats on table sv1 with distribution). Однако, видя, что ParamTable имеет только одну строку, можно предположить, что она меняется довольно часто, поэтому вам нужно убедиться, что статистика обновляется каждый раз, когда изменяется ParamTable.identval.

  2. Предоставьте оптимизатору явную оценку селективности: Select Col1 from TableA where identcol > (select bigint(identval) from ParamTable) selectivity 0.001. Прежде чем вы сможете это сделать, вам нужно установить переменную реестра DB2, разрешающую это поведение (db2set DB2_SELECTIVITY=ALL), и перезапустить экземпляр. 0.001 указывает долю таблицы, в данном случае 0,1%, которая удовлетворяет условию. Замените 0.001 реалистичной оценкой доли совпадающих строк.

  3. Создайте индекс для TableA (identcol, Col1), если он еще не существует (и соберите для него статистику) — это может разрешить доступ только к индексу.

person mustaccio    schedule 10.09.2015
comment
Большое спасибо! Я попробую оценить селективность и посмотрю, работает ли. № 3 невозможен в моем конкретном случае, есть и другие соображения. - person dbza; 10.09.2015
comment
Явная избирательность не работает с подзапросом, он выдает SQL20046N, требуется допустимый определяемый пользователем предикат. - person dbza; 10.09.2015
comment
Похоже, вы не выполнили db2set DB2_SELECTIVITY=YES или не перезапустили экземпляр после этого, или и то, и другое. - person mustaccio; 10.09.2015
comment
Вместо этого попробуйте db2set DB2_SELECTIVITY=ALL. - person mustaccio; 10.09.2015
comment
хорошо спасибо! Я думал, что это синтаксическая ошибка из сообщения. К сожалению, у меня нет доступа для изменения параметров базы данных. - person dbza; 10.09.2015