Index-Organized Tables

An index-organized table is a table stored in a variation of a B-tree index structure. In a heap-organized table, rows are inserted where they fit. In an index-organized table, rows are stored in an index defined on the primary key for the table. Each index entry in the B-tree also stores the non-key column values. Thus, the index is the data, and the data is the index. Applications manipulate index-organized tables just like heap-organized tables, using SQL statements.

For an analogy of an index-organized table, suppose a human resources manager has a book case of cardboard boxes. Each box is labeled with a number—1, 2, 3, 4, and so on—but the boxes do not sit on the shelves in sequential order. Instead, each box contains a pointer to the shelf location of the next box in the sequence.

Folders containing employee records are stored in each box. The folders are sorted by employee ID. Employee King has ID 100, which is the lowest ID, so his folder is at the bottom of box 1. The folder for employee 101 is on top of 100, 102 is on top of 101, and so on until box 1 is full. The next folder in the sequence is at the bottom of box 2.

In this analogy, ordering folders by employee ID makes it possible to search efficiently for folders without having to maintain a separate index. Suppose a user requests the records for employees 107, 120, and 122. Instead of searching an index in one step and retrieving the folders in a separate step, the manager can search the folders in sequential order and retrieve each folder as found.

Index-organized tables provide faster access to table rows by primary key or a valid prefix of the key. The presence of non-key columns of a row in the leaf block avoids an additional data block I/O. For example, the salary of employee 100 is stored in the index row itself. Also, because rows are stored in primary key order, range access by the primary key or prefix involves minimal block I/Os. Another benefit is the avoidance of the space overhead of a separate primary key index.

Index-organized tables are useful when related pieces of data must be stored together or data must be physically stored in a specific order. This type of table is often used for information retrieval, spatial and OLAP .

Index-organized tables are useful when

Ø  Basically an index-organized table is an index without a table. So if you have a table whose columns consist of the primary key and at most one other column then you have a possible candidate for INDEX ORGANIZED.

Ø  But if you find yourself contemplating the need for additional indexes on the non-primary key columns then you're probably better off with a regular heap table. So, as most tables probably need additional indexes most tables are not suitable for IOTs.

Ø  In practice, index organized tables are most likely to be reference data, code look-up affairs. Application tables are almost always just heap organized.

Ø  An Index-Organized Table--in contrast to an ordinary table--has its own way of structuring, storing, and indexing data.

Ø  Index organized tables (IOT) are indexes which actually hold the data which is being indexed, unlike the indexes which are stored somewhere else and have links to actual data.

Ø  An index-organized table is generally a good choice if you only access data from that table by the key, the whole key, and nothing but the key.

Ø  "small" "lookup" type tables (e.g. queried frequently, updated infrequently, fits in a relatively small number of blocks) are excellent candidates for IOTs.

Ø  Any table that you already are going to have an index that covers all the columns anyway (i.e. may as well save the space used by the table if the index duplicates 100% of the data) are excellent candidates for IOTs.

Index-Organized Table Characteristics

The database system performs all operations on index-organized tables by manipulating the B-tree index structure. Table summarizes the differences between index-organized tables and heap-organized tables.

Table : Comparison of Heap-Organized Tables with Index-Organized Tables

Heap-Organized Table	Index-Organized Table
The rowid uniquely identifies a row. Primary key constraint may optionally be defined.	Primary key uniquely identifies a row. Primary key constraint must be defined.
Physical rowid in ROWID pseudocolumn allows building secondary indexes.	Logical rowid in ROWID pseudocolumn allows building secondary indexes.
Individual rows may be accessed directly by rowid.	Access to individual rows may be achieved indirectly by primary key.
Sequential full table scan returns all rows in some order.	A full index scan or fast full index scan returns all rows in some order.
Can be stored in a table cluster with other tables.	Cannot be stored in a table cluster.
Can contain a column of the LONG data type and columns of LOB data types.	Can contain LOB columns but not LONG columns.
Can contain virtual columns (only relational heap tables are supported).	Cannot contain virtual columns.

Figure 1 illustrates the structure of an index-organized departments table. The leaf blocks contain the rows of the table, ordered sequentially by primary key. For example, the first value in the first leaf block shows a department ID of 20, department name of Marketing, manager ID of 201, and location ID of 1800.

Figure 1 Index-Organized Table

An index-organized table stores all data in the same structure and does not need to store the rowid. As shown in Figure 1, leaf block 1 in an index-organized table might contain entries as follows, ordered by primary key:

20,Marketing,201,1800

30,Purchasing,114,1700

Leaf block 2 in an index-organized table might contain entries as follows:

50,Shipping,121,1500

60,IT,103,1400

A scan of the index-organized table rows in primary key order reads the blocks in the following sequence:

1. Block 1
2. Block 2
3.  

To contrast data access in a heap-organized table to an index-organized table, suppose block 1 of a heap-organized departments table segment contains rows as follows:

50,Shipping,121,1500

20,Marketing,201,1800

Block 2 contains rows for the same table as follows:

30,Purchasing,114,1700

60,IT,103,1400

A B-tree index leaf block for this heap-organized table contains the following entries, where the first value is the primary key and the second is the rowid:

20,AAAPeXAAFAAAAAyAAD

30,AAAPeXAAFAAAAAyAAA

50,AAAPeXAAFAAAAAyAAC

60,AAAPeXAAFAAAAAyAAB

A scan of the table rows in primary key order reads the table segment blocks in the following sequence:

1. Block 1
2. Block 2
3. Block 1
4. Block 2

Thus, the number of block I/Os in this example is double the number in the index-organized example.

Index-Organized Tables with Row Overflow Area

When creating an index-organized table, you can specify a separate segment as a row overflow area. In index-organized tables, B-tree index entries can be large because they contain an entire row, so a separate segment to contain the entries is useful. In contrast, B-tree entries are usually small because they consist of the key and rowid.

If a row overflow area is specified, then the database can divide a row in an index-organized table into the following parts:

·         The index entry

This part contains column values for all the primary key columns, a physical rowid that points to the overflow part of the row, and optionally a few of the non-key columns. This part is stored in the index segment.

