This sort of index will gives the ultimate performance when created on table with range based queries .PPI ( Partition primary index ) which will make physical data in to organized blocks . This organization of data will help the retrieval very smoother.
Partitions are usually defined based on Range or Case based columns .
Eg :
For Range based : Date range such as Date between 01-01-2000 and 20-01-2016
For case based : Dept_id in (20,40)
Example Diagram:
We have on Payment table having two columns Payment_date and Payment _id,in which PI is defined on Payment _id.
Before applying PPI on Payment_date
Now when we execute query
Select*from order_table where order_date between 1-1-2003 and 1-31-2003
This query will result in a all table scan despite of order_date being PI.
After applying PPI on Payment_date with interval 1 year
Syntax :
Sample :
1. Partition by CASE
CREATE TABLE ORDER_Table
(
Payment_id integer NOT NULL,
Payed_by ,
Payment_date date ,
)
PRIMARY INDEX (Payment_id )
PARTITION BY range_n (
Payment_date BETWEEN date '1990-01-01' AND date '2999-12-01'
EACH interval '1' Year,
NO RANGE
OR UNKNOWN);
2. Partition by CASE
CREATE TABLE ORDER_Table
(
ORD_number integer NOT NULL,
customer_number integer NOT NULL,
order_date date ,
order_total integer
)
PRIMARY INDEX (customer_number)
PARTITION BY case_n (
order_total < 10000 ,
order_total < 20000 ,
order_total < 30000,
NO CASE OR UNKNOWN ) ;
Advantages :
• Avoids full table scan
• Access a subset of a large table quickly.
• Performs DML Operations fastly on the column with PPI
• SI subtable disadvantage can be overcome by creating PPI , if SI is eligible for range based .
Disadvantages:
• Takes 2 bit for every row of Perm space which is acceptable
• Performance can be degraded if
a) Used in TPT and Tpump
b) PPI column Joined with NOPPI
c) Partition column is not part of the PI
d) Unique value for Partitioned column
Partitions are usually defined based on Range or Case based columns .
Eg :
For Range based : Date range such as Date between 01-01-2000 and 20-01-2016
For case based : Dept_id in (20,40)
Example Diagram:
We have on Payment table having two columns Payment_date and Payment _id,in which PI is defined on Payment _id.
Before applying PPI on Payment_date
Now when we execute query
Select*from order_table where order_date between 1-1-2003 and 1-31-2003
This query will result in a all table scan despite of order_date being PI.
After applying PPI on Payment_date with interval 1 year
Syntax :
Sample :
1. Partition by CASE
CREATE TABLE ORDER_Table
(
Payment_id integer NOT NULL,
Payed_by ,
Payment_date date ,
)
PRIMARY INDEX (Payment_id )
PARTITION BY range_n (
Payment_date BETWEEN date '1990-01-01' AND date '2999-12-01'
EACH interval '1' Year,
NO RANGE
OR UNKNOWN);
2. Partition by CASE
CREATE TABLE ORDER_Table
(
ORD_number integer NOT NULL,
customer_number integer NOT NULL,
order_date date ,
order_total integer
)
PRIMARY INDEX (customer_number)
PARTITION BY case_n (
order_total < 10000 ,
order_total < 20000 ,
order_total < 30000,
NO CASE OR UNKNOWN ) ;
Advantages :
• Avoids full table scan
• Access a subset of a large table quickly.
• Performs DML Operations fastly on the column with PPI
• SI subtable disadvantage can be overcome by creating PPI , if SI is eligible for range based .
Disadvantages:
• Takes 2 bit for every row of Perm space which is acceptable
• Performance can be degraded if
a) Used in TPT and Tpump
b) PPI column Joined with NOPPI
c) Partition column is not part of the PI
d) Unique value for Partitioned column
1 Comments
Online order
Online Ordering
Restaurant Kiosks
Pay at the table
Order Pay & Go