Partition Table

Range Partitioning

Partition table can not be created on Express Edition, please install 10g.

Fortunately I had got it from staksale, installed new 10g database as XE10g, I already had XE for express edition.

------------------------------------------------------------------------

C:\Documents and Settings\Administrator>tnsping XE10g

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 04-SEP-2
011 08:06:38

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:
D:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = mandar)(
PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE10g)))
OK (70 msec)

C:\Documents and Settings\Administrator>
------------------------------------------------------------------------

First partition table

CREATE TABLE Partition_employees

(employee_id  VARCHAR2(30),
first_name VARCHAR2(30),
last_name  VARCHAR2(30),
email    VARCHAR2(30),
phone_number VARCHAR2(30),
hire_date date,
job_id VARCHAR2(30),
salary VARCHAR2(30),
commission_pct VARCHAR2(30),
manager_id VARCHAR2(30),
department_id VARCHAR2(30))
PARTITION BY RANGE(hire_date)
(
PARTITION hire_1987 VALUES LESS THAN(TO_DATE('01/01/1987','DD/MM/YYYY')),
PARTITION hire_1988 VALUES LESS THAN(TO_DATE('01/01/1988','DD/MM/YYYY')),
PARTITION hire_1989 VALUES LESS THAN(TO_DATE('01/01/1989','DD/MM/YYYY')),
PARTITION hire_1990 VALUES LESS THAN(TO_DATE('01/01/1990','DD/MM/YYYY')),
PARTITION hire_1991 VALUES LESS THAN(TO_DATE('01/01/1991','DD/MM/YYYY')),
PARTITION hire_1992 VALUES LESS THAN(TO_DATE('01/01/1992','DD/MM/YYYY')),
PARTITION hire_1993 VALUES LESS THAN(TO_DATE('01/01/1993','DD/MM/YYYY')),
PARTITION hire_1994 VALUES LESS THAN(TO_DATE('01/01/1994','DD/MM/YYYY')),
PARTITION hire_1995 VALUES LESS THAN(TO_DATE('01/01/1995','DD/MM/YYYY')),
PARTITION hire_1996 VALUES LESS THAN(TO_DATE('01/01/1996','DD/MM/YYYY')),
PARTITION hire_1997 VALUES LESS THAN(TO_DATE('01/01/1997','DD/MM/YYYY')),
PARTITION hire_1998 VALUES LESS THAN(TO_DATE('01/01/1998','DD/MM/YYYY')),
PARTITION hire_1999 VALUES LESS THAN(TO_DATE('01/01/1999','DD/MM/YYYY')),
PARTITION hire_2000 VALUES LESS THAN(TO_DATE('01/01/2000','DD/MM/YYYY'))
);

I was trying to fire below statement

insert /*+ append */   into  Partition_employees

select *
from employees

Since, employees table having values for year 2000 and I have not defined partition for year 2000 (partition is exists for year < 2000 but not for 2000), my insert statement was failing giving error as

ORA-14400: inserted partition key does not map to any partition

Re-created table with one more partition - below is the script

CREATE TABLE Partition_employees3
(employee_id  VARCHAR2(30),
first_name VARCHAR2(30),
last_name  VARCHAR2(30),
email    VARCHAR2(30),
phone_number VARCHAR2(30),
hire_date date,
job_id VARCHAR2(30),
salary VARCHAR2(30),
commission_pct VARCHAR2(30),
manager_id VARCHAR2(30),
department_id VARCHAR2(30))
PARTITION BY RANGE(hire_date)
(
PARTITION hire_1987 VALUES LESS THAN(TO_DATE('01/01/1987','DD/MM/YYYY')),
PARTITION hire_1988 VALUES LESS THAN(TO_DATE('01/01/1988','DD/MM/YYYY')),
PARTITION hire_1989 VALUES LESS THAN(TO_DATE('01/01/1989','DD/MM/YYYY')),
PARTITION hire_1990 VALUES LESS THAN(TO_DATE('01/01/1990','DD/MM/YYYY')),
PARTITION hire_1991 VALUES LESS THAN(TO_DATE('01/01/1991','DD/MM/YYYY')),
PARTITION hire_1992 VALUES LESS THAN(TO_DATE('01/01/1992','DD/MM/YYYY')),
PARTITION hire_1993 VALUES LESS THAN(TO_DATE('01/01/1993','DD/MM/YYYY')),
PARTITION hire_1994 VALUES LESS THAN(TO_DATE('01/01/1994','DD/MM/YYYY')),
PARTITION hire_1995 VALUES LESS THAN(TO_DATE('01/01/1995','DD/MM/YYYY')),
PARTITION hire_1996 VALUES LESS THAN(TO_DATE('01/01/1996','DD/MM/YYYY')),
PARTITION hire_1997 VALUES LESS THAN(TO_DATE('01/01/1997','DD/MM/YYYY')),
PARTITION hire_1998 VALUES LESS THAN(TO_DATE('01/01/1998','DD/MM/YYYY')),
PARTITION hire_1999 VALUES LESS THAN(TO_DATE('01/01/1999','DD/MM/YYYY')),
PARTITION hire_2000 VALUES LESS THAN(TO_DATE('01/01/2000','DD/MM/YYYY')),
PARTITION hire_2001 VALUES LESS THAN(TO_DATE('01/01/2001','DD/MM/YYYY'))--newly addd
--PARTITION hire_max VALUES LESS THAN(maxvalue )
);

