Oracle Partitioning

The basic concept of partitioning is to divide one large table into multiple smaller units. Each of the smaller units (or partitions) can then be accessed and managed separately.
The Partitioning can be done in several ways according to the data that will be stored. They are

• Range partitioning (eg: create_date)
• Hashmap partitioning (eg: stock_id) :- equal physical split among partitions. Improves internal I/O performance.
• List partitioning (eg: south, north, etc)
• Composite partitioning (Combination of above)

Whe When partitioning the table, you need to take care of the indexes also. There are two basic types

• Local - All index entries in a single partition will correspond to a single table partition (equipartitioned). They are created with the LOCAL keyword and support partition independence. Equipartioning allows oracle to be more efficient whilst devising query plans.
• Global - Index in a single partition may correspond to multiple table partitions. They are created with the GLOBAL keyword and do not supports partition independence. Global indexes can only be range partitioned and may be partitioned in such a fashion that they look equipartitioned, but Oracle will not take advantage of this structure.

Few important terms that we need to know before partitioning the table :

• ROW MOVEMNT - automatically moves the data from one partition to another if particular data is modified.
• Eg: ALTER TABLE STT_LOG_NEW ENABLE ROW MOVEMENT;
  
• NOLOGGING - The NOLOGGING clause causes minimal redo information to be generated during the table creation. This has the following benefits:
• Space is saved in the redo log files.
• The time it takes to create the table is decreased.
• Performance improves for parallel creation of large tables.

• COMPRESS - Table compression saves disk space and reduces memory use in the buffer cache. Table compression can also speed up query execution during reads.
• To enable compression for all operations you must use the COMPRESS FOR ALL OPERATIONS clause.
• 2. To enable compression for direct-path inserts only, you use the COMPRESS FOR DIRECT_LOAD OPERATIONS clause. (default)
• SHRINK – used for memory management.

• PARTITION PRUNING – partition pruning is the skipping of unnecessary index anddata partitions or sub partitions in a query.

will be continued ...