看到DBA发了这么一个脚本，不知道是什么意思！

wffffc1

不错

anlinew

原帖由 Yong Huang 于 2008-11-27 11:21 发表


oradbHome was saying that when status is null, the index still has (null, '1'), so it's still an entry in the index. If the index were on (status) only, when status is null, the index wouldn't even have an entry for this row so a full table scan is the only option.

It's a clever trick to improve performance in special cases. "其实在设计中尽量避免字段为null, 不能用null 代表业务意义" means you should not have or should only have very few cases where you'll have a where-clase like "where status is null"; instead, you should design the model such that a special value, say, 'NULL', is put in the column whenever you would otherwise put null (i.e. nothing) in the column and your where-clause becomes "where status='NULL'". That's a good suggestion. But sometimes it's just not easy to predict during the design time.

Yong Huang

所以：软件系统的优化应该是全生命周期的

Yong Huang

原帖由 anlinew 于 2008-11-27 02:02 发表

所以：软件系统的优化应该是全生命周期的

Agree. For now, the DBA's remedy works. Alternatively, if status doesn't have many distinct values and the table doesn't have frequent small DMLs, a bitmap index on (status) will do, too. (Infrequent bulk DMLs are fine with bitmap indexes.) One other idea is to talk to the application team to see if it's too much trouble to change the code to handle a new value, say, "NULL", instead of an empty null. If it's OK, just update the column to change all null's to "NULL". Then a NOT NULL constraint can be added, which may even further boost performance of this or other SQLs because CBO has more information about this column in considering the best plan.

Yong Huang

alonemo

YH说的很有创见

sqysl

学习了，就是为了增加利用索引的机会啊。

晶晶小妹

的确是一个很不错的方法，值得学习。

beybey911

学习了，谢谢

beybey911

create index idx_prodd on prodd (status,'1');
刚刚试了下，这会在每个叶节点上都增加一个为‘1’的值，所以记录数很大的时候应该会增加不少索引所占的空间，使用时要衡量利弊。
dump的内容。
row#0[8020] flag: ------, lock: 0, len=16
col 0; len 4; (4):  74 65 73 74   \\该列原值
col 1; len 1; (1):  31    \\31=0011 0001=49  chr（49）=1
col 2; len 6; (6):  01 00 01 ae 00 03  \\ rowid 无object_id
row#1[8006] flag: ------, lock: 0, len=14
col 0; len 2; (2):  74 6a
col 1; len 1; (1):  31
col 2; len 6; (6):  01 00 01 ae 00 02
row#2[7991] flag: ------, lock: 0, len=15
col 0; len 3; (3):  74 6d 6a
col 1; len 1; (1):  31
col 2; len 6; (6):  01 00 01 ae 00 00
row#3[7979] flag: ------, lock: 0, len=12
col 0; NULL
col 1; len 1; (1):  31
col 2; len 6; (6):  01 00 01 ae 00 01
row#4[7967] flag: ------, lock: 0, len=12
col 0; NULL
col 1; len 1; (1):  31
col 2; len 6; (6):  01 00 01 ae 00 04
----- end of leaf block dump -----


其实这也就是人为的将包含null的列的单列索引转换为了该列与一个常数‘1’所组成的复合索引。

[ 本帖最后由 beybey911 于 2008-12-7 11:54 编辑 ]

jboracle1981

学习了,顶

chenzhen232

有创意的想法 学习了