In this article, I will tell you about a nice technology like Partitioning which is present in almost all database management systems.

Partitioning technology is a technology that allows us to create very large tables, indexes or index-organized tables in separate segments. In other words, when we convert large tables or indexes that appear to be logical as a whole into Partitioned structure, we can divide them into smaller physical sections.

Especially in very large database systems (VLDB = Very Large DB), the presence of terabyte level data causes problems both in the maintenance of this data and the necessary operations on it. The most effective way to cope with this is to divide this large piece into smaller pieces. The concept of partitioning comes into play here, which can be explained roughly as the division of data into a whole.

With partitioning, a table or index can be decomposed into smaller pieces in itself. You don't need to make any changes to scrips. However, once the partitions are defined, DDL queries can access and process individual partitions instead of all tables or indexes. This is how partitioning can simplify the manageability of large database objects.

Each partition of a table or index must have the same logical attributes, such as column names, data types, and constraints, but each partition can have separate physical attributes, such as physical storage settings and table space, whether compression is enabled or disabled. For example, when routing the oldest 10-year data of a table to SATA drives, we can position the current data on the SAS.

Partitioning is useful for many different types of applications, especially applications that handle large volumes of data. OLTP systems often benefit from improvements in manageability and availability, while storage systems benefit from performance and manageability.

Partitioning offers the following advantages:

  • It provides data management operations such as indexing and rebuilding, partition-level backup and recovery instead of the entire table. This results in significantly reduced times for these operations. (For example, depending on the partition method, you can drop partition 2 in a table with 5 different partitions, create a different index, or truncate that partition independently of the table.)
  • It deals with small parts instead of dealing with large parts, especially in query operations.
  • Significantly reduces the impact of scheduled downtime for maintenance operations.
  • Parallel execution provides special advantages to optimize resource usage and minimize execution time. Parallel execution is supported for queries and DML and DDL.


Any table can be divided into millions of separate partitions, except for tables that contain columns with LONG or LONG RAW data types. However, you can also use tables that contain columns with CLOB or BLOB data types. In addition, when partitioning is recommended in a table or index;

For the table;

  • Tables larger than 2 GB. These tables should always be considered candidates for partitioning.
  • The most recent partition is a table containing historical data to which new data is added. For example, it is a historical table in which only the current month's data can be updated and the other 11 months are read-only.
  • Tables whose content must be distributed to different storage devices.

For Index;

  • To avoid index maintenance when data is deleted. For example, suppose that there are 12 partitions based on the month, only the data of the third month is deleted and the entire index is normally processed, but if the index has a partition, only the index in the 3rd month is processed.
  • Disable access to some of the index data without overriding the entire index.

Partition Key

The first step to do partitioning, This is the step of specifying a KEY consisting of one or more columns that determines which partition a record should reside in. In a parititioned table, each row of information is strictly stored in a single partition. This is the key partition key sağlayan. Once defined, query, update, delete etc. This key is used to perform operations on the corresponding partitone.

Oracle Partitioning provides three basic data distribution methods as basic partitioning strategies that control how data is placed on individual partitions:

Using these data distribution methods, it can be partitioned as single-level or as a composite-partitioned table.

  • Single-Level Partitioning
  • Composite Partitioning

Single-Level Partitioning

Partitions defined by specifying one of the following data distribution methods using one or more columns as the Partition Key:

  • Range Partitioning
  • Hash Partitioning
  • List Partitioning

Range Partitioning

It is one of the most commonly used partitioning methods. In this method, the data is separated by a specific date and number range. Each partition has upper and lower limits, and data is stored in partitions within this range.


CREATE TABLE sales_range
(
   salesman_id NUMBER (5),
   salesman_name VARCHAR2 (30),
   sales_amount NUMBER (10),
   sales_date DATE
)
PARTITION BY RANGE (sales_date)
   (PARTITION sales_jan2000 VALUES LESS THAN (TO_DATE ('02 / 01/2000 ',' MM / DD / YYYY '))),
    PARTITION sales_feb2000 VALUES LESS THAN (TO_DATE ('03 / 01/2000 ',' MM / DD / YYYY ')),
    PARTITION sales_mar2000 VALUES LESS THAN (TO_DATE ('04 / 01/2000 ',' MM / DD / YYYY ')),
    PARTITION sales_apr2000 VALUES LESS THAN (TO_DATE ('05 / 01/2000 ',' MM / DD / YYYY ')));

