(8.0.26)
drop table tab1;
create table tab1(col1 int primary key,col2 int,col3 varchar(30) );
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,i,cast(i as char) );
end while;
commit;
end
//
delimiter ;
call proc1(1000000);
analyze table tab1;
create index ind12 on tab1(col2);
create index ind13 on tab1(col3);
-- 数値型に数値
explain
select * from tab1 where col2 = 123;
-- 数値型に文字
explain
select * from tab1 where col2 = '123';
-- 文字型に数値
explain
select * from tab1 where col3 = 123;
→テーブルフルスキャン★
-- 文字型に文字
explain
select * from tab1 where col3 = '123';
(19c)
drop table tab1 purge;
create table tab1(col1 int primary key,col2 int,col3 varchar2(30) );
declare
begin
for i in 1..1000000 loop
insert into tab1 values(i,i,to_char(i) );
commit;
end loop;
end;
/
exec dbms_stats.gather_table_stats( user, 'TAB1');
create index ind12 on tab1(col2);
create index ind13 on tab1(col3);
-- 数値型に数値
explain plan for
select * from tab1 where col2 = 123;
select * from table(dbms_xplan.display() );
-- 数値型に文字
explain plan for
select * from tab1 where col2 = '123';
select * from table(dbms_xplan.display() );
-- 文字型に数値
explain plan for
select * from tab1 where col3 = 123;
select * from table(dbms_xplan.display() );
→TABLE ACCESS FULL★
-- 文字型に文字
explain plan for
select * from tab1 where col3 = '123';
select * from table(dbms_xplan.display() );
(14)
drop table tab1;
create table tab1(col1 int primary key,col2 int,col3 varchar(30) );
insert into tab1 select g,g,g::varchar from generate_series(1,1000000) g;
analyze tab1;
create index ind12 on tab1(col2);
create index ind13 on tab1(col3);
-- 数値型に数値
explain
select * from tab1 where col2 = 123;
-- 数値型に文字
explain
select * from tab1 where col2 = '123';
-- 文字型に数値
explain
select * from tab1 where col3 = 123;
→エラーとなる★
ERROR: operator does not exist: character varying = integer
-- 文字型に文字
explain
select * from tab1 where col3 = '123';
(2019)
drop table tab1;
create table tab1(col1 int primary key,col2 int,col3 varchar(30) );
begin
set nocount on;
declare @i int;
set @i = 1;
begin transaction;
while (@i <= 1000000)
begin
insert into tab1 values(@i,@i,cast(@i as varchar) );
set @i = @i + 1;
end
end
commit;
go
update statistics tab1;
create index ind12 on tab1(col2);
create index ind13 on tab1(col3);
-- 数値型に数値
set showplan_all on
go
select * from tab1 where col2 = 123;
go
set showplan_all off
go
-- 数値型に文字
set showplan_all on
go
select * from tab1 where col2 = '123';
go
set showplan_all off
go
-- 文字型に数値
set showplan_all on
go
select * from tab1 where col3 = 123;
go
set showplan_all off
go
→クラスタインデックスフルスキャン★
-- 文字型に文字
set showplan_all on
go
select * from tab1 where col3 = '123';
go
set showplan_all off
go
