44 积分	0 好友	2 主题

发消息

请教字段nullable对优化的影响

1^#

发表于 2012-7-5 12:00:33 | 查看: 4408| 回复: 5

今天看到刘“11g中AWR新快照视图”的贴子。使用其中的语句查询出几个topsql
SN SQL_ID  CPU WAIT       IO    TOTAL
---------- ------------- ---------- ---------- ---------- ----------
  1 4ah1s5jkpg2nd 0 6033    112 6145
  2 d90zzgkqqb4s4 0 1227    2463 3690
  3 8szmwam7fysa3 0    0    3150 3150
  4 1bbruszu1d2z4 0 1792       75 1867
  5 7ztdck1vvj1yg 0    0    1693 1693
  6 db7vhxa3p8v7w 0    0    1599 1599
  7 7azp46cfpx90p 0    6    1517 1523
  8 272hznyn8bpcx 0 360    820 1180
  9 8mfngut080fvf 0    65    1101 1166
10 1nmzgq7mp6d9r 0    0    1111 1111
10 rows selected.

其中第二句sql_text如下
select /*+ parallel_index(t, "IDX_JZDJINFORJZDJMIDSHIT",128) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad  no_expand index_ffs(t,"IDX_JZDJINFORJZDJMIDSHIT") */ count(*) as nrw,count(distinct sys_op_lbid(76203,'L',t.rowid)) as nlb,count(distinct hextoraw(sys_op_descend("MEMBERID")||sys_op_descend("CYJSSH")||sys_op_descend("INTYPE"))) as ndk,sys_op_countchg(substrb(t.rowid,1,15),1) as clf from "HCHGB"."JZDJINFOR" sample block (  2.8695793365,1)  t where "MEMBERID" is not null or "CYJSSH" is not null or "INTYPE" is not null

想请教字段 nullable 设置对select,insert,update的影响，及各位的意建与建议。

分享0

收藏0 回复只看该作者道具举报

Maclean Liu(刘相兵

2^#

发表于 2012-7-5 12:02:32

请直接参考约束条件对于查询优化的作用http://www.oracledatabase12g.com ... BD%9C%E7%94%A8.html

回复只看该作者道具举报

26856649

3^#

发表于 2012-7-5 12:08:34

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
SQL_ID d90zzgkqqb4s4, child number 0
-------------------------------------
select /*+ parallel_index(t, "IDX_JZDJINFORJZDJMIDSHIT",128) dbms_stats
cursor_sharing_exact use_weak_name_resl dynamic_sampling(0)
no_monitoring no_substrb_pad  no_expand
index_ffs(t,"IDX_JZDJINFORJZDJMIDSHIT") */ count(*) as
nrw,count(distinct sys_op_lbid(76203,'L',t.rowid)) as
nlb,count(distinct hextoraw(sys_op_descend("MEMBERID")||sys_op_descend("
CYJSSH")||sys_op_descend("INTYPE"))) as
ndk,sys_op_countchg(substrb(t.rowid,1,15),1) as clf from
"HCHGB"."JZDJINFOR" sample block (  2.8695793365,1)  t where "MEMBERID"
is not null or "CYJSSH" is not null or "INTYPE" is not null

Plan hash value: 121253995

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

| Id  | Operation       | Name          | Starts | E-Rows | A-Rows | A-Time | Buffers |  OMem
|  1Mem | Used-Mem |

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

| 0 | SELECT STATEMENT       |          |    1 |    | 1 |00:00:07.59 |    5 |
|    |       |

| 1 |  SORT GROUP BY       |          |    1 |    1 | 1 |00:00:07.59 |    5 | 73728
| 73728 |       |

| 2 | PX COORDINATOR       |          |    1 |    | 380 |00:00:07.59 |    5 |
|    |       |

| 3 | PX SEND QC (RANDOM)       | :TQ10001          |    0 |    1 | 0 |00:00:00.01 |    0 |
|    |       |

| 4 |    SORT GROUP BY       |          |    0 |    1 | 0 |00:00:00.01 |    0 | 36864
| 36864 |32768  (0)|

| 5 |    PX RECEIVE       |          |    0 |    1 | 0 |00:00:00.01 |    0 |
|    |       |

| 6 |    PX SEND HASH       | :TQ10000          |    0 |    1 | 0 |00:00:00.01 |    0 |
|    |       |

| 7 |       SORT GROUP BY       |          |    0 |    1 | 0 |00:00:00.01 |    0 | 52224
| 52224 |47104  (0)|

| 8 | PX BLOCK ITERATOR       |          |    0 | 158K| 0 |00:00:00.01 |    0 |
|    |       |

|*  9 |    INDEX SAMPLE FAST FULL SCAN| IDX_JZDJINFORJZDJMIDSHIT |    0 | 158K| 0 |00:00:00.01 |    0 |
|    |       |

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

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

9 - access(:Z>=:Z AND :Z<=:Z)
   filter(("MEMBERID" IS NOT NULL OR "INTYPE" IS NOT NULL OR "CYJSSH" IS NOT NULL))

36 rows selected.

回复只看该作者道具举报