Oracle执行计划和真正的执行过程

                            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,在做表设计时
尽量要定义好相关的约束，以利于数据库得到更加完整的信息.