连接到另一个表时未使用 Oracle 索引

Question

我在 Oracle 10g 数据库上运行以下 sql：

select  /*+ ALL_ROWS */
       to_number(cv.old_value) as cv_id,
       to_number(job.old_value) as job_id
  from amendments,
       amnddets cv,
       amnddets job
 where amendments.table_name = 'TIMESHEET_LAYER'
   and amendments.dml_type = 'D'
   and cv.amnd_id = amendments.amnd_id 
   and cv.column_name = 'CV_ID'
   and job.amnd_id = amendments.amnd_id
   and job.column_name = 'JOB_ID';

已创建以下索引：

create index amendments_dmp_type_upper on amendments upper(dmp_type);
create index amendments_table_name_upper on amendments upper(table_name);
create index amendments_pk on amendments (amnd_id);
create index amended_column_name_idx on amnddets (column_name);
create index amnddets_amnd_id_idx on amnddets (amnd_id);

我还尝试使用 ANSI 连接（下面的 sql），但这也不使用索引，将 upper( table_name 和 dml_type 周围的 ) 也没有影响。

上述查询大约需要 30 - 40 秒才能检索大约 2500 行。

我查看了解释计划，看不到正在使用 table_name 和 dml_type 的 amendments 索引。

以下是 ANSI 解释计划：

select  /*+ ALL_ROWS */
       to_number(cv.old_value) as cv_id,
       to_number(job.old_value) as job_id
  from amendments
  JOIN amnddets cv on cv.amnd_id = amendments.amnd_id and cv.column_name = 'CV_ID'
  JOIN amnddets job on job.amnd_id = amendments.amnd_id and job.column_name = 'JOB_ID'
 where upper(amendments.table_name) = 'TIMESHEET_LAYER'
   and amendments.dml_type = 'D';

任何人都可以建议为什么 table_name、dml_type 和上述查询中未使用 column_name 索引？

Answer 1

如果您实际上按照您所说的那样运行了此命令：

create index amendments_dmp_type_upper on amendments upper(dmp_type);

那么您实际创建的是 amendments(dmp_type) 上的索引，而没有 upper 函数！

正确的语法是：

create index amendments_dmp_type_upper on amendments (upper(dmp_type));

也许令人惊讶的是，您的语句有效，但单词“upper”被视为表别名 - 这也有效：

create index amendments_dmp_type_upper on amendments foo(dmp_type);

Answer 2

我不是 Oracle 专家，但它似乎没有使用（或无法使用）索引合并。这意味着您需要复合索引。

create index amnddets_column_name_amnd_id_idx on amnddets (column_name, amnd_id);

create index amendments_dml_type_table_name_amnd_id_idx on amendments (dml_type, table_name, amnd_id)