Tuning of SQL Statements: Setup Example

set echo on
set lines 300
set pages 1000

prompt Tablespace DEMO
drop tablespace DEMO including contents and datafiles;
create tablespace DEMO datafile '/home/db/u02/oradata/LCTD112/ts_lctd112_DEMO_DATA_01.DBF' size 128M autoextend on next 10M maxsize 4G;

prompt User DEMO
drop user demo cascade;
create user demo identified by demo default tablespace DEMO ;
alter  user demo quota unlimited on  DEMO;
grant create session, create table,select_catalog_role to DEMO;

prompt Tables CUSTOMERS / ORDERS
drop table demo.orders purge;
drop table DEMO.CUSTOMERS purge;

create table DEMO.customers (
CUSTOMER_ID        NUMBER(6)          NOT NULL,
CUST_FIRST_NAME    VARCHAR2(20)       NOT NULL,
CUST_LAST_NAME     VARCHAR2(20)       NOT NULL,
CUST_ADDRESS       VARCHAR2(20),
PHONE_NUMBERS              VARCHAR2(20),
CREDIT_LIMIT               NUMBER(9,2)    ,   
CUST_EMAIL                 VARCHAR2(30)    ,  
DATE_OF_BIRTH              DATE              ,
MARITAL_STATUS             VARCHAR2(20)       ,
GENDER                     VARCHAR2(1)        ,
INCOME_LEVEL               VARCHAR2(20)  
)
PCTFREE 70;



create table DEMO.orders (
ORDER_ID     NUMBER(12) NOT NULL,                      
ORDER_DATE   date,
ORDER_MODE            VARCHAR2(8)                       ,
CUSTOMER_ID   NUMBER(6)                         NOT NULL,
ORDER_STATUS         VARCHAR2(10) ,                       
ORDER_TOTAL           NUMBER(8,2),                      
SALES_REP_ID          NUMBER(6)   ,                      
PROMOTION_ID          NUMBER(6)   )
PCTFREE 70;

prompt table data
begin dbms_random.seed('ora-solutions');
end;
/

insert into DEMO.CUSTOMERS (CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_ADDRESS,PHONE_NUMBERS,CREDIT_LIMIT,CUST_EMAIL,
DATE_OF_BIRTH, MARITAL_STATUS, GENDER, INCOME_LEVEL)
select
         rownum as CUSTOMER_ID,
       dbms_random.string('A', 20) as CUST_FIRST_NAME,
       dbms_random.string('A', 20) as CUST_LAST_NAME,
       dbms_random.string('A', 20) as CUST_ADDRESS,
       dbms_random.string('A', 20) as PHONE_NUMBERS,
       decode(mod(rownum,3), 0, 1000
                           , 1, 5000
                 , 2, 10000) as CREDIT_LIMIT,
       dbms_random.string('A', 30) as CUST_EMAIL,
       sysdate - dbms_random.value(20*365,80*365) as DATE_OF_BIRTH,
       decode(mod(rownum,2) , 0, 'MARRIED'
                                  , 1, 'SINGLE') as MARITAL_STATUS,
       decode(mod(rownum,10), 0, 'M'
                  , 2, 'M'
                  , 3, 'M'
                  , 4, 'M'
                  , 5, 'M'
                  , 6, 'M'
                  , 7, 'M'
                  , 8, 'M'
                  , 9, 'F') as GENDER,
       decode(mod(rownum,3), 0, '<2000'
                           , 1, '>=2000 < 5000'
                 , 2, '>=5000' ) as INCOME_LEVEL
           from
                   (select rownum r from dual connect by rownum <= 100) a,
                   (select rownum r from dual connect by rownum <= 100) b,
                   (select rownum r from dual connect by rownum <= 100) c
           where rownum <= 100000;
commit;     

/*      
===================================================================================================================================
COLUMN STATISTICS
===================================================================================================================================
Name             Analyzed             Null?  NDV        Density  # Nulls   # Buckets   Sample   AvgLen  Lo-Hi Values
===================================================================================================================================
credit_limit      11-SEP-12  Y      3          .333333  0         1           100000   3          1000 | 10000
customer_id       11-SEP-12  N      100000     .000010  0         1           100000   5          1 | 100000
cust_address      11-SEP-12  Y      100000     .000010  0         1           100000   21         AAAMNpdeKlDZwJYzIVVA | zzybgVBKYnCXbmEkuXNa
cust_email        11-SEP-12  Y      98192      .000010  0         1           100000   31         AABvQMiSnfTiiyiqSzsEjeSMkpcIKm | zzyumjWBmVaxbDlMDjgMhdgzQjOput
cust_first_name   11-SEP-12  N      100000     .000010  0         1           100000   21         AABHxvKzIAFHverLlHVO | zzxagpLxOARgGfkTHHZa
cust_last_name    11-SEP-12  N      99976      .000010  0         1           100000   21         AABcpOFVKiIYzqWwgtVW | zzyBsyYMnmVcDAQzkoIy
date_of_birth     11-SEP-12  Y      98584      .000010  0         1           100000   8          10/01/1932 11:50:08 | 09/16/1992 05:02:23
gender            11-SEP-12  Y      2          .500000  10000     1           90000    2          F | M
income_level      11-SEP-12  Y      3          .333333  0         1           100000   10         <2000 | >=5000
marital_status    11-SEP-12  Y      2          .500000  0         1           100000   8          MARRIED | SINGLE
phone_numbers     11-SEP-12  Y      100000     .000010  0         1           100000   21         AAAvlbEtVwWQreMqhOtf | zzzjtEKMsRsEBgdgBwRX

*/
      
