SQL

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.

Your assignments are:

1- To create folder for each department,

2- To create tablespace with following specification for each department:

a. Automatic Allocation for an unlimited size,

b. Generates redo logs and recoverable, and

c. Type must be permanent.

3- To create the partition table,

4- To copy the EMP table into the partition table,

5- To check the table statistics,

6- To create a local partition indexes, and

7- To check the index table statistics.

Connect to SQLPLUS as the oracle user.
SQL> CONNECT oracle/learning
 

Set the linesize to 100 and the pagesize to 55.
SQL> SET LINESIZE 100 PAGESIZE 55
 

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. The HOST command tells the SQLPLUS tool that the command is an operating system command. It will execute it at OS level.

SQL> HOST mkdir c:dept10
SQL> HOST mkdir c:dept20
SQL> HOST mkdir c:dept30
SQL> HOST mkdir c:deptxx
Your folders were created.
 

Tablespace is a logical place that you will store your object in it. An Oracle user can store all their tables in their default tablespace. Notice that all procedures, functions and packages will be stored in the SYSTEM tablespace and you have no control to enforce that to any other tablespace.

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.
SQL> CREATE TABLESPACE dept10ts
LOGGING
DATAFILE 'c:dept10ts_01.dbf' SIZE 10M
AUTOEXTEND ON

MAXSIZE UNLIMITED
SQL> /
Notice that you don't have the sufficient privilege to create a tablespace.
 

Now, connect to SQLPLUS as the system/manager user
SQL> CONNECT system/manager
 

Grant the CREATE TABLESPACE and DROP TABLESPACE privilege to the oracle user.
SQL> GRANT CREATE TABLESPACE TO oracle
SQL> /
SQL> GRANT DROP TABLESPACE TO oracle
SQL> /
The CREATE TABLESPACE privilege and DROP TABLESPACE privilege was granted to the ORACLE user.
 

Now, go back and connect to SQLPLUS as the oracle user.
SQL> CONNECT oracle/learning
 

Create tablespaces for the accounting department.
SQL> CREATE TABLESPACE dept10ts
LOGGING
DATAFILE 'c:dept10ts_01.dbf' SIZE 10m
AUTOEXTEND ON

MAXSIZE UNLIMITED
SQL> /
The accounting department tablespace was created.
 

Create tablespaces for the research department.
SQL> CREATE TABLESPACE dept20ts
LOGGING
DATAFILE 'c:dept20ts_01.dbf' SIZE 10M
AUTOEXTEND ON

MAXSIZE UNLIMITED
SQL> /
Here, the research department tablespace was created.
 

Create tablespaces for the sales department.
SQL> CREATE TABLESPACE dept30ts
LOGGING
DATAFILE 'c:dept30ts_01.dbf' SIZE 10M
AUTOEXTEND ON

MAXSIZE UNLIMITED
SQL> /
And here, the sales department tablespace was created.

Create tablespaces for the other departments.
SQL> CREATE TABLESPACE deptxxts
LOGGING
DATAFILE 'c:deptxx_01.dbf' SIZE 10M
AUTOEXTEND ON

MAXSIZE UNLIMITED
SQL> /
And now, the other departments tablespace was created.
 

Now, create a range-based partitioning table named p_emp. Make 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.
SQL> 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

TABLESPACE deptxxts)
SQL> /
Your partition table should be created. Notice that the VALUES LESS THAN clause indicates the partition key value must be less then its assigned value in order to be illegible for any DML transaction on its assigned tablespace.
 

Insert records into your partition table (p_emp).
SQL> INSERT INTO p_emp SELECT * FROM emp
SQL> /
SQL> COMMIT
SQL> /
 

Analyze your partition table.
SQL> ANALYZE TABLE p_emp COMPUTE STATISTICS
SQL> /
 

Query the PARTITION_NAME, TABLESPACE_NAME, and NUM_ROWS columns from the dictionary view.
SQL> SELECT partition_name, tablespace_name, num_rows
FROM user_tab_partitions
WHERE table_name = 'P_EMP'
SQL> /
Notice that in the accounting tablespace you have three records and research tablespace you have 5 records, etc.
 

Compare these two tables, EMP and P_EMP.
 

First, query the EMP table.
SQL> SELECT * FROM emp
SQL> /
 

Now, query the p_emp table.
SQL> SELECT * FROM p_emp
SQL> /
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.
 

Query the accounting employees from the partition table. Use the partition option.
SQL> SELECT * FROM p_emp PARTITION (dept10)
SQL> /
Notice that your query is only against the accounting department partition and you don't have to have a WHERE clause.
 

Query employee number 7900 from the sales department.
SQL> SELECT * FROM p_emp PARTITION (dept30)
WHERE empno = 7900
SQL> /
 

Create a local partition index.
SQL> CREATE INDEX p_emp_ind ON p_emp (deptno) LOCAL
SQL> /
 

Analyze the partition table index.
SQL> ANALYZE INDEX p_emp_ind COMPUTE STATISTICS
SQL> /
 

Query the statistics on the number of records in each partition.
SQL> SELECT index_name, partition_name, num_rows

Drop the table and all of the tablespaces.
SQL> DROP TABLE p_emp CASCADE CONSTRAINTS
SQL> /

Now, connect to SQLPLUS as the system/manager user
SQL> CONNECT system/manager

REVOKE CREATE TABLESPACE privileges

Revoke the CREATE TABLESPACE and DROP TABLESPACE privileges from the ORACLE user.
SQL> REVOKE CREATE TABLESPACE FROM oracle
SQL> /

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.
SQL> HOST rmdir c:dept10 /S /Q
SQL> HOST rmdir c:dept20 /S /Q
SQL> HOST rmdir c:dept30 /S /Q
SQL> HOST rmdir c:deptxx /S /Q

Questions:

Q: What is a data partitioning in the Oracle database?

Q: When should you use data partitioning?

Q: What is the advantage of using a data partitioning?

Q: What is a partition key?

Q: What is a local index in the data partitioning?

Q: What is a global index in the data partitioning?

Q: What are the differences between local and global indexes?

Q: How does the �VALUE LESS THAN� method work in the data partitioning?

Q: Why do you need multiple tablespaces?

Q: Create a range-based partitioning table named p_emp. Be sure that the data entry of the each department goes to its own provided tablespaces such as the accounting department goes to the dept10ts tablespace, the data entry of the research department goes to the dept20ts tablespace, etc.

Q: What does the MAXVALUE parameter mean in the data partitioning?

Q: How do you analyze a partition table?

Q: What does the USER_TAB_PARTITIONS view contain?

Q: Write a query to list the accounting employees from the partition table. Use the partition option.

Q: Write a query to list employee number 7900 from the sales department?

Q: How do you create a local partition index?

Q: How do you analyze a partition table index?

Q: What does the USER_IND_PARTITIONS view contain?