技术开发 频道

巧用Cartesian连接优化SQL语句

  【IT168 技术文档】一条看上去很简单的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了。

0
相关文章