In the example, the partition key is the “sales_date” field. Based on the value of this field, partition VALUES LESS THAN ”is used to identify 4 different partitions.

  • Each partition is created with the keyword U VALUES LESS THAN .B. Data equal to or greater than this value goes to the above defined partiton.
  • Except for the first partition, the others are limited to the partition key value that follows it.
  • In the highest partition, the keyword “MAXVALUE kullanılabilir can be used. In this case, the data that cannot be entered in the highest partition is redirected here (including NULL values).

In addition, the following method allows the identification of multiple columns as partitone keys.


CREATE TABLE ORDERS
(
   ORDID NUMBER (4) NOT NULL,
   ORDERDATE DATE,
   COMMPLAN VARCHAR2 (1),
   CUSTID NUMBER (6) NOT NULL,
   SHIPDATE_MM NUMBER (2) NOT NULL,
   SHIPDATE_YY NUMBER (2) NOT NULL,
   TOTAL NUMBER (8, 2)
)
PARTITION BY RANGE (SHIPDATE_YY, SHIPDATE_MM)
   (PARTITION Q197 VALUES LESS THAN (97, 04) TABLESPACE TS_Q197,
    PARTITION Q297 VALUES LESS THAN (97, 07) TABLESPACE TS_Q297
                ...
                )

NOTE : If we want to select on the basis of partiton;


SELECT * FROM sales_range PARTITION (sales_feb2000);

List Partitioning

In the range method, it is important whether the partiton key exists in a given list while the limit values ​​are based. For example, you have a column that holds city information in your table. When you select this column value as partiton key, you can define the data as “Istanbul, Ankara’ to P1 partition and ”İzmir, Antalya, Bursa P2 to P2 partition. Sample :


CREATE TABLE sales_list

(
   salesman_id NUMBER (5),
   salesman_name VARCHAR2 (30),
   sales_state VARCHAR2 (20),
   sales_amount NUMBER (10),
   sales_date DATE
)
PARTITION BY LIST (sales_state)
   (PARTITION sales_west VALUES ('California', 'Hawaii'),
    PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),
    PARTITION sales_central VALUES ('Texas', 'Illinois'),
    PARTITION sales_other VALUES (DEFAULT));

The partition specified as FA DEFAULT kullanılır is used by Oracle when it comes to data other than the user-defined definitions.

NOTE : Multiple columns cannot be defined as partition key.

Hash Partitioning

In this method, a hash algorithm is applied to the partition key column and the data is distributed to the paritions according to the result. Hash partitioning is the ideal method to distribute data evenly between devices. Hash partitioning is an easy-to-use alternative to partitioning, especially when the data to be partitioned is not historical or there is no obvious partition key. You cannot modify the hash algorithms used by partitioning. However, this method cannot be used to separate, drop, and merge partitons like others. Only a new partiton can be added or the data in the partition (or subpartition) that we call “COALESCE a can be added to other partitions. (The edited partion (or subpartition) is deleted after this process.) For example, the TC ID Number column in the Citizenship table does not correspond to any of the above methods, in which case Hash partitioning is used. An advantage of this method is that it allows parallel DML operations, partition-wise join, etc.


CREATE TABLE sales_hash
(
   salesman_id NUMBER (5),
   salesman_name VARCHAR2 (30),
   sales_amount NUMBER (10),
   week_no NUMBER (2)
)
PARTITION BY HASH (salesman_id)
   PARTITIONS 4 STORE IN (tablespace1, tablespace2, tablespace3, tablespace4);

Composite Partitioning

This partitioning method is to combine other partitioning methods together. Composite partitioning is a combination of basic data distribution methods; a table is partitioned with a data distribution method, and then each partition is subpartitions using a second data distribution method. All subpartitions for a given partition represent a logical subset of data. This method is to separate the data that we have separated according to a certain logic according to a rule. For example, by adding the partition data we have separated according to the Range method to the list and hash method, Range-List and Range-Hash partition can be applied.

  • Composite Range-Range Partitioning
  • Composite Range-Hash Partitioning
  • Composite Range-List Partitioning
  • Composite List-Range Partitioning
  • Composite List-Hash Partitioning
  • Composite List-List Partitioning
  • Composite Hash-Hash Partitioning
  • Composite Hash-List Partitioning
  • Composite Hash-Range Partitioning

