Как запросом вывести partition in table oracle
Перейти к содержимому

Как запросом вывести partition in table oracle

  • автор:

Partitioned Tables And Indexes

Maintenance of large tables and indexes can become very time and resource consuming. At the same time, data access performance can reduce drastically for these objects. Partitioning of tables and indexes can benefit the performance and maintenance in several ways.

  • Partition independance means backup and recovery operations can be performed on individual partitions, whilst leaving the other partitons available.
  • Query performance can be improved as access can be limited to relevant partitons only.
  • There is a greater ability for parallelism with more partitions.

Partitioning

All the examples shown here use the users tablespace for all partitions. In a real situation it is likely that these partitions would be assigned to different tablespaces to reduce device contention.

  • Range Partitioning Tables
  • Hash Partitioning Tables
  • Composite Partitioning Tables
  • Partitioning Indexes
  • Local Prefixed Indexes
  • Local Non-Prefixed Indexes
  • Global Prefixed Indexes
  • Global Non-Prefixed Indexes
  • Partitioning Existing Tables

Range Partitioning Tables

Range partitioning is useful when you have distinct ranges of data you want to store together. The classic example of this is the use of dates. Partitioning a table using date ranges allows all data of a similar age to be stored in same partition. Once historical data is no longer needed the whole partition can be removed. If the table is indexed correctly search criteria can limit the search to the partitions that hold data of a correct age.

CREATE TABLE invoices (invoice_no NUMBER NOT NULL, invoice_date DATE NOT NULL, comments VARCHAR2(500)) 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 (TO_DATE('01/01/2002', 'DD/MM/YYYY')) TABLESPACE users);

Hash Partitioning Tables

Hash partitioning is useful when there is no obvious range key, or range partitioning will cause uneven distribution of data. The number of partitions must be a power of 2 (2, 4, 8, 16. ) and can be specified by the PARTITIONS. STORE IN clause.

CREATE TABLE invoices (invoice_no NUMBER NOT NULL, invoice_date DATE NOT NULL, comments VARCHAR2(500)) PARTITION BY HASH (invoice_no) PARTITIONS 4 STORE IN (users, users, users, users);

Or specified individually.

CREATE TABLE invoices (invoice_no NUMBER NOT NULL, invoice_date DATE NOT NULL, comments VARCHAR2(500)) PARTITION BY HASH (invoice_no) (PARTITION invoices_q1 TABLESPACE users, PARTITION invoices_q2 TABLESPACE users, PARTITION invoices_q3 TABLESPACE users, PARTITION invoices_q4 TABLESPACE users);

Composite Partitioning Tables

Composite partitioning allows range partitions to be hash subpartitioned on a different key. The greater number of partitions increases the possiblities for parallelism and reduces the chances of contention. The following example will range partition the table on invoice_date and subpartitioned these on the invoice_no giving a totol of 32 subpartitions.

CREATE TABLE invoices (invoice_no NUMBER NOT NULL, invoice_date DATE NOT NULL, comments VARCHAR2(500)) PARTITION BY RANGE (invoice_date) SUBPARTITION BY HASH (invoice_no) SUBPARTITIONS 8 (PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')), PARTITION invoices_q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')), PARTITION invoices_q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')), PARTITION invoices_q4 VALUES LESS THAN (TO_DATE('01/01/2002', 'DD/MM/YYYY'));

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.

For more information see:

Hope this helps. Regards Tim.

Created: 2005-05-14 Updated: 2019-07-31

11
Partitioned Tables and Indexes

This chapter describes partitioned tables and indexes. It covers the following topics:

Note: Oracle supports partitioning only for tables, indexes on tables, materialized views, and indexes on materialized views. Oracle does not support partitioning of clustered tables or indexes on clustered tables.

Introduction to Partitioning

Partitioning addresses key issues in supporting very large tables and indexes by letting you decompose them into smaller and more manageable pieces called partitions. SQL queries and DML statements do not need to be modified in order to access partitioned tables. However, after partitions are defined, DDL statements can access and manipulate individuals partitions rather than entire tables or indexes. This is how partitioning can simplify the manageability of large database objects. Also, partitioning is entirely transparent to applications.

Each partition of a table or index must have the same logical attributes, such as column names, datatypes, and constraints, but each partition can have separate physical attributes such as pctfree, pctused, and tablespaces.

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

All partitions of a partitioned object must reside in tablespaces of a single block size.