·         The overflow part

This part contains column values for the remaining non-key columns. This part is stored in the overflow storage area segment.

Secondary Indexes on Index-Organized Tables

A secondary index is an index on an index-organized table. In a sense, it is an index on an index. The secondary index is an independent schema object and is stored separately from the index-organized table.

Oracle Database uses row identifiers called logical rowids for index-organized tables. A logical rowid is a base64-encoded representation of the table primary key. The logical rowid length depends on the primary key length.

Rows in index leaf blocks can move within or between blocks because of insertions. Rows in index-organized tables do not migrate as heap-organized rows do . Because rows in index-organized tables do not have permanent physical addresses, the database uses logical rowids based on primary key.

For example, assume that the departments table is index-organized. The location_id column stores the ID of each department. The table stores rows as follows, with the last value as the location ID:

10,Administration,200,1700

20,Marketing,201,1800

30,Purchasing,114,1700

40,Human Resources,203,2400

A secondary index on the location_id column might have index entries as follows, where the value following the comma is the logical rowid:

1700,*BAFAJqoCwR/+ 

1700,*BAFAJqoCwQv+

1800,*BAFAJqoCwRX+

2400,*BAFAJqoCwSn+

Secondary indexes provide fast and efficient access to index-organized tables using columns that are neither the primary key nor a prefix of the primary key. For example, a query of the names of departments whose ID is greater than 1700 could use the secondary index to speed data access.

Why Secondary Indexes have no ROWID

A direct pointer to the table row would be desirable for a secondary index as well. But that is only possible, if the table row stays at fixed storage positions. That is, unfortunately, not possible if the row is part of an index structure, which is kept in order. Keeping the index order needs to move rows occasionally. This is also true for operations that do not affect the row itself. An insert statement, for example, might split a leaf node to gain space for the new entry. That means that some entries are moved to a new data block at a different place.

A heap table, on the other hand, doesn’t keep the rows in any order. The database saves new entries wherever it finds enough space. Once written, data doesn’t move in heap tables.

Accessing a secondary index does not deliver a ROWID but a logical key for searching the clustered index. A single access, however, is not sufficient for searching clustered index—it requires a full tree traversal. That means that accessing a table via a secondary index searches two indexes: the secondary index once (INDEX RANGE SCAN), then the clustered index for each row found in the secondary index (INDEX UNIQUE SCAN).

Figure: Secondary Index on an IOT

Figure makes it clear, that the B-tree of the clustered index stands between the secondary index and the table data.

Important

Accessing an index-organized table via a secondary index is very inefficient.

Accessing an index-organized table via a secondary index is very inefficient, and it can be prevented in the same way one prevents a table access on a heap table: by using an index-only scan—in this case better described as “secondary-index-only scan”. The performance advantage of an index-only scan is even bigger because it not only prevents a single access but an entire INDEX UNIQUE SCAN.

Using this example we can also see that databases exploit all the redundancies they have. Bear in mind that a secondary index stores the clustering key for each index entry. Consequently, we can query the clustering key from a secondary index without accessing the index-organized table:

SELECT sale_id

  FROM sales_iot

 WHERE sale_date = ?

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

| Id | Operation        | Name           | Cost |

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

|  0 | SELECT STATEMENT |                |    4 |

|* 1 |  INDEX RANGE SCAN| SALES_IOT_DATE |    4 |

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

Predicate Information (identified by operation id):

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

   1 - access("SALE_DATE"=:DT)

The table SALES_IOT is an index-organized table that uses SALE_ID as clustering key. Although the index SALE_IOT_DATE is on the SALE_DATE column only, it still has a copy of the clustering key SALE_ID so it can satisfy the query using the secondary index only.

When selecting other columns, the database has to run an INDEX UNIQUE SCAN on the clustered index for each row:

SELECT eur_value

  FROM sales_iot

 WHERE sale_date = ?

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

| Id  | Operation         | Name           | Cost |

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

|   0 | SELECT STATEMENT  |                |   13 |

|*  1 |  INDEX UNIQUE SCAN| SALES_IOT_PK   |   13 |

|*  2 |   INDEX RANGE SCAN| SALES_IOT_DATE |    4 |

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

Predicate Information (identified by operation id):

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

   1 - access("SALE_DATE"=:DT)

   2 - access("SALE_DATE"=:DT)

Index-organized tables and clustered indexes are, after all, not as useful as it seems at first sight. Performance improvements on the clustered index are easily lost on when using a secondary index. The clustering key is usually longer than a ROWID so that the secondary indexes are larger than they would be on a heap table, often eliminating the savings from the omission of the heap table. The strength of index-organized tables and clustered indexes is mostly limited to tables that do not need a second index. Heap tables have the benefit of providing a stationary master copy that can be easily referenced.

Important

Tables with one index only are best implemented as clustered indexes or index-organized tables.

Tables with more indexes can often benefit from heap tables. You can still use index-only scans to avoid the table access. This gives you the select performance of a clustered index without slowing down other indexes.

The Oracle database uses heap tables by default. Index-organized tables can be created using the ORGANIZATION INDEX clause:

CREATE TABLE (

   id    NUMBER NOT NULL PRIMARY KEY,

   [...]

) ORGANIZATION INDEX

The Oracle database always uses the primary key as the clustering key.

Logical Rowids and Physical Guesses

Secondary indexes use the logical rowids to locate table rows. A logical rowid includes a physical guess, which is the physical rowid of the index entry when it was first made. Oracle Database can use physical guesses to probe directly into the leaf block of the index-organized table, bypassing the primary key search. When the physical location of a row changes, the logical rowid remains valid even if it contains a physical guess that is stale.

For a heap-organized table, access by a secondary index involves a scan of the secondary index and an additional I/O to fetch the data block containing the row. For index-organized tables, access by a secondary index varies, depending on the use and accuracy of physical guesses:

·         Without physical guesses, access involves two index scans: a scan of the secondary index followed by a scan of the primary key index.

·         With physical guesses, access depends on their accuracy:

o    With accurate physical guesses, access involves a secondary index scan and an additional I/O to fetch the data block containing the row.

