Create table and insert into table script

create table t1

(id            number not null

,version       number not null

,create_ts     timestamp not null

,modify_ts     timestamp

,status        varchar2(24) generated always as (NVL2("MODIFY_TS",'SUPERSEDED','LATEST'))

,id2           number not null

,yn            varchar2(1) not null

,business_date date not null);

 




insert into t1

(id, version, create_ts, id2, yn, business_date)

select rownum

,      1

,      systimestamp

,      rownum

,      case when mod(rownum,2) = 1 then 'Y' else 'N' end

,      trunc(sysdate,'MON') + mod(rownum,10)

from   dual

connect by level <= 1000;

 

exec dbms_stats.gather_table_stats(USER,'T1');

  

 

explain plan for

with x as

(select * from t1

 union all

 select * from t1)

select *

from x

where yn = 'Y';

 

select * from table(dbms_xplan.display);







=================================================================================




create tablespace mssm datafile '/data01/orcl19c/data/mssm.dbf' size 500M

 segment space management manual

 extent management local uniform size 10M;

 

drop table mytest;

 

create table mytest(x number) tablespace mssm;

 

create index i$mytest$x on mytest(x) tablespace mssm;

 

insert into  mytest

select level

from dual

connect by level <= 200126;

 

commit;

 

delete mytest where x<=199000;

 

commit;

 

insert into mytest

  with x as

  (select level i from dual connect by level <= 199000)

  select i from x where mod(i, 250) = 0;

 

commit;