You can add required partition or add maxvalue partition, as mentioned above.

insert /*+ append */   into  Partition_employees

select *
from employees

Now the 107 rows got inserted successfully. I have not commited the transaction and fired below select statement

got an error. Not sure why. It says can not read object after modifying it in parallel.

I committed the transaction and now select went fine.

Checking Explain Plan:

select *
from Partition_employees
where hire_date = '31-Jan-2001'

Here, it says that it has accessed data from partition#15.



What if we replace = with between clause ?

Check below query

select *
from Partition_employees
where hire_date between '1-Jan-1999' and '31-Jan-2001'

Here, it has access partition #14 and #15. Which is quite obvious and logical.









Selecting data from Particular Partition

select *
from Partition_employees partition(hire_1988)

If you check its explain plan then it has accessed 2nd partition.

What if I fire a query based on date range instead of partition; Ultimately it will access same 2nd partition but only with based on data range.

select *
from Partition_employees
where hire_date between '1-Jan-1987' and '31-Dec-1987'

Both queries result and explain plan will be same.

1. Question arises is, which one to use in case you want all the values of that range or partition.
2. Second question is, what will be the performance if I use hash / value partition instead of range partition.

Need to check...

==================================================

Local Index Creation -

I fired below statement to create a local index on our partitioned table. Logically local index will point to each partition which are available on base table.

Here, I am trying to create an index only on first 3 partitions instead of all. When I fired a statement, oracle cried with giving below error--

CREATE INDEX LocalPartitionIndex
on Partition_employees (hire_date)
LOCAL
(PARTITION hire_1987,
PARTITION hire_1988,
PARTITION hire_1989);

ORA-14024: number of partitions of LOCAL index must equal that of the underlying table



When I specified all the partitions at index level, it got succeeded

CREATE INDEX LocalPartitionIndex
on Partition_employees (hire_date)
LOCAL
(PARTITION hire_1987,
PARTITION hire_1988,
PARTITION hire_1989,
PARTITION hire_1990,
PARTITION hire_1991,
PARTITION hire_1992,
PARTITION hire_1993,
PARTITION hire_1994,
PARTITION hire_1995,
PARTITION hire_1996,
PARTITION hire_1997,
PARTITION hire_1998,
PARTITION hire_1999,
PARTITION hire_2000,
partition hire_max);


Even this will also works


CREATE INDEX LocalPartitionIndex
on Partition_employees(hire_date) local

Below, Local index got created even on non-partitioned column
CREATE INDEX LocalPartitionIndex11
on Partition_employees_2 (employee_id)
LOCAL
(PARTITION hire_1987,
PARTITION hire_1988,
PARTITION hire_1989,
PARTITION hire_1990,
PARTITION hire_1991,
PARTITION hire_1992,
PARTITION hire_1993,
PARTITION hire_1994,
PARTITION hire_1995,
PARTITION hire_1996,
PARTITION hire_1997,
PARTITION hire_1998,
PARTITION hire_1999,
PARTITION hire_2000,
partition hire_max);

CREATE INDEX LocalPartitionIndex4
on Partition_employees3 (employee_id) local


Global Index Creation


CREATE INDEX GlobalPartitionIndex ON Partition_employees(employee_id)
GLOBAL PARTITION BY RANGE(employee_id)
(PARTITION p1 VALUES LESS THAN(500),
 PARTITION p2 VALUES LESS THAN(MAXVALUE));

CREATE INDEX GlobalPartitionIndex1_2 ON Partition_employees3(hire_date)
GLOBAL PARTITION BY RANGE(hire_date)
(PARTITION p1 VALUES LESS THAN('1-Jan-2000'),
 PARTITION p2 VALUES LESS THAN(MAXVALUE));

You can define global index on partitioned or non-partitioned column both.

DATA DICTIONARY VIEW

SELECT *
FROM ALL_PART_TABLES -- PARTITION TABLE NAMES
where table_name = upper('Partition_employees')

This will give all information, like
1. Type of partition
2. SubPartition
3. Number of partition
4. partition status
5. table name

---------------------------------------
SELECT *
FROM LOADER_PART_INFO

SELECT *
FROM ALL_PARTIAL_DROP_TABS

---------------------------------------
SELECT *
FROM ALL_PART_INDEXES
where table_name = upper('Partition_employees')

This will give us information like
1. Types of indexes on partition table
2. Type of partition
3. sub partitions
4. index partition count (Here its 15 and 2, please see above scripts. We have
            defined 2 partitions on global and for local it will be same as table partitions)
5. locality of partition
6. alignment
---------------------------------------
SELECT *
FROM ALL_PART_KEY_COLUMNS

It will show all partition objects (index and table in this case) and column on which its partition is defined.


---------------------------------------
SELECT *
FROM ALL_TAB_PARTITIONS

SELECT *
FROM ALL_IND_PARTITIONS

These both tables shows table and index partition information respectively as
1. index or table name
2. partition name
3. sub partition count
4. high_value (i.e. criteria)
5. status
6. tablespace name
7. logging

There are some doubts like can we define loggig or tablespace for a particular partition separately ?