o    With inaccurate physical guesses, access involves a secondary index scan and an I/O to fetch the wrong data block (as indicated by the guess), followed by an index unique scan of the index organized table by primary key value.

Bitmap Indexes on Index-Organized Tables

A secondary index on an index-organized table can be a bitmap index. As explained in "Bitmap Indexes", a bitmap index stores a bitmap for each index key.

When bitmap indexes exist on an index-organized table, all the bitmap indexes use a heap-organized mapping table. The mapping table stores the logical rowids of the index-organized table. Each mapping table row stores one logical rowid for the corresponding index-organized table row.

The database accesses a bitmap index using a search key. If the database finds the key, then the bitmap entry is converted to a physical rowid. With heap-organized tables, the database uses the physical rowid to access the base table. With index-organized tables, the database uses the physical rowid to access the mapping table, which in turn yields a logical rowid that the database uses to access the index-organized table. Figure 2 illustrates index access for a query of the departments_iot table.

Figure 2 Bitmap Index on Index-Organized Table

Note:

Movement of rows in an index-organized table does not leave the bitmap indexes built on that index-organized table unusable.

I hope you all have enjoyed reading this article. Comments are welcome....

Partitioning in Oracle 11G

Partitioning is one of the most sought after options for data warehousing. Almost all Oracle data warehouses use partitioning to improve the performance of queries and also to ease the day-to-day maintenance complexities. Starting with 11G, more partitioning options have been provided and these should reduce the burden of the DBA to a great extent.

This article should be helpful to DBAs and Developers who work primarily in a data warehousing environment. The new features provided by Oracle in 11G should enhance the partitioning options and provide more flexibility for partition usage and maintenance.

Table partitioning was first introduced in Oracle version 8.0 and Oracle was the first RDBMS vendor to support physical partitioning. Both SQL Server(2000) and DB2 provide only logical partitions (using the UNION ALL views) while SQL Server 2005 supports physical partitioning though not directly (implemented through partition functions). The partitioning option in Oracle was overwhelmingly received by the user community as it improved the performance, manageability and availability of applications and most importantly, DSS applications. The wide appreciation of this feature has resulted in frequent enhancements through subsequent releases.

The following table lists the partitioning features (high level) that have evolved over each version/release:

Oracle Database Version	Partitioning Features
8.0.5	Introduced Range Partitioning
8i	Introduced Hash and composite Range-Hash partitioning.
9i	Introduced List Partitioning, Composite Range-List partitioning.
10G	Introduced Range, List and Hash partitioning of Index Organized tables. Also introduced other composite partitioning options.
11G	Introduced partition extensions: -Interval partitioning -REF partitioning -Virtual Column-based partitioning -Introduced Partition Advisor.

Partitioning Strategies and Extensions at a Glance

The following table gives a conceptual overview of all available basic partitioning

strategies in Oracle Database 11g:

In addition to the available partitioning strategies, Oracle Database 11g provides the following partitioning extensions:

Table partitioning methods

Table partitioning already exists since 8i, but a lot of new possibilities were added since 11g. The different types of partitioning are:

Range partitioning

The table is divided in ranges, typically used for date ranges. This is beneficial when the filters using inbetween, greater than or less than. It is able to skip all partitions not in the range.

CREATE TABLE orders_range(order_id NUMBER 

                         ,client_id NUMBER 

                         ,order_date DATE) 

  PARTITION BY RANGE(order_date) 

    (PARTITION orders2011 VALUES LESS THAN (to_date('1/1/2012','dd/mm/yyyy')) 

    ,PARTITION orders2012 VALUES LESS THAN (to_date('1/1/2013','dd/mm/yyyy')) 

    ,PARTITION orders2013 VALUES LESS THAN (MAXVALUE));

Note :  When inserting a record that does not belong to any of the partitions,  an ORA-14400 error will be raised. If you create a partition with “MAXVALUE”, it will be used as default partition. New partitions can be added or dropped manually when needed, or they can be created automatically with interval partitioning.

CREATE TABLE orders_range(order_id NUMBER 

                         ,client_id NUMBER 

                         ,order_date DATE)  

  PARTITION BY RANGE(order_date)

    INTERVAL (NUMTOYMINTERVAL(1,'year'))

    (PARTITION orders2011 VALUES LESS THAN (to_date('1/1/2012','dd/mm/yyyy'))

    ,PARTITION orders2012 VALUES LESS THAN (to_date('1/1/2013','dd/mm/yyyy')));

 

List partitioning

A limited set of possible values is given; rows containing the same value are grouped. This can be used for columns with few distinct values, when all values are known, like department or country. It can be beneficial to group values together that are combined in filters.

To avoid the ORA-14400 error, one can use the “DEFAULT”-keyword.

CREATE TABLE clients_list(client_id NUMBER

                         ,name VARCHAR2(50)

                         ,country VARCHAR2(2))

  PARTITION BY LIST(country)

    (PARTITION clients_benelux VALUES ('BE','NE','LU')

    ,PARTITION clients_uk      VALUES ('UK')

    ,PARTITION clients_other   VALUES (DEFAULT));

 

Hash partitioning

A value is hashed, and random distribution occurs. This is used with many distinct values, when there are no searches on ranges. The advantages can be a more evenly distribution than with range partitioning.

CREATE TABLE clients_hash(client_id NUMBER

                         ,name VARCHAR2(50)

                         ,country VARCHAR2(2))

  PARTITION BY HASH(name)

  PARTITIONS 5;

 

Composite partitioning

A partition can be subpartitioned by any of the previous methods, in any combination since 11g. This will allow more queries to benefit from table partitioning. Partition pruning will occur when filtering on both keys or only one of the keys.

CREATE TABLE clients_hl(client_id NUMBER

                       ,name      VARCHAR2(50)

                       ,country   VARCHAR2(2))

  PARTITION BY LIST(country)

  SUBPARTITION BY HASH(name)

    SUBPARTITIONS 5

    (PARTITION clients_benelux VALUES ('BE','NE','LU')

    ,PARTITION clients_uk      VALUES ('UK')

    ,PARTITION clients_other   VALUES (DEFAULT));

 

Refpartitioning