Partitioning offers these advantages:

  • Partitioning enables data management operations such data loads, index creation and rebuilding, and backup/recovery at the partition level, rather than on the entire table. This results in significantly reduced times for these operations.
  • Partitioning improves query performance. In many cases, the results of a query can be achieved by accessing a subset of partitions, rather than the entire table. For some queries, this technique (called partitionpruning) can provide order-of-magnitude gains in performance.
  • Partitioning can significantly reduce the impact of scheduled downtime for maintenance operations. Partition independence for partition maintenance operations lets you perform concurrent maintenance operations on different partitions of the same table or index. You can also run concurrent SELECT and DML operations against partitions that are unaffected by maintenance operations.
  • Partitioning increases the availability of mission-critical databases if critical tables and indexes are divided into partitions to reduce the maintenance windows, recovery times, and impact of failures.
  • Partitioning can be implemented without requiring any modifications to your applications. For example, you could convert a nonpartitioned table to a partitioned table without needing to modify any of the SELECT statements or DML statements which access that table. You do not need to rewrite your application code to take advantage of partitioning.

Figure 11-1 offers a graphical view of how partitioned tables differ from nonpartitioned tables.

Figure 11-1 A View of Partitioned Tables

Partition Key

Each row in a partitioned table is unambiguously assigned to a single partition. The partition key is a set of one or more columns that determines the partition for each row. Oracle9i automatically directs insert, update, and delete operations to the appropriate partition through the use of the partition key. A partition key:

  • Consists of an ordered list of 1 to 16 columns
  • Cannot contain a LEVEL , ROWID , or MLSLABEL pseudocolumn or a column of type ROWID
  • Can contain columns that are NULL able

Partitioned Tables

Tables can be partitioned into up to 64,000 separate partitions. Any table can be partitioned except those tables containing columns with LONG or LONG RAW datatypes. You can, however, use tables containing columns with CLOB or BLOB datatypes.

Partitioned Index-Organized Tables

You can range partition index-organized tables. This feature is very useful for providing improved manageability, availability and performance for index-organized tables. In addition, data cartridges that use index-organized tables can take advantage of the ability to partition their stored data. Common examples of this are the Image and interMedia cartridges.

For partitioning an index-organized table:

  • Only range and hash partitioning are supported
  • Partition columns must be a subset of primary key columns
  • Secondary indexes can be partitioned — locally and globally
  • OVERFLOW data segments are always equipartitioned with the table partitions

Partitioning Methods

Oracle provides the following partitioning methods:

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

Figure 11-2 offers a graphical view of the methods of partitioning.

Figure 11-2 List, Range, and Hash Partitioning

Composite partitioning is a combination of other partitioning methods. Oracle currently supports range-hash and range-list composite partitioning. Figure 11-3 offers a graphical view of range-hash and range-list composite partitioning.

Figure 11-3 Composite Partitioning

Range Partitioning

Range partitioning maps data to partitions based on ranges of partition key values that you establish for each partition. It is the most common type of partitioning and is often used with dates. For example, you might want to partition sales data into monthly partitions.

When using range partitioning, consider the following rules:

  • Each partition has a VALUES LESS THAN clause, which specifies a noninclusive upper bound for the partitions. Any binary values of the partition key equal to or higher than this literal are added to the next higher partition.
  • All partitions, except the first, have an implicit lower bound specified by the VALUES LESS THAN clause on the previous partition.
  • A MAXVALUE literal can be defined for the highest partition. MAXVALUE represents a virtual infinite value that sorts higher than any other possible value for the partition key, including the null value.

A typical example is given in the following section. The statement creates a table ( sales_range ) that is range partitioned on the sales_date field.

Range Partitioning Example
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','DD/MM/YYYY')), PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')), PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')), PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY')) );

List Partitioning

List partitioning enables you to explicitly control how rows map to partitions. You do this by specifying a list of discrete values for the partitioning key in the description for each partition. This is different from range partitioning, where a range of values is associated with a partition and from hash partitioning, where a hash function controls the row-to-partition mapping. The advantage of list partitioning is that you can group and organize unordered and unrelated sets of data in a natural way.

The details of list partitioning can best be described with an example. In this case, let’s say you want to partition a sales table by region. That means grouping states together according to their geographical location as in the following example.

List Partitioning Example
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) );

A row is mapped to a partition by checking whether the value of the partitioning column for a row falls within the set of values that describes the partition. For example, the rows are inserted as follows:

  • ( 10 , ‘Jones’ , ‘Hawaii’ , 100 , ’05-JAN-2000′ ) maps to partition sales_west
  • ( 21 , ‘Smith’ , ‘Florida’ , 150 , ’15-JAN-2000′ ) maps to partition sales_east
  • ( 32 , ‘Lee’ , ‘Colorado’ , 130 , ’21-JAN-2000′ ) does not map to any partition in the table

Unlike range and hash partitioning, multicolumn partition keys are not supported for list partitioning. If a table is partitioned by list, the partitioning key can only consist of a single column of the table.

