巧用rowid简化sql查询

简介: 生产系统中有一条sql语句,目前执行的时间有点长了,而且看起来有些臃肿,客户问能不能改进一下。得到的sql语句如下: SELECT COUNT(1)   FROM (          SELECT /*+ leading (payment_temp_ta...
生产系统中有一条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                                   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                                   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
                                  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调优的进步。
目录
相关文章
|
4天前
|
SQL
sql语句加正则 简化查询
sql语句加正则 简化查询
17 0
sql语句加正则 简化查询
|
4天前
|
SQL
sql server链接查询
sql server链接查询
19 1
|
4天前
|
SQL
sql server简单查询
sql server简单查询
15 1
|
4天前
|
SQL 关系型数据库 MySQL
mysql一条sql查询出多个统计结果
mysql一条sql查询出多个统计结果
17 0
|
3天前
|
SQL 关系型数据库 MySQL
MYSQL根据查询结果删除sql 去除重复id 新增对比前一条与后一条数据 去重3种方法​ 窗口函数
MYSQL根据查询结果删除sql 去除重复id 新增对比前一条与后一条数据 去重3种方法​ 窗口函数
|
4天前
|
SQL Java 关系型数据库
Mybatis多表关联查询与动态SQL(下)
Mybatis多表关联查询与动态SQL
18 0
|
4天前
|
SQL Java 数据库连接
Mybatis多表关联查询与动态SQL(上)
Mybatis多表关联查询与动态SQL
9 0
|
4天前
|
SQL Java 数据库连接
JDBC的连接参数的设置导致rowid自动添加到sql
JDBC的连接参数的设置导致rowid自动添加到sql
13 1
|
4天前
|
SQL 分布式计算 DataWorks
实时数仓 Hologres产品使用合集之查询分区表的生命周期(即之前设置的'auto_partitioning.num_retention'值)的SQL语句,可以使用什么查询
实时数仓Hologres是阿里云推出的一款高性能、实时分析的数据库服务,专为大数据分析和复杂查询场景设计。使用Hologres,企业能够打破传统数据仓库的延迟瓶颈,实现数据到决策的无缝衔接,加速业务创新和响应速度。以下是Hologres产品的一些典型使用场景合集。
17 0
|
4天前
|
SQL 前端开发
基于jeecgboot复杂sql查询的列表自定义列实现
基于jeecgboot复杂sql查询的列表自定义列实现
11 0