使用hint改变执行计划
Drop table if exists t_f_comp;
create table t_f_comp(id1 int,id2 int unique ,name varchar(10) not null );
insert into t_f_comp values (1,1,'a');
insert into t_f_comp values (1,2,'b');
create index t_f_idx on t_f_comp(id1);
create index t_f_idx_name on t_f_comp(name);
update statistics
Dbaccess执行set explain on;后执行以下sql;
select * from t_f_comp where id1=1 and name='a';
select {+INDEX(t_f_comp,t_f_idx)} * from t_f_comp where id1=1 and name='a';
select {+INDEX(t_f_comp,t_f_idx_name)} * from t_f_comp where id1=1 and name='a';
绑定执行SQL绑定执行计划能力
onconfig EXT_DIRECTIVES=1
export IFX_EXTDIRECTIVES=1
create table t_key(
id int,
c1 varchar(20),
c2 char(20)
);
create index c1_idx on t_key(c1);
insert into t_key values(1,'t_gbase1','ndty');
insert into t_key values(2,'t_gbase2','ndty');
查看执行计划,如下sql走索引
select * from t_key where c1 like 't_%';
通过定义绑定执行计划使用顺序扫描
SAVE EXTERNAL DIRECTIVES {+FULL(t_key)} ACTIVE FOR
select * from t_key where c1 like 't_%';
set explain on;
select * from t_key where c2 like 't_%';