Composite Range-Range Partitioning

Useful for applications that store time-dependent data at multiple time sizes. Typically, these applications do not use a specific time dimension to access the data, but instead use another time dimension, or sometimes both. For example, a web vendor wants to analyze sales data based on when orders are placed and when orders are submitted. Example The following example shows the account_balance_history table with a range-range partition. A bank can use access to separate subpartitions to communicate with customers for low-balance reminders or specific promotions for a particular customer category.


CREATE TABLE account_balance_history
(
   id NUMBER NOT NULL,
   account_number NUMBER NOT NULL,
   customer_id NUMBER NOT NULL,
   transaction_date DATE NOT NULL,
   amount_credited NUMBER,
   amount_debited NUMBER,
   end_of_day_balance NUMBER NOT NULL
)
PARTITION BY RANGE (transaction_date)
   INTERVAL (NUMTODSINTERVAL (7, 'DAY'))
   SUBPARTITION BY RANGE (end_of_day_balance)
      SUBPARTITION TEMPLATE (
         SUBPARTITION unacceptable VALUES LESS THAN (-1000),
         SUBPARTITION credit VALUES LESS THAN (0),
         SUBPARTITION low VALUES LESS THAN (500),
         SUBPARTITION normal VALUES LESS THAN (5000),
         SUBPARTITION high VALUES LESS THAN (20000),
         SUBPARTITION extraordinary VALUES LESS THAN (MAXVALUE))
   (PARTITION p0 VALUES LESS THAN (TO_DATE ('01 -JAN-2007 ',' dd-MON-yyyy ')));

Composite Range-Hash Partitioning

In this method, the data allocated to the partition by Range method can also be divided into sub partitions by hashing. Composite range-hash partitioning is especially common for tables that store history, as a result are very large, and are often combined with other large tables.


CREATE TABLE call_detail_records
(
   id NUMBER,
   from_number VARCHAR2 (20),
   TO_NUMBER VARCHAR2 (20),
   date_of_call DATE,
   distance VARCHAR2 (1),
   call_duration_in_s NUMBER (4)
)
PARTITION BY RANGE (date_of_call)
   INTERVAL (NUMTODSINTERVAL (1, 'DAY'))
   SUBPARTITION BY LIST (distance)
      SUBPARTITION TEMPLATE (
         SUBPARTITION local VALUES ('L') TABLESPACE tbs1,
         SUBPARTITION medium_long VALUES ('M') TABLESPACE tbs2,
         SUBPARTITION long_distance VALUES ('D') TABLESPACE tbs3,
         SUBPARTITION international VALUES ('I') TABLESPACE tbs4)
   (PARTITION p0
       VALUES LESS THAN (TO_DATE ('01 -JAN-2005 ',' dd-MON-yyyy '))) PARALLEL;

CREATE INDEX from_number_ix ON call_detail_records (from_number) LOCAL PARALLEL NOLOGGING;

CREATE INDEX to_number_ix ON call_detail_records (TO_NUMBER) LOCAL PARALLEL NOLOGGING;

Composite Range-List Partitioning

In this method, the data allocated to the partition by the Range method can also be divided into a List sub partition. used for large tables that store opened data and are typically accessed in multiple sizes. Typically, the historical view of data is an access path, but in some cases adds another category to the access path. For example, regional account managers are very interested in how many new customers they have registered in their region during a given time period.


CREATE TABLE call_detail_records
(
   id NUMBER,
   from_number VARCHAR2 (20),
   TO_NUMBER VARCHAR2 (20),
   date_of_call DATE,
   distance VARCHAR2 (1),
   call_duration_in_s NUMBER (4)
)
PARTITION BY RANGE (date_of_call)
   INTERVAL (NUMTODSINTERVAL (1, 'DAY'))
   SUBPARTITION BY LIST (distance)
      SUBPARTITION TEMPLATE (
         SUBPARTITION local VALUES ('L') TABLESPACE tbs1,
         SUBPARTITION medium_long VALUES ('M') TABLESPACE tbs2,
         SUBPARTITION long_distance VALUES ('D') TABLESPACE tbs3,
         SUBPARTITION international VALUES ('I') TABLESPACE tbs4)
   (PARTITION p0
       VALUES LESS THAN (TO_DATE ('01 -JAN-2005 ',' dd-MON-yyyy '))) PARALLEL;

