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 iself user.
-- 
pause

CONNECT iself/schooling
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 iself user. 
-- 
pause

GRANT CREATE TABLESPACE TO iself
/

GRANT DROP TABLESPACE TO iself
/
-- The CREATE TABLESPACE privilege and DROP TABLESPACE privilege
-- was granted to the ISELF user.
--
pause 


CLEAR SCR
-- Now, go back and connect to SQLPLUS as the iself user.
-- 
pause

CONNECT iself/schooling
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 ISELF user.
--
pause

REVOKE CREATE TABLESPACE FROM iself 
/
REVOKE DROP TABLESPACE FROM iself 
/
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