Partitioning is also possible on parent and child relations. When the parent table is partitioned, the child table can be partitioned based on the foreign key. For instance when the “ORDERS” table is partitioned on “ORDER_DATE”, the “ORDER_LINES” table can be partitioned on the order date as well, without storing the actual value in the “ORDER_LINES” table. This is very beneficial when joining the two tables on the foreign key.

CREATE TABLE orders_range(order_id NUMBER PRIMARY KEY

                         ,order_date DATE)  

  PARTITION BY RANGE(order_date) 

    (PARTITION orders2011 VALUES LESS THAN (to_date('1/1/2012','dd/mm/yyyy')) 

    ,PARTITION orders2012 VALUES LESS THAN (to_date('1/1/2013','dd/mm/yyyy')) 

    ,PARTITION orders2013 VALUES LESS THAN (MAXVALUE));

CREATE TABLE order_lines (order_line_id NUMBER PRIMARY KEY

                         ,order_id NUMBER NOT NULL

                         ,line VARCHAR2(50)

                         ,CONSTRAINTo_ol_fk FOREIGN KEY (order_id) REFERENCES orders_range(order_id))

  PARTITION BY REFERENCE (o_ol_fk);

Extended Composite Partitioning

In previous releases of Oracle, composite partitioning was limited to Range-Hash and Range-List partitioning. Oracle 11g Release 1 extends this to allow the following composite partitioning schemes:

• Range-Hash (available since 8i)
• Range-List (available since 9i)
• Range-Range
• List-Range
• List-Hash
• List-List

Interval partitioning, described below, is a form of range partitioning, so the previous list also implies the following combinations:

• Interval-Hash
• Interval-List
• Interval-Range

The follow code provides an example of one of the new composite partitioning schemes. First we create a table with List-Hash composite partitioning.

CREATE TABLE list_hash_tab (

  id           NUMBER,

  code         VARCHAR2(10),

  description  VARCHAR2(50),

  created_date DATE

)

PARTITION BY LIST (code)

SUBPARTITION BY HASH (id)

(

   PARTITION part_aa values ('AA')

   (

      SUBPARTITION part_aa_01,

      SUBPARTITION part_aa_02

   ),

   partition part_bb values ('BB')

   (

      SUBPARTITION part_bb_01,

      SUBPARTITION part_bb_02

   )

);

Next we populate it with some data, which we expect to be spread throughout the subpartitions.

DECLARE

  l_code  VARCHAR2(10);

BEGIN

  FOR i IN 1 .. 40 LOOP

    IF MOD(i, 2) = 0 THEN

      l_code := 'BB';

    ELSE

      l_code := 'AA';

    END IF;

    INSERT INTO list_hash_tab (id, code, description, created_date)

    VALUES (i, l_code, 'Description for ' || i || ' ' || l_code, SYSDATE);

  END LOOP;

  COMMIT;

END;

/

EXEC DBMS_STATS.gather_table_stats(USER, 'LIST_HASH_TAB', granularity=>'ALL');

Finally, we query the USER_TAB_SUBPARTITIONS view to see if the data has been distributed across the subpartitions.

COLUMN table_name FORMAT A20

COLUMN partition_name FORMAT A20

COLUMN subpartition_name FORMAT A20

SELECT table_name, partition_name, subpartition_name, num_rows

FROM   user_tab_subpartitions

ORDER by table_name, partition_name, subpartition_name;

TABLE_NAME           PARTITION_NAME       SUBPARTITION_NAME      NUM_ROWS

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

LIST_HASH_TAB        PART_AA              PART_AA_01                    7

LIST_HASH_TAB        PART_AA              PART_AA_02                   13

LIST_HASH_TAB        PART_BB              PART_BB_01                   10

LIST_HASH_TAB        PART_BB              PART_BB_02                   10

4 rows selected.

SQL>

The query shows the data has been split into the two partitions based on the vale of the CODE column, then hashed between the subpartitions.

Interval Partitioning

Interval partitioning is an extension of range partitioning, where the system is able to create new partitions as they are required. The PARTITION BY RANGE clause is used in the normal way to identify the transition point for the partition, then the new INTERVAL clause used to calculate the range for new partitions when the values go beyond the existing transition point.

The following code shows an example of a table using interval partitioning.

CREATE TABLE interval_tab (

  id           NUMBER,

  code         VARCHAR2(10),

  description  VARCHAR2(50),

  created_date DATE

)

PARTITION BY RANGE (created_date)

INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))

(

   PARTITION part_01 values LESS THAN (TO_DATE('01-NOV-2007','DD-MON-YYYY'))

);

Querying the USER_TAB_PARTITIONS view shows there is only a single partition.

EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');11g

COLUMN table_name FORMAT A20

COLUMN partition_name FORMAT A20

COLUMN high_value FORMAT A40

SELECT table_name, partition_name, high_value, num_rows

FROM   user_tab_partitions

ORDER BY table_name, partition_name;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS

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

INTERVAL_TAB         PART_01              TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M          0

                                          M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

1 row selected.

SQL>

Provided we insert data with a created_date value less than '01-NOV-2007' the data will be placed in the existing partition and no new partitions will be created.

INSERT INTO interval_tab VALUES (1, 'ONE', 'One', TO_DATE('16-OCT-2007', 'DD-MON-YYYY'));

INSERT INTO interval_tab VALUES (2, 'TWO', 'Two', TO_DATE('31-OCT-2007', 'DD-MON-YYYY'));

COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');

SELECT table_name, partition_name, high_value, num_rows

FROM   user_tab_partitions

ORDER BY table_name, partition_name;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS

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

INTERVAL_TAB         PART_01              TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M          2

                                          M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

1 row selected.

SQL>

If we add data beyond the range of the existing partition, a new partition is created.

INSERT INTO interval_tab VALUES (3, 'THREE', 'Three', TO_DATE('01-NOV-2007', 'DD-MON-YYYY'));

INSERT INTO interval_tab VALUES (4, 'FOUR', 'Four', TO_DATE('30-NOV-2007', 'DD-MON-YYYY'));

COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');

SELECT table_name, partition_name, high_value, num_rows

