在Steve O'Hearn的《SQL认证专家考试指南》中,我发现了以下段落:
在极少数情况下,当您创建一个复合索引以及调用同一索引的多个约束时,需要使用特殊语法。例如,如果我们决定在INVOICES表中的两列上创建一个复合索引,我们可以使用以下语法:
CREATE TABLE invoices
(
invoice_id NUMBER(11),
invoice_date DATE,
CONSTRAINT un_invoices_invoice_id UNIQUE (invoice_id, invoice_date)
USING INDEX (CREATE INDEX ix_invoices
ON invoices(invoice_id, invoice_date)),
CONSTRAINT un_invoices_invoice_date UNIQUE (invoice_date, invoice_id)
USING INDEX ix_invoices
);
以下是我的问题:
创建两个唯一的约束只更改声明中的列顺序有什么意义?
我们创建了一个多列索引:"invoice_id"作为第一列,"invoise_date"作为第二列。但是,让我们假设我们经常运行与"invoice_date"本身相关的查询,而没有"invoise_id"的参与。在"invoice_date"上创建第二个单列索引是个好主意吗?我知道:
由于Oracle支持多列索引,因此很容易意外创建"重复"索引,这些索引会给DML增加开销,并且无助于加快SQL执行。[来源]
我也知道:
由于跳过扫描,引用的WHERE子句在处理复合索引时可以调用该索引。[Steve O'Hearn]
但我也知道:
这并不像简单的单列索引那样有益,而且它的好处各不相同,具体取决于第一列中值的唯一性。[Steve O'Hearn]
因此,让我们假设我们很少在该表上使用DML命令,并假设我们与SELECT的WHERE子句中的两列相关,就像分别与"index_date"或"index_id"相关一样频繁。在某些情况下,创建两个索引是否合理?第一,多列索引,在(index_id,index_date)上,第二,单列索引,在?
您的问题是:
在某些情况下,创建两个索引是否合理?一个,多列索引,在(index_id,index_date)上,第二个,单列索引,在(index_date)上?
答案是肯定的。第一个索引将用于满足以下条件的查询:
where
子句中index_id
的过滤where
子句中index_id
和index_date
的过滤where
子句中index_id
的过滤与index_date
的排序
在这种情况下不会使用第二个索引。它将用于:
where
子句中index_date
的过滤
在这种情况下,第一个索引将不使用。
索引中列的顺序很重要。它们是从左到右使用的。因此,这两个索引是有用的。然而,单独在index_id
上使用第三个索引是没有用的,因为第一个索引已经处理了使用该索引的相同情况。
你问
"创建两个唯一的约束只更改声明中的列顺序有什么意义?"
没有任何意义。复合约束中列的顺序没有任何区别:
SQL> select * from t23
2 /
COL1 COL
---------- ---
1 WTF
SQL> create index t23_i on t23(col1, col2);
Index created.
SQL> alter table t23 add constraint t23_uk unique (col1 , col2) using index t23_i
2 /
Table altered.
SQL> insert into t23 values (1, 'WTF')
2 /
insert into t23 values (1, 'WTF')
*
ERROR at line 1:
ORA-00001: unique constraint (APC.T23_UK) violated
SQL> alter table t23 drop constraint t23_uk
2 /
Table altered.
SQL> alter table t23 add constraint t23_uk unique (col2, col1) using index t23_i
2 /
Table altered.
SQL> insert into t23 values (1, 'WTF')
2 /
insert into t23 values (1, 'WTF')
*
ERROR at line 1:
ORA-00001: unique constraint (APC.T23_UK) violated
SQL>
这就是应试者的问题:他们通常只是说些什么,没有提供解释或上下文。
你还问:
"在
invoice_date
上创建第二个单列索引是个好主意吗?"
在不了解数据的情况下,很难判断,但我希望日期列的选择性不如ID列(尤其是在时间元素被截断的情况下),所以通常我希望索引构建为(invoice_date, invoice_id)
。这可能允许我们使用索引压缩。
跳过扫描并不像Steve所说的那样起作用:它从探测索引的前沿开始,但前提是WHERE子句中引用了复合索引中的第二列。优化器可能会选择"完全快速索引扫描"来搜索第三列或更低的列。此外,如果前沿有太多不同的值,它也不会选择跳过扫描路径:这是选择性地以低列开头的另一个很好的原因。
所以,这并不能完全回答你的问题,但我认为它确实传达了一个重要的观点:没有通用的规则来管理为性能创建索引。我们需要了解数据的概况——它的值分布和数量——以及将使用该表的最重要的查询。