老熊的三分地-Oracle及数据恢复

一条看上去很简单的SQL：

SELECT * FROM V_CALL_EVENT_10906 
WHERE to_char(start_date, 'yyyymmdd') in ('20090620', '20090621', '20090622') 

执行时长比较长，以至于出现ORA-01555错误，由于返回的结果数据行数非常大，取1月之内3天的数据，不太适合于使用索引，同时应用结构上决定了，也不能按天分区。

这里如果我们能够把表访问从6次，改为1次，那么性能就能大幅提升，这里修改视图的定义如下：

V_CALL_EVENT_10906视图定义如下：

CREATE VIEW V_CALL_EVENT_10906 
AS
SELECT ACCT_ID1 ACCT_ID,
               SERV_ID,
               EVENT_TYPE_ID,
               ACCT_ITEM_TYPE_ID1 ACCT_ITEM_TYPE_ID,
               CALLING_AREA_CODE,
               CALLING_NBR,
               CALLED_AREA_CODE,
               CALLED_NBR,
               START_DATE,
               START_DATE + DURATION / 3600 / 24 END_DATE,
               DURATION,
               CHARGE1 CHARGE,
               BILLING_CYCLE_ID,
               TO_DATE(CREATED_DATE) CREATED_DATE,
               TO_DATE(START_DATE) DATA_DATE,
               RESERVED_FIELD1,
               1 SPLIT_ID
 FROM CALL_EVENT_10906
union all
SELECT ACCT_ID1 ACCT_ID,
               SERV_ID,
               EVENT_TYPE_ID,
               ACCT_ITEM_TYPE_ID2 ACCT_ITEM_TYPE_ID,
               CALLING_AREA_CODE,
               CALLING_NBR,
               CALLED_AREA_CODE,
               CALLED_NBR,
               START_DATE,
               START_DATE + DURATION / 3600 / 24 END_DATE,
               DURATION,
               CHARGE2 CHARGE,
               BILLING_CYCLE_ID,
               TO_DATE(CREATED_DATE) CREATED_DATE,
               TO_DATE(START_DATE) DATA_DATE,
               RESERVED_FIELD1,
               2 SPLIT_ID
 FROM CALL_EVENT_10906
WHERE ACCT_ITEM_TYPE_ID2 != 0
  AND ACCT_ITEM_TYPE_ID2 IS NOT NULL

为节省篇幅，这个视图的定义实际上没有完全列出，视图中实际有5个“UNION ALL”，也就是CALL_EVENT_10906实际访问了6次。

CREATE VIEW V_CALL_EVENT_10906 
AS 
select /*+ no_merge(v) no_push_pred(v) */
v.* FROM 
(SELECT /*+   parallel(a,4) */
          ACCT_ID1 ACCT_ID,
                   SERV_ID,
                   EVENT_TYPE_ID,
                   DECODE(B.SPLIT_ID, 1, ACCT_ITEM_TYPE_ID1, 2, ACCT_ITEM_TYPE_ID2, 3, ACCT_ITEM_TYPE_ID3,   4, ACCT_ITEM_TYPE_ID4, 5, ACCT_ITEM_TYPE_ID5, 6,ACCT_ITEM_TYPE_ID6,0) ACCT_ITEM_TYPE_ID,
                   CALLING_AREA_CODE,
                   CALLING_NBR,
                   CALLED_AREA_CODE,
                   CALLED_NBR,
                   START_DATE,
                   START_DATE + DURATION / 3600 / 24 END_DATE,
                   DURATION,
                   DECODE(B.SPLIT_ID, 1, CHARGE1, 2, CHARGE2, 3, CHARGE3, 4, CHARGE4, 5, CHARGE5, 6,CHARGE6,0) CHARGE,
                   BILLING_CYCLE_ID,
                   TO_DATE(CREATED_DATE) CREATED_DATE,
                   TO_DATE(START_DATE) DATA_DATE,
                   RESERVED_FIELD1,
                   B.SPLIT_ID SPLIT_ID
           FROM CALL_EVENT_10906812 A,
                                   (
                  SELECT 1 SPLIT_ID
                    FROM DUAL
                  UNION ALL
                  SELECT 2
                    FROM DUAL  
                  UNION ALL  
                  SELECT 3
                    FROM DUAL
                  UNION ALL  
                  SELECT 4
                    FROM DUAL
                  UNION ALL  
                  SELECT 5
                    FROM DUAL
                  UNION ALL  
                  SELECT 6 FROM DUAL) B
) v,(select /*+ no_merge */ 0 id from dual) K
where nvl(v.acct_item_type_id,0) !=k.id;

通过UNION DUAL表，得到6行结果，同时与CALL_EVENT_10906表之间没有任何关联条件，这样就会形成笛卡尔连接（cartesian join），CALL_EVENT_10906这个表的每一行数据，将实际产生6行输出。这样就避免了对这个表扫描6次。

为什么这里还要嵌套一层，再加上这样的条件：

where nvl(v.acct_item_type_id,0) !=k.id

这个条件实际上是：

where nvl(v.acct_item_type_id,0) !=0

如果不嵌套一层，那么就会形成CALL_EVENT_10906与DUAL表UNION之后的结果之后的连接关系，就不会使用cartesian join了。