FROM   user_tab_partitions

ORDER BY table_name, partition_name;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS

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

INTERVAL_TAB         PART_01              TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M          2

                                          M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

INTERVAL_TAB         SYS_P44              TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-M          2

                                          M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

11g

2 rows selected.

SQL>

If we insert data for two months after the current largest transition point, only the required partition is created, not the intervening partitions.

INSERT INTO interval_tab VALUES (5, 'FIVE', 'Five', TO_DATE('01-JAN-2008', 'DD-MON-YYYY'));

INSERT INTO interval_tab VALUES (4, 'FOUR', 'Four', TO_DATE('31-JAN-2008', 'DD-MON-YYYY'));

COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');

SELECT table_name, partition_name, high_value, num_rows

FROM   user_tab_partitions

ORDER BY table_name, partition_name;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS

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

INTERVAL_TAB         PART_01              TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M          2

                                          M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

INTERVAL_TAB         SYS_P44              TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-M          2

                                          M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

INTERVAL_TAB         SYS_P45              TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-M          2

                                          M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

3 rows selected.

SQL>

Notice that a partition to hold the December (less than 1st January 2008) data has not been created. If we insert data for this time period, the missing partition is created.

INSERT INTO interval_tab VALUES (7, 'SEVEN', 'Seven', TO_DATE('01-DEC-2007', 'DD-MON-YYYY'));

INSERT INTO interval_tab VALUES (8, 'EIGHT', 'Eight', TO_DATE('31-DEC-2007', 'DD-MON-YYYY'));

COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');

SELECT table_name, partition_name, high_value, num_rows

FROM   user_tab_partitions

ORDER BY table_name, partition_name;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS

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

INTERVAL_TAB         PART_01              TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M          2

                                          M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

INTERVAL_TAB         SYS_P44              TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-M          2

                                          M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

INTERVAL_TAB         SYS_P45              TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-M          2

                                          M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

INTERVAL_TAB         SYS_P46              TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-M          2

                                          M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

4 rows selected.

SQL>

The following restrictions apply to interval partitioned tables:

• Interval partitioning is restricted to a single partition key that must be a numerical or date range.
• At least one partition must be defined when the table is created.
• Interval partitioning is not supported for index-organized tables.
• You cannot create a domain index on an interval partitioned table.
• Interval partitioning can be used as the primary partitioning mechanism in composite partitioning, but it can't be used at the subpartition level.
• A MAXVALUE partition cannot be defined for an interval partitioned table.
• NULL values are not allowed in the partition column.

 System Partitioning

As you would expect, system partitioning allows large tables to be broken down into smaller partitions, but unlike other partitioning schemes, the database has no control over the placement of rows during insert operations. The following example shows the creation of a system partitioned table.

CREATE TABLE system_partitioned_tab (

  id           NUMBER,

  code         VARCHAR2(10),

  description  VARCHAR2(50),

  created_date DATE

)

PARTITION BY SYSTEM

(

  PARTITION part_1,

  PARTITION part_2

);

The partition must be explicitly defined in all insert statements or an error is produced.

INSERT INTO system_partitioned_tab VALUES (1, 'ONE', 'One', SYSDATE);

            *

ERROR at line 1:

ORA-14701: partition-extended name or bind variable must be used for DMLs on tables partitioned by

the System method

SQL>

The PARTITION clause is used to define which partition the row should be placed in.

INSERT INTO system_partitioned_tab PARTITION (part_1) VALUES (1, 'ONE', 'One', SYSDATE);

INSERT INTO system_partitioned_tab PARTITION (part_2) VALUES (2, 'TWO', 'Two', SYSDATE);

COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER, 'SYSTEM_PARTITIONED_TAB');

COLUMN table_name FORMAT A25

COLUMN partition_name FORMAT A20

COLUMN high_value FORMAT A10

SELECT table_name, partition_name, high_value, num_rows

FROM   user_tab_partitions

ORDER BY table_name, partition_name;

TABLE_NAME                PARTITION_NAME       HIGH_VALUE   NUM_ROWS

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

SYSTEM_PARTITIONED_TAB    PART_1                                   1

SYSTEM_PARTITIONED_TAB    PART_2                                   1

2 rows selected.

SQL>

Notice that the HIGH_VALUE for the partitions is blank.

The PARTITION clause is optional for update and delete statements, but omitting this clause will force all partitions to be scanned, since there is no way perform automatic partition pruning when the database has no control over row placement. When the PARTITION clause is used, you must be sure to perform the operation against the correct partition.

SQL> DELETE FROM system_partitioned_tab PARTITION (part_2) WHERE id = 1;

0 rows deleted.

SQL> UPDATE system_partitioned_tab PARTITION (part_1) SET code = 'TWO' WHERE id = 2;

0 rows deleted.

SQL>

The PARTITION clause can also be used in queries to target specific partitions.

SELECT COUNT(*)

FROM   system_partitioned_tab PARTITION (part_1);

  COUNT(*)

----------

         1

1 row selected.

SQL>

System partitioning gives you many of the advantages of partitioning, but leaves the decision of how the data is partitioned to the application layer.

Conditions and restrictions on system partitioning include:

• If you specify the PARTITION BY SYSTEM clause, but don't define partitions, a single partition is created with the name in the format of "SYS_Pn".
• If you specify PARTITION BY SYSTEM PARTITIONS n clause, the database creates "n" partitions with the name in the format of "SYS_Pn". The range of allowable values for "n" is from 1 to 1024K-1.
• System partitioning is not available for index-organized tables or a table that is part of a cluster.
• System partitioning can play no part in composite partitioning.
• You cannot split a system partition.
• System partitioning cannot be specified in a CREATE TABLE ... AS SELECT statement.
• To insert data into a system-partitioned table using an INSERT INTO ... AS subquery statement, you must use partition-extended syntax to specify the partition into which the values returned by the subquery will be inserted.

 Reference Partitioning

Reference partitioning allows tables related by foreign keys to be logically equi-partitioned. The child table is partitioned using the same partitioning key as the parent table without having to duplicate the key columns. Partition maintenance operations performed on the parent table are reflected on the child table, but no partition maintenance operations are allowed on the child table.