CREATE INDEX from_number_ix ON call_detail_records (from_number) LOCAL PARALLEL NOLOGGING;

CREATE INDEX to_number_ix ON call_detail_records (TO_NUMBER) LOCAL PARALLEL NOLOGGING;

Composite List-Range Partitioning

In this method, the data allocated to the partition by List method can also be divided into range sub partitions.


CREATE TABLE donations
(
   id NUMBER,
   name VARCHAR2 (60),
   beneficiary VARCHAR2 (80),
   payment_method VARCHAR2 (30),
   currency VARCHAR2 (3),
   amount NUMBER
)
PARTITION BY LIST (currency)
   SUBPARTITION BY RANGE (amount)
   (PARTITION p_eur
       VALUES ('EUR') (
       SUBPARTITION p_eur_small VALUES LESS THAN (8),
       SUBPARTITION p_eur_medium VALUES LESS THAN (80),
       SUBPARTITION p_eur_high VALUES LESS THAN (MAXVALUE)),
    PARTITION p_inr
       VALUES ('INR') (
       SUBPARTITION p_inr_small VALUES LESS THAN (400),
       SUBPARTITION p_inr_medium VALUES LESS THAN (4000),
       SUBPARTITION p_inr_high VALUES LESS THAN (MAXVALUE)),
    PARTITION p_zar
       VALUES ('ZAR') (
       SUBPARTITION p_zar_small VALUES LESS THAN (70),
       SUBPARTITION p_zar_medium VALUES LESS THAN (700),
       SUBPARTITION p_zar_high VALUES LESS THAN (MAXVALUE)),
    PARTITION p_default
       VALUES (DEFAULT) (
       SUBPARTITION p_default_small VALUES LESS THAN (10),
       SUBPARTITION p_default_medium VALUES LESS THAN (100),
       SUBPARTITION p_default_high VALUES LESS THAN (MAXVALUE)))
ENABLE ROW MOVEMENT;

Composite List-Hash Partitioning

In this method, the data allocated to partition by List method can also be divided into hash sub partitions.


CREATE TABLE credit_card_accounts
(
   account_number NUMBER (16) NOT NULL,
   customer_id NUMBER NOT NULL,
   customer_region VARCHAR2 (2) NOT NULL,
   is_active VARCHAR2 (1) NOT NULL,
   date_opened DATE NOT NULL
)
PARTITION BY LIST (customer_region)
   SUBPARTITION BY HASH (customer_id)
      SUBPARTITIONS 16
   (PARTITION emea VALUES ('EU', 'ME', 'AF'),
    PARTITION amer VALUES ('NA', 'LA'),
    PARTITION apac VALUES ('SA', 'AU', 'NZ', 'IN', 'CH')) PARALLEL;

CREATE BITMAP INDEX is_active_bix ON credit_card_accounts (is_active) LOCAL PARALLEL NOLOGGING;

Composite List-List Partitioning

In this method, the data allocated to the partition by List method can also be divided into a list sub partition.


CREATE TABLE current_inventory
(
   warehouse_id NUMBER,
   warehouse_region VARCHAR2 (2),
   product_id NUMBER,
   product_category VARCHAR2 (12),
   amount_in_stock NUMBER,
   unit_of_shipping VARCHAR2 (20),
   products_per_unit NUMBER,
   last_updated DATE
)
PARTITION BY LIST (warehouse_region)
   SUBPARTITION BY LIST (product_category)
      SUBPARTITION TEMPLATE (
         SUBPARTITION PERISHABLE VALUES ('DAIRY', 'PRODUCE', 'MEAT', 'BREAD'),
         SUBPARTITION non_perishable VALUES ('CANNED', 'PACKAGED'),
         SUBPARTITION durable VALUES ('TOYS', 'KITCHENWARE'))
   (PARTITION p_northwest VALUES ('OR', 'WA'),
    PARTITION p_southwest VALUES ('AZ', 'UT', 'NM'),
    PARTITION p_northeast VALUES ('NY', 'VM', 'NJ'),
    PARTITION p_southeast VALUES ('FL', 'GA'),
    PARTITION p_northcentral VALUES ('SD', 'WI'),
    PARTITION p_southcentral VALUES ('OK', 'TX'));

CREATE INDEX warehouse_id_ix ON current_inventory (warehouse_id) LOCAL PARALLEL NOLOGGING;

