ヒント句の分割指定

 

(8.0.26)

drop table tab1;
create table tab1(
    col1 bigint 
   ,col2 bigint
   );

drop table tab2;
create table tab2(
    col1 bigint 
   ,col2 bigint
   );

drop procedure proc1;

delimiter //
create procedure proc1(in x int)
begin
  declare i int;
  set i = 0;
  start transaction;
  while i < x do
    set i = i + 1;
    insert into tab1 values(
     i
    ,floor(rand() * 1000)+1
    );
    insert into tab2 values(
     i
    ,floor(rand() * 1000)+1
    );
  end while;
  commit;
end
//
delimiter ;

call proc1(1000000);

select count(*) from tab1;
select * from tab1 order by rand() limit 20;
alter table tab1 add constraint tab1pk primary key (col1);
create index ind12 on tab1(col2);

select count(*) from tab2;
select * from tab2 order by rand() limit 20;
alter table tab2 add constraint tab2pk primary key (col1);
create index ind22 on tab2(col2);

 

analyze table tab1;
analyze table tab2;

explain
select /*+ JOIN_PREFIX(t2) NO_INDEX(t2 ind22) */
count(1)
from tab1 t1
inner join tab2 t2
on t1.col2 = t2.col2
where mod(t2.col1,3) = 0
;
show warnings;


explain
select /*+ JOIN_PREFIX(t2) */ /*+ NO_INDEX(t2 ind22) */
count(1)
from tab1 t1
inner join tab2 t2
on t1.col2 = t2.col2
where mod(t2.col1,3) = 0
;
show warnings;

explain
select /*+ NO_INDEX(t2 ind22) */ /*+ JOIN_PREFIX(t2) */
count(1)
from tab1 t1
inner join tab2 t2
on t1.col2 = t2.col2
where mod(t2.col1,3) = 0
;
show warnings;


ヒント句の分割指定は無効で、複数指定した場合、最初のヒント句が使用される

ヒントコメントには複数のヒントを含めることができますが、クエリーブロックに複数のヒントコメントを含めることはできません。

(19c)

drop table tab1 purge;
create table tab1(
    col1 int 
   ,col2 int
  );

drop table tab2 purge;
create table tab2(
    col1 int 
   ,col2 int
   );

declare
begin
for i in 1..1000000 loop
  insert into tab1 values(
    i
   ,floor(dbms_random.value(1, 1001) )
   );
  
  insert into tab2 values(
    i
   ,floor(dbms_random.value(1, 1001) )
   );
end loop;
end;
/

commit;


select count(*) from tab1;
select * from tab1 order by dbms_random.value()  fetch first 20 rows only;
alter table tab1 add constraint tab1pk primary key (col1);
create index ind12 on tab1(col2);

select count(*) from tab2;
select * from tab2 order by dbms_random.value()  fetch first 20 rows only;
alter table tab2 add constraint tab2pk primary key (col1);
create index ind22 on tab2(col2);

set time on
set timing on


exec dbms_stats.gather_table_stats(user,'TAB1');
exec dbms_stats.gather_table_stats(user,'TAB2');


explain plan for
select /*+ LEADING(t1 t2) INDEX(t2 tab2pk) */
count(1)
from tab1 t1
inner join tab2 t2
on t1.col2 = t2.col2
where mod(t2.col1,3) = 0
;
select * from table(dbms_xplan.display(format=>'ALL') );

explain plan for
select /*+ LEADING(t1 t2) */  /*+ INDEX(t2 tab2pk) */
count(1)
from tab1 t1
inner join tab2 t2
on t1.col2 = t2.col2
where mod(t2.col1,3) = 0
;
select * from table(dbms_xplan.display(format=>'ALL') );

explain plan for
select /*+ INDEX(t2 tab2pk) */  /*+ LEADING(t1 t2) */
count(1)
from tab1 t1
inner join tab2 t2
on t1.col2 = t2.col2
where mod(t2.col1,3) = 0
;
select * from table(dbms_xplan.display(format=>'ALL') );


ヒント句の分割指定は無効で、複数指定した場合、最初のヒント句が使用される


ヒントの使用方法
文ブロックはヒントを含むコメントを1つだけ持つことができ、
SELECT、UPDATE、INSERT、MERGEまたはDELETEの各キーワードに続けてコメントを指定します。

 

(14)

drop table tab1;
create table tab1(
    col1 bigint 
   ,col2 bigint
  );
drop table tab2;
create table tab2(
    col1 bigint 
   ,col2 bigint
   );
   

start transaction;
insert into tab1 select
   g
  ,floor(random() * 1000)+1
from generate_series(1,1000000) g;

commit;

start transaction;
insert into tab2 select
   g
  ,floor(random() * 1000)+1
from generate_series(1,1000000) g;

commit;


select count(*) from tab1;
select * from tab1 order by random() limit 20;
alter table tab1 add constraint tab1pk primary key (col1);
create index ind12 on tab1(col2);
select count(*) from tab2;
select * from tab2 order by random() limit 20;
alter table tab2 add constraint tab2pk primary key (col1);
create index ind22 on tab2(col2);

 

\timing 1
\pset pager 0

analyze tab1;
analyze tab2;

set pg_hint_plan.debug_print=1;
show pg_hint_plan.debug_print;

/*+ Leading( (t2 t1) ) NestLoop(t1 t2) */
explain
select
count(1)
from tab1 t1
inner join tab2 t2
on t1.col2 = t2.col2
where mod(t2.col1,3) = 0
;


/*+ Leading( (t2 t1) ) */ /*+ NestLoop(t1 t2) */
explain
select
count(1)
from tab1 t1
inner join tab2 t2
on t1.col2 = t2.col2
where mod(t2.col1,3) = 0
;

/*+ NestLoop(t1 t2) */ /*+ Leading( (t2 t1) ) */
explain
select
count(1)
from tab1 t1
inner join tab2 t2
on t1.col2 = t2.col2
where mod(t2.col1,3) = 0
;

ヒント句の分割指定は無効で、複数指定した場合、最初のヒント句が使用される

pg_hint_plan reads hints from only the first block comment

 

(2019)


drop table tab1;
create table tab1(
    col1 bigint not null
   ,col2 bigint
   );

drop table tab2;
create table tab2(
    col1 bigint not null
   ,col2 bigint
   );


set nocount on
declare @i int;
set @i = 1;
begin transaction;
while @i <= 1000000
begin
  insert into tab1 values(
     @i
    ,floor(rand() * 1000)+1
    );
   insert into tab2 values(
     @i
    ,floor(rand() * 1000)+1
    );
 set @i = @i + 1;
end
commit;


select count(*) from tab1;
select top 20 * from tab1 order by newid();
alter table tab1 add constraint tab1pk primary key (col1);
create index ind12 on tab1(col2);


select count(*) from tab2;
select top 20 * from tab2 order by newid();
alter table tab2 add constraint tab2pk primary key (col1);
create index ind22 on tab2(col2);


set statistics time on


update statistics tab1;
update statistics tab2;


select
count(1)
from tab1 t1
inner join tab2 t2
on t1.col2 = t2.col2
where t2.col1%3 = 0
OPTION(LOOP JOIN , TABLE HINT(t1, FORCESEEK) );

select
count(1)
from tab1 t1
inner join tab2 t2
on t1.col2 = t2.col2
where t2.col1%3 = 0
OPTION(LOOP JOIN)
OPTION(TABLE HINT(t1, FORCESEEK) );
;

ヒント句の分割指定は無効で、複数指定した場合、構文エラーとなる