最初由 mychary 发布
[B]
基于COST可以 [/B]
[php]
SQL> truncate table t;
Table truncated.
SQL> insert into t(id ,name) values(1 , 'test');
1 row created.
SQL> insert into t(id ,name) values(2 , 'test1');
1 row created.
SQL> insert into t(id ,name) values(3 , 'test2');
1 row created.
SQL> commit;
Commit complete.
SQL> create index t_idx on t (id , name);
Index created.
SQL> set autotrace on explain
SQL> select * from t where id=1 and name='test';
ID NAME
COL
---------- -------------------- --------------------
1 test
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE
10 TABLE ACCESS (BY INDEX ROWID) OF 'T'
21 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE)
SQL> select * from t where name='test' and id =1;
ID NAME
COL
---------- -------------------- --------------------
1 test
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE
10 TABLE ACCESS (BY INDEX ROWID) OF 'T'
21 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE)
SQL> select * from t where id=1 or name='test';
ID NAME
COL
---------- -------------------- --------------------
1 test
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE
10 TABLE ACCESS (FULL) OF 'T'
SQL> select * from t where name='test' or id =1;
ID NAME
COL
---------- -------------------- --------------------
1 test
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE
10 TABLE ACCESS (FULL) OF 'T'
SQL> analyze table t compute statistics;
Table analyzed.
SQL> select * from t where id=1 and name='test';
ID NAME
COL
---------- -------------------- --------------------
1 test
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=12)
10 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=1 Bytes=
12)
21 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card=
1)
SQL> select * from t where name='test' and id =1;
ID NAME
COL
---------- -------------------- --------------------
1 test
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=12)
10 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=1 Bytes=
12)
21 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card=
1)
SQL> select * from t where id=1 or name='test';
ID NAME
COL
---------- -------------------- --------------------
1 test
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=2 Bytes=24)
10 CONCATENATION
21 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=1 Byte
s=12)
32 INDEX (SKIP SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card
=1)
41 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=1 Byte
s=12)
54 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Car
d=1)
SQL> select * from t where name='test' or id =1;
ID NAME
COL
---------- -------------------- --------------------
1 test
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=2 Bytes=24)
10 CONCATENATION
21 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=4 Card=1 Byte
s=12)
32 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=3 Car
d=1)
41 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=4 Card=1 Byte
s=12)
54 INDEX (SKIP SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=3 Card
=1)
'''''''''''''''''''''''''
SQL>
[/php]
有疑惑就做试验！
|
