表t的信息如下:
CREATE TABLE t (
id NUMBER,
d1 DATE,
n1 NUMBER,
n2 NUMBER,
n3 NUMBER,
n4 NUMBER,
n5 NUMBER,
n6 NUMBER,
c1 VARCHAR2(20),
c2 VARCHAR2(20),
pad VARCHAR2(4000),
CONSTRAINT t_pk PRIMARY KEY (id)
);
execute dbms_random.seed(0)
INSERT INTO t
SELECT rownum AS id,
trunc(to_date('2007-01-01','yyyy-mm-dd')+rownum/27.4) AS d1,
nullif(1+mod(rownum,19),10) AS n1,
nullif(1+mod(rownum,113),10) AS n2,
nullif(1+mod(rownum,61),10) AS n3,
nullif(1+mod(rownum,19),10) AS n4,
nullif(1+mod(rownum,113),10) AS n5,
nullif(1+mod(rownum,61),10) AS n6,
dbms_random.string('p',20) AS c1,
dbms_random.string('p',20) AS c2,
dbms_random.string('p',255) AS pad
FROM dual
CONNECT BY level <= 10000
ORDER BY dbms_random.value;
CREATE INDEX i_n1 ON t (n1);
CREATE INDEX i_n2 ON t (n2);
CREATE INDEX i_n3 ON t (n3);
CREATE INDEX i_n123 ON t (n1, n2, n3);
CREATE BITMAP INDEX i_n4 ON t (n4);
CREATE BITMAP INDEX i_n5 ON t (n5);
CREATE BITMAP INDEX i_n6 ON t (n6);
CREATE INDEX i_c1 ON t (c1);
CREATE BITMAP INDEX i_c2 ON t (c2);
BEGIN
dbms_stats.gather_table_stats(
ownname => user,
tabname => 'T',
estimate_percent => 100,
method_opt => 'for all columns size skewonly',
cascade => TRUE
);
END;
/
用到了组合索引,这个没错。
SQL> explain plan for
2 select * from t where n1=6 and n2=42 and n3=11;
Explained.
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 327 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 327 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_N123| 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - access("N1"=6 AND "N2"=42 AND "N3"=11)
但是只查N1和N3时候就有问题了。
SQL> explain plan for
2 select * from t where n1=6 and n3=11;
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 2943 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 9 | 2943 | 6 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP AND | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 5 | INDEX RANGE SCAN | I_N3 | 9 | | 1 (0)| 00:00:01 |
| 6 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 7 | INDEX RANGE SCAN |I_N1| 9 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("N3"=11)
7 - access("N1"=6)
20 rows selected.
跟原本还是用组合索引I_N123的期望不一样啊。这是怎么回事呢?少加一个条件就用不了了。查看了下直方图信息都有。