The DEFAULT partition enables you to avoid specifying all possible values for a list-partitioned table by using a default partition, so that all rows that do not map to any other partition do not generate an error.

Hash Partitioning

Hash partitioning enables easy partitioning of data that does not lend itself to range or list partitioning. It does this with a simple syntax and is easy to implement. It is a better choice than range partitioning when:

  • You do not know beforehand how much data maps into a given range
  • The sizes of range partitions would differ quite substantially or would be difficult to balance manually
  • Range partitioning would cause the data to be undesirably clustered
  • Performance features such as parallel DML, partition pruning, and partition-wise joins are important

The concepts of splitting, dropping or merging partitions do not apply to hash partitions. Instead, hash partitions can be added and coalesced.

Hash Partitioning Example
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 (data1, data2, data3, data4);

The preceding statement creates a table sales_hash , which is hash partitioned on salesman_id field. The tablespace names are data1 , data2 , data3 , and data4 .

Composite Partitioning

Composite partitioning partitions data using the range method, and within each partition, subpartitions it using the hash or list method. Composite range-hash partitioning provides the improved manageability of range partitioning and the data placement, striping, and parallelism advantages of hash partitioning. Composite range-list partitioning provides the manageability of range partitioning and the explicit control of list partitioning for the subpartitions.

Composite partitioning supports historical operations, such as adding new range partitions, but also provides higher degrees of parallelism for DML operations and finer granularity of data placement through subpartitioning.

Composite Partitioning Range-Hash Example
CREATE TABLE sales_composite (salesman_id NUMBER(5), salesman_name VARCHAR2(30), sales_amount NUMBER(10), sales_date DATE) PARTITION BY RANGE(sales_date) SUBPARTITION BY HASH(salesman_id) SUBPARTITION TEMPLATE( SUBPARTITION sp1 TABLESPACE data1, SUBPARTITION sp2 TABLESPACE data2, SUBPARTITION sp3 TABLESPACE data3, SUBPARTITION sp4 TABLESPACE data4) (PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')) PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')) PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')) PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY')) PARTITION sales_may2000 VALUES LESS THAN(TO_DATE('06/01/2000','DD/MM/YYYY')));

This statement creates a table sales_composite that is range partitioned on the sales_date field and hash subpartitioned on salesman_id . When you use a template, Oracle names the subpartitions by concatenating the partition name, an underscore, and the subpartition name from the template. Oracle places this subpartition in the tablespace specified in the template. In the previous statement, sales_jan2000_sp1 is created and placed in tablespace data1 while sales_jan2000_sp4 is created and placed in tablespace data4 . In the same manner, sales_apr2000_sp1 is created and placed in tablespace data1 while sales_apr2000_sp4 is created and placed in tablespace data4 . Figure 11-4 offers a graphical view of the previous example.

Figure 11-4 Composite Range-Hash Partitioning
Composite Partitioning Range-List Example
CREATE TABLE bimonthly_regional_sales (deptno NUMBER, item_no VARCHAR2(20), txn_date DATE, txn_amount NUMBER, state VARCHAR2(2)) PARTITION BY RANGE (txn_date) SUBPARTITION BY LIST (state) SUBPARTITION TEMPLATE( SUBPARTITION east VALUES('NY', 'VA', 'FL') TABLESPACE ts1, SUBPARTITION west VALUES('CA', 'OR', 'HI') TABLESPACE ts2, SUBPARTITION central VALUES('IL', 'TX', 'MO') TABLESPACE ts3)

( PARTITION janfeb_2000 VALUES LESS THAN (TO_DATE(‘1-MAR-2000′,’DD-MON-YYYY’)), PARTITION marapr_2000 VALUES LESS THAN (TO_DATE(‘1-MAY-2000′,’DD-MON-YYYY’)), PARTITION mayjun_2000 VALUES LESS THAN (TO_DATE(‘1-JUL-2000′,’DD-MON-YYYY’)) );

This statement creates a table bimonthly_regional_sales that is range partitioned on the txn_date field and list subpartitioned on state . When you use a template, Oracle names the subpartitions by concatenating the partition name, an underscore, and the subpartition name from the template. Oracle places this subpartition in the tablespace specified in the template. In the previous statement, janfeb_2000_east is created and placed in tablespace ts1 while janfeb_2000_central is created and placed in tablespace ts3 . In the same manner, mayjun_2000_east is placed in tablespace ts1 while mayjun_2000_central is placed in tablespace ts3 . Figure 11-5 offers a graphical view of the table bimonthly_regional_sales and its 9 individual subpartitions.

Figure 11-5 Composite Range-List Partitioning

When to Partition a Table

