ヒント句の大文字小文字と別名

(5.6)

-- データ準備
drop table tab1;
create table tab1(col1 int);
alter table tab1 add constraint tab1pk primary key(col1);


drop procedure proc1;

delimiter //
create procedure proc1()
begin
declare i int;
set i = 1;

while i <= 100000 do
insert into tab1 values(i);
set i = i+1;
end while;
end
//
delimiter ;

call proc1();

analyze table tab1;

-- 動作確認

explain
select
count(*) from tab1 where col1 = 1;


-- 1.オペレータ部分がすべて大文字(※マニュアル記載) → ヒント句は有効

explain
select
count(*) from tab1 IGNORE INDEX (primary) where col1 = 1;

-- 2.オペレータ部分がすべて小文字 → ヒント句は有効
explain
select
count(*) from tab1 ignore index (primary) where col1 = 1;


-- 3.オペレータ部分が大文字小文字混合 → ヒント句は有効
explain
select
count(*) from tab1 Ignore Index (primary) where col1 = 1;

 

-- 4.テーブル名が小文字で、パラメータ部分がすべて大文字 → ヒント句は有効
explain
select
count(*) from tab1 IGNORE INDEX (PRIMARY) where col1 = 1;


-- 5.テーブル名が大文字で、パラメータ部分がすべて子文字 → ERROR 1146 (42S02): Table 'test.TAB1' doesn't exist
explain
select
count(*) from TAB1 IGNORE INDEX (primary) where col1 = 1;


-- 6.テーブル名が大文字で、パラメータ部分がすべて大文字 → ERROR 1146 (42S02): Table 'test.TAB1' doesn't exist
explain
select
count(*) from TAB1 IGNORE INDEX (PRIMARY) where col1 = 1;

 


(8.0.18)

-- データ準備

drop table tab1;
drop table tab2;

create table tab1(col1 int);
create table tab2(col1 int);


drop procedure proc1;

delimiter //
create procedure proc1()
begin
declare i int;
set i = 1;

while i <= 100000 do
insert into tab1 values(i);
set i = i+1;
end while;
end
//
delimiter ;

call proc1();

SET @num=0;
insert into tab2 select @num:=@num+1 from tab1 limit 100;

 

analyze table tab1;
analyze table tab2;


-- 動作確認

explain analyze
select
count(*) from tab1 inner join tab2 on tab1.col1 = tab2.col1 ;
show warnings;

 

-- 1.オペレータ部分がすべて大文字(※マニュアル記載) → ヒント句は有効
explain analyze
select /*+ JOIN_ORDER(tab1, tab2) */
count(*) from tab1 inner join tab2 on tab1.col1 = tab2.col1 ;
show warnings;


-- 2.オペレータ部分がすべて小文字 → ヒント句は有効

explain analyze
select /*+ join_order(tab1, tab2) */
count(*) from tab1 inner join tab2 on tab1.col1 = tab2.col1 ;
show warnings;

 

-- 3.オペレータ部分が大文字小文字混合 → ヒント句は有効
explain analyze
select /*+ Join_Order(tab1, tab2) */
count(*) from tab1 inner join tab2 on tab1.col1 = tab2.col1 ;
show warnings;

 

-- 4.テーブル名が小文字で、パラメータ部分がすべて大文字 → ヒント句は有効
explain analyze
select /*+ JOIN_ORDER(TAB1, TAB2) */
count(*) from tab1 inner join tab2 on tab1.col1 = tab2.col1 ;
show warnings;


-- 5.テーブル名が大文字で、パラメータ部分がすべて子文字 → ERROR 1146 (42S02): Table 'test.TAB1' doesn't exist
explain analyze
select /*+ JOIN_ORDER(tab1, tab2) */
count(*) from TAB1 inner join TAB2 on TAB1.col1 = TAB2.col1 ;
show warnings;


-- 6.テーブル名が大文字で、パラメータ部分がすべて大文字 → ERROR 1146 (42S02): Table 'test.TAB1' doesn't exist
explain analyze
select /*+ JOIN_ORDER(TAB1, TAB2) */
count(*) from TAB1 inner join TAB2 on TAB1.col1 = TAB2.col1 ;
show warnings;

 


-- 7.テーブル名が別名で、パラメータ部分で別名使用せず → ヒント句は無効★
explain analyze
select /*+ JOIN_ORDER(tab1, tab2) */
count(*) from tab1 t1 inner join tab2 t2 on t1.col1 = t2.col1 ;
show warnings;


-- 8.テーブル名が別名で、パラメータ部分で別名使用 → ヒント句は有効
explain analyze
select /*+ JOIN_ORDER(t1, t2) */
count(*) from tab1 t1 inner join tab2 t2 on t1.col1 = t2.col1 ;
show warnings;

-- 結論
テーブル名が別名の場合はヒント句でも別名を使用する必要がある。

 


(19c)

-- データ準備
drop table tab1 purge;
create table tab1(col1 int);
create index ind1 on tab1(col1);
alter table tab1 add constraint tab1pk primary key(col1) using index ind1;


declare
begin
for i in 1..100000 loop
insert into tab1 values(i);
commit;
end loop;
end;
/


exec dbms_stats.gather_table_stats('TEST','TAB1');


-- 動作確認

explain plan for
select
count(*) from tab1 where col1 = 1;
select plan_table_output from table(dbms_xplan.display(format=>'ALL') );


