19 | 03 | 2024
Latest Articles
Popular Articles

SQL Tuning

SQL Tuning : Setup sample schema ( 1 )

User Rating:  / 0
PoorBest 

SQL Tuning : Setup Sample Schema

 

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

 

1. view merging

2. subquery unnesting

3. predicate pushing

 

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


create table T_CURRENCY_CR (CR_PK varchar2(3 CHAR),CR_COMMENT varchar2(30 CHAR));

insert into T_CURRENCY_CR values ('EUR','Euro');
insert into T_CURRENCY_CR values ('USD','American Dollar');
insert into T_CURRENCY_CR values ('GBP','Britisch Pound');
insert into T_CURRENCY_CR values ('YEN','Japanese Yen');

commit;

alter table T_CURRENCY_CR add constraint CR_PK primary key (CR_PK);

create table T_ORDER_OR ( OR_PK number, SU_FK number, OR_ORDERDATE DATE, OR_DELIVERYDATE date, OR_PAYMENTMODE number, OR_TOTALAMOUNT number, OR_SHORTCOMMENT varchar2(100), OR_LONGCOMMENT clob);

alter table T_ORDER_OR add constraint OR_PK primary key (OR_PK);
alter table T_ORDER_OR add CR_FK varchar2(3 CHAR);
alter table T_ORDER_OR modify cr_fk not null;
alter table T_ORDER_OR add constraint CR_FK foreign key (CR_FK) references T_CURRENCY_CR (CR_PK);
alter table T_ORDER_OR modify SU_FK not null;
alter table T_ORDER_OR modify OR_ORDERDATE not null;
alter table T_ORDER_OR modify OR_DELIVERYDATE not null;
alter table T_ORDER_OR modify OR_TOTALAMOUNT not null;
alter table T_ORDER_OR modify OR_PAYMENTMODE not null;

create index OR_IDX1 on T_ORDER_OR (SU_FK);

create sequence OR_SEQ;

declare
cur varchar2(3 CHAR);
begin
for i in 1 .. 1000000 loop
select CR_PK into cur from (select cr_pk from t_currency_cr order by dbms_random.value) where rownum = 1;
insert into T_ORDER_OR values ( OR_SEQ.nextval, trunc(dbms_random.value(1,100)),
sysdate,sysdate+30,
trunc(dbms_random.value(1,8)),
round(dbms_random.value(50,100000),2),
dbms_random.string('U',100),empty_clob,cur);
end loop;
end;
/

commit;

create table T_SUPPLIER_SU (su_pk number,su_name varchar2(100 CHAR),su_comment varchar2(100 CHAR));


alter table T_SUPPLIER_SU add constraint su_pk primary key (su_pk);


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

commit;

alter table T_ORDER_OR add constraint SU_FK foreign key (SU_FK) references T_SUPPLIER_SU (SU_PK);

insert into T_SUPPLIER_SU values (100,'We love SQL tuning','We love SQL tuning');

insert into T_CURRENCY_CR values ('CAD','Canadian Dollar');
insert into T_CURRENCY_CR values ('BRL','Brazilian Real');

commit;



create or replace view V_ORDERSTAT_OS as select su_fk,cr_fk,avg(or_totalamount) OS_AVGAMOUNT,
min(or_totalamount) OS_MINAMOUNT,
max(or_totalamount) OS_MAXAMOUNT
from t_order_or group by su_fk,cr_fk;

 

create table T_DELIVERY_DL ( DL_PK number, OR_FK number, DL_DELIVERYESTDATE date, DL_DELIVERYREALDATE date,DL_SHORTCOMMENT varchar2(100), DL_LONGCOMMENT clob);

alter table T_DELIVERY_DL add constraint DL_PK primary key (DL_PK);

alter table T_DELIVERY_DL modify OR_FK not null;

alter table T_DELIVERY_DL add constraint OR_FK foreign key (OR_FK) references T_ORDER_OR (OR_PK);

alter table T_DELIVERY_DL modify DL_DELIVERYESTDATE not null;

alter table T_DELIVERY_DL modify DL_DELIVERYREALDATE not null;

 

 

create index DL_IDX1 on T_DELIVERY_DL (OR_FK);

create sequence DL_SEQ;

 

declare
cursor lc1 is select or_pk,or_orderdate from t_order_or;
begin
for i in lc1 loop
if mod(i.or_pk,1000) != 0 then
insert into T_DELIVERY_DL values ( DL_SEQ.nextval,
i.or_pk,
i.or_orderdate + trunc(dbms_random.value(15,35)),
i.or_orderdate + trunc(dbms_random.value(15,40)),
dbms_random.string('U',100),
empty_clob);
end if;
end;
/

 

-- orders for which there is a second delivery

declare
cursor lc1 is select or_pk,or_orderdate from t_order_or;
begin
for i in lc1 loop
if mod(i.or_pk,700) = 0 then
insert into T_DELIVERY_DL values ( DL_SEQ.nextval,
i.or_pk,
i.or_orderdate + trunc(dbms_random.value(15,35)),
i.or_orderdate + trunc(dbms_random.value(12,43)),
dbms_random.string('U',100),
empty_clob);
end if;
end loop;
end;
/

 

-- orders for which there is a thirth delivery

declare
cursor lc1 is select or_pk,or_orderdate from t_order_or;
begin
for i in lc1 loop
if mod(i.or_pk,7000) = 0 then
insert into T_DELIVERY_DL values ( DL_SEQ.nextval, 
i.or_pk,
i.or_orderdate + trunc(dbms_random.value(15,35)),
i.or_orderdate + trunc(dbms_random.value(12,43)),
dbms_random.string('U',100),
empty_clob);
end if;
end loop;
end;
/

 

STEP 3 : GATHER SCHEMA STATS 

 

begin 

dbms_stats.gather_schema_stats(user, cascade=>true, estimate_percent => 100, method_opt=>'FOR ALL COLUMNS SIZE AUTO',no_invalidate => false ); 

end;

/