一条看上去很简单的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了。

,
Trackback

16 comments untill now

  1. 精彩~~
    老熊,要实现一次全表扫,有没考虑用行转列过程来实现呢?

    老熊 Reply:

    @Freedba, 你说的行列转换可不能使1行输出为6行啊。

  2. 精彩!

  3. 老熊,no_merge(v) no_push_pred(v)这两个参数主要的作用是什么?

  4. @kenlee, no_merge,不要进行视图合并,no_push_pred,不要将连接谓词推到视图内部。no_push_pred一般是跟no_merge一起使用。可以参考:
    http://download.oracle.com/docs/cd/B12037_01/server.101/b10752/hintsref.htm

  5. 如果b
    定义为(select level SPLIT_ID from dual connect by level<=6 )是否可以达到同样效果

    老熊 Reply:

    @l1t, 对,完全可以,这种写法更简洁。

  6. 老熊,这里你用Decode一次把所有的资料都找到,而替换了union all.所以我不明白为什么还要下面这些?

    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)

    感觉这个是画蛇添足,不知道最后做这个笛卡尔积优势很么用处

    老熊 Reply:

    @zabll, DECODE(B.SPLIT_ID 这段代码,SPLIT是一个构造出来的列,并不是表中实际的数据。

    通常的SQL,结果集行数是小于等于源数据的,但这里我的目的是要多于源数据,也就是源数据是1行,结果是6行。

  7. 谢谢,小弟才疏学浅,能否模拟一个类似的表和SQL让我们测试一下?
    光看的话,看不明白啊。。。。。。。。。。

    老熊 Reply:

    @zabll, 我这里举一个简单的例子:
    CREATE TABLE T1 (
        ORDER_ID NUMBER,
        CHARGE1 NUMBER,
        CHARGE2 NUMBER,
        CHARGE3 NUMBER
    )

    假设这里CHARGE1,CHARGE2,CHARGE3分别为三种不同的费用。如果现在某一个应用需要这个表转换为如下的形式:
    CREATE TABLE T2 (
        ORDER_ID NUMBER,
        CHARGE_TYPE NUMBER,
        CHARGE NUMBER

    那么就可以用下面的SQL:
    SELECT ORDER_ID,CHARGE_TYPE,DECODE(CHARGE_TYPE,1,CHARGE1,2,CHARGE2,3,CHARGE3) CHARGE
    FROM T1,(SELECT 1 CHARGE_TYPE FROM DUAL 
       UNION ALL SELECT 2 FROM DUAL 
       UNION ALL SELECT 3 FROM DUAL
    )
         

  8. hz8193102 @ 2009-09-29 15:51

    用函数索引就可以了吧。

    老熊 Reply:

    @hz8193102, 我觉得恐怕不行。

  9. 熊哥,还是不是很明白

    CREATE TABLE T1 (
    ORDER_ID NUMBER,
    CHARGE1 NUMBER,
    CHARGE2 NUMBER,
    CHARGE3 NUMBER
    )
    insert into t1 values (1,11,”,”);
    insert into t1 values (2,”,22,”);
    insert into t1 values (3,”,”,33);

    commit

    SELECT ORDER_ID,CHARGE_TYPE,DECODE(CHARGE_TYPE,1,CHARGE1,2,CHARGE2,3,CHARGE3) CHARGE
    FROM T1
    ,(SELECT 1 CHARGE_TYPE FROM DUAL UNION ALL SELECT 2 FROM DUAL UNION ALL SELECT 3 FROM DUAL)

    ORDER_ID|CHARGE_TYPE|CHARGE
    1|1|11
    2|1|
    3|1|
    1|2|
    2|2|22
    3|2|
    1|3|
    2|3|
    3|3|33

    觉得数据很乱,不知道为什么要这样,或者说我构造数据不对头,理解上有点问题?

    老熊 Reply:

    @zabll,
    对的,要的就是你的效果。是这样子的,这个数据呢,是一个应用系统的表,要提供给另一个应用使用,而另一个应用对表数据的要求就是这样。再举个例子,以考试成绩为例:
    原始表是
    姓名 语文成绩 数学成绩 英语成绩
    而另一个应用要求是下面的形式
    姓名 科目 成绩
    就是把同一姓名的成绩,分成几行来表达。

  10. 很好的思路

Add your comment now