巧用rowid简化sql查询(r2笔记47天)

生产系统中有一条sql语句,目前执行的时间有点长了,而且看起来有些臃肿,客户问能不能改进一下。得到的sql语句如下: SELECT COUNT(1) FROM ( SELECT /*+ leading (payment_temp_table payment PAYMENT_DETAILS account memo) use_nl (payment_temp_table ar1_payment PAYMENT_DETAILS account memo) index (payment payment_pk) index (PAYMENT_DETAILS PAYMENT_DETAILS_pk) */ PAYMENT_DETAILS.PAYMENT_ID, PAYMENT.CREDIT_ID, PAYMENT.ACCOUNT_ID, PAYMENT.AMOUNT, PAYMENT.ORIGINAL_AMOUNT, PAYMENT.ORIGINAL_CONVERTED_AMOUNT, PAYMENT_DETAILS.PAYMENT_METHOD, PAYMENT_DETAILS.DEPOSIT_DATE, PAYMENT_DETAILS.CURRENCY, PAYMENT_DETAILS.CHECK_NO, PAYMENT_DETAILS.CREDIT_CARD_NUMBER, PAYMENT_DETAILS.BANK_CODE, PAYMENT_DETAILS.BANK_ACCOUNT_NUMBER, ACCOUNT.ACCOUNT_TIMESTAMP, ACCOUNT.CURRENCY, PAYMENT_DETAILS.PAYMENT_SUB_METHOD, ACCOUNT.BE, MEMO.MEMO_TEXT, PAYMENT_DETAILS.PERIOD_KEY, PAYMENT_DETAILS.PARTITION_ID, PAYMENT_DETAILS.AMOUNT, PAYMENT.ACTIVITY_DATE, PAYMENT_DETAILS.ACCOUNT_ID, PAYMENT.L9_RT_ID, PAYMENT_DETAILS.L9_CONV_INV_NUMBER, PAYMENT_DETAILS.CC_EXPIRY_DATE, PAYMENT_DETAILS.DIRECT_DEBIT_VOUCHER, PAYMENT_DETAILS.PAYMENT_SOURCE_TYPE, PAYMENT.L9_WHT_AMT, PAYMENT.L9_WHT_CERT_NO, PAYMENT.L9_WHT_RATE, PAYMENT.L9_VAT_AMOUNT, PAYMENT.L9_PRINT_RT_IND, PAYMENT.L9_USER_ID, PAYMENT.L9_RT_EXTRACT_IND, PAYMENT.L9_ECA_REASON_CODE, PAYMENT.L9_RT_GENERATE_MODE, PAYMENT_DETAILS.L9_VAT_AMOUNT, PAYMENT.BILL_SEQ_NO, PAYMENT.ACTIVITY_INDICATOR, ACCOUNT.L9_COMPANY_CODE, PAYMENT_DETAILS.PAYMENT_SOURCE_ID, PAYMENT.OPERATOR_ID FROM PAYMENT_DETAILS, PAYMENT, ACCOUNT, MEMO, (SELECT row_number, PAYMENT_ID, CREDIT_ID, ACCOUNT_ID, PD_PARTITION_ID, PD_PERIOD_KEY, PT_PARTITION_ID, PT_PERIOD_KEY FROM (SELECT rownum as row_number, PAYMENT_ID, CREDIT_ID, ACCOUNT_ID, PD_PARTITION_ID, PD_PERIOD_KEY, PT_PARTITION_ID, PT_PERIOD_KEY FROM (SELECT /*+ leading (pt a pd) use_nl (pt a pd) index (pd PAYMENT_DETAILS_pk) */ DISTINCT PD.PAYMENT_ID, PT.CREDIT_ID, A.ACCOUNT_ID, pd.partition_id pd_partition_id, pd.period_key pd_period_key, pt.partition_id pt_partition_id, pt.period_key pt_period_key FROM PAYMENT_DETAILS PD, PAYMENT PT, ACCOUNT A WHERE PT.PARTITION_ID = A.PARTITION_ID AND PT.ACCOUNT_ID = A.ACCOUNT_ID AND PD.PARTITION_ID = PT.PYMDT_PARTITION_ID AND PD.PERIOD_KEY = PT.PYMDT_PERIOD_KEY AND PD.PAYMENT_ID = PT.PAYMENT_ID AND (a.be IN (SELECT child_be_id FROM gn1_boh_relation START WITH parent_be_id = NVL(0, 0) CONNECT BY PRIOR child_be_id = parent_be_id) OR a.be = NVL(0, 0)) AND ROWNUM <= 2000 ORDER BY pt.ACTIVITY_DATE DESC, pt.CREDIT_ID DESC)) WHERE row_number BETWEEN 1 AND 100) PAYMENT_TEMP_TABLE WHERE PAYMENT_DETAILS.PAYMENT_ID = PAYMENT_TEMP_TABLE.PAYMENT_ID AND PAYMENT_DETAILS.partition_id = payment_temp_table.pd_partition_id AND PAYMENT_DETAILS.period_key = payment_temp_table.pd_period_key AND ACCOUNT.account_id = payment_temp_table.account_id AND PAYMENT.credit_id = payment_temp_table.credit_id AND PAYMENT.partition_id = payment_temp_table.pt_partition_id AND PAYMENT.period_key = payment_temp_table.pt_period_key AND MEMO.memo_id(+) = PAYMENT_DETAILS.memo_id); index的信息如下:

   INDEX_NAME                     TABLESPACE INDEX_TYPE UNIQUENES PAR COLUMN_LIST                        TABLE_TYPE STATUS   NUM_ROWS LAST_ANAL G