CREATE INDEX product_id_ix ON current_inventory (product_id) LOCAL PARALLEL NOLOGGING;

Composite Hash-Hash Partitioning

In this method, the data that has been allocated to partition by hash method can also be divided into hash sub partitions.

Composite Hash-List Partitioning

In this method, the data that has been partitioned by hash method can also be divided into list sub partitions.

Composite Hash-Range Partitioning

In this method, the data that is allocated to partition by hash method can also be divided into range sub partitions.

Partitioning Extensions

In addition to basic partitioning strategies, Oracle Database provides the following partitioning plug-ins:

  • Manageability Extensions
    • Interval Partitioning
    • Partition Advisor: Partition Advisor is part of SQL Access Advisor. Partition Advisor can propose a partitioning strategy for a table based on the workload of supplied SQL statements that can be supplied by SQL Server or provided by a SQL Tuning set or user-defined.
  • Partitioning Key Extensions
    • Reference Partitioning
    • Virtual Column-Based Partitioning: A virtual column is an expression that is based on one or more existing columns in the table. a virtual column is stored only as metadata and does not consume physical space, but can be added to the index and can also contain optimizer statistics and histograms. Oracle 11g also includes partitioning support for a table using a partition key in a virtual column. Before 11g, a partition key was limited to the use of physical columns. For example, for a 10-digit account ID column, the first 3 characters contain account branch information. With virtual column based partitioning, an ACCOUNTS table containing an ACCOUNT_ID column can be extended with an ACCOUNT_BRANCH virtual (derived) column. ACCOUNT_BRANCH is derived from the first three digits of the ACCOUNT_ID column, which becomes the partitioning key for this table.

Interval Partitioning

This partitioning method is a new method used with Range partitioning that comes with Oracle 11g. In the range partitioning method, the incoming data was added to the appropriate partititone, but when it could not find the appropriate partition, it gave ORA-14400 error. With Interval Partititoning, this problem is now solved automatically. In other words, Interval partitioning can be summarized as oracle's opening a new partition on your behalf according to the criterion you specified before when a record comes out of the partition intervals that you created when creating the table with a very simple expression. There are some rules that we have to follow when using Interval partitioning, let's talk about these rules / limitations;

  • Interval partitioning can only be placed on a single column and the type of this column must be Number or Date. In other words, if your partition key consists of more than one column for your partitioned table, you cannot use Interval partitioning.
  • You cannot use Interval partitioning when indexing index-organized tables. Because
  • Interval partitioning does not support index-organized tables.
  • You cannot create a domain index on tables created using Interval Partition.
  • In order to use the Interval partitioning option, at least one partition must be defined when creating a partitioned table.
  • Interval Partitioning can be used in composite partitioning, but composite partitions can only be used at the first level, ie you cannot use interval partitioning at the subpartition level.
  • Maxvalue paritition cannot be defined where interval paritition is to be used.
    Null values ​​are not allowed in partition columns.

To make an example;


CREATE TABLE interval_test_table2
(
   no NUMBER,
   name VARCHAR2 (30),
   Explanation VARCHAR2 (60),
   insert_date DATE
)
PARTITION BY RANGE (insert_date)
   INTERVAL (NUMTOYMINTERVAL (1, 'MONTH'))
   (PARTITION part_date1 VALUES LESS THAN (TO_DATE ('01 / 01/2012 ',' DD / MM / YYYY '))) TABLESPACE tbs1,
    PARTITION part_date2 VALUES LESS THAN (TO_DATE ('01 / 02/2012 ',' DD / MM / YYYY ')) TABLESPACE tbs2,
    PARTITION part_date3 VALUES LESS THAN (TO_DATE ('01 / 03/2012 ',' DD / MM / YYYY ')) TABLESPACE tbs3,
    PARTITION part_date4 VALUES LESS THAN (TO_DATE ('01 / 04/2012 ',' DD / MM / YYYY ')) TABLESPACE tbs4,
    PARTITION part_date5 VALUES LESS THAN (TO_DATE ('01 / 05/2012 ',' DD / MM / YYYY ')) TABLESPACE tbs5);

If we want to use Interval Partitioning when performing range-list partitioning, an example syntax is;


