先
analyze table tab_a compute statistics;
analyze table tab_b compute statistics;
alter session set optimizer_index_caching=90;
alter session set optimizer_index_cost_adj=50;
再试试
analyze table tab_a compute statistics;
analyze table tab_b compute statistics;
alter session set optimizer_index_caching=90;
alter session set optimizer_index_cost_adj=50;
再试试
where里面要有主键字段,获取的数据量占表的20%一下,用索引才是比较有意义的
FROM tab_a a, tab_b b
WHERE a.cola = b.coa
AND a.colb = 'a';a.colb上有索引吗?如果没有索引的话不用全表扫描还能有别的办法吗?
FROM tab_a a, tab_b b
WHERE a.cola = b.coa
AND a.colb = 'a';
a.colb这列没有索引,oracle当然要全表扫描了.
11:22:08 SQL> create table t2(a int primary key);表已创建。已用时间: 00: 00: 00.15
11:22:08 SQL> insert into t1 values (1);已创建 1 行。已用时间: 00: 00: 00.16Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE11:22:08 SQL> insert into t1 values (2);已创建 1 行。已用时间: 00: 00: 00.00Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE11:22:08 SQL> insert into t1 values (3);已创建 1 行。已用时间: 00: 00: 00.00Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE11:22:08 SQL> insert into t1 values (4);已创建 1 行。已用时间: 00: 00: 00.00Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE11:22:08 SQL> insert into t2 values (2);已创建 1 行。已用时间: 00: 00: 00.00Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE11:22:08 SQL> insert into t2 values (4);已创建 1 行。已用时间: 00: 00: 00.16Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE11:22:08 SQL> insert into t2 values (6);已创建 1 行。已用时间: 00: 00: 00.16Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE11:22:08 SQL> insert into t2 values (8);已创建 1 行。已用时间: 00: 00: 00.00Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE11:22:08 SQL> set autotrace on exp;
11:22:13 SQL> select a.a,b.a from t1 a,t2 b where a.a=b.a and a.a=2; A A
---------- ----------
2 2已用时间: 00: 00: 00.00Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 INDEX (UNIQUE SCAN) OF 'SYS_C006084' (UNIQUE)
3 1 INDEX (UNIQUE SCAN) OF 'SYS_C006085' (UNIQUE)11:22:20 SQL> show parameter optNAME TYPE VALUE
------------------------------------ ------- ------------------------------
object_cache_optimal_size integer 102400
optimizer_features_enable string 8.1.6
optimizer_index_caching integer 90
optimizer_index_cost_adj integer 50
optimizer_max_permutations integer 80000
optimizer_mode string CHOOSE
optimizer_percent_parallel integer 0
并且对这两个表以及表上的索引进行分析以便oracle得到统计信息。
select a.a,b.a from t2 b,t1 a where b.a=a.a and a.a=2;
第二:看a.a=2这些记录占总记录数的比例.大于20%建议使用全表扫描.
第三:使用全表扫描,使用hash表进行关联.速度会快一点.