Here are some suggestions for when to partition a table:

  • Tables greater than 2GB should always be considered for partitioning.
  • Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month’s data is updatable and the other 11 months are read-only.

Partitioned Indexes

Just like partitioned tables, partitioned indexes improve manageability, availability, performance, and scalability. They can either be partitioned independently (global indexes) or automatically linked to a table’s partitioning method (local indexes).

Oracle9i Data Warehousing Guide for more information about partitioned indexes

Local Partitioned Indexes

Local partitioned indexes are easier to manage than other types of partitioned indexes. They also offer greater availability and are common in DSS environments. The reason for this is equipartitioning: each partition of a local index is associated with exactly one partition of the table. This enables Oracle to automatically keep the index partitions in sync with the table partitions, and makes each table-index pair independent. Any actions that make one partition’s data invalid or unavailable only affect a single partition.

You cannot explicitly add a partition to a local index. Instead, new partitions are added to local indexes only when you add a partition to the underlying table. Likewise, you cannot explicitly drop a partition from a local index. Instead, local index partitions are dropped only when you drop a partition from the underlying table.

A local index can be unique. However, in order for a local index to be unique, the partitioning key of the table must be part of the index’s key columns. Unique local indexes are useful for OLTP environments.

Figure 11-6 offers a graphical view of local partitioned indexes.

Figure 11-6 Local Partitioned Index

Global Partitioned Indexes

Global partitioned indexes are flexible in that the degree of partitioning and the partitioning key are independent from the table’s partitioning method. They are commonly used for OLTP environments and offer efficient access to any individual record.

The highest partition of a global index must have a partition bound, all of whose values are MAXVALUE . This ensures that all rows in the underlying table can be represented in the index. Global prefixed indexes can be unique or nonunique.

You cannot add a partition to a global index because the highest partition always has a partition bound of MAXVALUE . If you wish to add a new highest partition, use the ALTER INDEX SPLIT PARTITION statement. If a global index partition is empty, you can explicitly drop it by issuing the ALTER INDEX DROP PARTITION statement. If a global index partition contains data, dropping the partition causes the next highest partition to be marked unusable. You cannot drop the highest partition in a global index.

Maintenance of Global Partitioned Indexes

By default, the following operations on partitions on a heap-organized table mark all global indexes as unusable:

ADD (HASH) COALESCE (HASH) DROP EXCHANGE MERGE MOVE SPLIT TRUNCATE

These indexes can be maintained by appending the clause UPDATE GLOBAL INDEXES to the SQL statements for the operation. The two advantages to maintaining global indexes:

  • The index remains available and online throughout the operation. Hence no other applications are affected by this operation.
  • The index doesn’t have to be rebuilt after the operation.
Example:
ALTER TABLE DROP PARTITION P1 UPDATE GLOBAL INDEXES

This feature is supported only for heap organized tables.

Oracle9i SQL Reference for more information about the UPDATE GLOBAL INDEX clause

Figure 11-7 offers a graphical view of global partitioned indexes.

Figure 11-7 Global Partitioned Index

Global Nonpartitioned Indexes

Global nonpartitioned indexes behave just like a nonpartitioned index. They are commonly used in OLTP environments and offer efficient access to any individual record.

Figure 11-8 offers a graphical view of global nonpartitioned indexes.

Figure 11-8 Global Nonpartitioned Index

Partitioned Index Examples

Example of Index Creation: Starting Table Used for Examples
CREATE TABLE employees (employee_id NUMBER(4) NOT NULL, last_name VARCHAR2(10), department_id NUMBER(2)) PARTITION BY RANGE (department_id) (PARTITION employees_part1 VALUES LESS THAN (11) TABLESPACE part1, PARTITION employees_part2 VALUES LESS THAN (21) TABLESPACE part2, PARTITION employees_part3 VALUES LESS THAN (31) TABLESPACE part3);
Example of a Local Index Creation
CREATE INDEX employees_local_idx ON employees (employee_id) LOCAL;
Example of a Global Index Creation
CREATE INDEX employees_global_idx ON employees(employee_id);
Example of a Global Partitioned Index Creation
CREATE INDEX employees_global_part_idx ON employees(employee_id) GLOBAL PARTITION BY RANGE(employee_id) (PARTITION p1 VALUES LESS THAN(5000), PARTITION p2 VALUES LESS THAN(MAXVALUE));
Example of a Partitioned Index-Organized Table Creation
CREATE TABLE sales_range ( salesman_id NUMBER(5), salesman_name VARCHAR2(30), sales_amount NUMBER(10), sales_date DATE, PRIMARY KEY(sales_date, salesman_id)) ORGANIZATION INDEX INCLUDING salesman_id OVERFLOW TABLESPACE tabsp_overflow PARTITION BY RANGE(sales_date) (PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')) OVERFLOW TABLESPACE p1_overflow, PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')) OVERFLOW TABLESPACE p2_overflow, PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')) OVERFLOW TABLESPACE p3_overflow, PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY')) OVERFLOW TABLESPACE p4_overflow);