CREATE TABLE call_detail_records
(
   id NUMBER,
   from_number VARCHAR2 (20),
   TO_NUMBER VARCHAR2 (20),
   date_of_call DATE,
   distance VARCHAR2 (1),
   call_duration_in_s NUMBER (4)
)
PARTITION BY RANGE (date_of_call)
   INTERVAL (NUMTODSINTERVAL (1, 'DAY'))
   SUBPARTITION BY LIST (distance)
      SUBPARTITION TEMPLATE (
         SUBPARTITION local VALUES ('L') TABLESPACE tbs1,
         SUBPARTITION medium_long VALUES ('M') TABLESPACE tbs2,
         SUBPARTITION long_distance VALUES ('D') TABLESPACE tbs3,
         SUBPARTITION international VALUES ('I') TABLESPACE tbs4)
   (PARTITION p0 VALUES LESS THAN (TO_DATE ('01 -JAN-2005 ',' dd-MON-yyyy ')))
PARALLEL;

If we want to use Interval Partitioning when performing range-range partitioning, an example syntax is;


CREATE TABLE account_balance_history
(
   id NUMBER NOT NULL,
   account_number NUMBER NOT NULL,
   customer_id NUMBER NOT NULL,
   transaction_date DATE NOT NULL,
   amount_credited NUMBER,
   amount_debited NUMBER,
   end_of_day_balance NUMBER NOT NULL
)
PARTITION BY RANGE (transaction_date)
   INTERVAL (NUMTODSINTERVAL (7, 'DAY'))
   SUBPARTITION BY RANGE (end_of_day_balance)
      SUBPARTITION TEMPLATE (
         SUBPARTITION credit VALUES LESS THAN (0),
         SUBPARTITION low VALUES LESS THAN (500),
         SUBPARTITION normal VALUES LESS THAN (5000),
         SUBPARTITION high VALUES LESS THAN (20000),
         SUBPARTITION extraordinary VALUES LESS THAN (MAXVALUE))
   (PARTITION p0 VALUES LESS THAN (TO_DATE ('01 -JAN-2007 ',' dd-MON-yyyy ')));

Reference Partitioning

Reference partitioning provides partitioning of two tables associated with reference constraints. Partitioning key is resolved by an existing parent-child relationship implemented by active and active primary key and foreign key constraints. The benefit of this plug-in is that the tables with parent-child relationships are logically divided by inheriting the partitioning key from the parent table without duplicating primary key columns. Logical dependency also cascades partition maintenance operations automatically, making application development easier and less error-prone.


CREATE TABLE orders (
  order_id NUMBER PRIMARY KEY,
  order_date DATE NOT NULL,
  customer_id NUMBER NOT NULL,
  shipper_id NUMBER)
PARTITION BY RANGE (order_date) (
  PARTITION y1 VALUES LESS THAN (TO_DATE ('01 -JAN-2006 ',' DD-MON-YYYY ')),
  PARTITION y2 VALUES LESS THAN (TO_DATE ('01 -JAN-2007 ',' DD-MON-YYYY ')),
  PARTITION y3 VALUES LESS THAN (TO_DATE ('01 -JAN-2008 ',' DD-MON-YYYY ')));
  
CREATE TABLE order_items (
  order_id NUMBER NOT NULL,
  product_id NUMBER NOT NULL,
  price NUMBER,
  quantity NUMBER,
  CONSTRAINT order_items_fk FOREIGN KEY (order_id) REFERENCES orders)
PARTITION BY REFERENCE (order_items_fk);  

Creating Indexes on Partitioned Tables

Assuming that we have created our partition table, I would like to talk about how and what kind of index we will create on these partitioned tables. Like partitioned tables, partitioned indexes have a developing effect in terms of manageability, usability, performance and scalability.

Indexes are either independent of partition (global indexes) create or automatically on partitioned areas (local indexes) You can create indexes. When we look at the general usage logic, if you are working on OLTP systems, global index is mostly used, if you are working on datawarehouse systems, local indexes are used intensively. However, you can also use local indexes in OLTP systems if you can.

We talked about global index and local index. Another important question here is actually; How do I decide which of these index types to use? Oracle has some suggestions in this regard;

  • You can use local index if the table is one of the columns you partition and one of the columns you will index.
  • You can use global index if the column you index is going to be unique index and the table is not one of the columns you partition.
  • If the first priority for you to create index is manageability, you should use local index,
  • If you are doing this work on the OLTP system and the responce time of your query is critical to you, you can use global index, if you are doing this work on Datawarehouse system and users are more interested in the output of the query, you can use local index.