insert into DEMO.orders (ORDER_ID, ORDER_DATE, ORDER_MODE, CUSTOMER_ID,ORDER_TOTAL,SALES_REP_ID,PROMOTION_ID, ORDER_STATUS)
select
           rownum as ORDER_ID,
       sysdate-dbms_random.value(1,10*365) as ORDER_DATE,
       decode(mod(rownum,2), 0, 'direct'
                           , 1, 'online') as ORDER_MODE,
       decode(mod(rownum,10),0, 1001
                            ,1, 1001
                ,2, 1001
                ,3, 1001
                ,4, 1001
                ,5, 1001
                ,6, 1001
                ,7, 9999
                ,8, 9999
                ,9, trunc(dbms_random.value(1,100001))) as CUSTOMER_ID,
       round(dbms_random.value(100,10000),2) as ORDER_TOTAL,
       trunc(dbms_random.value(1,101)) as SALES_REP_ID,
       trunc(dbms_random.value(1,101))as PROMOTION_ID,
       decode(mod(rownum,10),0, 'COMPLETED'
                            ,1, 'COMPLETED'
                ,2, 'COMPLETED'
                ,3, 'COMPLETED'
                ,4, 'COMPLETED'
                ,5, 'COMPLETED'
                ,6, 'COMPLETED'
                ,7, 'COMPLETED'
                ,8, 'COMPLETED'
                ,9, 'PENDING') as ORDER_STATUS
           from
                   (select rownum r from dual connect by rownum <= 100) a,
                   (select rownum r from dual connect by rownum <= 100) b,
                   (select rownum r from dual connect by rownum <= 100) c
           where rownum <= 1000000;
commit;     

-- prompt DBMS_STATS
begin dbms_stats.gather_table_stats('DEMO','CUSTOMERS');
end;
/

begin dbms_stats.gather_table_stats('DEMO','ORDERS');
end;
/

/*
===================================================================================================================================
COLUMN STATISTICS CUSTOMERS
===================================================================================================================================
Name             Analyzed             Null?  NDV        Density  # Nulls   # Buckets   Sample   AvgLen  Lo-Hi Values
===================================================================================================================================
credit_limit      11-SEP-12  Y      3          .333333  0         1           100000   3          1000 | 10000
customer_id       11-SEP-12  N      100000     .000010  0         1           100000   5          1 | 100000
cust_address      11-SEP-12  Y      100000     .000010  0         1           100000   21         AAAMNpdeKlDZwJYzIVVA | zzybgVBKYnCXbmEkuXNa
cust_email        11-SEP-12  Y      98192      .000010  0         1           100000   31         AABvQMiSnfTiiyiqSzsEjeSMkpcIKm | zzyumjWBmVaxbDlMDjgMhdgzQjOput
cust_first_name   11-SEP-12  N      100000     .000010  0         1           100000   21         AABHxvKzIAFHverLlHVO | zzxagpLxOARgGfkTHHZa
cust_last_name    11-SEP-12  N      99976      .000010  0         1           100000   21         AABcpOFVKiIYzqWwgtVW | zzyBsyYMnmVcDAQzkoIy
date_of_birth     11-SEP-12  Y      98584      .000010  0         1           100000   8          10/01/1932 11:50:08 | 09/16/1992 05:02:23
gender            11-SEP-12  Y      2          .500000  10000     1           90000    2          F | M
income_level      11-SEP-12  Y      3          .333333  0         1           100000   10         <2000 | >=5000
marital_status    11-SEP-12  Y      2          .500000  0         1           100000   8          MARRIED | SINGLE
phone_numbers     11-SEP-12  Y      100000     .000010  0         1           100000   21         AAAvlbEtVwWQreMqhOtf | zzzjtEKMsRsEBgdgBwRX

*/


/*
===================================================================================================================================
COLUMN STATISTICS ORDERS
===================================================================================================================================
Name          Analyzed             Null?  NDV         Density  # Nulls   # Buckets   Sample    AvgLen  Lo-Hi Values
===================================================================================================================================
customer_id    11-SEP-12  N      63600       .000016  0         1           1000000   5          1 | 100000
order_date     11-SEP-12  Y      1000000     .000001  0         1           1000000   8          09/14/2002 09:01:10 | 09/10/2012 08:39:41
order_id       11-SEP-12  N      1000000     .000001  0         1           1000000   5          1 | 1000000
order_mode     11-SEP-12  Y      2           .500000  0         1           1000000   7          direct | online
order_status   11-SEP-12  Y      2           .500000  0         1           1000000   10         COMPLETED | PENDING
order_total    11-SEP-12  Y      622208      .000002  0         1           1000000   5          100.01 | 10000
promotion_id   11-SEP-12  Y      100         .010000  0         1           1000000   3          1 | 100
sales_rep_id   11-SEP-12  Y      100         .010000  0         1           1000000   3          1 | 100

*/
            
prompt Indexes / Constraints
alter table demo.customers add constraint customers_pk primary key (customer_id);
alter table demo.orders add constraint orders_pk primary key (order_id);
create index demo.customer_idx on demo.orders(customer_id);
alter table demo.orders add constraint ord_cust_fk foreign key (customer_id) references demo.customers(customer_id);


No comments:

Post a Comment