使用组合索引优化SQL案例-优快云博客

老外发来邮件：
Hi Robinson,

I tried running the attached queries on ADW1U database. Even after 10 mins both the queries were still running.

Could you please check the issue?

邮件就是老外叫我对附件中的2个SQL进行调优，由于这两条SQL语句基本差不多，这里我只贴出其中一条SQL：
SELECT
distinct(PROD_9005_GDF_WK_SS_FDIM.PROD_4_NAME),
PROD_9005_GDF_WK_SS_FDIM.PROD_5_NAME
FROM
GLOBL_DEMND_FRCST_WK_FCT,
PROD_9005_GDF_WK_SS_FDIM,
GDF_SRCE_REGN_LKP
WHERE
( GDF_SRCE_REGN_LKP.SRCE_REGN_ID=GLOBL_DEMND_FRCST_WK_FCT.SRCE_REGN_ID )
AND ( GLOBL_DEMND_FRCST_WK_FCT.PROD_SKID=PROD_9005_GDF_WK_SS_FDIM.PROD_SKID )
AND
( 'ASIA'
=
Case When GDF_SRCE_REGN_LKP.SRCE_REGN_ID IN ('AA','GC','NE')
then 'ASIA'
When GDF_SRCE_REGN_LKP.SRCE_REGN_ID IN ('NA','LA')
Then 'NALA'
When GDF_SRCE_REGN_LKP.SRCE_REGN_ID IN ('WE','CE')
Then 'EMEA'
End );
SQL调优的步骤以及思路：
一：首先，我们要观察SQL语句，这条SQL写得很烂，因为最下面的case 语句根本就不用写，它完全是画蛇添足
SQL语句可以改写如下：
SELECT
distinct(B.PROD_4_NAME),
B.PROD_5_NAME
FROM
GLOBL_DEMND_FRCST_WK_FCT A,
PROD_9005_GDF_WK_SS_FDIM B,
GDF_SRCE_REGN_LKP C
WHERE
C.SRCE_REGN_ID=A.SRCE_REGN_ID
AND A.PROD_SKID=B.PROD_SKID
AND C.SRCE_REGN_ID IN ('AA','GC','NE');
二：查看对象是表或者是视图或者是同义词
select owner,object_name,object_type from dba_objects where owner='&username' and object_name='table_name';
如果是表最好办，如果是视图，那我们还要查询视图的定义，如果是同义词我们还要查询该同义词的原表。这里我查到他们是表。
三：改写完SQL之后，我们就要查看SQL语句中的表的详细信息
select dbms_metadata.get_ddl('TABLE','&TABLE_NAME','USER_NAME') index_name FROM DUAL;
具体内容就不贴出了，这里查看到
GLOBL_DEMND_FRCST_WK_FCT 是组合分区表总共有900多个分区，连接列 SRCE_REGN_ID是子分区键.它总共大约8亿条数据

表 GDF_SRCE_REGN_LKP 只有9行数据
SQL> select sum(num_rows)
2 from dba_tab_statistics where owner='ADWU' and table_name='GDF_SRCE_REGN_LKP';

SUM(NUM_ROWS)
-------------
9
表PROD_9005_GDF_WK_SS_FDIM 分区表，PROD_SKID是主键，根据PROD_1_SKID做range分区,数据量在60万左右

改写好SQL之后，我们就查看该SQL的执行计划：
SQL> EXPLAIN PLAN FOR SELECT
2 distinct(B.PROD_4_NAME),
3 B.PROD_5_NAME
4 FROM
5 GLOBL_DEMND_FRCST_WK_FCT A,
6 PROD_9005_GDF_WK_SS_FDIM B,
7 GDF_SRCE_REGN_LKP C
8 WHERE
9 C.SRCE_REGN_ID=A.SRCE_REGN_ID
10 AND A.PROD_SKID=B.PROD_SKID
11 AND C.SRCE_REGN_ID IN ('AA','GC','NE');

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
Plan hash value: 1843757323

----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24114 | 824K| | 65164 (98)| 00:09:28 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10003 | 24114 | 824K| | 65164 (98)| 00:09:28 | | | Q1,03 | P->S | QC (RAND) |
| 3 | SORT UNIQUE | | 24114 | 824K| 88G| 65164 (98)| 00:09:28 | | | Q1,03 | PCWP | |
| 4 | PX RECEIVE | | 1978M| 64G| | 6103 (78)| 00:00:54 | | | Q1,03 | PCWP | |
| 5 | PX SEND HASH | :TQ10002 | 1978M| 64G| | 6103 (78)| 00:00:54 | | | Q1,02 | P->P | HASH |
|* 6 | HASH JOIN | | 1978M| 64G| | 6103 (78)| 00:00:54 | | | Q1,02 | PCWP | |
| 7 | BUFFER SORT | | | | | | | | | Q1,02 | PCWC | |
| 8 | PX RECEIVE | | 301K| 6780K| | 651 (4)| 00:00:06 | | | Q1,02 | PCWP | |
| 9 | PX SEND BROADCAST | :TQ10000 | 301K| 6780K| | 651 (4)| 00:00:06 | | | | S->P | BROADCAST |
| 10 | PARTITION RANGE SINGLE | | 301K| 6780K| | 651 (4)| 00:00:06 | 1 | 1 | | | |
| 11 | TABLE ACCESS FULL | PROD_9005_GDF_WK_SS_FDIM | 301K| 6780K| | 651 (4)| 00:00:06 | 1 | 1 | | | |
|* 12 | HASH JOIN | | 4887K| 55M| | 913 (19)| 00:00:08 | | | Q1,02 | PCWP | |
| 13 | BUFFER SORT | | | | | | | | | Q1,02 | PCWC | |
| 14 | PX RECEIVE | | 3 | 9 | | 1 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 15 | PX SEND BROADCAST | :TQ10001 | 3 | 9 | | 1 (0)| 00:00:01 | | | | S->P | BROADCAST |
| 16 | INLIST ITERATOR | | | | | | | | | | | |
|* 17 | INDEX UNIQUE SCAN | GDF_SRCE_REGN_LKP_PK | 3 | 9 | | 1 (0)| 00:00:01 | | | | | |
| 18 | PX BLOCK ITERATOR | | 8146K| 69M| | 895 (18)| 00:00:08 |KEY(I) |KEY(I) | Q1,02 | PCWC | |
| 19 | MAT_VIEW REWRITE ACCESS FULL| GDFW_M_9005P5_2MV | 8146K| 69M| | 895 (18)| 00:00:08 | KEY | KEY | Q1,02 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

6 - access("PROD_5_SKID"="GDFW_M_9005P5_2MV"."PROD_5_SKID")
12 - access("C"."SRCE_REGN_ID"="GDFW_M_9005P5_2MV"."SRCE_REGN_ID")
17 - access("C"."SRCE_REGN_ID"='AA' OR "C"."SRCE_REGN_ID"='GC' OR "C"."SRCE_REGN_ID"='NE')
从上面的执行计划中，我发现该查询使用了并行，并且CBO预计SORT UNIQUE 操作将会使用88G的临时表空间，并且对表PROD_9005_GDF_WK_SS_FDIM的访问方式是全表扫描，而表GLOBL_DEMND_FRCST_WK_FCT的访问方式是查询物化视图，对表GDF_SRCE_REGN_LKP的访问方式是走索引唯一扫描，由于有并行操作，我们看执行计划很累，所以我打算将并行特征去掉，这样我们看执行计划就方便了：
SQL> alter session disable parallel query;

Session altered.
SQL> EXPLAIN PLAN FOR SELECT
2 distinct(B.PROD_4_NAME),
3 B.PROD_5_NAME
4 FROM
5 GLOBL_DEMND_FRCST_WK_FCT A,
6 PROD_9005_GDF_WK_SS_FDIM B,
7 GDF_SRCE_REGN_LKP C
8 WHERE
9 C.SRCE_REGN_ID=A.SRCE_REGN_ID
10 AND A.PROD_SKID=B.PROD_SKID
11 AND C.SRCE_REGN_ID IN ('AA','GC','NE');

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
Plan hash value: 2669462634

--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24114 | 824K| | 3694K (7)| 08:56:17 | | |
| 1 | SORT UNIQUE | | 24114 | 824K| 88G| 3694K (7)| 08:56:17 | | |
|* 2 | HASH JOIN | | 1978M| 64G| 10M| 22175 (77)| 00:03:14 | | |
| 3 | PARTITION RANGE SINGLE | | 301K| 6780K| | 651 (4)| 00:00:06 | 1 | 1 |
| 4 | TABLE ACCESS FULL | PROD_9005_GDF_WK_SS_FDIM | 301K| 6780K| | 651 (4)| 00:00:06 | 1 | 1 |
|* 5 | HASH JOIN | | 4887K| 55M| | 3322 (20)| 00:00:29 | | |
| 6 | INLIST ITERATOR | | | | | | | | |
|* 7 | INDEX UNIQUE SCAN | GDF_SRCE_REGN_LKP_PK | 3 | 9 | | 1 (0)| 00:00:01 | | |
| 8 | PARTITION RANGE ALL | | 8146K| 69M| | 3254 (18)| 00:00:29 | 1 | 106 |
| 9 | PARTITION LIST INLIST | | 8146K| 69M| | 3254 (18)| 00:00:29 |KEY(I) |KEY(I) |
| 10 | MAT_VIEW REWRITE ACCESS FULL| GDFW_M_9005P5_2MV | 8146K| 69M| | 3254 (18)| 00:00:29 | KEY | KEY |
--------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("PROD_5_SKID"="GDFW_M_9005P5_2MV"."PROD_5_SKID")
5 - access("C"."SRCE_REGN_ID"="GDFW_M_9005P5_2MV"."SRCE_REGN_ID")
7 - access("C"."SRCE_REGN_ID"='AA' OR "C"."SRCE_REGN_ID"='GC' OR "C"."SRCE_REGN_ID"='NE')

24 rows selected.
从上面的执行计划中可以看到，对表的访问路径是没有改变的，和并行查是一样的(当然应该一样)。
这里我们不要去考虑只有9行数据的小表，我们应该把重点放在大表上面。注意观察老外要我执行的SQL语句。
其实查询就是返回PROD_9005_GDF_WK_SS_FDIM中的两个字段，恩这里给了我一个提示，我可以用组合索引来优化该SQL访问路径
于是我创建如下两个索引：
select * from table(dbms_xplan.display);
SQL> create index adwu.prod_skid_4_5_name on adwu.PROD_9005_GDF_WK_SS_FDIM(PROD_SKID,PROD_4_NAME,PROD_5_NAME )
2 online nologging parallel tablespace DEM_PLAN01S;

Index created
上面的语句花了不到1分钟

SQL> create index adwu.prod_skdi_srce_regn_id on adwu.GLOBL_DEMND_FRCST_WK_FCT(PROD_SKID,SRCE_REGN_ID)
2 online nologging parallel tablespace DEM_PLAN01S;

Index created
上面的语句花了大概30分钟

再次查看执行计划：
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 2669462634

--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24403 | 834K| | 3757K (7)| 09:05:32 | | |
| 1 | SORT UNIQUE | | 24403 | 834K| 90G| 3757K (7)| 09:05:32 | | |
|* 2 | HASH JOIN | | 2012M| 65G| 10M| 22459 (78)| 00:03:16 | | |
| 3 | PARTITION RANGE SINGLE | | 307K| 6897K| | 651 (4)| 00:00:06 | 1 | 1 |
| 4 | TABLE ACCESS FULL | PROD_9005_GDF_WK_SS_FDIM | 307K| 6897K| | 651 (4)| 00:00:06 | 1 | 1 |
|* 5 | HASH JOIN | | 4887K| 55M| | 3322 (20)| 00:00:29 | | |
| 6 | INLIST ITERATOR | | | | | | | | |
|* 7 | INDEX UNIQUE SCAN | GDF_SRCE_REGN_LKP_PK | 3 | 9 | | 1 (0)| 00:00:01 | | |
| 8 | PARTITION RANGE ALL | | 8146K| 69M| | 3254 (18)| 00:00:29 | 1 | 106 |
| 9 | PARTITION LIST INLIST | | 8146K| 69M| | 3254 (18)| 00:00:29 |KEY(I) |KEY(I) |
| 10 | MAT_VIEW REWRITE ACCESS FULL| GDFW_M_9005P5_2MV | 8146K| 69M| | 3254 (18)| 00:00:29 | KEY | KEY |
--------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

24 rows selected.
发现执行计划居然没有改变，于是，使用hint index，结果也不走索引(这里我就不贴出了)，后来我发现，导致不走索引的原因是DISTINCT关键字，我们知道DISTINCT要排序，按照老外给我的SQL，我又重新写了一下：

explain plan for select distinct * from (
SELECT
(B.PROD_4_NAME),
B.PROD_5_NAME
FROM
GLOBL_DEMND_FRCST_WK_FCT A,
PROD_9005_GDF_WK_SS_FDIM B,
GDF_SRCE_REGN_LKP C
WHERE
C.SRCE_REGN_ID=A.SRCE_REGN_ID
AND A.PROD_SKID=B.PROD_SKID
AND C.SRCE_REGN_ID IN ('AA','GC','NE'));

为了不让查询重写干扰我，我关闭查询重写

SQL> ALTER SESSION SET query_rewrite_enabled = FALSE;

Session altered.

执行计划如下：

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 557900754

-----------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 24403 | 834K| | 123K (14)| 00:17:54 |

| 1 | SORT UNIQUE | | 24403 | 834K| 380M| 123K (14)| 00:17:54 |