Local Partition Index

Local partition indexes are the easiest to manage among the partition indexes. As mentioned above, dataware is mostly used in house systems. Each partition of the local index is fully integrated with the partitioning of the table. This functionality allows oracle to automatically synchronize index partitions with table partitions, where each table –index partition is considered independently of each other. As a result of each operation, only the relevant index partition is affected.

You cannot add a new partition to local indexes. To add a new partition to the local index, a new partition must be added to the table. When you look at the same logic, you cannot drop a partition from the local index. However, when you drop one of the table partitions, the index of the local index is also dropped.

Local partition indexes are based on the table partitioning structure as can be seen from the graph below.

Local Prefixed Indexes

PREFIXED index is created on the partitioned column on the table.


CREATE INDEX invoices_idx ON invoices (invoice_date) LOCAL;

CREATE INDEX invoices_idx ON invoices (invoice_date) LOCAL
 (PARTITION invoices_q1 TABLESPACE users,
  PARTITION invoices_q2 TABLESPACE users,
  PARTITION invoices_q3 TABLESPACE users,
  PARTITION invoices_q4 TABLESPACE users); 

Local Nonprefixed Indexes

NONPREFIXED index is not created on the partitioned column on the table.


CREATE INDEX invoices_idx ON invoices (invoice_no) LOCAL
 (PARTITION invoices_q1 TABLESPACE users,
  PARTITION invoices_q2 TABLESPACE users,
  PARTITION invoices_q3 TABLESPACE users,
  PARTITION invoices_q4 TABLESPACE users);

Global Partitioned Indexes

An index type that is created using a partition key or structure different from the table. Range or Hash partition method can be created. The table itself is partitioned according to the month field and consists of 12 partitions, while the index range can be created with partitioning and can have a large number of partitions. It is mostly used in OLTP type databases.

  • Global Range Partition Index
  • Global Hash Partition Index

Global Range Partition Index

The global range partitioned index is flexible because the partitioning level and partitioning key are independent of the partitioning method of the table.

The latest partition of the global index must be connected with Maxvalue. In this way, all values ​​in the table are evaluated in the index. The global prefixed index can be unique or non-unique.

You cannot add a partition to the global range partition because the last partition already contains all data with maxvalue. If you want to add a new partition to the end, you can use the ALTER INDEX SPLIT PARTITION command. If the global index partition is empty, so if no data is entered in that partitioning, you can drop the partition using the ALTER INDEX DROP PARTITION command. Another important point here is that if the global index contains parition data and you drop this partition, the last partition will be unusable. In global indexes, the last partition cannot be dropped.


CREATE INDEX invoices_idx ON invoices (invoice_date)
GLOBAL PARTITION BY RANGE (invoice_date)
 (PARTITION invoices_q1 VALUES LESS THAN (TO_DATE ('01 / 04/2001 ',' DD / MM / YYYY '))) TABLESPACE users,
  PARTITION invoices_q2 VALUES LESS THAN (TO_DATE ('01 / 07/2001 ',' DD / MM / YYYY ')) TABLESPACE users,
  PARTITION invoices_q3 VALUES LESS THAN (TO_DATE ('01 / 09/2001 ',' DD / MM / YYYY ')) TABLESPACE users,
  PARTITION invoices_q4 VALUES LESS THAN (MAXVALUE) TABLESPACE users);

Global Hash Partition Index

Global hash partition indexler, index’ in tek bir noktada büyümesi durumunda, performans artırıcı bir etkisi bulunmaktadır.

Global Nonpartitioned Indexes

Partition’sız bir tablo üzerindeki index tanımı gibi olan partition yapısına sahip olmayan indextir. OLTP veritabanlarında daha çok PK veya unique değerleri temin etmek amacı ile kullanılan indextir.

Partitioning Availability, Manageability ve Performance

Partition Pruning

Partition pruning, veri ambarları için önemli bir performans özelliğidir. Partition pruning, optimizer, access list listesini oluştururken gereksiz partitionları ortadan kaldırmak için SQL sorgularındaki FROM ve WHERE yan tümcelerini analiz eder. Bu işlevsellik, Oracle Database’in yalnızca SQL deyimi ile ilgili partitionlarda işlem gerçekleştirmesini sağlar. Yani büyük bir segmenti küçük parçalar bölüp sorgulama… Tabloyu partition adlı kümelere bölüp, tüm kayıtlara bakmak yerine ilgilendiğimiz setleri sorgulama yöntemi. Burada dikkat edilmesi gereken, Oracle’ın sorguya bakarak ilgili partitionları çıkartabilecek biçimde bir sorgu yazmaktır.

