Oracle执行计划和真正的执行过程
Oracle查询执行计划只是表示优化器生成的执行计划，并不代表真正的执行就是
这样的一个过程。我们来看一个例子，
创建一个表，并定义非空,然后插入一些数据，
create table t11 (a int not null);
SQL> insert into t11 select object_id from dba_objects;
91493 rows inserted
SQL> commit;
Commit complete
SQL> exec dbms_stats.gather_table_Stats('SYS','T1');
PL/SQL procedure successfully completed
首先我们来看执行一个查询
SQL> set autotrace on;
SQL> select * from t11 where a=1;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3265068757
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 26 | 41 (3)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T11 | 2 | 26 | 41 (3)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=1)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
220 consistent gets
0 physical reads
0 redo size
337 bytes sent via SQL*Net to client
540 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
由于a列上没有索引，该执行计划首先做全表扫描，然后再过滤.
我们再看第二个查询，只是谓词被换成了"a is null".
SQL> select * from t11 where a is null;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2137692275
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 0 (0)| |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| T11 | 101K| 1284K| 40 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
337 bytes sent via SQL*Net to client
540 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
从执行计划来看也是走全表扫描，但是我们看consistent gets这栏显示为0。所以我们在这里
可以计为oracle根本就没有做全表扫描。
总结：1，执行计划并不代表真正执行也是如此，在中间可能还一些语义优化;2,在做表设计时
尽量要定义好相关的约束，以利于数据库得到更加完整的信息.