</p> SQL> CONNECT system
$ U4 B0 D. H) h& Y3 i% t" \ Enter password:+ P+ b3 N( L r" v
Connected." W/ v% X3 V8 k0 m+ J( p, U
SQL> -- Configure Oracle-Managed (Data) Files
6 A* G# S+ l8 L7 b SQL> ALTER SYSTEM
, `8 O2 }; H) }3 @+ r 2 SET db_create_file_dest = ’/data01/oracle/’
3 x. I L8 L/ w 3 SCOPE = MEMORY;3 R2 G; v2 `+ I- v! N
System altered. q2 @! s; w0 n; W4 }
SQL> -- Create two new tablespaces for the demo,
& C8 P0 Z6 |) v' M2 a: ? SQL> -- one for data segments, one for index segments
. k# o4 |" r; b; e+ Z SQL> CREATE TABLESPACE data_001- R" w) _% Q! K
2 DATAFILE SIZE 1G;+ H: [2 J: ]9 S2 N
Tablespace created.1 K" E1 B" y" M4 G- \ {
SQL> CREATE TABLESPACE indx_001* I$ V! @, P8 K! N4 ~
2 DATAFILE SIZE 500M; p9 N- H/ E2 e( N/ s, s& \/ {
Tablespace created.+ b- ^+ z9 X. j6 Y# Y5 q
SQL> -- Create a user for the demo% d i" a5 a/ u& A
SQL> CREATE USER app_001 IDENTIFIED BY app
. i3 w1 q+ v4 D( i; y& J 2 DEFAULT TABLESPACE data_001
2 A+ M j% R) _( w9 @$ \ 3 TEMPORARY TABLESPACE temp" x; D6 `) {' s+ W' F4 c
4 QUOTA UNLIMITED ON data_001
9 V+ g( _% j$ G+ C0 L 5 QUOTA UNLIMITED ON indx_001;$ ]# I5 c7 q6 [7 ^* o( D; k
User created.0 Y) o2 _( Q1 C
SQL> GRANT CREATE SESSION, CREATE TABLE TO app_001;- y* C+ i% d0 x$ j: ?8 T
Grant succeeded.
( {5 |. x9 \. N& J% T& C9 I- ^ SQL> -- Work as the demo user
' h9 a) ^4 S" X' m/ M SQL> CONNECT app_001/app;
8 Y& g# M9 I8 F& m: r Connected.
7 g/ ^6 G" _5 x4 ?! _5 U6 s3 g SQL> -- Create the demo table in the default tablespace, T- [; `! R# b+ D8 x' [
SQL> CREATE TABLE app_001.transactions (" m! T4 F& C# }6 B! ]
2 trans_id INTEGER
* H! }) _1 o0 P4 K1 Q. S/ L 3 CONSTRAINT transactions_pk PRIMARY KEY* v& ], w/ A0 l% p5 M7 y
4 USING INDEX TABLESPACE indx_001,* P/ R Z" \, m; q1 R# q
5 credit_card INTEGER NOT NULL
* |% R1 |! J' m2 b( \ 6 );
! S* h7 F- G: e$ L5 R4 C- o9 F( w Table created.
" q4 F7 c8 e, o: K/ B SQL> -- Create an index in the INDX_001 tablespace( a' ]! m2 t; _8 Q8 ^1 q
SQL> CREATE INDEX app_001.transactions_ndx1
2 b5 l9 {. ^0 A- W* D' X- Y 2 ON app_001.transactions(credit_card)
* d( U8 D) f* g 3 TABLESPACE indx_001;* Y# P' Z! ^' Y1 P7 V- r
Index created.0 `: n; ?5 w$ @( P
SQL> -- Time how long it takes to load data in the clear- g ~( k6 x# S7 R1 [
SQL> SET TIMING ON;% H( @9 s) X1 N2 m* n
SQL> BEGIN: E3 l7 W' h3 I- ^5 C
2 -- AMEX* Y. `" o# V7 J! e v8 G( t" b
3 FOR i IN 1 .. 100000 LOOP% s/ i3 b' f* Q ]
4 INSERT INTO app_001.transactions(trans_id, credit_card). N! K) i/ w8 ]3 k
5 VALUES (4 B" s+ }% |8 b5 L, o4 o# X5 w- D/ K
6 i,1 I1 Y% }/ @2 n+ k" d
7 ’34’ || TRUNC(DBMS_RANDOM.VALUE(low=>0, high=>99999999999999))
7 V4 T& b% _0 a! \ 8 );
! Z/ k" X' c- V9 K! z S 9 END LOOP;
/ B! s: B+ [: g9 A1 D 10 COMMIT;
8 `$ ~8 K& x5 @$ Z0 ` O% ^5 @ 11 -- VISA
! j% a( y! w3 S A+ V& h5 R# N4 O2 z1 a 12 FOR i IN 100001 .. 400000 LOOP
6 I5 F8 a6 l$ u" @7 Q/ n 13 INSERT INTO app_001.transactions(trans_id, credit_card)6 ~; h: j' X8 Y% ?* W- b N
14 VALUES (
2 s, d7 i& W8 u& d: n* L1 ~7 ^ 15 i,. k3 `& Y' H- s7 t/ w2 }; g/ H- Y
16 ’4’ || TRUNC(DBMS_RANDOM.VALUE(low=>0, high=>999999999999999))
& ?7 q/ Q5 ~+ f% T, Y8 G% M. ? 17 );
7 q6 U; Q( {/ B; a3 |3 E; V$ }# i/ U
18 END LOOP; |