Partition pruning, diskten alınan veri miktarını önemli ölçüde azaltır ve işlem süresini kısaltır, böylece sorgu performansını artırır ve kaynak kullanımını optimize eder. İndexi ve tabloyu farklı sütunlarda partitionlarsanız, partition pruning, temel tablodaki partitionlar ortadan kaldırılamadığında bile index partitionlarını elimine eder.

SQL ifadesine bağlı olarak, Oracle Database statik veya dinamik pruning kullanabilir. Statik pruning, derleme sırasında önceden erişilen partitionlar hakkında bilgi ile birlikte gerçekleşir. Dinamik pruning, çalışma zamanında gerçekleşir; bu, bir sorgu tarafından erişilecek kesin partitionların önceden bilinmediği anlamına gelir. Statik pruning için örnek bir senaryo, partition key sütunundaki sabit değişmeze sahip bir WHERE koşulu içeren bir SQL ifadesidir. Dinamik budama örneği, WHERE koşulundaki operatörlerin veya functionların kullanılmasıdır. Partition pruning, pruning gerçekleştiği nesnelerin istatistiklerini etkiler ve aynı zamanda bir ifadenin yürütme planını da etkiler.

Partition-Wise Joins

Tablo joinlerken, tablolardan en az birisi join edilen alan üzerinde partition’lu ise bu yöntem ile performans artışı sağlanabilir. Partition-wise join, büyük join işlemini küçük joinlere böler.

Index Partitioning

Partitioning indexes ile ilgili kurllar tablodakine bernzerdir.

  • Cluster indexler partition yapılamaz,
  • Cluster tablolar üzerine tanımlanacak olan index partition yapılamaz,
  • Partition tablo üzerine, partition veya nonpartition index tanımlanabilir,
  • Nonpartition tablo üzerine , partition veya nonpartition index tanımlanabilir,
  • Bitmap indexler, nonpartition tablolar üzerine partitionlı olarak tanımlanamazlar.
  • Bitmap indexler, partitionlı tablolar üzerine local index olarak tanımlanır.

Partitioned Table and Indexler hakkında Bilgileri Görüntüleme

  • DBA_PART_TABLES : Veritabanındaki tüm partitionlanmış tabloların partitioning bilgilerini görüntüler.
  • DBA_TAB_PARTITIONS : Partition düzeyinde partition bilgilerini, partition depolama parametrelerini ve DBMS_STATS paketi veya ANALYZE deyimi tarafından oluşturulan partition istatistiklerini görüntüleyin.
  • DBA_TAB_SUBPARTITIONS : Subpartition düzeyinde partition bilgilerini, sub partition depolama parametrelerini ve DBMS_STATS paketi veya ANALYZE deyimi tarafından oluşturulan alt bölüm istatistiklerini görüntüleyin.
  • DBA_PART_KEY_COLUMNS : Partitionlanmış tablolar için partition key sütunlarını görüntüler.
  • DBA_SUBPART_KEY_COLUMNS : Subpartitionlanmış tablolar için partition key sütunlarını görüntüler.
  • DBA_PART_COL_STATISTICS
  • DBA_SUBPART_COL_STATISTICS
  • DBA_PART_HISTOGRAMS
  • DBA_PART_INDEXES : Veritabanındaki tüm partitionlanmış indexlerin partitioning bilgilerini görüntüler
  • DBA_IND_PARTITIONS
  • DBA_IND_SUBPARTITIONS
  • DBA_SUBPARTITION_TEMPLATES

Kaynaklar;
https://docs.oracle.com/database/121/VLDBG/GUID-FBA59FA7-7F42-4039-96D1-ACEC71A07DD5.htm#VLDBG001
https://hakkioktay.wordpress.com/2007/03/15/partitioning-kavrami-ve-table-partitioning/
http://www.kamilturkyilmaz.com/2012/04/28/oracle-partitioning/
https://mehmetsalihdeveci.net/2014/06/06/oracle-partitioning/

More professionals named Mustafa Bektas Tepe
Good work

1,481 total views, 6 views today