Partition Pruning with Example

In previous article we saw the benefits of partition pruning.

Partition Pruning in Oracle

Here we will see how partition pruning takes place when you run the query accessing your partitioned table.

For instance , you have transaction table from year 2011 to 2019 with different partition for each year. 

And you want statement of your transaction from year 2017 to 2019. 

In traditional way oracle will scan whole table finds your data (more...)

Partition Pruning in Oracle

Pruning itself means to cut the dead parts and work on the pieces which are indeed important one.

In Oracle partition pruning works in the same manner. Oracle can recognize which partitions and sub-partitions need to be eliminated and which partitions should be accessed for the query to be processed.

When you have created some partition on the table and you are using joins or where condition when accessing the same table , oracle prune (more...)

Types of Index Partitions

In previous article we have seen overview of index partitioning and advantages of it.

In this article we will see example of different types of indexes on partitioned tables.

1.Non-partitioned Index:

We can create normal non-partitioned index on partitioned table.

Example : TRANSACTIONS table is partitioned table in my database.

SQL> CREATE INDEX TRANSACTIONS_IND ON TRANSACTIONS(DATE_OF_TRANSACTION);

Index created.

SQL>

2.Global Range Partitioned Indexes : We can create GLOBAL index on partitioned (more...)

Composite Partitioning in Oracle

In this article we will see examples of various types of composite partitions

1.RANGE-HASH composite partitioning :

SQL> CREATE TABLE TRANSACTIONS_MAIN_COMP_HASH(
  2    TRAN_ID NUMBER,
  DATE_OF_TRANSACTION DATE,
  AMOUNT VARCHAR2(100),
  ACCOUNT_NUMBER NUMBER,
CONSTRAINT TRAN_MAIN_COMP_HASH_PK PRIMARY KEY(TRAN_ID)
)
PARTITION BY RANGE (DATE_OF_TRANSACTION) SUBPARTITION BY HASH(ACCOUNT_NUMBER) SUBPARTITIONS 4
(PARTITION Y_2011 VALUES LESS THAN (TO_DATE('01-JAN-2012', 'DD-MON-YYYY')),
 PARTITION Y_2012 VALUES LESS THAN (TO_DATE('01-JAN-2013', 'DD-MON-YYYY')),
 PARTITION Y_2013 VALUES LESS THAN (TO_DATE('01-JAN-2014', 'DD-MON-YYYY')),
 PARTITION Y_2014 VALUES LESS THAN (TO_DATE('01-JAN-2015', 'DD-MON-YYYY')),
 PARTITION Y_2015  (more...)

Types of Table Partitions in Oracle

In this article we will see types of partitions available in Oracle.

Range Partitioning :

Range Partitioning works with range of column values. It sets lower boundaries and upper boundaries to the partition. It is widely used with DATE data types.

Lets create one range partition to understand it better.It is generally used with DATE columns.

Example :

CREATE TABLE TRANSACTIONS(
  TRAN_ID NUMBER,
  DATE_OF_TRANSACTION DATE,
  AMOUNT VARCHAR2(100),
  ACCOUNT_NUMBER NUMBER)
PARTITION BY RANGE (DATE_OF_TRANSACTION)
 (PARTITION  (more...)

Index Partitioning

In previous article we saw what is partitioning in oracle and the benefits its brings .

http://oracle-help.com/oracle-database/oracle-partitioning/        

In this article we will see Index partitioning.

Indexes are the essential part of any SELECT and DML operations which we run on the database. So , when we tune the table we must consider index too. Index partitioning is a magnificent feature when it comes to performance. Just like we saw partitioning in (more...)

ORA-14652: reference partitioning foreign key is not supported

SQL> CREATE TABLE TRANSACTION_DETAILS
(TRAN_DTL_ID NUMBER,
DETAIL VARCHAR2(100),
CONSTRAINT TRAN_DTL_FK
FOREIGN KEY(TRAN_DTL_ID) 
REFERENCES TRANSACTIONS_MAIN(TRAN_ID))
PARTITION BY REFERENCE(TRAN_DTL_FK)  2    3    4    5    6    7  ;
PARTITION BY REFERENCE(TRAN_DTL_FK)
                       *
ERROR at line 7:
ORA-14652: reference partitioning foreign key is not supported

Note : while creating reference partitioning table , foreign key must be not null

SQL> 
CREATE TABLE TRANSACTION_DETAILS
(TRAN_DTL_ID NUMBER NOT NULL,
DETAIL VARCHAR2(100),
CONSTRAINT TRAN_DTL_FK
FOREIGN KEY(TRAN_DTL_ID) 
REFERENCES TRANSACTIONS_MAIN(TRAN_ID))
PARTITION BY REFERENCE(TRAN_DTL_FK)SQL>   2    3     (more...)