索引定义改进-(Oracle数据库)

Question

我有以下表格定义，希望改进索引：

CREATE TABLE MATE (
GUID NUMBER(38,0),
SITE_KEY NUMBER(38,0),
LAST_NAME VARCHAR2(200),
FIRST_NAME VARCHAR2(200),   
BOOKING_NUM VARCHAR2(200),
RELEASE_DATE DATE,
STATUS VARCHAR2(200), -- Contains 'ACTIVE', 'RELEASED', 'DELETED', 'EXCLUDED', 'INACTIVE' and NULL
CONSTRAINT SYS_C008630 CHECK ("GUID" IS NOT NULL),
CONSTRAINT SYS_C008631 CHECK ("SITE_KEY" IS NOT NULL),
CONSTRAINT SYS_C008632 PRIMARY KEY (GUID, SITE_KEY),
CONSTRAINT FK8100EDAADECFC243 FOREIGN KEY (SITE_KEY) REFERENCES SITES<KEY>()
);
CREATE UNIQUE INDEX SYS_C008632 ON MATE (GUID, SITE_KEY);  -- This is the PK (1)
CREATE INDEX IDX_STATUS ON MATE (STATUS);  -- (2)
CREATE INDEX IDX_SITE_KEY ON MATE (SITE_KEY); -- (3)
CREATE INDEX IDX_BOOKING_NUMBER ON MATE (BOOKING_NUM); -- (4)
CREATE INDEX IDX_FNAME ON MATE (FIRST_NAME); -- (5)
CREATE INDEX IDX_LNAME ON MATE (LAST_NAME); -- (6)
CREATE INDEX BRIAN2_IX ON MATE (SITE_KEY,BOOKING_NUM); -- (7)
CREATE INDEX IDX_SITE_STATUS ON MATE (SITE_KEY,STATUS); -- (8)
CREATE INDEX IDX_PIN_SITEKEY ON MATE (BOOKING_NUM,SITE_KEY); -- (9)
CREATE INDEX IDX_SITE_NAME_STATUS ON MATE (SITE_KEY,LAST_NAME,STATUS); -- (10)
CREATE UNIQUE INDEX IDX_GUID_SITE_BOOKING ON MATE (GUID, SITE_KEY, BOOKING_NUM); -- (11)
CREATE UNIQUE INDEX IXU_SITE_BOOKING_GUID ON MATE (SITE_KEY, BOOKING_NUM, RELEASE_DATE, GUID); -- (12)

符合逻辑：

1. 删除索引(7(，因为已在(9(中定义
2. 删除索引(3(，因为(8(和(10(中最左边
3. 删除索引(4(，因为它是(9(中最左边的
4. 是否删除索引(12(，因为SITE_KEY、BOOKING_NUM、GUID已作为(11(中的唯一索引
5. 还有其他改进吗

Answer 1

您不能仅通过查看索引的定义来优化索引。在删除索引之前，您需要了解索引是如何使用的。

你的索引不一定是多余的

对于项目#1和#3，在极少数情况下，您希望有两个仅根据列顺序不同的索引。例如，对于下面的两个查询，有一个同时包含两列的索引会有所帮助，这样就可以避免从表中读取。两个不同的前导列对每个查询都能更好地工作。只有一个索引通常就足够了，但这些查询可能是需要彻底优化的关键查询。

SELECT A, B FROM TABLE1 WHERE A = 1;
SELECT A, B FROM TABLE1 WHERE B = 2;

对于项目#2和#4，单列索引可以针对筛选进行优化，而多列索引可以针对索引快速全扫描进行优化(其中索引的作用类似于表的精简版本(。例如，对于下面的查询，第一个查询只在列A上有索引时运行得最好，因为该索引较小，读取速度更快，而且更有可能放入缓存。但是，如果在(A，B，C(上有索引，则第二个查询效果最好。拥有单个较大的索引通常就足够了，但并不总是如此。

SELECT * FROM TABLE1 WHERE A = 1;
SELECT A, B, C FROM TABLE1;

哪些索引是必要的

要找出哪些索引是必要的，您应该使用索引使用情况跟踪。全面优化索引是一个漫长而困难的过程。但是，如果您已经收集了一个可疑索引列表，并且任何SQL语句都没有使用这些索引，那么它们可能可以安全删除。

--Check that index statsitics are collected.
select * from gv$index_usage_info;
--Check which indexes are used.
select * from dba_index_usage order by last_used desc;
--Find recent SQL statements that used the index.
select * from gv$sql_plan where object_owner = 'JHELLER' and object_name = 'TEST1_IDX';
--Find historical SQL statements that used the index.
select * from dba_hist_sql_plan where object_owner = 'JHELLER' and object_name = 'TEST1_IDX';