[PHP]
数据库版本:9.2.0.8.0
--创建测试表
SQL> create table TT
2(
3A NUMBER,
4B NUMBER,
5C NUMBER,
6D NUMBER
7)
8;
Table created.
SQL> create sequence seq_id;
Sequence created.
SQL> begin
2for i in 1..10000 loop
3 insert into tt values(seq_id.nextval,seq_id.nextval,seq_id.nextval,seq_id.nextval);
4 end loop;
5 commit;
6end;
7/
PL/SQL procedure successfully completed.
SQL> create table t as select * from tt;
Table created.
--创建索引
SQL> create index idx_t_a on t(a);
Index created.
SQL> create index idx_tt_c on tt(c);
Index created.
--分析表和索引
SQL> analyze table t compute statistics for table for all columns for all indexes;
Table analyzed.
SQL> analyze table tt compute statistics for table for all indexed columns for all indexes;
Table analyzed.
SQL> set autotrace on;
SQL> select t.*from t,tt
2where t.a=4173 and t.b=tt.b and tt.c=4173;
A
B
C
D
---------- ---------- ---------- ----------
4173 4173 4173 4173
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=18)
10 TABLE ACCESS (BY INDEX ROWID) OF \'TT\' (Cost=1 Card=1 Bytes
=6)
21 NESTED LOOPS (Cost=2 Card=1 Bytes=18)--这段是最费解的
32 TABLE ACCESS (BY INDEX ROWID) OF \'T\' (Cost=1 Card=1 By
tes=12)
43 INDEX (RANGE SCAN) OF \'IDX_T_A\' (NON-UNIQUE) (Cost=1
Card=1)
52 INDEX (RANGE SCAN) OF \'IDX_TT_C\' (NON-UNIQUE)
都不知道oracle按照上面的计划执行，怎么也会出来正确的结果，令人费解啊！
Statistics
----------------------------------------------------------
0recursive calls
0db block gets
8consistent gets
0physical reads
0redo size
559bytes sent via SQL*Net to client
503bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
1rows processed
如果我在t(a,b)建组合索引，在tt(b,c)上建组合索引，执行下列查询，执行计划将会如下：
SQL>select count(*) from t,tt
2
where t.a=4173 and t.b=tt.b and tt.c=4173;
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=12)
10 SORT (AGGREGATE)
21 NESTED LOOPS (Cost=2 Card=1 Bytes=12)
32 INDEX (RANGE SCAN) OF \'IDX_T_AB\' (NON-UNIQUE) (Cost=2
Card=1 Bytes=6)
42 INDEX (RANGE SCAN) OF \'IDX_TT_BC\' (NON-UNIQUE)
[/PHP]
|
