iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

Basics - SQL 

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 |

 

Lesson 14

"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?