Учитывает ли оптимизатор Oracle базовые столбцы подзапроса?
Допустим, у вас есть такой запрос:
with subselect as (
select foo_id
from foo
)
select bar_id
from bar
join subselect on foo_id = bar_id
where foo_id = 1000
Представьте, что у вас есть индекс по foo_id. Достаточно ли умна база данных Oracle, чтобы использовать индекс в запросе для строки «where foo_id = 1000»? ИЛИ, поскольку foo_id заключен в подзапрос, теряет ли Oracle информацию индекса, относящуюся к этому столбцу?
Оптимизатор запросов учитывает все столбцы в вашем запросе. Что заставляет вас думать, что это не так? В этом можно убедиться, изучив план запроса.
В некоторой степени невозможно ответить с уверенностью (возможно, оптимизатор выполнит сканирование таблицы, потому что в самой большой таблице всего 5 строк и использование индексов не стоит усилий), но нет причин предполагать, поскольку вы используете подзапросы, что оптимизатор слишком глуп, чтобы их рассматривать.
Оптимизатор анализирует каждый бит вашего запроса, чтобы определить план, и в новых версиях даже изменит планы в середине выполнения, если увидит, что принял неоптимальное решение. Подзапросы не являются особенными ... они в основном обрабатываются как любой другой «обычный» запрос. Использование индекса не всегда улучшает ситуацию. Посмотрите на план выполнения, чтобы узнать, как обрабатывались ваши подзапросы.
Ответы 1
Проведите простой тест:
create table foo as
select t.object_id as foo_id, t.* from all_objects t;
create table bar as
select t.object_id as bar_id, t.* from all_objects t;
create index foo_id_ix on foo(foo_id);
exec dbms_stats.GATHER_TABLE_STATS(ownname=>user, tabname=>'FOO', method_opt=>'FOR ALL INDEXED COLUMNS' );
explain plan for
with subselect as (
select foo_id
from foo
)
select bar_id
from bar
join subselect on foo_id = bar_id
where foo_id = 1000;
select * from table( DBMS_XPLAN.DISPLAY );
и результат последнего запроса:
Plan hash value: 445248211
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 366 (1)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN| | 1 | 10 | 366 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | BAR | 1 | 5 | 365 (1)| 00:00:01 |
| 3 | BUFFER SORT | | 1 | 5 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | FOO_ID_IX | 1 | 5 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("BAR_ID"=1000)
4 - access("FOO_ID"=1000)
В приведенном выше примере Oracle использует |* 4 | INDEX RANGE SCAN, используя индекс: FOO_ID_IX для фильтра 4 - access("FOO_ID"=1000)
.
Итак, ответ:
да, база данных Oracle достаточно умен, чтобы использовать индекс в запросе для строки «где foo_id = 1000»
Что указано в плане?