The following code contains a partitioned parent table and a dependent reference partitioned child table.

CREATE TABLE parent_tab (

  id           NUMBER NOT NULL,

  code         VARCHAR2(10) NOT NULL,

  description  VARCHAR2(50),

  created_date DATE,

  CONSTRAINT parent_tab_pk PRIMARY KEY (id)

)

PARTITION BY RANGE (created_date)

(

   PARTITION part_2007 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY')),

   PARTITION part_2008 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY'))

);

CREATE TABLE child_tab (

  id             NUMBER NOT NULL,

  parent_tab_id  NUMBER NOT NULL,

  code           VARCHAR2(10),

  description    VARCHAR2(50),

  created_date   DATE,

  CONSTRAINT child_tab_pk PRIMARY KEY (id),

  CONSTRAINT child_parent_tab_fk FOREIGN KEY (parent_tab_id)

                               REFERENCES parent_tab (id)

)

PARTITION BY REFERENCE (child_parent_tab_fk);

Child records that foreign key to rows in the first partition of the parent table should be placed in the first partition of the child table. So we insert two rows into the first partition and one row into the second of the parent table. We then insert three rows into the child table, with one foreign keyed to a row in the first partition and two foreign keyed to a row in the second partition of the master table.

INSERT INTO parent_tab VALUES (1, 'ONE', '1 ONE', SYSDATE);

INSERT INTO parent_tab VALUES (2, 'TWO', '2 TWO', SYSDATE);

INSERT INTO parent_tab VALUES (3, 'THREE', '3 THREE', ADD_MONTHS(SYSDATE,12));

INSERT INTO child_tab VALUES (1, 1, 'ONE', '1 1 ONE', SYSDATE);

INSERT INTO child_tab VALUES (2, 3, 'TWO', '2 3 TWO', SYSDATE);

INSERT INTO child_tab VALUES (3, 3, 'THREE', '3 3 THREE', SYSDATE);

COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER, 'PARENT_TAB');

EXEC DBMS_STATS.gather_table_stats(USER, 'CHILD_TAB');

We now expect the parent table to have 2 records in the 2007 partition and 1 in the 2008 partition, while the child table should have 1 row in the 2007 partition and 2 rows in the 2008 partition. The following query confirms out expectation.

COLUMN table_name FORMAT A25

COLUMN partition_name FORMAT A20

COLUMN high_value FORMAT A40

SELECT table_name, partition_name, high_value, num_rows

FROM   user_tab_partitions

ORDER BY table_name, partition_name;

TABLE_NAME                PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS

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

CHILD_TAB                 PART_2007                                                              1

CHILD_TAB                 PART_2008                                                              2

PARENT_TAB                PART_2007            TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-M          2

                                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

PARENT_TAB                PART_2008            TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-M          1

                                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

4 rows selected.

SQL>

The following conditions and restrictions apply to reference partitioning:

• The child table must specify a referential integrity constraint defined on the table being created. This constraint must be in ENABLE VALIDATE NOT DEFERRABLE state (the default) and refer to a primary or unique key on the parent table.
• The foreign key columns referenced in constraint must be NOT NULL.
• The constraint cannot use the ON DELETE SET NULL clause.
• The parent table referenced must be an existing partitioned table. All partitioning methods except interval partitioning are supported.
• The foreign key cannot contain any virtual columns.
• The referenced primary key or unique constraint on the parent table cannot contain any virtual columns.
• Reference partitioning cannot be used for index-organized tables, external tables, or domain index storage tables.
• A chain of reference partitioned tables can be created, but constraint used can't be self-referencing.
• The ROW MOVEMENT setting for both tables must match.
• Reference partitioning cannot be specified in a CREATE TABLE ... AS SELECT statement.

 Virtual Column-Based Partitioning

Oracle 11g supports the concept of virtual columns on tables. These virtual columns are not physically stored in the table, but derived from data in the table. These virtual columns can be used in the partition key in all basic partitioning schemes. The example below creates a table that is list partitioned on a virtual column that represents the first letter in the username column of the table.

CREATE TABLE users (

  id           NUMBER,

  username     VARCHAR2(20),

  first_letter VARCHAR2(1)

    GENERATED ALWAYS AS

      (

        UPPER(SUBSTR(TRIM(username), 1, 1))

      ) VIRTUAL

)

PARTITION BY LIST (first_letter)

(

  PARTITION part_a_g VALUES ('A','B','C','D','E','F','G'),

  PARTITION part_h_n VALUES ('H','I','J','K','L','M','N'),

  PARTITION part_o_u VALUES ('O','P','Q','R','S','T','U'),

  PARTITION part_v_z VALUES ('V','W','X','Y','Z')

);

The following code inserts two rows into each partition defined in the table.

INSERT INTO users (id, username) VALUES (1, 'Andy Pandy');

INSERT INTO users (id, username) VALUES (1, 'Burty Basset');

INSERT INTO users (id, username) VALUES (1, 'Harry Hill');

INSERT INTO users (id, username) VALUES (1, 'Iggy Pop');

INSERT INTO users (id, username) VALUES (1, 'Oliver Hardy');

INSERT INTO users (id, username) VALUES (1, 'Peter Pervis');

INSERT INTO users (id, username) VALUES (1, 'Veruca Salt');

INSERT INTO users (id, username) VALUES (1, 'Wiley Cyote');

COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER, 'USERS');

The following query shows the data was distributed as expected.

COLUMN table_name FORMAT A25

COLUMN partition_name FORMAT A20

COLUMN high_value FORMAT A40

SELECT table_name, partition_name, high_value, num_rows

FROM   user_tab_partitions

ORDER BY table_name, partition_name;

TABLE_NAME                PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS

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

USERS                     PART_A_G             'A', 'B', 'C', 'D', 'E', 'F', 'G'                 2

USERS                     PART_H_N             'H', 'I', 'J', 'K', 'L', 'M', 'N'                 2

USERS                     PART_O_U             'O', 'P', 'Q', 'R', 'S', 'T', 'U'                 2

USERS                     PART_V_Z             'V', 'W', 'X', 'Y', 'Z'                           2