Miscellaneous Information about Creating Indexes on Partitioned Tables

You can create bitmap indexes on partitioned tables, with the restriction that the bitmap indexes must be local to the partitioned table. They cannot be global indexes.

Global indexes can be unique. Local indexes can only be unique if the partitioning key is a part of the index key.

Using Partitioned Indexes in OLTP Applications

Here are a few guidelines for OLTP applications:

  • Global indexes and unique, local indexes provide better performance than nonunique local indexes because they minimize the number of index partition probes.
  • Local indexes offer better availability when there are partition or subpartition maintenance operations on the table.

Using Partitioned Indexes in Data Warehousing and DSS Applications

Here are a few guidelines for data warehousing and DSS applications:

  • Local indexes are preferable because they are easier to manage during data loads and during partition-maintenance operations.
  • Local indexes can improve performance because many index partitions can be scanned in parallel by range queries on the index key.

Partitioned Indexes on Composite Partitions

Here are a few points to remember when using partitioned indexes on composite partitions:

  • Only range partitioned global indexes are supported.
  • Subpartitioned indexes are always local and stored with the table subpartition by default.
  • Tablespaces can be specified at either index or index subpartition levels.

Partitioning to Improve Performance

Partitioning can help you improve performance and manageability. Some topics to keep in mind when using partitioning for these reasons are:

  • Partition Pruning
  • Partition-wise Joins
  • Parallel DML

Partition Pruning

The Oracle server explicitly recognizes partitions and subpartitions. It then optimizes SQL statements to mark the partitions or subpartitions that need to be accessed and eliminates (prunes) unnecessary partitions or subpartitions from access by those SQL statements. In other words, partition pruning is the skipping of unnecessary index and data partitions or subpartitions in a query.

For each SQL statement, depending on the selection criteria specified, unneeded partitions or subpartitions can be eliminated. For example, if a query only involves March sales data, then there is no need to retrieve data for the remaining eleven months. Such intelligent pruning can dramatically reduce the data volume, resulting in substantial improvements in query performance.

If the optimizer determines that the selection criteria used for pruning are satisfied by all the rows in the accessed partition or subpartition, it removes those criteria from the predicate list (WHERE clause) during evaluation in order to improve performance. However, the optimizer cannot prune partitions if the SQL statement applies a function to the partitioning column (with the exception of the TO_DATE function). Similarly, the optimizer cannot use an index if the SQL statement applies a function to the indexed column, unless it is a function-based index.

Pruning can eliminate index partitions even when the underlying table’s partitions cannot be eliminated, but only when the index and table are partitioned on different columns. You can often improve the performance of operations on large tables by creating partitioned indexes that reduce the amount of data that your SQL statements need to access or modify.

Equality, range, LIKE , and IN -list predicates are considered for partition pruning with range or list partitioning, and equality and IN -list predicates are considered for partition pruning with hash partitioning.

Partition Pruning Example

We have a partitioned table called orders . The partition key for orders is order_date . Let’s assume that orders has six months of data, January to June, with a partition for each month of data. If the following query is run:

SELECT SUM(value) FROM orders WHERE order_date BETWEEN '28-MAR-98' AND '23-APR-98'

