表结构优化实例

表结构优化实例

--&gt

xzh2000 | 19 一月, 2005 16:14

表结构优化实例
本方法适合表中存在大量的标志字段,如下表,很多字段就只存储Y/N或0/1/2/4等,

如结构如下,
create table agent_settime_onself
( agent_settime_onself_id number(11,0),
agent_card_type_id number(11,0) not null enable,
start_time_1 char(5) default '00:00' not null enable,
start_time_1_enabled char(1) default 'N',
stop_time_1 char(5) default '23:59' not null enable,
stop_time_1_enable char(1) default 'N',
start_time_2 char(5) default '00:00' not null enable,
start_time_2_enabled char(1) default 'N',
stop_time_2 char(5) default '23:59' not null enable,
stop_time_2_enable char(1) default 'N',
start_time_3 char(5) default '00:00' not null enable,
start_time_3_enabled char(1) default 'N',
stop_time_3 char(5) default '23:59' not null enable,
stop_time_3_enable char(1) default 'N',
start_time_4 char(5) default '00:00' not null enable,
start_time_4_enabled char(1) default 'N',
stop_time_4 char(5) default '23:59' not null enable,
stop_time_4_enable char(1) default 'N',
start_time_5 char(5) default '00:00' not null enable,
start_time_5_enabled char(1) default 'N',
stop_time_5 char(5) default '23:59' not null enable,
stop_time_5_enable char(1) default 'N',
start_time_6 char(5) default '00:00' not null enable,
start_time_6_enabled char(1) default 'N',
stop_time_6 char(5) default '23:59' not null enable,
stop_time_6_enable char(1) default 'N',
start_time_7 char(5) default '00:00' not null enable,
start_time_7_enabled char(1) default 'N',
stop_time_7 char(5) default '23:59' not null enable,
stop_time_7_enable char(1) default 'N')

表的访问SQL如下:
select a.*,sysdate now,to_char(sysdate,'D') as week
from agent_settime_onself a
where ((start_time_1_enabled = 'Y' OR start_time_2_enabled = 'Y'
OR start_time_3_enabled = 'Y' OR start_time_4_enabled = 'Y'
OR start_time_5_enabled = 'Y' OR start_time_6_enabled = 'Y'
OR start_time_7_enabled = 'Y')
OR (stop_time_1_enable = 'Y' OR stop_time_2_enable = 'Y'
OR stop_time_3_enable = 'Y' OR stop_time_4_enable = 'Y'
OR stop_time_5_enable = 'Y' OR stop_time_6_enable = 'Y'
OR stop_time_7_enable = 'Y'))

这么多用于判断的字段,如果都创建索引也是不合理的,再说这些字段的数据分布不均匀,
如何对这样的表结构进行优化呢?优化后的表结构如下:
create table agent_settime_onself
( agent_settime_onself_id number(11,0),
agent_card_type_id number(11,0) not null enable,
start_time_1 char(5) default '00:00' not null enable,
start_time_enabled char(7) default 'NNNNNNN',
stop_time_1 char(5) default '23:59' not null enable,
stop_time_enable char(7) default 'NNNNNNN',
start_time_2 char(5) default '00:00' not null enable,
stop_time_2 char(5) default '23:59' not null enable,
start_time_3 char(5) default '00:00' not null enable,
stop_time_3 char(5) default '23:59' not null enable,
start_time_4 char(5) default '00:00' not null enable,
stop_time_4 char(5) default '23:59' not null enable,
start_time_5 char(5) default '00:00' not null enable,
stop_time_5 char(5) default '23:59' not null enable,
start_time_6 char(5) default '00:00' not null enable,
stop_time_6 char(5) default '23:59' not null enable,
start_time_7 char(5) default '00:00' not null enable,
stop_time_7 char(5) default '23:59' not null enable)

create index idx_aso_sta_enabled on agent_settime_onself(start_time_enabled);
create index idx_aso_sto_enabled on agent_settime_onself(stop_time_enabled);

那以后访问该表的SQL如下:
select a.*,sysdate now,to_char(sysdate,'D') as week
from agent_settime_onself a
where start_time_enabled = 'YYYYYYY'

select a.*,sysdate now,to_char(sysdate,'D') as week
from agent_settime_onself a
where stop_time_enabled = 'YYYYYYY'

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/257699/viewspace-815294/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/257699/viewspace-815294/