4 rows selected.

SQL>

e.g.: Adding a virtual column to the table ACCOUNTS:

SQL>CREATE TABLE ACCOUNTS

(acc_no number(10) not null,

acc_name varchar2(50) not null, 

acc_loc  varchar2(5),

acc_branch number(2) generated always as

(to_number(substr(to_char(acc_no),1,2)));

Using the virtual column as the partitioning key:

SQL>CREATE TABLE accounts

(acc_no number(10) not null,

acc_name varchar2(50) not null, 

acc_loc varchar2(5),

acc_branch number(2) generated always as

(to_number(substr(to_char(acc_no),1,2)))

partition by list (acc_branch);

 

Single Partition Transportable for Oracle Data Pump

Oracle 11g allows single table partitions to be transported between databases, rather than requiring the whole table to be transported. To show this in action we need to create two tablespaces to hold the table partitions and give the test use a quota on these tablespaces.

CONN sys/password@db11g AS SYSDBA

CREATE TABLESPACE transport_test_ts_1

  DATAFILE '/u01/app/oracle/oradata/DB11G/tt_ts_1'

  SIZE 128K AUTOEXTEND ON NEXT 128K;

CREATE TABLESPACE transport_test_ts_2

  DATAFILE '/u01/app/oracle/oradata/DB11G/tt_ts_2'

  SIZE 128K AUTOEXTEND ON NEXT 128K;

ALTER USER test

  QUOTA UNLIMITED ON transport_test_ts_1

  QUOTA UNLIMITED ON transport_test_ts_2;

CONN test/test@db11g

Next, we create and populate a test partitioned table.

CREATE TABLE transport_test_tab (

  id           NUMBER NOT NULL,

  code         VARCHAR2(10) NOT NULL,

  description  VARCHAR2(50),

  created_date DATE,

  CONSTRAINT transport_test_pk PRIMARY KEY (id)

)

PARTITION BY RANGE (created_date)

(

   PARTITION part_2007 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY'))

     TABLESPACE transport_test_ts_1,

   PARTITION part_2008 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY'))

     TABLESPACE transport_test_ts_2

);

INSERT INTO transport_test_tab VALUES (1, 'ONE', '1 ONE', SYSDATE);

INSERT INTO transport_test_tab VALUES (2, 'TWO', '2 TWO', SYSDATE);

INSERT INTO transport_test_tab VALUES (3, 'THREE', '3 THREE', ADD_MONTHS(SYSDATE,12));

INSERT INTO transport_test_tab VALUES (4, 'FOUR', '4 FOUR', ADD_MONTHS(SYSDATE,12));

COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER, 'TRANSPORT_TEST_TAB');

The following query shows that each partition is on a separate tablespace and contains some data.

COLUMN table_name FORMAT A20

COLUMN partition_name FORMAT A20

COLUMN tablespace_name FORMAT A20

SELECT table_name, partition_name, tablespace_name, num_rows

FROM   user_tab_partitions;

TABLE_NAME           PARTITION_NAME       TABLESPACE_NAME        NUM_ROWS

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

TRANSPORT_TEST_TAB   PART_2007            TRANSPORT_TEST_TS_1           2

TRANSPORT_TEST_TAB   PART_2008            TRANSPORT_TEST_TS_2           2

2 rows selected.

SQL>

Any tablespaces we wish to transport must be made read only.

CONN sys/password@db11g AS SYSDBA

ALTER TABLESPACE transport_test_ts_1 READ ONLY;

When we perform the data pump export, we can specify the individual partition we wish to export using the following syntax.

tables=schema.table:partition transportable=always

Notice the ":partition" section of the TABLES parameter. The TRANSPORTABLE parameter indicates that we wish to capture just the metadata for the partiton.

We can now run the Data Pump export using the following command.

expdp system/password tables=test.transport_test_tab:part_2007 transportable=always

  directory=data_pump_dir dumpfile=part_2007.dmp

The output is displayed below.

$ expdp system/password tables=test.transport_test_tab:part_2007 transportable=always

  directory=data_pump_dir dumpfile=part_2007.dmp

Export: Release 11.1.0.6.0 - Production on Friday, 19 October, 2007 16:40:45

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** tables=test.transport_test_tab:part_2007

  transportable=always directory=data_pump_dir dumpfile=part_2007.dmp

Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK

Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:

  /u01/app/oracle/admin/DB11G/dpdump/part_2007.dmp

******************************************************************************

Datafiles required for transportable tablespace TRANSPORT_TEST_TS_1:

  /u01/app/oracle/oradata/DB11G/tt_ts_1

Datafiles required for transportable tablespace USERS:

  /u01/app/oracle/oradata/DB11G/users01.dbf

Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 16:40:55

$

To simulate transporting the tablespace, we will drop the existing table and tablespaces. Notice, that we don't actually remove the datafile associated with the first tablespace, as this is the datafile containing our transportable partition.

CONN sys/password@db11g AS SYSDBA

DROP TABLE test.transport_test_tab;

DROP TABLESPACE transport_test_ts_1 INCLUDING CONTENTS;

DROP TABLESPACE transport_test_ts_2 INCLUDING CONTENTS AND DATAFILES;

We can now import the dump file to snap in our transportable partition using the PARTITION_OPTIONS=DEPARTITION parameter setting, which converts all partitions into standalone table segments.

impdp system/password partition_options=departition dumpfile=part_2007.dmp

  transport_datafiles='/u01/app/oracle/oradata/DB11G/tt_ts_1'

The expected output is displayed below.

$ impdp system/password partition_options=departition dumpfile=part_2007.dmp

  transport_datafiles='/u01/app/oracle/oradata/DB11G/tt_ts_1'

Import: Release 11.1.0.6.0 - Production on Friday, 19 October, 2007 16:47:04

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** partition_options=departition

  dumpfile=part_2007.dmp transport_datafiles=/u01/app/oracle/oradata/DB11G/tt_ts_1

Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

ORA-39083: Object type INDEX failed to create with error:

ORA-00942: table or view does not exist

Failing sql is:

CREATE UNIQUE INDEX "TEST"."TRANSPORT_TEST_PK" ON "TEST"."TRANSPORT_TEST_TAB" ("ID") PCTFREE 10

INITRANS 2 MAXTRANS 255  STORAGE(SEG_FILE 4 SEG_BLOCK 59 OBJNO_REUSE 70550 INITIAL 65536

NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT) TABLESPACE "USERS" PARALLEL 1

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

ORA-39083: Object type CONSTRAINT failed to create with error:

ORA-00942: table or view does not exist

Failing sql is:

ALTER TABLE "TEST"."TRANSPORT_TEST_TAB" ADD CONSTRAINT "TRANSPORT_TEST_PK" PRIMARY KEY ("ID")

USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255  STORAGE(SEG_FILE 4 SEG_BLOCK 59 OBJNO_REUSE 70550

INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1

FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"  ENABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type

INDEX:"TEST"."TRANSPORT_TEST_PK" creation failed

Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK

Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" completed with 3 error(s) at 16:47:07

$

The table segment is named using a combination of the table and partition name, so dependent objects fail because they are referencing the wrong table name. We can see the new segment using the following query.

CONN test/test@db11g

EXEC DBMS_STATS.gather_schema_stats(USER);

COLUMN table_name FORMAT A30

COLUMN tablespace_name FORMAT A20

SELECT table_name, tablespace_name, partitioned, num_rows

FROM   user_tables;

TABLE_NAME                     TABLESPACE_NAME      PAR   NUM_ROWS

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

TRANSPORT_TES_PART_2007        TRANSPORT_TEST_TS_1  NO           2

1 row selected.

SQL>

The Partition Advisor

Oracle 11g also provides Partition Advisor, that supports generating partitioning recommendations similar to what was provided in 10G for materialized views, materialized view logs and indexes. In fact, partition advisor is a part of the SQL Access Advisor in oracle 11g. This advisor helps to generate recommendations that will show the anticipated performance gains that will result if the partitions are implemented. It also generates the script for creating the efficient partitions which can be manually submitted to Oracle through SQL*Plus or can be queued through Enterprise Manager.

 

With the extension of partitioning advice, customers not only can get recommendation specifically for partitioning but also a more comprehensive holistic recommendation of SQL Access Advisor, improving the collective performance of SQL statements overall.

The Partition Advisor, integrated into the SQL Access Advisor, is part of Oracle's Tuning Pack, an extra licensable option. It can be used from within Enterprise Manager or via a command line interface.

Enhanced Statistics Collection for Partitioned Objects

Oracle 11g includes improvements to statistics collection for partitioned objects so untouched partitions are not rescanned. This significantly increases the speed of statistics collection on large tables where some of the partitions contain static data. Where partition exchange load (PEL) is used to add data to the a table, only the newly added partition must be scanned to update the global statistics. 

Partitioning Indexes

There are two basic types of partitioned index.

• 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 independance. 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 support partition independance. 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.

Both types of indexes can be subdivided further.

• Prefixed - The partition key is the leftmost column(s) of the index. Probing this type of index is less costly. If a query specifies the partition key in the where clause partition pruning is possible, that is, not all partitions will be searched.
• Non-Prefixed - Does not support partition pruning, but is effective in accessing data that spans multiple partitions. Often used for indexing a column that is not the tables partition key, when you would like the index to be partitioned on the same key as the underlying table.

 

Local Prefixed Indexes

Assuming the INVOICES table is range partitioned on INVOICE_DATE, the followning are examples of local prefixed indexes.

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);

Oracle will generate the partition names and build the partitions in the default tablespace using the default size unless told otherwise.

 

Local Non-Prefixed Indexes

Assuming the INVOICES table is range partitioned on INVOICE_DATE, the following example is of a local non-prefixed index. The indexed column does not match the partition key.

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 Prefixed Indexes

Assuming the INVOICES table is range partitioned on INVOICE_DATE, the followning examples is of a global prefixed index.

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);