------------------------------ ---------- ---------- --------- --- ------------------------------     ---------- ------ ---------- --------- -
   PAYMENT_DETAILS_PK                     NORMAL     UNIQUE    YES PAYMENT_ID,PARTITION_ID,PERIOD_KEY TABLE      N/A       6718838 16-JUL-14 N                        ------------------------------ ---------- ---------- --------- --- ------------------------------ ---------- ------ ---------- --------- -
    PAYMENT_PK                            NORMAL     UNIQUE    YES CREDIT_ID,PARTITION_ID,PERIOD_KEY  TABLE      N/A       6914026 16-JUL-14 N

可以看到基本索引在查询条件中能用都用到了,而且使用了hint,根据sql内容来看,这个查询只是需要得到对应的记录条数而已,是在原有的查询语句的基础上直接加了select count(1) from xxx改进的来的。 所以对于这类查询,就需要摆脱思想的束缚,可以最大程度上简化sql,达到同样的效果。 改进后的sql语句如下:

SELECT COUNT(1)
  FROM ( 
        SELECT
         PAYMENT_DETAILS.rowid, 
         PAYMENT.rowid,
          ACCOUNT.rowid ,
        --AR1_MEMO.rowid
          FROM PAYMENT_DETAILS,
                PAYMENT,
                ACCOUNT,
                --  AR1_MEMO,
                (SELECT pdrowid, ptrowid, arowid
                   FROM (SELECT rownum as row_number, pdrowid, ptrowid, arowid
                           FROM (SELECT /*+ leading (pt a pd)   */
                                 DISTINCT PD.rowid pdrowid,
                                          PT.rowid ptrowid,
                                          A.rowid  arowid
                                   FROM PAYMENT_DETAILS PD,
                                        PAYMENT         PT,
                                        ACCOUNT         A
                                  WHERE PT.PARTITION_ID = A.PARTITION_ID
                                    AND PT.ACCOUNT_ID = A.ACCOUNT_ID
                                    AND PD.PARTITION_ID = PT.PYMDT_PARTITION_ID
                                    AND PD.PERIOD_KEY = PT.PYMDT_PERIOD_KEY
                                    AND PD.PAYMENT_ID = PT.PAYMENT_ID
                                    AND (a.be IN
                                        (SELECT child_be_id
                                            FROM gn1_boh_relation
                                           START WITH parent_be_id = NVL(0, 0)
                                          CONNECT BY PRIOR
                                                      child_be_id = parent_be_id) OR
                                        a.be = NVL(0, 0))
                                    AND ROWNUM <= 2147483627
                                  ORDER BY pt.ACTIVITY_DATE DESC,
                                           pt.CREDIT_ID     DESC))
                  WHERE row_number BETWEEN 1 AND 9223372036854775807) PAYMENT_TEMP_TABLE
         WHERE PAYMENT_DETAILS.rowid = PAYMENT_TEMP_TABLE.pdrowid
           AND ACCOUNT.rowid = payment_temp_table.arowid
           AND PAYMENT.rowid = payment_temp_table.ptrowid
          AND MEMO.memo_id(+) = PAYMENT_DETAILS.memo_id

直接通过rowid来做关联,因为不需要输出所有的数据,只要输出列出含有主键列,就可以考虑使用rowid来代替。 sql语句极大的简化了,不过还没有完,还可以考虑做点什么。因为memo这个表比较大,没有走主键,查询会走全表扫描,耗费不少时间,但是结果集中貌似也不是很需要,因为结果集只考虑最终的返回数据条数,可以考虑是否能够从查询中去掉这个表。 简单验证一下,模拟这种类似的外连接,看看结果集是否会有影响。

SQL>
insert into test_full values(1);
insert into test_full values(2);
insert into test_full values(3);
insert into test_partial values(1,'a');
insert into test_partial values(2,'b');
1 row created.
SQL> 
1 row created.
SQL> 
1 row created.
SQL> 
1 row created.
SQL> 
1 row created.

SQL> 
SQL> select test_full.id, test_partial.name from test_full,test_partial
  2   where  test_partial.id(+) =  test_full.id
  3  /


        ID NAME
---------- ------------------------------
         1 a
         2 b
         3

通过上面的简单测试,可以说明,如果表test_full中含有的记录,通过和test_partial做外连接,也是全量输出test_full的值。 这样,可以考虑把memo表去除,整理后的sql语句简化为:

SELECT COUNT(1)
  FROM ( 
        SELECT
         PAYMENT_DETAILS.rowid, 
         PAYMENT.rowid,
          ACCOUNT.rowid 
          FROM PAYMENT_DETAILS,
                PAYMENT,
                ACCOUNT,
                (SELECT pdrowid, ptrowid, arowid
                   FROM (SELECT rownum as row_number, pdrowid, ptrowid, arowid
                           FROM (SELECT /*+ leading (pt a pd)   */
                                 DISTINCT PD.rowid pdrowid,
                                          PT.rowid ptrowid,
                                          A.rowid  arowid
                                   FROM PAYMENT_DETAILS PD,
                                        PAYMENT         PT,
                                        ACCOUNT         A
                                  WHERE PT.PARTITION_ID = A.PARTITION_ID
                                    AND PT.ACCOUNT_ID = A.ACCOUNT_ID
                                    AND PD.PARTITION_ID = PT.PYMDT_PARTITION_ID
                                    AND PD.PERIOD_KEY = PT.PYMDT_PERIOD_KEY
                                    AND PD.PAYMENT_ID = PT.PAYMENT_ID
                                    AND (a.be IN
                                        (SELECT child_be_id
                                            FROM gn1_boh_relation
                                           START WITH parent_be_id = NVL(0, 0)
                                          CONNECT BY PRIOR
                                                      child_be_id = parent_be_id) OR
                                        a.be = NVL(0, 0))
                                    AND ROWNUM <= 2147483627
                                  ORDER BY pt.ACTIVITY_DATE DESC,
                                           pt.CREDIT_ID     DESC))
                  WHERE row_number BETWEEN 1 AND 9223372036854775807) PAYMENT_TEMP_TABLE
         WHERE PAYMENT_DETAILS.rowid = PAYMENT_TEMP_TABLE.pdrowid
           AND ACCOUNT.rowid = payment_temp_table.arowid
           AND PAYMENT.rowid = payment_temp_table.ptrowid 

对于一些Hint也做了删减,保证不必要的资源消耗。 总之,性能sql,对于sql的简化也是一种考验,如果能够最大程度的简化,也是sql调优的进步。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2014-07-28

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据处理

CEdit只能输入16进制数

14750
来自专栏帘卷西风的专栏

关于SQL中Union和Join的用法

转载请注明出处:帘卷西风的专栏(http://blog.csdn.net/ljxfblog)

11030
来自专栏Netkiller

Spring Data 最佳实践

摘要: ORM的出现解决了程序猿学习数据库学历成本,也加快了开发的速度。程序猿无需再学习数据库定义语言DDL以及数据库客户端,也无需关注建表这些繁琐的工作,同时...

48470
来自专栏跟着阿笨一起玩NET

(1)显示每个类别最新更新的数据

在项目中经常遇到求每个类别最新显示的数据,比如显示某某某类别最新更新的5条数据。特写下这个sql记录于此:

8810
来自专栏数据和云

Oracle Hints - 先知的提示

在上周恩墨微信大讲堂的讨论中,几个有趣的视图跃入我们的视野,可以分享给大家。 在Oracle 11g中,新增的视图V$SQL_HINT记录了Oracle数据库中...

30460
来自专栏杨建荣的学习笔记

sql嵌入html格式显示报表(r6笔记第34天)

在使用监控系统报警的时候,如果显示的报警信息为纯粹的文本,会枯燥很多,而且看起来很不清晰。 比如我们要监控表空间的使用情况,输出列有表空间名,状态,区管理方式,...

34470
来自专栏数据库新发现

Oracle9i新特性-使用DBMS_METADATA包获得对象DDL语句

从Oracle9i开始Oracle提供了一个新的系统包DBMS_METADATA,可以用于提取对象创建的DDL语句。

15220
来自专栏乐沙弥的世界

SQL server 2005 切换分区表

如转载,请注明出处:http://blog.csdn.net/robinson_0612/archive/2009/11/10/4794371.aspx

10430
来自专栏杨建荣的学习笔记

生产系统调优之_毫秒级的改进 (92天)

生产中有一个sql语句,做了union-all操作,对于时间的要求是极其严格的,目前已经从2秒的改进调整到了1秒以内,在此基础上还想做进一步的调整,因为极其频繁...

35980
来自专栏Jerry的SAP技术分享

How to update BOL entity property value via ABAP code

Suppose I have one product with ID I042416 which could be found in CRM WebClient...

8100

扫码关注云+社区

领取腾讯云代金券