|
Your organization�s
database is growing very fast. They want to have a
database that contains each department in a separate
tablespace. They projected that their employee�s
table is growing to more than 100 million rows in
each department. Also, upon their requirements, they
want to have each department�s employees in a
separate hard disk, and to be independent of other
department employee�s data. Also, they want to be
sure all the employees are in the same table. They
want it to be partitioned in such away, that when
they run a query against an employee�s information,
they would scan at most on the department�s hard
disk that he/she works, rather than the other
department�s hard disks on non-partitioned table.
You will need to
choose a partition key. You have been told that the
department�s employees are distributed equally among
the partition table. There are two types of
partitioning approaches that you, as a database
designer, must choose (they are Local and Global). A
Local index, is one that is partitioned exactly like
the table to which it belongs. A Global index,
unlike local indexes, you should explicitly
partition range boundaries using the �VALUE LESS
THAN� methods. They want you to create a �Local
Index� for the partition table.
-- Hands-On 05 (Data
Partitioning)
set echo on
CLEAR SCR
-- Connect to SQLPLUS as the oracle user.
--
pause
CONNECT oracle/learning
pause
CLEAR SCR
-- Set the linesize to 100 and the pagesize to 55.
--
pause
SET LINESIZE 100 PAGESIZE 55
pause
CLEAR SCR
-- Create folders for
-- the accounting department, Reseach department,
-- Sales department, and other departments.
-- Make sure that you have at least 50 Megabytes of
-- hard drive space available on your hard disk.
--
pause
HOST mkdir c:\disk4dept10
HOST mkdir c:\disk4dept20
HOST mkdir c:\disk4dept30
HOST mkdir c:\disk4deptxx
-- Your folders were created.
--
pause
CLEAR SCR
-- Create a tablespace for the accounting department
-- with the following options:
--
-- 1- Automatic Allocation,
-- 2- Generates the redo logs,
-- 3- Recoverable, and
-- 4- Tablespace type must be permanent.
--
pause
CREATE TABLESPACE dept10ts
LOGGING
DATAFILE 'c:\disk4dept10\dept10ts_01.dbf' SIZE 10M
AUTOEXTEND ON NEXT 5K MAXSIZE UNLIMITED
/
-- Notice that you don't have the sufficient
privilege
-- to create a tablespace.
--
pause
CLEAR SCR
-- Now, connect to SQLPLUS as the system/manager
user
--
pause
CONNECT system/manager
pause
CLEAR SCR
-- Grant the CREATE TABLESPACE and DROP TABLESPACE
-- privilege to the oracle user.
--
pause
GRANT CREATE TABLESPACE TO oracle
/
GRANT DROP TABLESPACE TO oracle
/
-- The CREATE TABLESPACE privilege and DROP
TABLESPACE privilege
-- was granted to the ORACLE user.
--
pause
CLEAR SCR
-- Now, go back and connect to SQLPLUS as the oracle
user.
--
pause
CONNECT oracle/learning
pause
CLEAR SCR
-- Create tablespaces for the accounting department.
--
pause
CREATE TABLESPACE dept10ts
LOGGING
DATAFILE 'c:\disk4dept10\dept10ts_01.dbf' SIZE 10m
AUTOEXTEND ON NEXT 5k MAXSIZE UNLIMITED
/
-- The accounting department tablespace was created.
--
pause
CLEAR SCR
-- Create tablespaces for the research department.
--
pause
CREATE TABLESPACE dept20ts
LOGGING
DATAFILE 'c:\disk4dept20\dept20ts_01.dbf' SIZE 10M
AUTOEXTEND ON NEXT 5K MAXSIZE UNLIMITED
/
-- Here, the research department tablespace was
created.
--
pause
CLEAR SCR
-- Create tablespaces for the sales department.
--
pause
CREATE TABLESPACE dept30ts
LOGGING
DATAFILE 'c:\disk4dept30\dept30ts_01.dbf' SIZE 10M
AUTOEXTEND ON NEXT 5K MAXSIZE UNLIMITED
/
-- And here, the sales department tablespace was
created.
--
pause
CLEAR SCR
-- Create tablespaces for the other departments.
--
pause
CREATE TABLESPACE deptxxts
LOGGING
DATAFILE 'c:\disk4deptxx\deptxxts_01.dbf' SIZE 10M
AUTOEXTEND ON NEXT 5K MAXSIZE UNLIMITED
/
-- And now, the other departments tablespace was
created.
--
pause
CLEAR SCR
-- Now, create a range-based partitioning table
named p_emp.
-- Be sure that the data entry of the accounting
department
-- goes to the dept10ts tablespace, the data entry
of
-- the research department goes to the dept20ts
tablespace,
-- etc.
--
pause
CREATE TABLE p_emp (
empno NUMBER(4) PRIMARY KEY,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sale NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2))
STORAGE (INITIAL 5K NEXT 5K PCTINCREASE 0)
PARTITION BY RANGE (deptno)
(PARTITION dept10 VALUES LESS THAN (20) TABLESPACE
dept10ts,
PARTITION dept20 VALUES LESS THAN (30) TABLESPACE
dept20ts,
PARTITION dept30 VALUES LESS THAN (40) TABLESPACE
dept30ts,
PARTITION deptxx VALUES LESS THAN (MAXVALUE)
TABLESPACE deptxxts)
/
-- Your partition table has now been created.
pause
CLEAR SCR
-- Insert records into your partition table (p_emp).
--
pause
INSERT INTO p_emp SELECT * FROM emp
/
COMMIT
/
pause
CLEAR SCR
-- Analyze your partition table.
--
pause
ANALYZE TABLE p_emp COMPUTE STATISTICS
/
pause
CLEAR SCR
-- Query the PARTITION_NAME, TABLESPACE_NAME, and
NUM_ROWS columns
-- from the dictionary view.
--
pause
SELECT partition_name, tablespace_name, num_rows
FROM user_tab_partitions
WHERE table_name = 'P_EMP'
/
-- Notice that in the accounting tablespace you have
three
-- records and research tablespace you have 5
records, etc.
pause
CLEAR SCR
-- Compare these two tables, EMP and P_EMP.
-- First, query the EMP table.
pause
SELECT * FROM emp
/
pause
CLEAR SCR
-- Now, query the p_emp table.
pause
SELECT * FROM p_emp
/
-- Can you see the difference?
-- Notice that the query scanned the partition table
by
-- the sequence of the tablespaces.
-- That is why the list is sorted by department
number.
pause
CLEAR SCR
-- Query the accounting employees from the partition
table.
-- Use the partition option.
--
pause
SELECT * FROM p_emp PARTITION (dept10)
/
-- Notice that your query is only against
-- the accounting department partition and
-- you don't have to have a WHERE clause.
--
pause
CLEAR SCR
-- Query employee number 7900 from the sales
department.
--
pause
SELECT * FROM p_emp PARTITION (dept30)
WHERE empno = 7900
/
pause
CLEAR SCR
-- Create a local partition index.
--
pause
CREATE INDEX p_emp_ind ON p_emp (deptno) LOCAL
/
pause
CLEAR SCR
-- Analyze the partition table index.
--
pause
ANALYZE INDEX p_emp_ind COMPUTE STATISTICS
/
pause
CLEAR SCR
-- Query the statistics on the number of records in
each partition.
--
pause
SELECT index_name, partition_name, num_rows FROM
user_ind_partitions
/
pause
CLEAR SCR
-- Drop the table and all of the tablespaces.
pause
DROP TABLE p_emp CASCADE CONSTRAINTS
/
DROP TABLESPACE dept10ts INCLUDING CONTENTS
/
DROP TABLESPACE dept20ts INCLUDING CONTENTS
/
DROP TABLESPACE dept30ts INCLUDING CONTENTS
/
DROP TABLESPACE deptxxts INCLUDING CONTENTS
/
pause
CLEAR SCR
-- Now, connect to SQLPLUS as the system/manager
user
--
pause
CONNECT system/manager
Pause
CLEAR SCR
-- Revoke the CREATE TABLESPACE and DROP TABLESPACE
privileges
-- from the ORACLE user.
--
pause
REVOKE CREATE TABLESPACE FROM oracle
/
REVOKE DROP TABLESPACE FROM oracle
/
pause
CLEAR SCR
-- Remove all of the created folders.
-- Use the /S option if folder is not empty.
-- And use the /Q option for the QUIET MODE option.
--
pause
HOST rmdir c:\disk4dept10 /S /Q
HOST rmdir c:\disk4dept20 /S /Q
HOST rmdir c:\disk4dept30 /S /Q
HOST rmdir c:\disk4deptxx /S /Q
pause
CLEAR SCR
-- Now, you should practice this over and over
-- until you become a master at it.
-- For more information about the subject, you are
encouraged
-- to read from a wide selection of available books.
-- Good luck!
pause |
