implicit data conversion


(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