大家帮忙看看该SQL是否还可以进一步优化:
SQL> SELECT DISTINCT GRJBXX.SFZMHM 身份证号, GRJBXX.XM 姓名, RZZT.MC 任职状态
2 FROM ODS.T_GS_GRSBJKMX SBJK,
3 ODS.T_DJ_JGNSR JGNSR,
4 ODS.T_GS_GRJBXX GRJBXX,
5 ODS.T_GS_GRRZXX RZXX,
6 ODS.T_DM_GS_RZZT RZZT
7 WHERE RZXX.RZZT_DM = RZZT.RZZT_DM
8 AND RZXX.NSGRNBM = GRJBXX.NSGRNBM
9 AND SBJK.NSGRNBM = GRJBXX.NSGRNBM
10 AND SBJK.NSRNBM = JGNSR.NSRNBM
11 AND JGNSR.NSRBM = '190499390'
12 AND SBJK.NSRNBM = '190499390'
13 /
一、以下是所有SQL中各表的索引情况
T_GS_GRSBJKMX表索引:
TABLE_NAME INDEX_NAME COLUMN_POSITION COLUMN_NAME
1 T_GS_GRSBJKMX IDX_T_GS_GRSBJKMX_BAK_NSR 1 NSGRNBM
2 T_GS_GRSBJKMX IDX_T_GS_GRSBJKMX_BAK_NSR 2 NSRNBM
3 T_GS_GRSBJKMX IDX_T_GS_GRSBJKMX_BAK_SBRQ 1 SB_RQ
4 T_GS_GRSBJKMX IDX_T_GS_GRSBJKMX_BAK_XG_SJ 1 XG_SJ
5 T_GS_GRSBJKMX PK_T_GS_GRSBJKMX_BAK 1 GS_XH
6 T_GS_GRSBJKMX PK_T_GS_GRSBJKMX_BAK 2 MX_XH
T_DJ_JGNSR表索引情况:
TABLE_NAME INDEX_NAME COLUMN_POSITION COLUMN_NAME
1 T_DJ_JGNSR IDX_T_DJ_JGNSR_GLJG_DM 1 GLJG_DM
2 T_DJ_JGNSR IDX_T_DJ_JGNSR_LRSJ 1 LR_SJ
3 T_DJ_JGNSR IDX_T_DJ_JGNSR_NSRBM 1 NSRBM
4 T_DJ_JGNSR IDX_T_DJ_JGNSR_NSRMC 1 NSR_MC
5 T_DJ_JGNSR IDX_T_DJ_JGNSR_NSRNBM 1 NSRNBM
6 T_DJ_JGNSR IDX_T_DJ_JGNSR_SWDJLB 1 SWDJLB_DM
7 T_DJ_JGNSR IDX_T_DJ_JGNSR_SWDJZH 1 SWDJZH
8 T_DJ_JGNSR IDX_T_DJ_JGNSR_ZGY 1 ZGY
9 T_DJ_JGNSR IDX_T_DJ_JGNSR_ZZJG_DM 1 ZZJG_DM
T_GS_GRJBXX表
TABLE_NAME INDEX_NAME COLUMN_POSITION COLUMN_NAME
1 T_GS_GRJBXX IDX_T_GS_GRJBXX_SFZMHM 1 SFZMHM
2 T_GS_GRJBXX PK_T_GS_GRJBXX 1 NSGRNBM
T_GS_GRRZXX表索引
TABLE_NAME INDEX_NAME COLUMN_POSITION COLUMN_NAME
1 T_GS_GRRZXX IDX_T_GS_GRRZXX_SWJG_DM 1 SWJG_DM
2 T_GS_GRRZXX PK_T_GS_GRRZXX 1 NSGRNBM
3 T_GS_GRRZXX PK_T_GS_GRRZXX 2 NSRNBM
T_DM_GS_RZZT 表索引情况
TABLE_NAME INDEX_NAME COLUMN_POSITION COLUMN_NAME
1 T_DM_GS_RZZT PK_DIM_RZZT 1 RZZT_DM
二、索引数据分布情况
ODS.T_GS_GRSBJKMX 按”每年每月”建分区表
见附邮
三 执行计划
SELECT DISTINCT GRJBXX.SFZMHM 身份证号, GRJBXX.XM 姓名, RZZT.MC 任职状态
2 FROM (SELECT DISTINCT NSGRNBM,NSRNBM FROM ODS.T_GS_GRSBJKMX) SBJK,
3 ODS.T_DJ_JGNSR JGNSR,
4 ODS.T_GS_GRJBXX GRJBXX,
5 ODS.T_GS_GRRZXX RZXX,
6 ODS.T_DM_GS_RZZT RZZT
7 WHERE
8 RZXX.RZZT_DM = RZZT.RZZT_DM
9 AND RZXX.NSGRNBM = GRJBXX.NSGRNBM
10 AND SBJK.NSGRNBM = GRJBXX.NSGRNBM
11 AND SBJK.NSRNBM = JGNSR.NSRNBM
12 AND JGNSR.NSRBM = '190499390'
13 /
已选择5246行。
执行计划
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=26463 Card=111 Bytes=11544)
1 0 SORT* (UNIQUE) (Cost=26463 Card=111 Bytes=11544) :Q120477
003
2 1 HASH JOIN* (Cost=26456 Card=111 Bytes=11544) :Q120477
002
3 2 TABLE ACCESS* (BY INDEX ROWID) OF 'T_GS_GRRZXX' (Cost=5 Card=1 Bytes=7) :Q120477
002
4 3 NESTED LOOPS* (Cost=26454 Card=111 Bytes=9213) :Q120477
002
5 4 NESTED LOOPS* (Cost=24534 Card=400 Bytes=30400) :Q120477
002
6 5 HASH JOIN* (Cost=23254 Card=400 Bytes=17200) :Q120477
002
7 6 TABLE ACCESS* (BY INDEX ROWID) OF 'T_DJ_JGNSR' (Cost=7 Card=1 Bytes=17) :Q120477
000
8 7 INDEX (RANGE SCAN) OF 'IDX_T_DJ_JGNSR_NSRBM'(NON-UNIQUE) (Cost=3 Card=1)
9 6 PARTITION RANGE* (ALL) :Q120477
002
10 9 INDEX* (FAST FULL SCAN) OF 'IDX_T_GS_GRSBJKMX_BAK_NSR' :Q120477
(NON-UNIQUE) (Cost=23247 Card=783958887 Bytes=203 002
82931062)
11 5 PARTITION HASH* (ITERATOR) :Q120477
002
12 11 TABLE ACCESS* (BY LOCAL INDEX ROWID) OF 'T_GS_GRJBXX' (Cost=4 Card=1 Bytes=33) :Q120477
002
13 12 INDEX* (UNIQUE SCAN) OF 'PK_T_GS_GRJBXX' (UNIQUE) (Cost=1 Card=1) :Q120477
002
14 4 INDEX* (RANGE SCAN) OF 'PK_T_GS_GRRZXX' (UNIQUE) (Cost=2 Card=1) :Q120477
002
15 2 TABLE ACCESS* (FULL) OF 'T_DM_GS_RZZT' (Cost=2 Card=4Bytes=84) :Q120477
001
1 PARALLEL_TO_SERIAL SELECT DISTINCT C0 C0,C1 C1,C2 C2 FROM :Q120
477002
2 PARALLEL_TO_PARALLEL SELECT /*+ ORDERED NO_EXPAND USE_HASH(A2) */
A1.C7 C0,A1.C8 C1,A2.C1 C2 FROM (SE
3 PARALLEL_COMBINED_WITH_CHILD
4 PARALLEL_COMBINED_WITH_PARENT
5 PARALLEL_COMBINED_WITH_PARENT
6 PARALLEL_COMBINED_WITH_PARENT
7 PARALLEL_FROM_SERIAL
9 PARALLEL_COMBINED_WITH_PARENT
10 PARALLEL_COMBINED_WITH_PARENT
11 PARALLEL_COMBINED_WITH_PARENT
12 PARALLEL_COMBINED_WITH_PARENT
13 PARALLEL_COMBINED_WITH_PARENT
14 PARALLEL_COMBINED_WITH_PARENT
15 PARALLEL_FROM_SERIAL
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3094471 consistent gets
1038159 physical reads
0 redo size
179929 bytes sent via SQL*Net to client
2682 bytes received via SQL*Net from client
351 SQL*Net roundtrips to/from client
133 sorts (memory)
0 sorts (disk)
5246 rows processed
颖问:
1.SQL是否还可以存在进一步优化可能?
2.在第10 步, INDEX* (FAST FULL SCAN) OF 'IDX_T_GS_GRSBJKMX_BAK_NSR' 所花费成本较高,该如何优化?
3.附件是一些统计数据信息?
[ 本帖最后由 bbs159 于 2011-6-29 13:11 编辑 ]
SQL> SELECT DISTINCT GRJBXX.SFZMHM 身份证号, GRJBXX.XM 姓名, RZZT.MC 任职状态
2 FROM ODS.T_GS_GRSBJKMX SBJK,
3 ODS.T_DJ_JGNSR JGNSR,
4 ODS.T_GS_GRJBXX GRJBXX,
5 ODS.T_GS_GRRZXX RZXX,
6 ODS.T_DM_GS_RZZT RZZT
7 WHERE RZXX.RZZT_DM = RZZT.RZZT_DM
8 AND RZXX.NSGRNBM = GRJBXX.NSGRNBM
9 AND SBJK.NSGRNBM = GRJBXX.NSGRNBM
10 AND SBJK.NSRNBM = JGNSR.NSRNBM
11 AND JGNSR.NSRBM = '190499390'
12 AND SBJK.NSRNBM = '190499390'
13 /
一、以下是所有SQL中各表的索引情况
T_GS_GRSBJKMX表索引:
TABLE_NAME INDEX_NAME COLUMN_POSITION COLUMN_NAME
1 T_GS_GRSBJKMX IDX_T_GS_GRSBJKMX_BAK_NSR 1 NSGRNBM
2 T_GS_GRSBJKMX IDX_T_GS_GRSBJKMX_BAK_NSR 2 NSRNBM
3 T_GS_GRSBJKMX IDX_T_GS_GRSBJKMX_BAK_SBRQ 1 SB_RQ
4 T_GS_GRSBJKMX IDX_T_GS_GRSBJKMX_BAK_XG_SJ 1 XG_SJ
5 T_GS_GRSBJKMX PK_T_GS_GRSBJKMX_BAK 1 GS_XH
6 T_GS_GRSBJKMX PK_T_GS_GRSBJKMX_BAK 2 MX_XH
T_DJ_JGNSR表索引情况:
TABLE_NAME INDEX_NAME COLUMN_POSITION COLUMN_NAME
1 T_DJ_JGNSR IDX_T_DJ_JGNSR_GLJG_DM 1 GLJG_DM
2 T_DJ_JGNSR IDX_T_DJ_JGNSR_LRSJ 1 LR_SJ
3 T_DJ_JGNSR IDX_T_DJ_JGNSR_NSRBM 1 NSRBM
4 T_DJ_JGNSR IDX_T_DJ_JGNSR_NSRMC 1 NSR_MC
5 T_DJ_JGNSR IDX_T_DJ_JGNSR_NSRNBM 1 NSRNBM
6 T_DJ_JGNSR IDX_T_DJ_JGNSR_SWDJLB 1 SWDJLB_DM
7 T_DJ_JGNSR IDX_T_DJ_JGNSR_SWDJZH 1 SWDJZH
8 T_DJ_JGNSR IDX_T_DJ_JGNSR_ZGY 1 ZGY
9 T_DJ_JGNSR IDX_T_DJ_JGNSR_ZZJG_DM 1 ZZJG_DM
T_GS_GRJBXX表
TABLE_NAME INDEX_NAME COLUMN_POSITION COLUMN_NAME
1 T_GS_GRJBXX IDX_T_GS_GRJBXX_SFZMHM 1 SFZMHM
2 T_GS_GRJBXX PK_T_GS_GRJBXX 1 NSGRNBM
T_GS_GRRZXX表索引
TABLE_NAME INDEX_NAME COLUMN_POSITION COLUMN_NAME
1 T_GS_GRRZXX IDX_T_GS_GRRZXX_SWJG_DM 1 SWJG_DM
2 T_GS_GRRZXX PK_T_GS_GRRZXX 1 NSGRNBM
3 T_GS_GRRZXX PK_T_GS_GRRZXX 2 NSRNBM
T_DM_GS_RZZT 表索引情况
TABLE_NAME INDEX_NAME COLUMN_POSITION COLUMN_NAME
1 T_DM_GS_RZZT PK_DIM_RZZT 1 RZZT_DM
二、索引数据分布情况
ODS.T_GS_GRSBJKMX 按”每年每月”建分区表
见附邮
三 执行计划
SELECT DISTINCT GRJBXX.SFZMHM 身份证号, GRJBXX.XM 姓名, RZZT.MC 任职状态
2 FROM (SELECT DISTINCT NSGRNBM,NSRNBM FROM ODS.T_GS_GRSBJKMX) SBJK,
3 ODS.T_DJ_JGNSR JGNSR,
4 ODS.T_GS_GRJBXX GRJBXX,
5 ODS.T_GS_GRRZXX RZXX,
6 ODS.T_DM_GS_RZZT RZZT
7 WHERE
8 RZXX.RZZT_DM = RZZT.RZZT_DM
9 AND RZXX.NSGRNBM = GRJBXX.NSGRNBM
10 AND SBJK.NSGRNBM = GRJBXX.NSGRNBM
11 AND SBJK.NSRNBM = JGNSR.NSRNBM
12 AND JGNSR.NSRBM = '190499390'
13 /
已选择5246行。
执行计划
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=26463 Card=111 Bytes=11544)
1 0 SORT* (UNIQUE) (Cost=26463 Card=111 Bytes=11544) :Q120477
003
2 1 HASH JOIN* (Cost=26456 Card=111 Bytes=11544) :Q120477
002
3 2 TABLE ACCESS* (BY INDEX ROWID) OF 'T_GS_GRRZXX' (Cost=5 Card=1 Bytes=7) :Q120477
002
4 3 NESTED LOOPS* (Cost=26454 Card=111 Bytes=9213) :Q120477
002
5 4 NESTED LOOPS* (Cost=24534 Card=400 Bytes=30400) :Q120477
002
6 5 HASH JOIN* (Cost=23254 Card=400 Bytes=17200) :Q120477
002
7 6 TABLE ACCESS* (BY INDEX ROWID) OF 'T_DJ_JGNSR' (Cost=7 Card=1 Bytes=17) :Q120477
000
8 7 INDEX (RANGE SCAN) OF 'IDX_T_DJ_JGNSR_NSRBM'(NON-UNIQUE) (Cost=3 Card=1)
9 6 PARTITION RANGE* (ALL) :Q120477
002
10 9 INDEX* (FAST FULL SCAN) OF 'IDX_T_GS_GRSBJKMX_BAK_NSR' :Q120477
(NON-UNIQUE) (Cost=23247 Card=783958887 Bytes=203 002
82931062)
11 5 PARTITION HASH* (ITERATOR) :Q120477
002
12 11 TABLE ACCESS* (BY LOCAL INDEX ROWID) OF 'T_GS_GRJBXX' (Cost=4 Card=1 Bytes=33) :Q120477
002
13 12 INDEX* (UNIQUE SCAN) OF 'PK_T_GS_GRJBXX' (UNIQUE) (Cost=1 Card=1) :Q120477
002
14 4 INDEX* (RANGE SCAN) OF 'PK_T_GS_GRRZXX' (UNIQUE) (Cost=2 Card=1) :Q120477
002
15 2 TABLE ACCESS* (FULL) OF 'T_DM_GS_RZZT' (Cost=2 Card=4Bytes=84) :Q120477
001
1 PARALLEL_TO_SERIAL SELECT DISTINCT C0 C0,C1 C1,C2 C2 FROM :Q120
477002
2 PARALLEL_TO_PARALLEL SELECT /*+ ORDERED NO_EXPAND USE_HASH(A2) */
A1.C7 C0,A1.C8 C1,A2.C1 C2 FROM (SE
3 PARALLEL_COMBINED_WITH_CHILD
4 PARALLEL_COMBINED_WITH_PARENT
5 PARALLEL_COMBINED_WITH_PARENT
6 PARALLEL_COMBINED_WITH_PARENT
7 PARALLEL_FROM_SERIAL
9 PARALLEL_COMBINED_WITH_PARENT
10 PARALLEL_COMBINED_WITH_PARENT
11 PARALLEL_COMBINED_WITH_PARENT
12 PARALLEL_COMBINED_WITH_PARENT
13 PARALLEL_COMBINED_WITH_PARENT
14 PARALLEL_COMBINED_WITH_PARENT
15 PARALLEL_FROM_SERIAL
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3094471 consistent gets
1038159 physical reads
0 redo size
179929 bytes sent via SQL*Net to client
2682 bytes received via SQL*Net from client
351 SQL*Net roundtrips to/from client
133 sorts (memory)
0 sorts (disk)
5246 rows processed
颖问:
1.SQL是否还可以存在进一步优化可能?
2.在第10 步, INDEX* (FAST FULL SCAN) OF 'IDX_T_GS_GRSBJKMX_BAK_NSR' 所花费成本较高,该如何优化?
3.附件是一些统计数据信息?
[ 本帖最后由 bbs159 于 2011-6-29 13:11 编辑 ]
分析统计信息.zip
(2011-06-29 12:37:12, Size: 150 KB, Downloads: 1)
分析统计信息.zip
(2011-06-29 12:43:42, Size: 150 KB, Downloads: 0)
分区表信息.zip
(2011-06-29 13:11:07, Size: 17.1 KB, Downloads: 0)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13824386/viewspace-700958/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/13824386/viewspace-700958/