Oracle约束Constraint对于CBO优化器的作用(2)

两个特点，一个是执行计划中首先进行了Index Full Scan对主键索引进行扫描，第二个是进行了Sort Group By Nosort。Sort Group是早期Hash Group出现之前的Group动作。而Sort Group By Nosort是做Sort Group By的动作，但是根本就不会进行排序、也不需要进行Group By。原因是什么？就是由于操作Index Full Scan获取到的结果集合是扫描叶子节点，本身就是有序的，不需要进行排序。不需要进行Group By的原因是每个叶子节点Group By的结果只有一个，就是1。
 
这种取巧的执行计划，我们在RBO上是看不到的。

SQL> explain plan for select /*+rule*/empno, count(*) from scott.emp group by empno;
 
 

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------
 
Plan hash value: 15469362

-----------------------------------

| Id  | Operation          | Name |

-----------------------------------

|  0 | SELECT STATEMENT  |      |

|  1 |  SORT GROUP BY    |      |

|  2 |  TABLE ACCESS FULL| EMP  |

-----------------------------------

Note

-----

- rule based optimizer used (consider using cbo)

13 rows selected

我们使用RBO的时候，Oracle进行Group By主键和其他列操作是一样的。

4、空值null Group

一般情况下，我们是不会遇到主键Group By的情况的。大多数的Group By都是有限Distinct值的。这种时候，在CBO情况下避免不了进行全表扫描。即使Group By列中创建了索引，索引也不会用到。
 
 

SQL> drop table t purge;

Table dropped

SQL> create table t as select * from dba_objects;

Table created

SQL> create index idx_t_owner on t(owner);

Index created

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

此时执行计划如下：

SQL> explain plan for select owner, count(*) from t group by owner;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------
 
Plan hash value: 47235625

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time    |

---------------------------------------------------------------------------

|  0 | SELECT STATEMENT  |      |    32 |  192 |  298  (2)| 00:00:04 |

|  1 |  HASH GROUP BY    |      |    32 |  192 |  298  (2)| 00:00:04 |

|  2 |  TABLE ACCESS FULL| T    | 75609 |  443K|  294  (1)| 00:00:04 |

---------------------------------------------------------------------------

9 rows selected

底层路径没有去走索引路径，根本原因在于object_id是null，允许为空。Group By操作是允许空值进入的。如果系统业务上恰恰不允许object_id设置为非空，怎么办？
 
问题的策略在于将空值object_id也进入到索引结构。此处可以使用常数组合索引方法。

SQL> create index idx_t_owner_cmp on t(owner,0);

Index created

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

SQL> explain plan for select owner, count(*) from t group by owner;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------
 
Plan hash value: 685106933

--------------------------------------------------------------------------------
 
| Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| T
 
--------------------------------------------------------------------------------
 
|  0 | SELECT STATEMENT      |                |    32 |  192 |    61  (9)| 0
 
|  1 |  HASH GROUP BY        |                |    32 |  192 |    61  (9)| 0
 
|  2 |  INDEX FAST FULL SCAN| IDX_T_OWNER_CMP | 75609 |  443K|    57  (2)| 0
 
--------------------------------------------------------------------------------
 
 

9 rows selected

执行成本值从原有的近300，下降到当前的61。