我可以在使用左联接时使用复合索引吗 [英] Can i have a composite index on when using a left join

问题描述

我的目标是在student表上使用复合索引.学生表将内部连接到xyz表上.我在学生表上创建了索引，如下所示:

My aim is to use a composite index on the student table. The Student table will be inner joined onto the xyz table. I created my index on the student table as follows:

 CREATE INDEX email_phonenumber_student_idx
  ON student(phonenumber, email);

运行查询时

SELECT Phonenumber, email from student 
left join enrolment on enrolment.studentnumber = student.studentnumber 
where months_between(SYSDATE, dateofbirth)/12 >= 18 and 
enrolment.studentnumber is null and 
student.phonenumber = '07123456788' and student.email = '[e-mail]’;

它可以按预期工作，但是索引没有被使用，因为当我'EXPLAIN PLAN FOR'查询时，我只能看到主键作为索引.我是否在错误的表上创建了索引?出现的问题是我想使用复合键，但是，联接表不包含任何用于复合索引的列.

It works as intended, but the index is not being used as when I 'EXPLAIN PLAN FOR' the query, I can only see the primary key as the index. Have I created the index on the wrong table? The issue arises is that I wanted to make use of a composite key, however, the joined table does not contain any columns for composite index use.

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1388008413

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |    63 |     0   (0)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI           |              |     1 |    63 |     0   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| STUDENT      |     1 |    50 |     0   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | SYS_C0022463 |     1 |       |     0   (0)| 00:00:01 |
|*  4 |   INDEX RANGE SCAN           | SYS_C0022468 |     1 |    13 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("STUDENT"."EMAIL"='[e-mail]' AND
              MONTHS_BETWEEN(SYSDATE@!,INTERNAL_FUNCTION("STUDENT"."DATEOFBIRTH"))/12>=18)
   3 - access("STUDENT"."PHONENUMBER"='07123456788')
   4 - access("ENROLMENT"."STUDENTNUMBER"="STUDENT"."STUDENTNUMBER")

推荐答案

它按预期工作. Oracle完全按照您的要求做了.

It works as expected. Oracle did exactly what you asked it to do.

CREATE INDEX email_phonenumber_student_idx
  ON student(phonenumber, email);

您在phonenumber, email上有一个复合索引，而没有使用查询的过滤谓词中的任何列:

You have a composite index on phonenumber, email, while you do not use any of the columns in the filter predicate of your query:

where months_between(SYSDATE, dateofbirth)/12 >= 18 
and  xyz.studentnumber is null;

因此，Oracle没有理由对phonenumber, email进行索引扫描.您只需选择组合键的这些列，而不过滤它们:

So there is no reason why Oracle would do an index scan on phonenumber, email. You are simply SELECTing those columns of composite key, not filtering them:

SELECT Phonenumber, email 
from student left join Xyz

索引将在您 PROJECT 这些列时使用，而不仅是 SELECT .如预期的那样，STUDENT表用于FULL TABLE SCAN，因为它是普通选择，并且在索引列上不使用任何过滤器.如果您想查看索引扫描的发生，请在过滤器下方添加:

Index will be used when you PROJECT those columns, not just SELECT. The STUDENT table as expected goes for a FULL TABLE SCAN as it is a plain select and doesn't use any filter on the indexed columns. If you want to see an index scan happening, then add below filter:

AND phonenumber = <value>
AND email = <value>

这篇关于我可以在使用左联接时使用复合索引吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！