29 | 03 | 2024
Latest Articles
Popular Articles

SQL Tuning

SQL Tuning : Setup sample schema ( 2 )

User Rating:  / 1
PoorBest 

SQL Tuning : Setup sample schema ( 2 )

 

 

The goal is to setup a sample schema to mine and understand some query transformations and optimizations, like

 

Simple Filter Pushdown ( FPD )

Join Elimation ( JE )

 

STEP 1 : SETUP USER

 

CREATE USER "TUNING" PROFILE "DEFAULT" IDENTIFIED BY "TUNING" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;
GRANT CREATE SESSION TO "TUNING";
GRANT CREATE VIEW TO "TUNING";
GRANT SELECT ANY DICTIONARY TO "TUNING";
GRANT SELECT ANY TABLE TO "TUNING";
GRANT UNLIMITED TABLESPACE TO "TUNING";
GRANT "PLUSTRACE" TO "TUNING";
GRANT "RESOURCE" TO "TUNING";

 

STEP 2 : CREATE SAMPLE SCHEMA

 

connect TUNING/TUNING;

 

drop table TI purge;
drop table TC purge;
drop table TS purge;
drop sequence TC_SEQ;
drop sequence TI_SEQ;

 

create table TS (TS_PK number, TS_NAME varchar2(20));

alter table TS add constraint TS_PK primary key (TS_PK) using index;

begin
for i in 1 .. 100 loop
insert into TS values (i, dbms_random.string('U',20));
end loop;
end;
/

commit;

create table TC ( TC_PK number, TS_FK number, TC_DATE1 date, TC_DATE2 date, TC_DATE3 date , TC_VAL1 number, TC_VAL2 number );

alter table TC add constraint TC_PK primary key (TC_PK) using index;

alter table TC add constraint TC_TS_FK foreign key (TS_FK) references TS (TS_PK);

create sequence TC_SEQ;

declare

ts number;
v_tc_date1 date;
v_tc_date2 date;
v_tc_date3 date;
v_tc_val1 number;

begin
for i in 1 .. 50000 loop
select ts_pk into ts from (select ts_pk from TS order by dbms_random.value) where rownum = 1;
SELECT TO_DATE ( TRUNC (sysdate ,'J') - dbms_random.value(100,300)) into v_tc_date1 FROM DUAL;
SELECT TO_DATE ( TRUNC (v_tc_date1 ,'J') + dbms_random.value(10,60)) into v_tc_date2 FROM DUAL;
SELECT TO_DATE ( TRUNC (v_tc_date2 ,'J') + dbms_random.value(-2,60)) into v_tc_date3 FROM DUAL;
insert into TC values ( TC_SEQ.nextval, ts , v_tc_date1, decode( mod(TC_SEQ.currval,10),0,null,v_tc_date2 ),
decode( mod(TC_SEQ.currval,10),0,null,v_tc_date3 ), round(dbms_random.value(50,100000),2), null );
end loop;
end;
/

commit;

create index TC_IDX1 on TC (TS_FK);

create table TI ( TI_PK number, TS_FK number, TC_FK number, TI_DATE1 date, TI_VAL1 number );

alter table TI add constraint TI_PK primary key (TI_PK) using index;

alter table TI add constraint TI_TC_FK foreign key (TC_FK) references TC (TC_PK);

alter table TI add constraint TI_TS_FK foreign key (TS_FK) references TS (TS_PK);

create sequence TI_SEQ;

declare
cursor lc_1 is select TC_PK, TS_FK, TC_DATE3, TC_VAL1 from TC where TC_DATE3 is not null;
begin
for i in lc_1 loop
insert into TI values (TI_SEQ.nextval, i.TS_FK, i.TC_PK, i.TC_DATE3, decode(mod(TI_SEQ.currval,25),0,i.TC_VAL1 * 1.06, i.TC_VAL1 * 1.21));
end loop;
end;
/

commit;

create index TI_IDX1 on TI (TC_FK);

create index TI_IDX2 on TI (TS_FK);

 

/* VIEW FOR FILTER PUSHDOWN DEMO */

create or replace view VALL_FPD as
select TS_PK, TS_NAME, PK, TS_FK, DATE1, VAL1, VAL1_SUM
from
TS,
( select TC_PK PK, TS_FK, TC_DATE1 DATE1, TC_VAL1 VAL1, sum(TC_VAL1) over ( partition by TS_FK ) VAL1_SUM from TC where TC_DATE3 is null
UNION ALL
select TI_PK PK, TS_FK, TI_DATE1 DATE1, TI_VAL1 VAL1, sum(TI_VAL1) over ( partition by TS_FK ) VAL1_SUM from TI ) V
where TS_PK = V.TS_FK
;

/* VIEW FOR JOIN ELIMINATION DEMO */

create or replace view VALL_JE
as
select TS_PK, TS_NAME, PK, TS_FK, DATE1, VAL1, VAL1_SUM
from
TS,
( select TC_PK PK, TS_FK, TC_DATE1 DATE1, TC_VAL1 VAL1, sum(TC_VAL1) over ( partition by TS_FK ) VAL1_SUM from TC,TS where TS_PK = TS_FK and TC_DATE3 is null
UNION ALL
select TI_PK PK, TS_FK, TI_DATE1 DATE1, TI_VAL1 VAL1, sum(TI_VAL1) over ( partition by TS_FK ) VAL1_SUM from TI,TS where TS_PK = TS_FK ) V
where TS_PK = V.TS_FK
;

 

STEP 3 : GATHER SCHEMA STATS

begin
dbms_stats.gather_table_stats(user,'TS',cascade => true, method_opt => 'FOR ALL COLUMNS SIZE AUTO', estimate_percent => 100, no_invalidate => false);
dbms_stats.gather_table_stats(user,'TC',cascade => true, method_opt => 'FOR ALL COLUMNS SIZE AUTO', estimate_percent => 100, no_invalidate => false);
dbms_stats.gather_table_stats(user,'TI',cascade => true, method_opt => 'FOR ALL COLUMNS SIZE AUTO', estimate_percent => 100, no_invalidate => false);
end;
/