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