时间戳

首页 > 解决方案 > 使用左连接时我可以有一个复合索引吗

sql - 使用左连接时我可以有一个复合索引吗

问题描述

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

 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 = 'Chris@Lailasman.com’;

它按预期工作，但是当我'EXPLAIN PLAN FOR'查询时没有使用索引，我只能将主键视为索引。我是否在错误的表上创建了索引？出现的问题是我想使用复合键，但是，连接表不包含任何用于复合索引的列。

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"='Chris@Lailasman.com' AND
              MONTHS_BETWEEN(SYSDATE@!,INTERNAL_FUNCTION("STUDENT"."DATEOFBIRTH"))/12>=18)
   3 - access("STUDENT"."PHONENUMBER"='07123456788')
   4 - access("ENROLMENT"."STUDENTNUMBER"="STUDENT"."STUDENTNUMBER")

标签： sqloracleindexingsqlplus

解决方案

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

CREATE INDEX email_phonenumber_student_idx
  ON student(phonenumber, email);

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

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

因此，Oracle 没有理由对phonenumber, email. 您只是选择复合键的那些列，而不是过滤它们：

SELECT Phonenumber, email 
from student left join Xyz

当您PROJECT这些列时，将使用索引，而不仅仅是SELECT。STUDENT正如预期的那样，该表适用于 a FULL TABLE SCAN，因为它是一个普通的选择，并且不对索引列使用任何过滤器。如果您想查看正在发生的索引扫描，请添加以下过滤器：

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

推荐阅读