-- 1.オペレータ部分がすべて大文字(※マニュアル記載) → ヒント句は有効
explain plan for
select /*+ FULL(tab1) */
count(*) from tab1 where col1 = 1;
select plan_table_output from table(dbms_xplan.display(format=>'ALL') );

-- 2.オペレータ部分がすべて小文字 → ヒント句は有効
explain plan for
select /*+ full(tab1) */
count(*) from tab1 where col1 = 1;
select plan_table_output from table(dbms_xplan.display(format=>'ALL') );

-- 3.オペレータ部分が大文字小文字混合 → ヒント句は有効
explain plan for
select /*+ Full(tab1) */
count(*) from tab1 where col1 = 1;
select plan_table_output from table(dbms_xplan.display(format=>'ALL') );


-- 4.テーブル名が小文字で、パラメータ部分がすべて大文字 → ヒント句は有効
explain plan for
select /*+ FULL(TAB1) */
count(*) from tab1 where col1 = 1;
select plan_table_output from table(dbms_xplan.display(format=>'ALL') );

-- 5.テーブル名が大文字で、パラメータ部分がすべて子文字 → ヒント句は有効
explain plan for
select /*+ FULL(tab1) */
count(*) from TAB1 where col1 = 1;
select plan_table_output from table(dbms_xplan.display(format=>'ALL') );

-- 6.テーブル名が大文字で、パラメータ部分がすべて大文字 → ヒント句は有効
explain plan for
select /*+ FULL(TAB1) */
count(*) from TAB1 where col1 = 1;
select plan_table_output from table(dbms_xplan.display(format=>'ALL') );

 

-- 7.テーブル名が別名で、パラメータ部分で別名使用せず → ヒント句は無効★
explain plan for
select /*+ FULL(tab1) */
count(*) from tab1 t1 where col1 = 1;
select plan_table_output from table(dbms_xplan.display(format=>'ALL') );

-- 8.テーブル名が別名で、パラメータ部分で別名使用 → ヒント句は有効
explain plan for
select /*+ FULL(t1) */
count(*) from tab1 t1 where col1 = 1;
select plan_table_output from table(dbms_xplan.display(format=>'ALL') );

-- 結論
テーブル名が別名の場合はヒント句でも別名を使用する必要がある。

 

 

(9.4)

-- データ準備
drop table tab1;
create table tab1(col1 int);
create unique index ind1 on tab1(col1);
alter table tab1 add constraint tab1pk primary key using index ind1;


insert into tab1 select a from generate_series(1,100000,1) a;

analyze tab1;

-- 動作確認
show pg_hint_plan.debug_print;
set pg_hint_plan.debug_print = on;
show pg_hint_plan.debug_print;

explain analyze
select
count(*) from tab1 where col1 = 1;

 

-- 1.オペレータ部分がすべて大文字 → ヒント句は有効
/*+ SEQSCAN(tab1) */
explain analyze
select
count(*) from tab1 where col1 = 1;


-- 2.オペレータ部分がすべて小文字 → ヒント句は有効
/*+ seqscan(tab1) */
explain analyze
select
count(*) from tab1 where col1 = 1;


-- 3.オペレータ部分が大文字小文字混合(※マニュアル記載) → ヒント句は有効
/*+ SeqScan(tab1) */
explain analyze
select
count(*) from tab1 where col1 = 1;

 


-- 4.テーブル名が小文字で、パラメータ部分がすべて大文字 → ヒント句は無効★
/*+ SeqScan(TAB1) */
explain analyze
select
count(*) from tab1 where col1 = 1;

-- 5.テーブル名が大文字で、パラメータ部分がすべて子文字 → ヒント句は有効
/*+ SeqScan(tab1) */
explain analyze
select
count(*) from TAB1 where col1 = 1;

-- 6.テーブル名が大文字で、パラメータ部分がすべて大文字 → ヒント句は無効★
/*+ SeqScan(TAB1) */
explain analyze
select
count(*) from TAB1 where col1 = 1;

 

-- 7.テーブル名が別名で、パラメータ部分で別名使用せず → ヒント句は無効★
/*+ SeqScan(tab1) */
explain analyze
select
count(*) from tab1 t1 where col1 = 1;


-- 8.テーブル名が別名で、パラメータ部分で別名使用 → ヒント句は有効
/*+ SeqScan(t1) */
explain analyze
select
count(*) from tab1 t1 where col1 = 1;

-- 結論
テーブル名が別名の場合はヒント句でも別名を使用する必要がある。
ヒント句に指定するテーブル名は小文字で指定する必要がある。

 

 

(2014)

-- データ準備

drop table tab1;
create table tab1(col1 int not null);
alter table tab1 add constraint tab1pk primary key (col1);


drop procedure proc1;
create procedure proc1
as
begin
declare @i integer;
execute('truncate table tab1');
set @i = 1;
while @i <= 100
begin
insert into tab1 values(@i);
set @i = @i + 1;
end
end
;

exec proc1;
update statistics tab1;

 

-- 動作確認
set statistics profile on;
go

select
count(*) from tab1 where col1 =1;


-- 1.オペレータ部分がすべて大文字(※マニュアル記載) → ヒント句は有効
select
count(*) from tab1 WITH (FORCESCAN) where col1 =1;


-- 2.オペレータ部分がすべて小文字 → ヒント句は有効
select
count(*) from tab1 with (forcescan) where col1 =1;


-- 3.オペレータ部分が大文字小文字混合 → ヒント句は有効
select
count(*) from tab1 With (ForceScan) where col1 =1;