Note that the partition range values must be specified. The GLOBAL keyword means that Oracle can not assume the partition key is the same as the underlying table.

Global Non-Prefixed Indexes

Oracle does not support Global Non Prefixed indexes.

Partitioning Existing Tables

The ALTER TABLE ... EXCHANGE PARTITION ... syntax can be used to partition an existing table, as shown by the following example. First we must create a non-partitioned table to act as our starting point.

CREATE TABLE my_table (

  id           NUMBER,

  description  VARCHAR2(50)

);

INSERT INTO my_table (id, description) VALUES (1, 'One');

INSERT INTO my_table (id, description) VALUES (2, 'Two');

INSERT INTO my_table (id, description) VALUES (3, 'Three');

INSERT INTO my_table (id, description) VALUES (4, 'Four');

COMMIT;

Next we create a new partitioned table with a single partition to act as our destination table.

CREATE TABLE my_table_2 (

  id           NUMBER,

  description  VARCHAR2(50)

)

PARTITION BY RANGE (id)

(PARTITION my_table_part VALUES LESS THAN (MAXVALUE));

Next we switch the original table segment with the partition segment.

ALTER TABLE my_table_2

  EXCHANGE PARTITION my_table_part

  WITH TABLE my_table

  WITHOUT VALIDATION;

We can now drop the original table and rename the partitioned table.

DROP TABLE my_table;

RENAME my_table_2 TO my_table;

Finally we can split the partitioned table into multiple partitions as required and gather new statistics.

ALTER TABLE my_table SPLIT PARTITION my_table_part AT (3)

INTO (PARTITION my_table_part_1,

      PARTITION my_table_part_2);

EXEC DBMS_STATS.gather_table_stats(USER, 'MY_TABLE', cascade => TRUE);

The following query shows that the partitioning process is complete.

COLUMN high_value FORMAT A20

SELECT table_name,

       partition_name,

       high_value,

       num_rows

FROM   user_tab_partitions

ORDER BY table_name, partition_name;

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE             NUM_ROWS

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

MY_TABLE                       MY_TABLE_PART_1                3                             2

MY_TABLE                       MY_TABLE_PART_2                MAXVALUE                      2

2 rows selected.

I hope you all have enjoyed reading this article. Comments are welcome....