Partition pruning is achieved by:

  • First, partition elimination of January, February, May, and June data partitions. Then either:
    • An index scan of the March and April data partition due to high index selectivity or
    • A full scan of the March and April data partition due to low index selectivity

    Partition-wise Joins

    A partition-wise join is a join optimization that you can use when joining two tables that are both partitioned along the join column(s). With partition-wise joins, the join operation is broken into smaller joins that are performed sequentially or in parallel. Another way of looking at partition-wise joins is that they minimize the amount of data exchanged among parallel slaves during the execution of parallel joins by taking into account data distribution.

    Oracle9i Data Warehousing Guide for more information about partitioning methods and partition-wise joins

    Parallel DML

    Parallel execution dramatically reduces response time for data-intensive operations on large databases typically associated with decision support systems and data warehouses. In addition to conventional tables, you can use parallel query and parallel DML with range- and hash-partitioned tables. By doing so, you can enhance scalability and performance for batch operations.

    The semantics and restrictions for parallel DML sessions are the same whether you are using index-organized tables or not.

    Oracle9i Data Warehousing Guide for more information about parallel DML and its use with partitioned tables

    Как запросом вывести partition in table oracle

    ALL_TAB_PARTITIONS displays partition-level partitioning information, partition storage parameters, and partition statistics generated by the DBMS_STATS package for the partitions accessible to the current user.

    • DBA_TAB_PARTITIONS displays such information for all partitions in the database.
    • USER_TAB_PARTITIONS displays such information for the partitions of all partitioned objects owned by the current user. This view does not display the TABLE_OWNER column.

    Columns marked with an asterisk ( * ) are populated only if you collect statistics on the table with the DBMS_STATS package.

    The following is true for the columns below that include double asterisks ( ** ) in the column description:

    The column can display information about segment-level attributes (for simple partitioned tables) or metadata (for composite partitioned tables). In a simple partitioned table, the partition physically contains the data (the segment) in the database. In a composite partitioned table, the partition is metadata and the data itself is stored in the subpartitions.

    Owner of the table

    Name of the table

    Indicates whether the table is composite-partitioned ( YES ) or not ( NO )

    Name of the partition

    If this is a composite partitioned table, the number of subpartitions in the partition

    Partition bound value expression

    Length of the partition bound value expression

    Position of the partition within the table

    Name of the tablespace containing the partition**

    Minimum percentage of free space in a block**

    Minimum percentage of used space in a block**

    Initial number of transactions**

    Maximum number of transactions**

    Size of the initial extent in bytes (for a range partition); size of the initial extent in blocks (for a composite partition)**

    Size of secondary extents in bytes (for a range partition); size of secondary extents in blocks (for a composite partition)**

    Minimum number of extents allowed in the segment**

    Maximum number of extents allowed in the segment**

    Maximum number of blocks allowed in the segment**

    Percentage increase in extent size**

    Number of process freelists allocated in this segment**

    Number of freelist groups allocated in this segment**

    Indicates whether or not changes to the table are logged:**

    • NONE — Not specified See Also: the *_TAB_SUBPARTITIONS view
    • YES
    • NO

    Indicates the actual compression property for a partition of a simple partitioned table, or the default (if specified) for subpartitions for composite partitioned tables, otherwise NONE .**

    • NONE — The partition is composite, and a default setting is not specified for compression. See Also: the *_TAB_SUBPARTITIONS view
    • ENABLED — The setting for compression is enabled.
    • DISABLED — The setting for compression is disabled.

    Default compression for what kind of operations:**

    • BASIC
    • ADVANCED
    • QUERY LOW
    • QUERY HIGH
    • ARCHIVE LOW
    • ARCHIVE HIGH
    • NULL

    The QUERY_LOW , QUERY_HIGH , ARCHIVE_LOW , and ARCHIVE_HIGH values are associated with Hybrid Columnar Compression, a feature of the Enterprise Edition of Oracle Database that is dependent on the underlying storage system. See Oracle Database Concepts for more information.

    Number of rows in the partition

    Number of used data blocks in the partition

    Number of empty (never used) data blocks in the partition. This column is populated only if you collect statistics on the table using the DBMS_STATS package.

    Average amount of free space, in bytes, in a data block allocated to the partition

    Number of rows in the partition that are chained from one data block to another, or which have migrated to a new block, requiring a link to preserve the old ROWID

    Average length of a row in the partition (in bytes)

    Sample size used in analyzing this partition

    Date on which this partition was most recently analyzed

    Buffer pool to be used for the partition blocks:**

    Database Smart Flash Cache hint to be used for partition blocks:**

    Solaris and Oracle Linux functionality only.

    Cell flash cache hint to be used for partition blocks:**

    See Also: Oracle Exadata Storage Server Software documentation for more information

    GLOBAL_STATS will be YES if statistics have been gathered or NO if statistics have been aggregated from subpartitions or have not been gathered

    Indicates whether statistics were entered directly by the user ( YES ) or not ( NO )

    Indicates whether this is a nested table partition ( YES ) or not ( NO )

    See Also: the *_NESTED_TABLES view for the parent table name/column

    Parent table’s corresponding partition

    See Also: the *_NESTED_TABLES view for the parent table name/column

    Indicates whether the partition is in the interval section of an interval partitioned table ( YES ) or whether the partition is in the range section ( NO )

    Indicates the actual segment creation property for a partition of a simple partitioned table, or the default (if specified) for subpartitions for composite partitioned tables, otherwise NONE .**

    For a simple partitioned table, this column indicates whether a segment was created ( YES ) or not ( NO ).

    For composite partitioned tables, this column indicates whether or not a default segment creation property was explicitly specified. Possible values:

    • NONE — No default segment creation property was specified at the partition level. This value appears only for composite partitions, and is treated as an unspecified value.
    • YES — Immediate segment creation was explicitly specified at the partition level and will be used as the default for all of its subpartitions.
    • NO — Deferred segment creation was explicitly specified at the partition level and will be used as the default for all of its subpartitions.

    Indicates the actual indexing property for a partition of a simple partitioned table, or the default (if specified) for subpartitions for composite partitioned tables, otherwise NONE.**

    • NONE — The partition is composite, and a default setting is not specified for indexing. This value appears only for composite partitions, and is treated as an unspecified value. When a user adds a subpartition to a table, since the defaults for the partition are unspecified, the ALL_PART_TABLES.DEF_INDEXING value is used for the newly created subpartition.
    • ON — INDEXING is on.
    • OFF — INDEXING is off.

    Indicates the default setting for the partition:

    • YES : The default setting for the partition is read-only.
    • NO : The default setting for the partition is read/write.
    • NONE : No default setting is specified for the partition.

    Indicates whether the In-Memory Column Store (IM column store) is enabled ( ENABLED ) or disabled ( DISABLED ) for this partition

    Indicates the priority for In-Memory Column Store (IM column store) population. Possible values:

    Indicates how the IM column store is distributed in an Oracle Real Application Clusters (Oracle RAC) environment:

    Indicates the compression level for the IM column store:

    • NO MEMCOMPRESS
    • FOR DML
    • FOR QUERY [ LOW | HIGH ]
    • FOR CAPACITY [ LOW | HIGH ]
    • NULL

    This column has a value based on where the segments lie for a table. For example, if the table is partitioned and is enabled for the IM column store, the value is NULL for ALL_TABLES but non- NULL for ALL_TAB_PARTITIONS .

    Indicates the duplicate setting for the IM column store in an Oracle RAC environment:

    The value for columnar compression in the storage cell flash cache. Possible values:

    • ENABLED : Oracle Exadata Storage will decide automatically whether to cache in columnar form
    • DISABLED : Oracle Exadata Storage is prevented from caching in columnar form
    • NO CACHECOMPRESS : Oracle Exadata Storage will cache in HCC format (no recompression)
    • FOR QUERY : Oracle Exadata Storage will recompress and cache in INMEMORY query high format
    • FOR CAPACITY : Oracle Exadata Storage will recompress and cache in INMEMORY capacity low format

    This column is intended for use with Oracle Exadata.

    Indicates how the IM column store is populated on various instances. The possible values are:

    • DEFAULT : Data is populated on all instances specified with the PARALLEL_INSTANCE_GROUP initialization parameter. If that parameter is not set, then the data is populated on all instances. This is the default.
    • NONE : Data is not populated on any instance.
    • ALL : Data is populated on all instances, regardless of the value of the PARALLEL_INSTANCE_GROUP initialization parameter.
    • USER_DEFINED : Data is populated only on the instances on which the user-specified service is active. The service name corresponding to this is stored in the INMEMORY_SERVICE_NAME column.

    Indicates the service name for the service on which the IM column store should be populated. This column has a value only when the corresponding INMEMORY_SERVICE is USER_DEFINED . In all other cases, this column is null.

    Indicates whether the table is enabled for Fast Key Based Access ( ENABLED ) or not ( DISABLED )

    Oracle SQL Tutorial Contents

    UNION, INTERSECT, MINUS Operators and Sorting Query Result

    Oracle SQL Functions

    Oracle DECODE function

    Oracle INSTR() function with examples

    Oracle TO_CHAR() function

    Number Functions (Math Functions)

    Character Functions

    Miscellaneous Functions

    Aggregate Functions

    Date and Time Functions

    Oracle Join Queries

    GROUP BY Queries, SUB Queries

    CUBE, ROLLUP Functions

    Oracle DML (INSERT, UPDATE, DELETE. )

    Oracle DDL (CREATE, ALTER, DROP. )

    COMMIT, ROLLBACK,SAVEPOINT

    Data Control Language (GRANT, REVOKE)

    Integrity Constraints (PRIMARY KEY, NOT NULL. )

    DEFAULT Values

    Dropping Constraints

    Disabling and Enabling

    Differing Constraints Check

    View Info about Constraints

    Working with Dates

    Oracle Views

    Oracle Sequences

    Oracle Synonyms

    Indexes and Clusters

    Table Partitioning

    Altering Partition Tables

    Dropping Partitions

    Merging Partitions

    Splitting Partitions

    Coalescing Partitions

    Oracle Objects and Object Types

    Altering and Managing Partition Tables

    ALTERING PARTITION TABLES

    To add a partition

    You can add add a new partition to the «high» end (the point after the last existing partition). To add a partition
    at the beginning or in the middle of a table, use the SPLIT PARTITION clause.

    For example to add a partition to sales table give the following command.

    alter table sales add partition p6 values less than (1996);

    To add a partition to a Hash Partition table give the following command.

    alter table products add partition;

    Then Oracle adds a new partition whose name is system generated and it is created in the default tablespace.
    To add a partition by user define name and in your specified tablespace give the following command.

    alter table products add partition p5 tablespace u5;

    To add a partition to a List partition table give the following command.

    alter table customers add partition central_India
    values (‘BHOPAL’,’NAGPUR’);

    Any value in the set of literal values that describe the partition(s) being added must not exist in any of the other partitions of the table.

    Coalescing Partitions

    Coalescing partitions is a way of reducing the number of partitions in a hash-partitioned table, or the number of subpartitions in a composite-partitioned table. When a hash partition is coalesced, its contents are redistributed into one or more remaining partitions determined by the hash function. The specific partition that is coalesced is selected by Oracle, and is dropped after its contents have been redistributed.

    To coalesce a hash partition give the following statement.

    alter table products coalesce partition;

    This reduces by one the number of partitions in the table products.

    DROPPING PARTITIONS

    To drop a partition from Range Partition table, List Partition or Composite Partition table give the following command.

    alter table sales drop partition p5;

    Once you have drop the partition and if you have created a global index on the table. Then you have to rebuild the global index after dropping the partition by giving the following statement.

    alter index sales_ind rebuild;

    To avoid rebuilding of indexes after dropping of the partitions you can also first delete all the records and then drop
    the partition like this

    delete from sales where year=1994;

    alter table sales drop partition p4;

    This method is most appropriate for small tables, or for large tables when the partition being dropped contains a small percentage of the total data in the table.

    Another method of dropping partitions is give the following statement.

    ALTER TABLE sales DROP PARTITION p5 UPDATE GLOBAL INDEXES;

    This causes the global index to be updated at the time the partition is dropped.

    Exchanging a Range, Hash, or List Partition

    To exchange a partition of a range, hash, or list-partitioned table with a nonpartitioned table, or the reverse, use the ALTER TABLE . EXCHANGE PARTITION statement. An example of converting a partition into a nonpartitioned table follows. In this example, table stocks can be range, hash, or list partitioned.

    ALTER TABLE stocks
    EXCHANGE PARTITION p3 WITH stock_table_3;

    Merging Partitions

    Use the ALTER TABLE . MERGE PARTITIONS statement to merge the contents of two partitions into one partition. Te two original partitions are dropped, as are any corresponding local indexes.

    You cannot use this statement for a hash-partitioned table or for hash subpartitions of a composite-partitioned table.

    You can only merged two adjacent partitions, you cannot merge non adjacent partitions.

    For example the merge the partition p2 and p3 into one partition p23 give the following statement.

    alter table sales merge partition p2 and p3 into partition p23;

    Modifying Partitions: Adding Values

    Use the MODIFY PARTITION . ADD VALUES clause of the ALTER TABLE statement to extend the value list of an existing partition. Literal values being added must not have been included in any other partition’s value list. The partition value list for any corresponding local index partition is correspondingly extended, and any global index, or global or local index partitions, remain usable.

    The following statement adds a new set of cities (‘KOCHI’, ‘MANGALORE’) to an existing partition list.

    ALTER TABLE customers
    MODIFY PARTITION south_india
    ADD VALUES (‘KOCHI’, ‘MANGALORE’);

    Modifying Partitions: Dropping Values

    Use the MODIFY PARTITION . DROP VALUES clause of the ALTER TABLE statement to remove literal values from the value list of an existing partition. The statement is always executed with validation, meaning that it checks to see if any rows exist in the partition that correspond to the set of values being dropped. If any such rows are found then Oracle returns an error message and the operation fails. When necessary, use a DELETE statement to delete corresponding rows from the table before attempting to drop values.

    You cannot drop all literal values from the value list describing the partition. You must use the ALTER TABLE . DROP PARTITION statement instead.

    The partition value list for any corresponding local index partition reflects the new value list, and any global index, or global or local index partitions, remain usable.

    The statement below drops a set of cities (‘KOCHI’ and ‘MANGALORE’) from an existing partition value list.

    ALTER TABLE customers
    MODIFY PARTITION south_india
    DROP VALUES (‘KOCHI’,’MANGALORE’);

    SPLITTING PARTITIONS

    You can split a single partition into two partitions. For example to split the partition p5 of sales table into two partitions give the following command

    alter table sales split partition p5 into
    (Partition p6 values less than (1996),
    Partition p7 values less then (MAXVALUE));

    TRUNCATING PARTITON

    Truncating a partition will delete all rows from the partition.

    To truncate a partition give the following statement

    alter table sales truncate partition p5;

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *