SQL调优之利用组合索引

HI TEAM

I need excete the select IN ADW2P.
And it has run more than three hours , no response!

Could you give me a hand?

select distinct srce_sys_id from
idws1.ODS_BR_SHENG_FB
where prod_id in
(
select dsend_id as prod_id
from idws1.prod_assoc_dnorm
where ctrl_perd = (select ctrl_perd
from idws1.ref_ctrl
where fact_type_code = 'BS'
AND strct_code = '5005'
and stage_id = 31)
and strct_code = '5005'
and parnt_id in (
'1106174171', -- Fekkai
'1106309497', -- Zirh
'1106407202', -- TAOS
'1106547727', -- DDF
'1105162919', -- SKII Skin
'1105162831' -- SKII Cosmetics
)
)

SQL> explain plan for select distinct srce_sys_id from
2 idws1.ODS_BR_SHENG_FB
3 where prod_id in
4 (
5 select dsend_id as prod_id
6 from idws1.prod_assoc_dnorm
7 where ctrl_perd = (select ctrl_perd
8 from idws1.ref_ctrl
9 where fact_type_code = 'BS'
10 AND strct_code = '5005'
11 and stage_id = 31)
12 and strct_code = '5005'
13 and parnt_id in (
14 '1106174171', -- Fekkai
15 '1106309497', -- Zirh
16 '1106407202', -- TAOS
17 '1106547727', -- DDF
18 '1105162919', -- SKII Skin
19 '1105162831' -- SKII Cosmetics
20 )
21 );

Explained.

Elapsed: 00:00:01.00
SQL> select * from table(dbms_xplan.display);

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

Plan hash value: 3648674968

------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 30 | 1410 | 6456K (1)| 25:06:34 | | |
| 1 | SORT UNIQUE | | 30 | 1410 | 6456K (1)| 25:06:34 | | |
|* 2 | HASH JOIN | | 367M| 16G| 6425K (1)| 24:59:12 | | |
| 3 | PARTITION RANGE SINGLE | | 26387 | 876K| 80 (0)| 00:00:02 | KEY | KEY |
| 4 | INLIST ITERATOR | | | | | | | |
|* 5 | INDEX RANGE SCAN | PROD_ASSOC_DNORM_PK | 26387 | 876K| 80 (0)| 00:00:02 | KEY | KEY |
| 6 | TABLE ACCESS BY INDEX ROWID| REF_CTRL | 1 | 19 | 1 (0)| 00:00:01 | | |
|* 7 | INDEX UNIQUE SCAN | REF_CTRL_PK | 1 | | 0 (0)| 00:00:01 | | |
| 8 | PARTITION RANGE ALL | | 365M| 4534M| 6422K (1)| 24:58:36 | 1 | 1240 |
| 9 | TABLE ACCESS FULL | ODS_BR_SHENG_FB | 365M| 4534M| 6422K (1)| 24:58:36 | 1 | 1240 |
------------------------------------------------------------------------------------------------------------------------

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

2 - access("PROD_ID"="DSEND_ID")
5 - access("CTRL_PERD"= (SELECT /*+ */ "CTRL_PERD" FROM "IDWS1"."REF_CTRL" "REF_CTRL" WHERE
"STRCT_CODE"='5005' AND "STAGE_ID"=31 AND "FACT_TYPE_CODE"='BS') AND "STRCT_CODE"='5005' AND
("PARNT_ID"='1105162831' OR "PARNT_ID"='1105162919' OR "PARNT_ID"='1106174171' OR "PARNT_ID"='1106309497' OR
"PARNT_ID"='1106407202' OR "PARNT_ID"='1106547727'))
7 - access("FACT_TYPE_CODE"='BS' AND "STAGE_ID"=31 AND "STRCT_CODE"='5005')

26 rows selected.

只所以跑2小时不出结果是由于对 ODS_BR_SHENG_FB 表进行全表扫描，但是这个表非常大，有1000多个分区,4亿3千多万数据，389G

SQL> select /*+ parallel(a,15)*/ count(*) from idws1.ODS_BR_SHENG_FB a;

COUNT(*)
----------
432683488

Elapsed: 00:35:38.57

SQL> select owner,segment_name,sum(bytes/1024/1024/1024) "Size(G)",sum(blocks) from dba_segments
2 where segment_name='ODS_BR_SHENG_FB' and owner='IDWS1' group by owner,segment_name ;

OWNER SEGMENT_NAME Size(G) SUM(BLOCKS)
-------------------- -------------------- ---------- -----------
IDWS1 ODS_BR_SHENG_FB 389.308594 25513728

我开了15个并行(实际上数据库只开了8个)跑了35分钟

并且这个表上面没有任何索引

SQL> select * from dba_ind_columns
2 where index_owner='IDWS1' AND table_name=upper('ODS_BR_SHENG_FB');

no rows selected

你的查询条件只是返回 srce_sys_id ，where 条件是 prod_id

所以要想让SQL跑的快， 可以加一个组合索引在 prod_id ,srce_sys_id 列上，避免全表扫描

SQL> create index idws1.prod_id_srce_sys_id on idws1.ODS_BR_SHENG_FB(prod_id,srce_sys_id) nologging parallel 18;

Index created.

Elapsed: 01:03:28.56

索引创建之后 占用12.7G

SQL> select owner,segment_name,bytes/1024/1024/1024 "Size(G)",blocks from dba_segments where segment_name='PROD_ID_SRCE_SYS_ID' and owner='IDWS1';

OWNER SEGMENT_NAME Size(G) BLOCKS
-------------------- -------------------- ---------- ----------
IDWS1 PROD_ID_SRCE_SYS_ID 12.7773438 837376

SQL> explain plan for select distinct srce_sys_id from
2 idws1.ODS_BR_SHENG_FB
3 where prod_id in
4 (
5 select dsend_id as prod_id
6 from idws1.prod_assoc_dnorm
7 where ctrl_perd = (select ctrl_perd
8 from idws1.ref_ctrl
9 where fact_type_code = 'BS'
10 AND strct_code = '5005'
11 and stage_id = 31)
12 and strct_code = '5005'
13 and parnt_id in (
14 '1106174171', -- Fekkai
15 '1106309497', -- Zirh
16 '1106407202', -- TAOS
17 '1106547727', -- DDF
18 '1105162919', -- SKII Skin
19 '1105162831' -- SKII Cosmetics
20 )
21 )
22 ;

Explained.

Elapsed: 00:00:01.86
SQL> select * from table(dbms_xplan.display);

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

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

Plan hash value: 3346352126

------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 30 | 1410 | 285K (14)| 01:06:31 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 30 | 1410 | 285K (14)| 01:06:31 | | | Q1,02 | P->S | QC (RAND) |
| 3 | SORT UNIQUE | | 30 | 1410 | 285K (14)| 01:06:31 | | | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 367M| 16G| 253K (3)| 00:59:09 | | | Q1,02 | PCWP | |
| 5 | PX SEND HASH | :TQ10001 | 367M| 16G| 253K (3)| 00:59:09 | | | Q1,01 | P->P | HASH |
|* 6 | HASH JOIN | | 367M| 16G| 253K (3)| 00:59:09 | | | Q1,01 | PCWP | |
| 7 | BUFFER SORT | | | | | | | | Q1,01 | PCWC | |
| 8 | PX RECEIVE | | 26387 | 876K| 80 (0)| 00:00:02 | | | Q1,01 | PCWP | |
| 9 | PX SEND BROADCAST | :TQ10000 | 26387 | 876K| 80 (0)| 00:00:02 | | | | S->P | BROADCAST |
| 10 | PARTITION RANGE SINGLE | | 26387 | 876K| 80 (0)| 00:00:02 | KEY | KEY | | | |
| 11 | INLIST ITERATOR | | | | | | | | | | |
|* 12 | INDEX RANGE SCAN | PROD_ASSOC_DNORM_PK | 26387 | 876K| 80 (0)| 00:00:02 | KEY | KEY | | | |
| 13 | TABLE ACCESS BY INDEX ROWID| REF_CTRL | 1 | 19 | 1 (0)| 00:00:01 | | | | | |
|* 14 | INDEX UNIQUE SCAN | REF_CTRL_PK | 1 | | 0 (0)| 00:00:01 | | | | | |
| 15 | PX BLOCK ITERATOR | | 365M| 4534M| 250K (2)| 00:58:34 | | | Q1,01 | PCWC | |
| 16 | INDEX FAST FULL SCAN | PROD_ID_SRCE_SYS_ID | 365M| 4534M| 250K (2)| 00:58:34 | | | Q1,01 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------------------------------

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

6 - access("PROD_ID"="DSEND_ID")
12 - access("CTRL_PERD"= (SELECT /*+ */ "CTRL_PERD" FROM "IDWS1"."REF_CTRL" "REF_CTRL" WHERE "STRCT_CODE"='5005' AND "STAGE_ID"=31 AND
"FACT_TYPE_CODE"='BS') AND "STRCT_CODE"='5005' AND ("PARNT_ID"='1105162831' OR "PARNT_ID"='1105162919' OR "PARNT_ID"='1106174171' OR
"PARNT_ID"='1106309497' OR "PARNT_ID"='1106407202' OR "PARNT_ID"='1106547727'))
14 - access("FACT_TYPE_CODE"='BS' AND "STAGE_ID"=31 AND "STRCT_CODE"='5005')

32 rows selected.

Elapsed: 00:00:02.67
SQL>
SQL> select distinct srce_sys_id from
2 idws1.ODS_BR_SHENG_FB
3 where prod_id in
4 (
5 select dsend_id as prod_id
6 from idws1.prod_assoc_dnorm
7 where ctrl_perd = (select ctrl_perd
8 from idws1.ref_ctrl
9 where fact_type_code = 'BS'
10 AND strct_code = '5005'
11 and stage_id = 31)
12 and strct_code = '5005'
13 and parnt_id in (
14 '1106174171', -- Fekkai
15 '1106309497', -- Zirh
16 '1106407202', -- TAOS
17 '1106547727', -- DDF
18 '1105162919', -- SKII Skin
19 '1105162831' -- SKII Cosmetics
20 )
21 );

SRCE_SYS_ID
-----------
2616
1743
616

Elapsed: 00:02:02.12

其实这是一个非常非常简单的案例，之所以记录于此，是希望给初学者一点帮助