SQL Tuning : Setup Sample Schema

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 loop;
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;
/

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

参考至:http://www.online-database.eu/index.php/sql-tuning/175-sql-tuning-setup-sample-schema

如有错误，欢迎指正

邮箱:czmcj@163.com