|
"Talent does
what it can; genius does what it must." - Edward George
Bulwer-Lytton (1803-1873) |
Read
first then play the video:
SQL-VIDEO -Data
Partitioning
Data Partitioning
Introduction
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
iself user.
SQL> CONNECT iself/schooling
Set the linesize to 100 and
the pagesize to 55.
SQL> SET LINESIZE 100 PAGESIZE 55
Creating OS directory from
DOS
HOST command
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.
CREATE TABLESPACE
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
NEXT 5K
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 CREATE TABLESPACE and
DROP TABLESPACE "
Grant the CREATE TABLESPACE
and DROP TABLESPACE privilege to the iself user.
SQL> GRANT CREATE TABLESPACE TO iself
SQL> /
SQL> GRANT DROP TABLESPACE TO iself
SQL> /
The CREATE TABLESPACE privilege and DROP TABLESPACE
privilege was granted to the ISELF user.
Now, go back and connect to
SQLPLUS as the iself user.
SQL> CONNECT iself/schooling
Create tablespaces for the
accounting department.
SQL> CREATE TABLESPACE dept10ts
LOGGING
DATAFILE 'c:dept10ts_01.dbf' SIZE 10m
AUTOEXTEND ON
NEXT 5k
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
NEXT 5K
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
NEXT 5K
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
NEXT 5K
MAXSIZE UNLIMITED
SQL> /
And now, the other departments tablespace was created.
CREATE partition table
PARTITION and MAXVALUE
options
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
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)
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> /
ANALZE TABLE COMPUTE
STATISTICS
Analyze your partition
table.
SQL> ANALYZE TABLE p_emp COMPUTE
STATISTICS
SQL> /
USER_TAB_PARTITIONS
Partition_name,
tablespace_name and num_rows columns
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.
SELECT " PARTITION ()
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 INDEX " LOCAL
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
FROM user_ind_partitions
SQL> /
DROP TABLE " CASCADE
CONSTRAINTS
Drop the table and all of
the tablespaces.
SQL> DROP TABLE p_emp CASCADE CONSTRAINTS
SQL> /
DROP TABLESPACE "
INCLUDING CONTENTS
SQL> DROP TABLESPACE dept10ts INCLUDING
CONTENTS
SQL> /
SQL> DROP TABLESPACE dept20ts INCLUDING CONTENTS
SQL> /
SQL> DROP TABLESPACE dept30ts INCLUDING CONTENTS
SQL> /
SQL> DROP TABLESPACE deptxxts INCLUDING CONTENTS
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 ISELF user.
SQL> REVOKE CREATE TABLESPACE FROM iself
SQL> /
REVOKE DROP TABLESPACE
privileges
SQL> REVOKE DROP
TABLESPACE FROM iself
SQL> /
RMDIR using /S /Q options
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
|
"I'm all in
favor of keeping dangerous weapons out of the hands of fools.
Let's start with typewriters." - Frank Lloyd Wright
(1868-1959) |
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?
|