事实胜于雄辩CASE1: SQL> select * from indextest where a = 1 and b = 2; A B C D E ---------- ---------- ---------- ---------- ---------- 1 2 3 1 3 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'INDEXTEST' 2 1 INDEX (RANGE SCAN) OF 'UNIONINDEX' (NON-UNIQUE) case2: SQL> select * from indextest where d = 1 and b = 2 aND A = 1; A B C D E ---------- ---------- ---------- ---------- ---------- 1 2 3 1 3 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'INDEXTEST' 2 1 INDEX (RANGE SCAN) OF 'UNIONINDEX' (NON-UNIQUE)case3: SQL> select * from indextest where a = 1 and b = 2 and c = 3; A B C D E ---------- ---------- ---------- ---------- ---------- 1 2 3 1 3 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'INDEXTEST' 2 1 INDEX (RANGE SCAN) OF 'UNIONINDEX' (NON-UNIQUE)case4: SQL> select max(d) from indextest group by a, b , c; MAX(D) ---------- 1 1 1 1 1 1 17 rows selected. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (GROUP BY) 2 1 TABLE ACCESS (FULL) OF 'INDEXTEST'
下面我们来看看再加上一个index(D column) SQL> create index anotherindexond on indextest (d);Index created.case5:SQL> select * from indextest where a = 1 and b = 2; A B C D E ---------- ---------- ---------- ---------- ---------- 1 2 3 1 3 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'INDEXTEST' 2 1 INDEX (RANGE SCAN) OF 'UNIONINDEX' (NON-UNIQUE)case6: SQL> select * from indextest where d = 1 and b = 2 aND A = 1; A B C D E ---------- ---------- ---------- ---------- ---------- 1 2 3 1 3 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'INDEXTEST' 2 1 INDEX (RANGE SCAN) OF 'ANOTHERINDEXOND' (NON-UNIQUE)/*注意用了另一个index*/case7: QL> select * from indextest where a = 1 and b = 2 and c = 3; A B C D E --------- ---------- ---------- ---------- ---------- 1 2 3 1 3 xecution Plan --------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'INDEXTEST' 2 1 INDEX (RANGE SCAN) OF 'UNIONINDEX' (NON-UNIQUE)case8:SQL> select max(d) from indextest group by a, b , c; MAX(D) ---------- 1 1 1 1 1 1 17 rows selected. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (GROUP BY) 2 1 TABLE ACCESS (FULL) OF 'INDEXTEST'
如果是CBO,选择哪个索引与被索引列的数据有关
多谢black_snail(●龙飞虎○) !!补充一个测试结果: case9: SQL> select * from indextest where d = <2 and A = 1; A B C D E ---------- ---------- ---------- ---------- ---------- 1 2 3 1 3/*这一次使用的是原来的index*/多谢black_snail(●龙飞虎○) !!再共享一个强制使用索引的例子: SQL> select address from address where upper(name) like 'JOHN' ; ADDRESS -------------------- cleveland 1 row selected. Execution Plan -------------------- SELECT STATEMENT TABLE ACCESS FULL ADDRESS 可以看到,在这种情况下,Oracle优化器对ADDRESS 表作了一次完整的扫描,而没有使用NAME 列的索引。这是因为索引是根据列中数据的实际值建立的,而UPPER 函数已经将字符转换成大写,即修改了这些值,因此该查询不能使用这列的索引。优化器不能与索引项比较"JOHN",没有索引项对应于"JOHN"-只有"john" 。 值得庆幸的是,如果在这种情况下想要强制使用索引,有一种简便的方法:只要在WHERE 子句中增加一个或多个特定的条件,用于测试索引值,并减少需要扫描的行,但这并没有修改原来SOL 编码中的条件。以下列查询语句为例: SQL> select address from address where upper(name) like 'JO%' AND (name like 'J%' or name like 'j%'); ADDRESS -------------------- cleveland 1 row selected. Execution Plan -------------------- SELECT STATEMENT CONCATENATION TABLE ACCESS BY INDEX ROWID ADDRESS INDEX RANGE SCAN ADDRESS_I TABLE ACCESS BY INDEX ROWID ADDRESS INDEX RANGE SCAN ADDRESS_I
1我一次需要插入大量数据,后来我采取了分类 分几次insert,commit,这样是不是可以提高速度??
2需要清空表的时候,我用的execute immediate 'truncate table ...';
是不是比delete ... commit;快?
2.truncate不能回滚,比delete快
------from oracle8数据库管理员手册还有上面的问题没有查到,到底第二种情况使用索引吗?第一种都可以使用
SQL> select * from indextest where a = 1 and b = 2; A B C D E
---------- ---------- ---------- ---------- ----------
1 2 3 1 3
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'INDEXTEST'
2 1 INDEX (RANGE SCAN) OF 'UNIONINDEX' (NON-UNIQUE)
case2:
SQL> select * from indextest where d = 1 and b = 2 aND A = 1; A B C D E
---------- ---------- ---------- ---------- ----------
1 2 3 1 3
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'INDEXTEST'
2 1 INDEX (RANGE SCAN) OF 'UNIONINDEX' (NON-UNIQUE)case3:
SQL> select * from indextest where a = 1 and b = 2 and c = 3; A B C D E
---------- ---------- ---------- ---------- ----------
1 2 3 1 3
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'INDEXTEST'
2 1 INDEX (RANGE SCAN) OF 'UNIONINDEX' (NON-UNIQUE)case4:
SQL> select max(d) from indextest group by a, b , c; MAX(D)
----------
1
1
1
1
1
1
17 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (FULL) OF 'INDEXTEST'
SQL> create index anotherindexond on indextest (d);Index created.case5:SQL> select * from indextest where a = 1 and b = 2; A B C D E
---------- ---------- ---------- ---------- ----------
1 2 3 1 3
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'INDEXTEST'
2 1 INDEX (RANGE SCAN) OF 'UNIONINDEX' (NON-UNIQUE)case6:
SQL> select * from indextest where d = 1 and b = 2 aND A = 1; A B C D E
---------- ---------- ---------- ---------- ----------
1 2 3 1 3
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'INDEXTEST'
2 1 INDEX (RANGE SCAN) OF 'ANOTHERINDEXOND' (NON-UNIQUE)/*注意用了另一个index*/case7:
QL> select * from indextest where a = 1 and b = 2 and c = 3; A B C D E
--------- ---------- ---------- ---------- ----------
1 2 3 1 3
xecution Plan
---------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'INDEXTEST'
2 1 INDEX (RANGE SCAN) OF 'UNIONINDEX' (NON-UNIQUE)case8:SQL> select max(d) from indextest group by a, b , c; MAX(D)
----------
1
1
1
1
1
1
17 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (FULL) OF 'INDEXTEST'
case9:
SQL> select * from indextest where d = <2 and A = 1; A B C D E
---------- ---------- ---------- ---------- ----------
1 2 3 1 3/*这一次使用的是原来的index*/多谢black_snail(●龙飞虎○) !!再共享一个强制使用索引的例子:
SQL> select address from address where upper(name) like 'JOHN' ;
ADDRESS
--------------------
cleveland
1 row selected.
Execution Plan
--------------------
SELECT STATEMENT
TABLE ACCESS FULL ADDRESS 可以看到,在这种情况下,Oracle优化器对ADDRESS 表作了一次完整的扫描,而没有使用NAME 列的索引。这是因为索引是根据列中数据的实际值建立的,而UPPER 函数已经将字符转换成大写,即修改了这些值,因此该查询不能使用这列的索引。优化器不能与索引项比较"JOHN",没有索引项对应于"JOHN"-只有"john" 。 值得庆幸的是,如果在这种情况下想要强制使用索引,有一种简便的方法:只要在WHERE 子句中增加一个或多个特定的条件,用于测试索引值,并减少需要扫描的行,但这并没有修改原来SOL 编码中的条件。以下列查询语句为例: SQL> select address from address where upper(name) like 'JO%' AND (name like 'J%' or name like 'j%'); ADDRESS
--------------------
cleveland
1 row selected.
Execution Plan
--------------------
SELECT STATEMENT
CONCATENATION
TABLE ACCESS BY INDEX ROWID ADDRESS
INDEX RANGE SCAN ADDRESS_I
TABLE ACCESS BY INDEX ROWID ADDRESS
INDEX RANGE SCAN ADDRESS_I