(8.0.26)
drop table tab1;
create table tab1
( col1 int primary key
, col2 int
, col3 int
, col4 int
)
partition by range (col1)
( partition p1 values less than (0)
, partition p2 values less than (maxvalue)
);
set @sSQL := concat(" explain "," select * from tab1 t1,tab1 t2 ");
prepare stmt from @sSQL;
execute stmt;
deallocate prepare stmt;
(19c)
drop table tab1 purge;
create table tab1
( col1 int primary key
, col2 int
, col3 int
, col4 int
)
partition by range (col2)
( partition p1 values less than (0)
, partition p2 values less than (maxvalue)
);
declare
sSQL varchar2(4000);
begin
sSQL := ' EXPLAIN PLAN ' || ' SET STATEMENT_ID = ''1'' FOR SELECT * from tab1 partition(p1) ';
execute immediate sSQL;
sSQL := ' EXPLAIN PLAN ' || ' SET STATEMENT_ID = ''2'' FOR SELECT * from tab1 partition(p2) ';
execute immediate sSQL;
end;
/
SELECT PLAN_TABLE_OUTPUT
FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', '1','TYPICAL'));
SELECT PLAN_TABLE_OUTPUT
FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', '2','TYPICAL'));
※PLAN_TABLEはグローバル一時表のため、同一セッションで確認する必要がある
(14)
create table tab1
( col1 int primary key
, col2 int
)
;
do
$$
declare
rec record;
cur1 refcursor;
begin
open cur1 for execute ' explain analyze ' || ' select * from tab1 t1,tab1 t2 ';
loop
fetch cur1 into rec;
if not found then
exit;
end if;
raise info 'QUERY PLAN = %' , rec."QUERY PLAN";
end loop;
close cur1;
end;
$$
language plpgsql
;
(2019)
drop table tab1;
create table tab1
( col1 int not null primary key
, col2 int
)
;
set statistics profile on
execute (N' select ' + N' * from tab1 t1,tab1 t2 ')
set statistics profile off