Oracle SQL多表关联索引优化案例

本文主要是介绍Oracle SQL多表关联索引优化案例，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

近期迁云，云上磁盘性能很差，对当前数据库优化IO

如下SQL消耗IO资源很高：

SELECT  SUM("A2"."FEE_CASH"),
       '1',
"A1"."CHL_REFUND_NO",
"A2"."PAYPLAT_TRADNO",
       2
  FROM (SELECT MAX("A4"."UUID") "MAX(TTTT.UUID)"
          FROM "HUZH_CREDIT"."B_SETTILE_DATA" "A4"
         GROUP BY "A4"."PAYPLAT_TRADNO") "A3",
"HUZH_CREDIT"."B_SETTILE_DATA" "A2",
"HUZH_CREDIT"."B_BILL_REFUND" "A1"
 WHERE "A2"."UUID" = "A3"."MAX(TTTT.UUID)"
   AND "A2"."PAYPLAT_TRADNO" LIKE 'M201%'
   AND "A2"."REF_STATE" = '1'
   AND "A2"."FEE_CASH" <> 0
   AND "A2"."PAYPLAT_TRADNO" = "A1"."BILL_TRAN_NO"(+)
   AND "A1"."BILL_TRAN_NO"(+) LIKE 'M201%'
 GROUP BY '1', "A1"."CHL_REFUND_NO", "A2"."PAYPLAT_TRADNO"

多表关联后返回数据行数34 rows 因此这里使用hash join是错误的,要走nl

对A1,A2,A3(子查询)进行数据量收集：

select  count(*) from
(SELECT MAX("A4"."UUID") "MAX(TTTT.UUID)"
          FROM "HUZH_CREDIT"."B_SETTILE_DATA" "A4"
         GROUP BY "A4"."PAYPLAT_TRADNO")
  COUNT(*)
----------
     55715

select  count(*) from "HUZH_CREDIT"."B_SETTILE_DATA" "A2"
WHERE "A2"."PAYPLAT_TRADNO" LIKE 'M201%'
   AND "A2"."REF_STATE" = '1'
   AND "A2"."FEE_CASH" <> 0;
  COUNT(*)
---------
        90


select  count(*) from "HUZH_CREDIT"."B_BILL_REFUND" "A1"
where "A1"."BILL_TRAN_NO" LIKE 'M201%'
  COUNT(*)
---------
      1296

选择性收集：

select a.column_name,
b.num_rows,
a.num_distinct cardinality,
round(a.num_distinct / b.num_rows * 100, 2) selectivity,
a.histogram,
a.num_buckets
from dba_tab_col_statistics a ,dba_tables b
where a.owner = b.owner
and a.table_name = b.table_name
and a.owner = 'HUZH_CREDIT'
and a.table_name = 'B_SETTILE_DATA';

select a.column_name,
b.num_rows,
a.num_distinct cardinality,
round(a.num_distinct / b.num_rows * 100, 2) selectivity,
a.histogram,
a.num_buckets
from dba_tab_col_statistics a ,dba_tables b
where a.owner = b.owner
and a.table_name = b.table_name
and a.owner = 'HUZH_CREDIT'
and a.table_name = 'B_BILL_REFUND';

相关关联列创建索引：

A1.BILL_TRAN_NO选择性好，A2驱动A1表,在A1表关联列建立索引：
create index HUZH_CREDIT.B_BILL_REFUND_01 on HUZH_CREDIT.B_BILL_REFUND(BILL_TRAN_NO)

A2谓词过滤条件建索引：
create index HUZH_CREDIT.B_SETTILE_DATA_01 on HUZH_CREDIT.B_SETTILE_DATA(REF_STATE,PAYPLAT_TRADNO,FEE_CASH)

A2,A3关联建索引：
CREATE INDEX HUZH_CREDIT.I_B_SETTILE_DATA_05 ON HUZH_CREDIT.B_SETTILE_DATA(uuid,PAYPLAT_TRADNO);

优化后执行计划：

这篇关于Oracle SQL多表关联索引优化案例的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！