Partitioned Tables and Indexes

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

• Introduction to Partitioning
• Partitioning Methods
• Partitioned Indexes
• Partitioning to Improve Performance

  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.

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 partition pruning) 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

Text description of the illustration cncpt162.gif

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 NULLable

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

Text description of the illustration cncpt168.gif

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

Text description of the illustration cncpt157.gif

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)

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

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

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

Figure 11-7 Global Partitioned Index

Text description of the illustration cncpt160.gif

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.

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

자 오늘은 concat()과 listagg()에 대해서 알아보도록 하겠습니다.

DB에서 정보를 추출할 때 여러줄(리스트 형태)로 나오는 것들을 한줄로 합쳐서 뽑아내고 싶거나  

한 줄에 여러 필드들이 있을때 이 필드들을 모두 합쳐서 한줄의 스트링으로 뽑아내고 싶을 때가 있을거에요.

예를들면 EMP테이블에 한명의 employee만 등록이 되어있다고 가정합시다.

이때 이 사람의 정보를 emp_nm 과 emp_id를 나눠서 emp객체의 name과 id필드에 뽑아올 수도 있지만 궂이 이걸 하나의 String으로 뽑아내고 싶을때가 있을 수도 있다고 가정하고 -_-ㅋ

emp_nm | emp_id

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

장군이   |     3

이렇게 데이터가 들어있을 때 "3장군이" 라는 스트링을 뽑아오려면 concat을 쓰면 됩니다.

select concat( emp_id, emp_nm) from emp; 

이제 데이타가 한줄 더 들어와서 두명의 employee가 있다고 가정합시다.

emp_nm | emp_id

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

장군이   |     3

이쁭이   |     5

그리고 이 직원들의 이름을 쉼표로 구분해서 하나의 스트링으로 뽑아내고 싶다고 가정합시다.

"장군이,이쁭이" 이렇게 말이죠.

이럴때는 listagg()를 쓰면 됩니다. 아래처럼 말이죠.

select listagg(emp_nm, ',') within group ( order by emp_id ) from emp; 

listagg(리스트애그)에 대한 자세한 내용은 아래 출처에 있는 주소로 가시면 좀 더 자세한 설명을 보실 수 있습니다.

그럼 좋은하루 되세요~ ^_^ 

출처 : http://oracle-base.com/articles/misc/string-aggregation-techniques.php ( listagg() )  

링크가 break될 것을 우려하여 밑에 위 링크로가면 볼 수 있는 내용을 가져왔습니다. ^^

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

String Aggregation Techniques

On occasion it is necessary to aggregate data from a number of rows into a single row, giving a list of data associated with a specific value. Using the SCOTT.EMP table as an example, we might want to retrieve a list of employees for each department. Below is a list of the base data and the type of output we would like to return from an aggregate query.

Base Data:

    DEPTNO ENAME
---------- ----------
        20 SMITH
        30 ALLEN
        30 WARD
        20 JONES
        30 MARTIN
        30 BLAKE
        10 CLARK
        20 SCOTT
        10 KING
        30 TURNER
        20 ADAMS
        30 JAMES
        20 FORD
        10 MILLER

Desired Output:

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,FORD,ADAMS,SCOTT,JONES
        30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

This article is based on a thread from asktom.oracle.com and contains several methods to achieve the desired results.

• LISTAGG Analytic Function in 11g Release 2
• WM_CONCAT Built-in Function (Not Supported)
• User-Defined Aggregate Function
• Specific Function
• Generic Function using Ref Cursor
• ROW_NUMBER() and SYS_CONNECT_BY_PATH functions in Oracle 9i
• COLLECT function in Oracle 10g

LISTAGG Analystic Function in 11g Release 2

The LISTAGG analytic function was introduced in Oracle 11g Release 2, making it very easy to aggregate strings. The nice thing about this function is it also allows us to order the elements in the concatenated list. If you are using 11g Release 2 you should use this function for string aggregation.

COLUMN employees FORMAT A50

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

3 rows selected.

WM_CONCAT Built-in Function (Not Supported)

If you are not running 11g Release 2, but are running a version of the database where the WM_CONCAT function is present, then it is a zero effort solution as it performs the aggregation for you. It is actually an example of a user defined aggregate function described below, but Oracle have done all the work for you.

COLUMN employees FORMAT A50

SELECT deptno, wm_concat(ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,FORD,ADAMS,SCOTT,JONES
        30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

3 rows selected.

Note. WM_CONCAT is an undocumented function and as such is not supported by Oracle for user applications (MOS Note ID 1336219.1). If this concerns you, use a User-Defined Aggregate Function described below.

User-Defined Aggregate Function

The WM_CONCAT function described above is an example of a user-defined aggregate function that Oracle have already created for you. If you don't want to use WM_CONCAT, you can create your own user-defined aggregate function as described at asktom.oracle.com.

CREATE OR REPLACE TYPE t_string_agg AS OBJECT
(
  g_string  VARCHAR2(32767),

  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg)
    RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg,
                                       value  IN      VARCHAR2 )
     RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg,
                                     ctx2  IN      t_string_agg)
    RETURN NUMBER
);
/
SHOW ERRORS


CREATE OR REPLACE TYPE BODY t_string_agg IS
  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg)
    RETURN NUMBER IS
  BEGIN
    sctx := t_string_agg(NULL);
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg,
                                       value  IN      VARCHAR2 )
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := self.g_string || ',' || value;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER IS
  BEGIN
    returnValue := RTRIM(LTRIM(SELF.g_string, ','), ',');
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg,
                                     ctx2  IN      t_string_agg)
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := SELF.g_string || ',' || ctx2.g_string;
    RETURN ODCIConst.Success;
  END;
END;
/
SHOW ERRORS


CREATE OR REPLACE FUNCTION string_agg (p_input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING t_string_agg;
/
SHOW ERRORS

The aggregate function is implemented using a type and type body, and is used within a query.

COLUMN employees FORMAT A50

SELECT deptno, string_agg(ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,FORD,ADAMS,SCOTT,JONES
        30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

3 rows selected.

Specific Function

One approach is to write a specific function to solve the problems. The get_employees function listed below returns a list of employees for the specified department.

CREATE OR REPLACE FUNCTION get_employees (p_deptno  in  emp.deptno%TYPE)
  RETURN VARCHAR2
IS
  l_text  VARCHAR2(32767) := NULL;
BEGIN
  FOR cur_rec IN (SELECT ename FROM emp WHERE deptno = p_deptno) LOOP
    l_text := l_text || ',' || cur_rec.ename;
  END LOOP;
  RETURN LTRIM(l_text, ',');
END;
/
SHOW ERRORS

The function can then be incorporated into a query as follows.

COLUMN employees FORMAT A50

SELECT deptno,
       get_employees(deptno) AS employees
FROM   emp
GROUP by deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

3 rows selected.

To reduce the number of calls to the function, and thereby improve performance, we might want to filter the rows in advance.

COLUMN employees FORMAT A50

SELECT e.deptno,
       get_employees(e.deptno) AS employees
FROM   (SELECT DISTINCT deptno
        FROM   emp) e;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
        
3 rows selected.

Generic Function using Ref Cursor

An alternative approach is to write a function to concatenate values passed using a ref cursor. This is essentially the same as the previous example, except that the cursor is passed in making it generic, as shown below.

CREATE OR REPLACE FUNCTION concatenate_list (p_cursor IN  SYS_REFCURSOR)
  RETURN  VARCHAR2
IS
  l_return  VARCHAR2(32767); 
  l_temp    VARCHAR2(32767);
BEGIN
  LOOP
    FETCH p_cursor
    INTO  l_temp;
    EXIT WHEN p_cursor%NOTFOUND;
    l_return := l_return || ',' || l_temp;
  END LOOP;
  RETURN LTRIM(l_return, ',');
END;
/
SHOW ERRORS

The CURSOR function is used to allow a query to be passed to the function as a ref cursor, as shown below.

COLUMN employees FORMAT A50

SELECT e1.deptno,
       concatenate_list(CURSOR(SELECT e2.ename FROM emp e2 WHERE e2.deptno = e1.deptno)) employees
FROM   emp e1
GROUP BY e1.deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

3 rows selected.

Once again, the total number of function calls can be reduced by filtering the distinct values, rather than calling the function for each row.

COLUMN employees FORMAT A50

SELECT deptno,
       concatenate_list(CURSOR(SELECT e2.ename FROM emp e2 WHERE e2.deptno = e1.deptno)) employees
FROM   (SELECT DISTINCT deptno
        FROM emp) e1;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

3 rows selected.

ROW_NUMBER() and SYS_CONNECT_BY_PATH functions in Oracle 9i

An example on williamrobertson.net uses the ROW_NUMBER() and SYS_CONNECT_BY_PATH functions to achieve the same result without the use of PL/SQL or additional type definitions.

SELECT deptno,
       LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
       KEEP (DENSE_RANK LAST ORDER BY curr),',') AS employees
FROM   (SELECT deptno,
               ename,
               ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr,
               ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev
        FROM   emp)
GROUP BY deptno
CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno
START WITH curr = 1;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

3 rows selected.

COLLECT function in Oracle 10g

An example on oracle-developer.net uses the COLLECT function in Oracle 10g to get the same result. This method requires a table type and a function to convert the contents of the table type to a string. I've altered his method slightly to bring it in line with this article.

CREATE OR REPLACE TYPE t_varchar2_tab AS TABLE OF VARCHAR2(4000);
/

CREATE OR REPLACE FUNCTION tab_to_string (p_varchar2_tab  IN  t_varchar2_tab,
                                          p_delimiter     IN  VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS
  l_string     VARCHAR2(32767);
BEGIN
  FOR i IN p_varchar2_tab.FIRST .. p_varchar2_tab.LAST LOOP
    IF i != p_varchar2_tab.FIRST THEN
      l_string := l_string || p_delimiter;
    END IF;
    l_string := l_string || p_varchar2_tab(i);
  END LOOP;
  RETURN l_string;
END tab_to_string;
/

The query below shows the COLLECT function in action.

COLUMN employees FORMAT A50

SELECT deptno,
       tab_to_string(CAST(COLLECT(ename) AS t_varchar2_tab)) AS employees
FROM   emp
GROUP BY deptno;
       
    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
        
3 rows selected.

이상입니다!!!

아...슬프도다....이 에러의 원인을 찾느라 얼마나 헤맸던지...

쿼리문의 인자로 들어가는 녀석들이 13개이고 이 쿼리문을 스트링 + 스트링 형식으로 묶어놨는데 콤마가 빠진 이유로 변수명이 부적합하다는 오류를 찍어대니 이거 원인을 어떻게 찾으라고.!!!!!!

구글링 해보니......

콤마가 빠져도 저런 오류를 뿌린다는 것...

잘 보니 인자가 많아서 줄바꿈할 때 쉼표를 하나 빼먹었던 것.

특정 테이블의 속성을 조회할 때에는 아래 두가지 테이블을 보면됩니다.

1. dba권한으로 모든 테이블을 조회할 경우 : dba_tables

2. 일반 사용자 권한으로 내 소유의 테이블을 조회할 경우 : user_tables

아래 예제를 보면

SQL > select * from user_tables;

를 실행하면 꽤나 긴 페이지를 보게 될 것입니다.  

putty를 이용해서 DB에 접속한 뒤 pagesize와 linesize를 설정하지 않았을 경우 특히 더 길게 나올거에요. 

만약 pagesize와 linesize를 설정하지 않았다면 아래와 같이 실행해보세요.

SQL > set pagesize 100

SQL > set linesize 3000

위 두 줄을의 내용은 한 페이지에 100 라인이 들어가고 한 라인에 3천개의 문자가 들어간다고 정의하는 것입니다.

그리고 다시 select문을 실행시켜보면 그나마 보기좋게 나올 것입니다. 그래도 아직 영 맘에 안들죠? 

위 select문을 실행시키면 테이블명과 해당 테이블이 속해있는 테이블 스페이스명을 시작으로 온갖 속성들의 값이 나올것입니다.  

아래는 user_tables에 어떤 컬럼들이 있는지를 뽑아본 것인데 아래 컬럼중에서 원하는 것만 뽑아서 조회를 하는것이 보이에 훨씬 좋을테니 몇개만 골라서 조회해보세요. 

TABLE_NAME                      

TABLESPACE_NAME                 

CLUSTER_NAME                                                                               IOT_NAME                                                                                    

STATUS      

PCT_FREE    

PCT_USED   

INI_TRANS   

MAX_TRANS  

INITIAL_EXTENT  

NEXT_EXTENT  

MIN_EXTENTS  

MAX_EXTENTS  

PCT_INCREASE   

FREELISTS  

FREELIST_GROUPS  

LOG  

B    

NUM_ROWS      

BLOCKS  

EMPTY_BLOCKS   

AVG_SPACE   

CHAIN_CNT  

AVG_ROW_LEN A

VG_SPACE_FREELIST_BLOCKS  

NUM_FREELISTOW_MOVE  

GLO  

USE  

DURATION                                             

SKIP_COR MON CLUSTER_OWNER                                                                               

DEPENDEN  

COMPRESS  

COMPRESS_FOR  

DRO  

REA  

SEG  

RESULT_

아래 쿼리문은 dba 유저로 모든 테이블 스페이스의 사용량( 총용량, 사용량, 남은용량 )에 대한 정보를 얻어온다.

select     a.TABLESPACE_NAME,
     a.BYTES MB_total,
     b.BYTES MB_free,
     b.largest,
     a.BYTES-b.BYTES MB_used,
     round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from
     (
          select      TABLESPACE_NAME,
               sum(BYTES)/1048576 BYTES
          from      dba_data_files
          group      by TABLESPACE_NAME
     )
     a,
     (
          select      TABLESPACE_NAME,
               sum(BYTES)/1048576 BYTES ,
               max(BYTES)/1048576 largest
          from      dba_free_space
          group      by TABLESPACE_NAME
     )
     b
where      a.TABLESPACE_NAME=b.TABLESPACE_NAME
order      by ((a.BYTES-b.BYTES)/a.BYTES) desc;

이 에러가 났을 때 뭐 때문에 busy라고 나오는지 궁금하다면??

sysdba권한으로 아래 쿼리를 날려보자. 무슨쿼리 때문에 어느 테이블이 lock이 걸려있는지 확인할 수 있다. 

SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,
S.MACHINE,S.PORT , S.LOGON_TIME,SQ.SQL_FULLTEXT 
FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, 
V$PROCESS P, V$SQL SQ 
WHERE L.OBJECT_ID = O.OBJECT_ID 
AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR 
AND S.SQL_ADDRESS = SQ.ADDRESS;


위 쿼리를 실행해서 SID와 SERIAL#을 알아내면 아래와 같은 명령으로 해당 세션을 죽일 수 있다.

ALTER SYSTEM KILL SESSION 'SID, SERIAL#';

이상~!!! 당신에게 노력과 행운의 여신이 함께하여 문제가 해결되기를~~

ORA-00845: MEMORY_TARGET not supported on this system 오류가 발생했다!!!!!

으악!!!!

오라클을 재시작하려고 shutdown시킨 뒤 startup을 실행시켰더니 갑자기 이런 오류가 발생했다.

뭥미?? 처음 본 오류라 당황ㅠㅠ

구글링해보니 /dev/shm 마운트한거랑 관련이 있었다. 무슨 이유 때문에 발생했는지는 아직도 확실치 않지만...아래와 같이 해결했다.

$ umount -l tmpfs

( tmpfs는 /dev/shm이 마운트된 이름이다. 쉘에서 mount명령어로 확인했을때 제일 좌측에 나오는 이름 )

( -l (소문자L)옵션없이 언마운트시키려고 했을때는 busy라고 계속뜨길래 옵션을 주고 언마운트 시켰다. lazy umount 옵션이다. )

$ mount -t tmpfs tmpfs /dev/shm

( 다시 마운트 하기 ) 

그리고 sqlplus에 sysdba로 conn해서 

SQL> startup

게임오버~!  

좀 더 자세한 사항은 이곳을 참조하세요. 

device is busy 문제로 umount가 안된다면 이곳을 참조하세요. 

수백만건의 데이타를 처리하다가 제목과 같은 오류가 발생했다.

~~  인덱스를 8192로  USERS 테이블스페이스에서 확장할 수 없습니다

이유인 즉슨 사용자가 특정 테이블스페이스에서 데이타를 더이상 추가할 수 없어서 나는 오류.

따라서 이런 경우 테이블 스페이스를 확장해주거나 변경해주거나 하는 등등의 작업이 필요하다.

- 특정 테이블의 테이블 스페이스를 변경하는 작업. 

alter table table_name move tablespace new_tablespace_name;  

- 인덱스의 테이블 스페이스를 변경하는 작업.

alter index index_name rebuild tablespace new_tablespace_name;

출처 : http://a07274.tistory.com/227

( 오라클 사이트에서 좀 더 자세한 사항을 확인하고 싶은 분들은 이곳을 이용하면된다. ) 

I. 오라클 파티션 정의

1. 파티션 개요
오늘날 기업에서 관리하는 데이터는 수백테라 바이트에 이르는 데이터베이스를 관리한다.
하지만 이런 데이터들 중 몇몇의 Big Transaction Table이 거의 모든 데이터를 가지고 있고
나머지 테이블들은 이 Big Transaction Table을 경유하여 액세스 하는 용도로 사용된다.

이렇게 데이터 크기도 크고 중요한 Big Transaction Table을 관리하는 부분에서 Troubleshooting이
발생될 경우 데이터베이스의 성능 및 관리작업에 심각한 영향을 받을 수 있다.

이러한 리스크가 있는 Big Transaction Table을 보다 효율적으로 관리하기 위해 Table을
작은 단위로 나눔으로써 데이터 액세스 작업의 성능 향상을 유도하고 데이터 관리를 보다
수월하게 하고자 하는 개념이다.

2. 파티션 테이블의 장점
1) 개선된 가용성
- 파티션은 독립적으로 관리된다.
- Backup and Restore를 파티션별로 작업할 수 있다.
- 같은 테이블에서 Unavailable한 파티션은 다른 파티션에 영향을 주지 않는다.
2) 관리의 용이성
- 사용자가 지정한 값으로 파티션이 가능하다.
- 테이블스페이스간에 파티션 이동이 가능하다.
- 파티션 레벨에서 Select, Delete, Update가 가능하다.
3) 개선된 성능
- 데이터를 액세스할 때 액세스하는 범위를 줄여 퍼포먼스 향상을 가져올 수 있다.
- RAC(Real Application Clusters) 환경에서 인스턴스간 Block Contention을 감소시킨다.

3. 파티션 테이블 사용시 주의할 점
1) 관리적인 관점
- 하나의 테이블을 세분화하여 관리하기 때문에 보다 세심한 관리가 요구된다.
- 파티션을 잘 못 구성 또는 관리하여 IU(Index Unusable)에 빠지는 것을 주의해야 한다.
2) 사용하는 관점
- 파티션 키로 나누어져 있는 테이블에 파티션 키를 조건으로 주지 않아 전체 파티션을 액세스하지
않도록 주의해야 한다.

4. 파티션 테이블의 특징
- 파티션 테이블은 파티션 키 값에 의해 구성되며, 한 테이블 당 가능한 파티션은 이론적으로 65,535개를
지원하나 실질적으로는 10,000개까지만 생성 가능하다(Oracle Ver 10.2.0.3 테스트)

- 모든 파티션 테이블(또는 인덱스)는 같은 Logical Attribute를 가져야 한다.
Ex) Columns, Data Types, Constraints...

- 모든 파티션 테이블(또는 인덱스)는 다른 Physical Attribute를 가져야 한다.
Ex) PCTFREE, PCTUSED, INITTRANS, MAXTRANS, TABLESPACE, STORAGE...

- 파티션 테이블은 'KEY', 'VALUES LESS THAN Literal', 'Physical Attributes'로 구성된다.

- 'VALUES LESS THAN Literal' 절에서 'Literal' 값에는 SQL Function을 지원한다.

- Composite Column 구성은 16개까지 가능하다.

II. 파티션 종류

1. Oracle 버전에 따른 파티션
1) Oracle Ver 7.3
- Partition View를 처음으로 도입하였다.
- 당시 Partition View는 큰 테이블을 동일한 템플릿을 가진 여러 개의 다른 테이블로 분할하고
UNION ALL을 사용하여 View로 묶은 형태이다.
- 그러나 이 방식은 관리의 어려움, 활용성의 부족, 성능등에 대한 이슈로 인하여 Oracle Ver 9i에서는
더이상 지원하지 않는다.

2) Oracle Ver 8.0
- 컬럼 값의 Range 기반으로 된 Range Partition이 최초로 도입되었고, 비로서 Partition의 모습을 갖추었다.
- 각 파티션은 각기 다른 테이블 스페이스, Segment에 저장이 가능하다.

3) Oracle Ver 8i
- 컬럼 값의 Hash 기반으로 된 hash partition과, Sub Partition을 할 수 있는 Composite Partition이 추가되었다.
- 이 당시 Composite Partition은 Range-Hash로만 구성 가능함.

4) Oracle Ver 9i
- 리스트 값으로 파티션을 할 수 있는 List Partition이 추가되었다.
- Composite Partition에서는 Range-Hash 이외에 Range-List가 추가 지원되었다.

5) Oracle Ver 10g
- IOT 파티션이 추가되었다.

6) Oracle Ver 11g
- Composite Partition에서 확장된 Extended Composite Partition이 지원된다.
-> Range-Range, List-Range, List-Hash, List-List
- Reference Partition 추가
- Interval Partition 추가
- System Partition 추가
- Virtual Column Partition 추가

2. Partition Table
1) Range Partition
- Column Value의 범위를 기준으로 하여 행을 분할하는 형태이다.
- Range Partition에서 Table은 단지 논리적인 구조이며 실제 데이터가 물리적으로 저장되는 곳은
Partition으로 나누어진 Tablespace에 저장이 된다.
- PARTITION BY RANGE ( column_list ) : 기본 Table에서 어느 Column을 기준으로 분할할지를 정함.
VALUES LESS THAN ( value_list ) : 각 Partition이 어떤 값의 범위를 포함 할지 Upper Bound를 정함.
- 구문 Sample
CREATE TABLE SALES_DATA_2008
(
COLUMN_1 NUMBER NOT NULL,
COLUMN_2 VARCHAR2(4),
COLUMN_3 VARCHAR2(4),
COLUMN_4 VARCHAR2(2),
COLUMN_5 VARCHAR2(2),
COLUMN_6 NUMBER
)
TABLESPACE TABLE_SPACE_DATA_1
PCTFREE 5
PCTUSED 40
INITRANS 11
MAXTRANS 255
STORAGE
(
INITIAL 2048K
NEXT 1024K
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 121
)
PARTITION BY RANGE ( COLUMN_3, COLUMN_4, COLUMN_5 )
(
PARTITION P_200801 VALUES LESS THAN ('2008', '04', '01' ),
PARTITION P_200802 VALUES LESS THAN ('2008', '07', '01' ),
PARTITION P_200803 VALUES LESS THAN ('2008', '10', '01' ),
PARTITION P_200804 VALUES LESS THAN ('2009', '01', '01' ),
PARTITION P_5 VALUES LESS THAN (MAXVALUE)
TABLESPACE TABLE_SPACE_DATA_2
PCTFREE 5
PCTUSED 40
INITRANS 11
MAXTRANS 255
STORAGE
(
INITIAL 1M
NEXT 1M
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 121
);

2) Hash Partition
- Partitioning column의 Partitioning Key 값에 Hash 함수를 적용하여 Data를 분할하는 방식.
- 데이터 이력관리의 목적 보다 성능 향상의 목적으로 나온 개념이다.
Hash Partition은 Range Partition에서 범위를 기반으로 나누었을 경우 특정 범위에 분포도가 몰려서
- 각기 Size가 다르게 되는 것을 보완하여, 일정한 분포를 가진 파티션으로 나누고 균등한 데이터 분포도를
이용한 병렬처리로 퍼포먼스를 보다 향상시킬 수 있다.
- Hash Partition에서 Table은 단지 논리적인 구조이며 실제 데이터가 물리적으로 저장되는 곳은
Partition으로 나누어진 Tablespace에 저장이 된다.
- 구문 Sample
CREATE TABLE SALES_DATA_2008
(
COLUMN_1 NUMBER NOT NULL,
COLUMN_2 VARCHAR2(4),
COLUMN_3 VARCHAR2(4),
COLUMN_4 VARCHAR2(2),
COLUMN_5 VARCHAR2(2),
COLUMN_6 NUMBER
)
TABLESPACE TABLE_SPACE_DATA_1
PCTFREE 5
PCTUSED 40
INITRANS 11
MAXTRANS 255
STORAGE
(
INITIAL 2048K
NEXT 1024K
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 121
)
PARTITION BY HASH ( COLUMN_3, COLUMN_4, COLUMN_5 )
(
PARTITION P_200801,
PARTITION P_200802,
PARTITION P_200803,
PARTITION P_200804,
PARTITION P_5 VALUES LESS THAN (MAXVALUE)
)
);

3) Composite(Sub) Partition
- 파티션의 컬럼을 Main-Sub 관계로 나누어 분할하는 방식.
- Composite Partition이 아닌 다른 파티션에서 물리적인 데이터가 저장되는 곳은 Table이 아닌
Partition Table에 저장이 되는 것처럼, Composite Partition에서는 Main Partition이 아닌
Sub Partition에 저장된다.
- Composite Partition의 조합 구성은 Oracle의 버전이 올라갈수록 조합하는 방식을 다양하게 지원한다.

- 구문 Sample
CREATE TABLE SALES_DATA_2008
(
COLUMN_1 NUMBER NOT NULL,
COLUMN_2 VARCHAR2(4),
COLUMN_3 VARCHAR2(4),
COLUMN_4 VARCHAR2(2),
COLUMN_5 VARCHAR2(2),
COLUMN_6 NUMBER
)
TABLESPACE TABLE_SPACE_DATA_1
PCTFREE 5
PCTUSED 40
INITRANS 11
MAXTRANS 255
STORAGE
(
INITIAL 2048K
NEXT 1024K
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 121
)
PARTITION BY RANGE ( COLUMN_3, COLUMN_4 )
SUBPARTITION BY HASH ( COLUMN_5 )
(PARTITION P_200801 VALUES LESS THAN ('2008', '04'),
PARTITION P_200802 VALUES LESS THAN ('2008', '07'),
PARTITION P_200803 VALUES LESS THAN ('2008', '10')
(SUBPARTITIONS P_200803_S1 TABLESPACE TABLE_SPACE_DATA_1_1,
SUBPARTITIONS P_200803_S2 TABLESPACE TABLE_SPACE_DATA_1_2,
SUBPARTITIONS P_200803_S3 TABLESPACE TABLE_SPACE_DATA_1_3,
SUBPARTITIONS P_200803_S4 TABLESPACE TABLE_SPACE_DATA_1_4,
SUBPARTITIONS P_200803_S5 TABLESPACE TABLE_SPACE_DATA_1_5,
SUBPARTITIONS P_200803_S6 TABLESPACE TABLE_SPACE_DATA_1_6,
SUBPARTITIONS P_200803_S7 TABLESPACE TABLE_SPACE_DATA_1_7,
SUBPARTITIONS P_200803_S8 TABLESPACE TABLE_SPACE_DATA_1_8
),
PARTITION P_200804 VALUES LESS THAN ('2009', '01')
);

4) List Partition
- Partitioning column의 특정 값으로 분할하는 방식
- 데이터 분포도가 낮지 않고, 균등하게 분포되어 있을 때 유용하다.
- Composite Partition에서 'Range-List'일 경우 그 효율이 더욱 높아진다.
- 다른 파티션 방식처럼 다중 컬럼을 지원하지 않고 단일 컬럼만 가능하다.
- 구문 Sample
CREATE TABLE SALES_DATA_2008
(
COLUMN_1 NUMBER NOT NULL,
COLUMN_2 VARCHAR2(4),
COLUMN_3 VARCHAR2(4),
COLUMN_4 VARCHAR2(2),
COLUMN_5 VARCHAR2(2),
COLUMN_6 NUMBER
)
TABLESPACE TABLE_SPACE_DATA_1
PCTFREE 5
PCTUSED 40
INITRANS 11
MAXTRANS 255
STORAGE
(
INITIAL 2048K
NEXT 1024K
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 121
)
PARTITION BY LIST(COLUMN_2)
(
PARTITION RS VALUES('A') TABLESPACE TABLE_SPACE_DATA_2,
PARTITION RM VALUES('B') TABLESPACE TABLE_SPACE_DATA_3,
PARTITION RN VALUES('C') TABLESPACE TABLE_SPACE_DATA_4,
);

5) Reference Partition
- Reference Key로 지정된 경우 부모 테이블의 컬럼이 존재하지 않아도 부모의 Partition Key로
분할하는 방식.
- 구문 Sample
CREATE TABLE CUSTOMERS
(
CUST_ID NUMBER PRIMARY KEY,
CUST_NAME VARCHAR2(200),
RATING VARCHAR2(1) NOT NULL
)
PARTITION BY LIST(RATING)
(
PARTITION PA VALUES('A'),
PARTITION PB VALUES('B')
);

-- Detail Table
CREATE TABLE SALES
(
SALES_ID NUMBER PRIMARY KEY,
CUST_ID NUMBER NOT NULL,
SALES_AMT NUMBER,
CONSTRAINT FK_SALES_01 FOREIGN KEY (CUST_ID) REFERENCES CUSTOMERS
)
PARTITION BY REFERENCE (FK_SALES_01);
- 제약조건
-> Foreign Key 제약조건이 설정되어 있어야 한다.
-> 상속받는 테이블의 Key값이 NOT NULL 이어야 한다.
- 테스트
-- Normal
SELECT *
FROM SALE_TMP A,
CUSTOMERS B
WHERE A.CUST_ID = B.CUST_ID
AND B.RATING = 'A';

Rows Row Source Operation
------- ------------------------------------
0 STATEMENT
28 HASH JOIN
28 PARTITION LIST SINGLE PARTITION: 1
28 TABLE ACCESS FULL CUSTOMERS PARTITION: 1
56 TABLE ACCESS FULL SALE_TMP

-- Reference Partition
SELECT *
FROM SALES A,
CUSTOMERS B
WHERE A.CUST_ID = B.CUST_ID
AND B.RATING = 'A';

Rows Row Source Operation
------- -------------------------------------
0 STATEMENT
28 PARTITION LIST SINGLE PARTITION: 1
28 HASH JOIN
28 TABLE ACCESS FULL CUSTOMERS PARTITION: 1
28 TABLE ACCESS FULL SALES PARTITION: 1

6) Interval Partition
- Range Partition에서 특정 범위를 지정하고 관리할때는 미리 Range를 만들어주어야 하고
생성 이후 분할 또는 병합을 할 때는 추가적인 작업을 해주어야 한다.
- 하지만 'Interval Partition'에서는 각 파티션을 미리 정의함으로써 파티션 생성을 오라클이
직업 해주는 방식이다.
- 예제 Sample
-- 1. Range Partition 생성
CREATE TABLE SALES6
(
SALES_ID NUMBER,
SALES_DT DATE
)
PARTITION BY RANGE(SALES_DT)
(
PARTITION P0701 VALUES LESS THAN (TO_DATE('20070201', 'YYYYMMDD')),
PARTITION P0701 VALUES LESS THAN (TO_DATE('20070301', 'YYYYMMDD'))
);

-- 2. Partition Key 값의 범위에 없는 값으로 Insert
INSERT INTO SALES6 VALUES(1, TO_DATE('20070401', 'YYYYMMDD'));

-- Error
ORA-14400: inserted partition key does not map to any PARTITION

-- 3. Interval Partition 생성
CREATE TABLE SALES6
(
SALES_ID NUMBER,
SALES_DT DATE
)
PARTITION BY RANGE(SALES_DT) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(
PARTITION P0701 VALUES LESS THAN(TO_DATE('20080201', 'YYYYMMDD'))
);

-- 4. Partition Key 값의 범위에 없는 값으로 Insert
INSERT INTO SALES6 VALUES(1, TO_DATE('20070601', 'YYYYMMDD'));

-- No Error
1 row created.

- 파티션을 특정 테이블 스페이스에 저장하고 싶다면 STORE IN 구문으로 가능하다.
-> INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) STORE IN (TS1, TS2, TS3)
- 어플리케이션 개발자가 특정 파티션에 접근하고 싶다면 다음의 구문으로 가능하다.
-> SELECT * FROM SALES6 PARTITION FOR(TO_DATE('20080501', 'YYYYMMDD'));

7) System Partition
- 테이블 생성시 파티션 구간을 미리 설정하는 것이 아니라 임의로 나눈 파티션에 대해
사용자가 원하는 파티션에 데이터를 저장하는 방식.
- 이 방식은 사용자가 'System Partition'으로 되어 있는 테이블의 데이터를 DML 하고자 할 때
직접 파티션을 지정하여 해주어야 한다.
- 로컬 인덱스 생성 시, 인덱스도 동일한 방법으로 파티셔닝된다.
- 예제 Sample
CREATE TABLE SALES3
(
SALES_ID NUMBER,
PRODUCT_CODE NUMBER,
STATE_CODE NUMBER
)
PARTITION BY SYSTEM
(
PARTITION P1 TABLESPACE USERS,
PARTITION P2 TABLESPACE USERS
);
- Insert 할 때는 반드시 파티션을 지정해 주어야 한다.
-- Insert 할 때 테이블의 파티션을 지정하지 않을 경우
INSERT INTO SALES3 VALUES(1,101,1);

-- Error
ERROR at line 1:
ORA-14701: partition-extended name or bind variable must be used for DMLs on
tables partitioned by the System method

-- Insert을 할 때 테이블의 파티션을 지정한 경우
insert into sales3 partition (p1) values (1,101,1);

-- No Error
1 row created.
- Delete, Update 할 때는 필수는 아니지만 파티션을 지정하지 않을 경우 모든 파티션을
찾아다녀야 하므로 이 경우에도 가급적 파티션을 지정해 주어야 한다.
-> DELETE SALES3 PARTITION(P1) WHERE STATUS_CODE = 1;

8) Virtual Column Partition
- 파티션으로 나누고자 하는 컬럼이 테이블에서 가공되어 얻을 수 있는 컬럼일 경우
11g 이전에서는 새로운 컬럼을 추가하고 트리거를 이용하여 컬럼 값을 생성하는 방법을
사용하여 많은 오버헤드를 감수하였으나, 11g에서는 'Virtual Column Partition'을
지원하여 실제로 저장되지 않는 컬럼을 런타임에 계산하여 생성할 수 있다. 또한
가상 컬럼에 파티션을 적용하는 것도 가능하다.
- 예제 Sample
-- Virtual Partition 생성
CREATE TABLE SALES
(
SALES_ID NUMBER,
CUST_ID NUMBER,
SALE_CATEGORY VARCHAR2(6)
GENERATED ALWAYS AS
(
CASE WHEN SALES_AMT <= 10000 THEN 'LOW'
WHEN SALES_AMT BETWEEN 10000 AND 100000 THEN CASE WHEN CUST_ID < 101 THEN 'LOW'
WHEN BETWEEN 101 AND 200 THEN 'MEDIUM'
ELSE 'LOW' END
WHEN SALES_AMT BETWEEN 100000 AND 1000000 THEN CASE WHEN CUST_ID < 101 THEN 'MEDIUM'
WHEN BETWEEN 101 AND 200 THEN 'HIGH'
ELSE 'ULTRA' END
ELSE 'ULTRA' END
) VIRTUAL
)
PARTITION BY LIST(SALES_CATEGORY)
(
PARTITION P_LOW VALUES ('LOW'),
PARTITION P_MEDIUM VALUES ('MEDIUM'),
PARTITION P_HIGH VALUES ('HIGH'),
PARTITION P_ULTRA VALUES ('ULTRA')
);

-- Insert 테스트
INSERT INTO SALES(SALES_ID, CUST_ID, SALES_AMT) VALUES(1, 1, 100);

-- No Error
1 row created.

3. Partition Index
1) Local Index
- 인덱스를 생성한 인덱스와 파티션된 인덱스가 동일하게 파티션된 형태를 말한다.
- 인덱스와 테이블은 같은 칼럼에 의해 파티션되며, 하나의 인덱스 파티션이 테이블
파티션 하나와 대응되며, 대응되는 인덱스 파티션과 테이블 파티션은 각각 같은 범위를 갖게 된다.
- 결국 특정한 하나의 인덱스에 포함된 모든 Key들은 하나의 테이블 파티션 내의 데이타만을 가리키게 된다.

1-1) Local Prefixed Index
- 인덱스의 맨 앞에 있는 컬럼에 의해 파티션되는 방식이다.
- Local Prefixed Index에서 컬럼은 Unique/Non-Unique를 모두 허용한다.
- Base Table의 파티션이 변경되면 Local Index의 관련 파티션만 변경이 된다.
CREATE TABLE DEPT
(
DEPTNO NUMBER NOT NULL,
DNAME VARCHAR2(10) NOT NULL,
LOC VARCHAR2(14)
)
PARTITION BY RANGE (DEPTNO)
(PARTITION PART_1 VALUES LESS THAN (30),
PARTITION PART_2 VALUES LESS THAN (MAXVALUE)
);

CREATE INDEX DEPT_N1 ON DEPT(DEPTNO) LOCAL;

1-2) Local Non-Prefixed Index
- Index의 첫번째 column이 Partition Key가 아닌 형태로 Base Table과 동일한 Partition구조를 가진 Index이다.
(equi-partitioned)
- 빠른 access가 요구될 때 유용하다(Base Table의 Partition Key 는 제외...)
- Partition단위로 관리할 수 있으므로 Global Index에 비해 운영상 편리하다.
- OLAP 측면에서 Global Index보다 조회 속도가 저하된다.
CREATE TABLE DEPT
(
DEPTNO NUMBER NOT NULL,
DNAME VARCHAR2(10) NOT NULL,
LOC VARCHAR2(14)
)
PARTITION BY RANGE (DEPTNO)
(PARTITION PART_1 VALUES LESS THAN (30),
PARTITION PART_2 VALUES LESS THAN (MAXVALUE)
);

CREATE INDEX DEPT_N2 ON DEPT(LOC) LOCAL;

2) Global Index
- Global Index는 테이블과 다르게 파티션되는 경우이다.

2-1) Global Prefixed Index
- Base Table과 비교하여 not equi-partitioned 상태이다.
- Oracle은 only Index structure만 관리한다 (Partition은 관리안함)
- 최종 Partition에는 Maxvalue값이 반드시 기술되어야 한다.
- Local index보다 관리하기 힘들다.
- 기준 Table의 Partition이 변경되면 global index의 모든 Partition에 영향을 미친다
(Global Index 재생성 해야 함) -- 테이블 생성
CREATE TABLE SALES_DATA_2008
(
COLUMN_1 NUMBER NOT NULL,
COLUMN_2 VARCHAR2(4),
COLUMN_3 VARCHAR2(4),
COLUMN_4 VARCHAR2(2),
COLUMN_5 VARCHAR2(2),
COLUMN_6 NUMBER
)
PARTITION BY RANGE ( COLUMN_3, COLUMN_4 )
(
PARTITION P_200801 VALUES LESS THAN ( '04', '01' ),
PARTITION P_200802 VALUES LESS THAN ( '07', '01' ),
PARTITION P_200803 VALUES LESS THAN ( '10', '01' ),
PARTITION P_200804 VALUES LESS THAN ( '12', MAXVALUE )
);

-- Global Prefixed Index
CREATE UNIQUE INDEX RANGE2_GPX8 ON SALES_DATA_2008( COLUMN_2, COLUMN_1)
TABLESPACE TABLE_SPACE_DATA_1
PCTFREE 10
STORAGE(INITIAL 100K NEXT 100K PCTINCREASE 0 )
GLOBAL PARTITION BY RANGE ( CODE )
(
PARTITION P_2008_P1 VALUES LESS THAN ('2000'),
PARTITION P_2008_P2 VALUES LESS THAN ('3000'),
PARTITION P_2008_P3 VALUES LESS THAN (MAXVALUE)
);

2-2) Non-Partitioned Index
- 파티션과는 아무런 상관없는 Normal Index를 말함.

III. 파티션을 사용할 때 알아야 할 사항들

1. 파티션 테이블 및 인덱스 관리를 위한 명령어들
1) 일반 테이블 파티션하기
- Export/Import! 하는 방법
-- 테이블을 Export 한다.
exp user/password tables=number file=exp.dmp

-- 백업받은 테이블을 제거한다.
drop table numbers;

-- 파티션 테이블을 생성한다.
CREATE TABLE NUMBER(QTY NUMBER(3), NAME VARCHAR2(15))
PARTITION BY RANGE(QTY)
(PARTITION P1 VALUES LESS THAN (501),
PARTITION P2 VALUES LESS THAN (MAXVALUE));

-- ignore=y를 사용하여 데이터를 Import!한다.
imp user/password tables=number file=exp.dmp ignore=y

- Subquery를 이용한 방법
-- 파티션 테이블을 생성한다.
CREATE TABLE PARTBL(QTY NUMBER(3), NAME VARCHAR2(15))
PARTITION BY RANGE(QTY)
(PARTITION P1 VALUES LESS THAN(501),
PARTITION P2 VALUES LESS THAN(MAXVALUE));

-- Subquery를 이용하여 파티션 테이블에 데이터를 입력한다.
INSERT INTO PARTBL(QTY, NAME) SELECT * FROM ORIGTBL;

-- Subquery를 이용하여 파티션 테이블에 데이터를 입력한다.

- Partition Exchange 명령어를 사용하는 방법
-- ALTER TABLE EXCHANGE PARTITION은 파티션 테이블을 일반 테이블로, 또는 파티션되어
-- 있지 않은 테이블을 파티션 테이블로 변경시킬 때 사용한다.
-- 파티션 테이블 생성
CREATE TABLE P_EMP (SAL NUMBER(7,2))
PARTITION BY RANGE(SAL)
(PARTITION EMP_P1 VALUES LESS THAN (2000),
PARTITION EMP_P2 VALUES LESS THAN (4000));

-- 첫번째 파티션에 들어갈 데이터
CREATE TABLE DUMMY_Y
SELECT SAL
FROM EMP
WHERE SAL < 2000;

-- 두번째 파티션에 들어갈 데이터
CREATE TABLE DUMMY_Z
SELECT SAL
FROM EMP
WHERE SAL BETWEEN 2000 AND 3999;

ALTER TABLE P_EMP EXCHANGE PARTITION EMP_P1
WITH TABLE DUMMY_Y;

ALTER TABLE P_EMP EXCHANGE PARTITION EMP_P2
WITH TABLE DUMMY_Z;

- 여러 개의 파티션으로 분리된 테이블 중 일부의 파티션만 가진 테이블 생성하기
-- 데이타를 Export한 후 필요한 파티션으로 이루어진 테이블을 생성한다.
-- 데이터 생성
CREATE TABLE YEAR(COL1 DATE) PARTITION BY RANGE (COL1)
(PARTITION OCTOBER VALUES LESS THAN ('01-NOV-1999) TABLESPACE OCTOBER,
PARTITION NOVEMBER VALUES LESS THAN ('01-DEC-1999) TABLESPACE NOVEMBER,
PARTITION DECEMBER VALUES LESS THAN (MAXVALUE) TABLESPACE DECEMBER);

-- 데이타를 Import!
IMP USERNAME/PASSWORD FILE=EXPDAT.DMP FROMUSER=<OWNER> TOUSER=<OWNER>
TABLES=(YEAR:OCTOBER, YEAR:NOVEMBER, YEAR:DECEMBER)

- 파티션을 추가하는 방법
-- 파티션 테이블 생성
CREATE TABLE PART_TBL
(IN_DATE CHAR(8) PRIMARY KEY,
EMPNO NUMBER,
ENAME VARCHAR2(20),
JOB VARCHAR2(20))
PARTITION BY RANGE (IN_DATE)
(PARTITION PART_TBL_03 VALUE LESS THAN ('20000331') TABLESPACE PTS_03,
PARTITION PART_TBL_04 VALUE LESS THAN ('20000430') TABLESPACE PTS_04,
PARTITION PART_TBL_05 VALUE LESS THAN ('20000531') TABLESPACE PTS_05,
PARTITION PART_TBL_06 VALUE LESS THAN ('20000630') TABLESPACE PTS_06,
PARTITION PART_TBL_07 VALUE LESS THAN ('20000731') TABLESPACE PTS_07,
PARTITION PART_TBL_08 VALUE LESS THAN ('20000831') TABLESPACE PTS_08,
PARTITION PART_TBL_09 VALUE LESS THAN ('20000930') TABLESPACE PTS_09,
PARTITION PART_TBL_10 VALUE LESS THAN ('20001031') TABLESPACE PTS_10);

-- 파티션 추가
ALTER TABLE PART_TBL ADD PARTITION PART_TBL_11 VALUES LESS THAN ('20001130') TABLESPACE PTS_11;
ALTER TABLE PART_TBL ADD PARTITION PART_TBL_12 VALUES LESS THAN ('20001231') TABLESPACE PTS_12;

- 특정 파티션을 삭제하는 방법
특정 파티션 삭제 이후 삭제한 파티션의 값이 들어올 경우 그 다음 VALUES LESS THAN으로 편입된다.
ALTER TABLE PART_TBL DROP PARTITION PART_TBL_08;

- 파티션을 나누는 방법
만약 3월만 들어가있는 파티션이 있을 경우, 여기서 1, 2월을 추가하려면 파티션에서 Add가 아닌
Split을 해 주어야 한다.
-- 3월 파티션에서 2월과 3월을 Split함.
ALTER TABLE PART_TBL SPLIT PARTITION PART_TBL_03 AT ('20000229')
INTO (PARTITION PART_TBL_02 TABLESPACE PTS_02,
PARTITION PART_TBL_03_1 TABLESPACE PTS_03);

-- 2월 파티션에서 1월과 2월을 Split함.
ALTER TABLE PART_TBL SPLIT PARTITION PART_TBL_02 AT ('20000131')
INTO (PARTITION PART_TBL_01 TABLESPACE PTS_01,
PARTITION PART_TBL_02_1 TABLESPACE PTS_02);

- 파티션 이름을 변경하는 방법
ALTER TABLE PART_TBL RENAME PARTITION PART_TBL_02_1 TO PART_TBL_02;

- 파티션의 테이블스페이스를 옮기는 방법
ALTER TABLE PART_TBL MOVE PARTITION PART_TBL_10 TABLESPACE PTS_10_1 NOLOGGING;

- 특정 파티션의 데이터를 Truncate 하는 방법
Partition의 Data를 모두 삭제하려면 Truncate하는 방법을 사용할 수가 있는데,
Truncate는 Rollback이 불가능하며 특정 Partition 전체를 삭제하므로 주의하여 사용하여야 한다.
ALTER TABLE PART_TBL TRUNCATE PARTITION PART_TBL_02;

- 파티션 테이블의 물리적인 속성 변경하는 방법
Partition Table은 특정 Partition의 속성만 변경할 수 있고,
Table의 속성을 변경하여 전체 Partition에 대해 동일한 변경을 할 수 있다.

-- part_tbl의 모든 Partition의 Next 값이 변경
ALTER TABLE PART_TBL STORAGE (NEXT 10M);

part_tbl_05 Partition의 Maxextents 값만 변경
ALTER TABLE PART_TBL MODIFY PARTITION PART_TBL_05 STORAGE (MAXEXTENTS 1000);

- 인덱스 관리
파티션 테이블 관련 변경작업을 한 후에는 테이블에 걸려있는 Local, Glocal Index에 대해
반드시 Rebuild를 해 주어야 한다. -- 특정 파티션의 인덱스 Rebuild
ALTER INDEX IND_PART_TBL REBUILD PARTITION I_PART_TBL_02;

-- 글로벌 인덱스 Rebuild
ALTER INDEX PART_TBL_PK REBUILD;

2. Backup & Recovery
1) Export
- Table-Level Export
기존의 Table Export처럼 Table 전체를 Export하는 경우이다.
이는 Emp Table(Partitioned 또는 Non-Partitioned) 전체를 Export하는 경우이다.
$ exp scott/tiger tables=emp file=emp.dmp

- Partition-Level Export
이는 Partition Table의 일부 Partition만을 Export하는 것으로,
Full Mode의 Export시에는 사용하지 못하고, Table단위의 Export시에만 가능하다.
':' 을 이용하여 Partition 이름을 지정하며 이 경우 Emp Table의 px Partition만을 Export
$ exp scott/tiger tables=emp:px file=emp_par.dmp

- 다음과 같이 두 가지 경우를 Level을 혼용하여 사용하는 것도 가능하다.
Sales Table은 전부를, Emp Table에서는 px Partition만을 Export.
$ exp scott/tiger tables=(emp:px, sales) file=both.dmp

2) Import!
- Table-Level Import!
Partitioned 또는 Non-Partitioned Table 전체를 Import!한다.
모든 Import! Mode (full, user, table)에서 사용된다.
emp table(Partitioned 또는 non-Partitioned) 전체를 Import!
$ imp scott/tiger file=wookpark.dmp tables=emp

- Partition-Level Import!
Export Dump File에서 (full, user, table중 어떠한 Mode를 이용하여 Export했건간에)
Partitioned Tabled의 일부 Partition만을 Import!한다.
Table Import! Mode에서만 사용가능하다.

-- emp table의 px Partition만을 Import!.
-- ':'을 이용하여 Partition을 지정
$ imp scott/tiger file=wookpark.dmp tables=emp:px

테이블 단위의 Import!시 우선 Table Creation 문장을 수행하고 Row Insert문을 수행하는 것과
마찬가지로, Partition-level Import!도 우선 Partitioned Table의 생성 문장을 수행하고
Row Insert문을 수행하게 된다.
따라서 ignore=y option등을 적절히 사용하면, Non-Partitioned Table과 Partitioned Table간의 변경,
Partitioned Table의 구조 변경등을 수행할 수 있게 된다.
다음에는 그 중 몇 가지 예이다.

1. 파티션되어 있지 않은 테이블을 Exp, Imp를 이용하여 파티션 하는 예

1) 파티션되어 있지 않은 테이블을 Export한다.
$ exp scott/tiger file=wookpark.dmp tables=emp

2) 해당 Table을 Drop한다.
DROP TABLE EMP

3) 파티션 테이블을 생성한다.
CREATE TABLE EMP
(EMPNO NUMBER(4) NOT NULL,
....
)
PARTITION BY RANGE (EMPNO),
(PARTITION EMP1 VALUES LESS THAN (1000) TABLESPACE TS1,
PARTITION EMP1 VALUES LESS THAN (2000) TABLESPACE TS2,
PARTITION EMP1 VALUES LESS THAN (3000) TABLESPACE TS3);

4) Import!한다.
$ imp scott/tiger file=wookpark.dmp tables=emp ignore=y

2. Partitioned Table의 Partition들을 exp/imp를 이용하여 Merge하는 예
1) Merge의 대상이 되는 Partition을 Export한다.
$exp scott/tiger file=wookpark.dmp tables=emp:emp2

2) Merge의 대상이 되는 Partition을 'alter table...'문장으로 Drop한다.
ALTER TABLE EMP DROP PARTITION EMP2;

3) Import!한다.
$imp scott/tiger file=wookpark.dmp tables=emp:emp3 ignore=y
이후 emp Table을 확인하면, emp2 Partition에 있던 Data가 emp3 Partition에 Merge되어 있음을
확인할 수 있다.

3. IU(Index Unusable) 발생 주의
1) IU(Index Unusable)란 파티션이 변경됨으로 인해 파티션 테이블에 있는 인덱스에 영향을 주어
SELECT나 DML을 시도할 때 오류가 발생되는 것을 말한다.

2) IU(Index Unusable)를 발생시키는 Case
- Direct path Load의 경우
Direct path SQL*Loader 수행 후 인덱스가 테이블의 해당 데이터보다 이전 것이면,
Unusable 상태가 된다 (Oracle7에서는 인덱스가 Direct Load State가 되었다고 표현한다).
인덱스가 테이블의 데이터보다 이전 상태라는 것은 데이터를 Load한 후 인덱스를 생성하는 중에
Space 부족 등의 원인으로 오류가 발생하였거나, SKIP_INDEX_MAINTENANCE Option을 사용한 경우이다.

- ROWID가 변경되는 경우
ALTER TABLE MOVE PARTITION과 같이 ROWID를 변화시키는 작업은 영향받는 Local Index와
전체 Global Index를 Unusable 상태가 되게 한다.

- ROWID를 지우는 작업
ALTER TABLE TRUNCATE PARTITION이나 DROP PARTITION과 같이 테이블의 Row를 지우는 경우
영향받는 Local Index Partition과 모든 Global Index Partition을 Unusable 상태로 만든다.

- 테이블 Partition 정의를 변경하는 경우
ALTER TABLE SPLIT PARTITION은 Local Index의 Partition Definition은 변경시키지만,
자동으로 인덱스를 새로운 Definition에 맞게 Rebuild하지 않기 때문에 영향받는
Local Index Partition을 Unusable 상태로 만든다.
또한 이것은 ROWID를 변경시키기 때문에 모든 Global Index Partition을 Unusable 상태로 만든다.

- 인덱스 Partition 정의를 변경하는 경우
ALTER INDEX SPLIT PARTITION은 Index의 Definition은 변경시키지만,
영향받은 Partition은 Rebuild시키지 않는다.
이 작업은 영향받는 인덱스 파티션 부분을 Unusable 상태로 만든다.
그러나 Global Index의 경우는 그대로 Usable 상태로 된다.

3) 결과적으로 IU 상태가 되면 다음과 같이 조치해야 한다.
- Partition Index : Rebuild
- Non-Partition Index : Drop and Recreate

IV. 일반 테이블과 파티션 테이블 테스트
0. 기초 데이터 만들기
1) 일반 테이블

① 테이블
CREATE TABLE APPS.NORMAL_TEST_TAB
(
MON_P1 DATE,
GRP_P2 NUMBER,
CNT_N1 NUMBER
);

② 샘플 데이터 넣기
INSERT INTO APPS.NORMAL_TEST_TAB
SELECT ADD_MONTHS(TO_DATE('200001', 'YYYYMM'),
DECODE(MOD(LEVEL, 10000),
0,
TRUNC(LEVEL / 10000),
TRUNC(LEVEL / 10000) + 1) - 1) MON_P1,
DECODE(MOD(LEVEL, 100),
0,
TRUNC(LEVEL / 100),
TRUNC(LEVEL / 100) + 1) * 100 GRP_P2,
LEVEL CNT_N1
FROM DUAL
CONNECT BY LEVEL <= 1000000
;

COMMIT;

③ 통계정보
BEGIN
DBMS_STATS.GATHER_TABLE_STATS('APPS',
'NORMAL_TEST_TAB',
CASCADE => TRUE);
END;
/

NORMAL_TEST_TAB
---------------
Rows=997,141 Blocks=3,032
Empty Blocks=0 Avg Space=0
Chain Count=0 Avg Row Length=16
Avg Space Freelist Blocks=0 Freelist Blocks=0
Sample Size=54,167 Last Analyze=2008/08/24
Partitioned=NO

Column Name Nullable Column Type Distinct Buckets
-------------------------------- -------- ------------- ---------- ----------
MON_P1 DATE 101 1
GRP_P2 NUMBER 9,999 1
CNT_N1 NUMBER 997,141 1

INDEX
-----------------------------------------------------------------------------

2) 파티션 테이블

① 테이블
CREATE TABLE APPS.PARTITION_TEST_TAB
(
MON_P1 DATE,
GRP_P2 NUMBER,
CNT_N1 NUMBER
)
PARTITION BY RANGE ( MON_P1 )
SUBPARTITION BY HASH( GRP_P2 )
(
PARTITION P200001 VALUES LESS THAN(TO_DATE('200001', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200002 VALUES LESS THAN(TO_DATE('200002', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200003 VALUES LESS THAN(TO_DATE('200003', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200004 VALUES LESS THAN(TO_DATE('200004', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200005 VALUES LESS THAN(TO_DATE('200005', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200006 VALUES LESS THAN(TO_DATE('200006', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200007 VALUES LESS THAN(TO_DATE('200007', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200008 VALUES LESS THAN(TO_DATE('200008', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200009 VALUES LESS THAN(TO_DATE('200009', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200010 VALUES LESS THAN(TO_DATE('200010', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200011 VALUES LESS THAN(TO_DATE('200011', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200012 VALUES LESS THAN(TO_DATE('200012', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200101 VALUES LESS THAN(TO_DATE('200101', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200102 VALUES LESS THAN(TO_DATE('200102', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200103 VALUES LESS THAN(TO_DATE('200103', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200104 VALUES LESS THAN(TO_DATE('200104', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200105 VALUES LESS THAN(TO_DATE('200105', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200106 VALUES LESS THAN(TO_DATE('200106', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200107 VALUES LESS THAN(TO_DATE('200107', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200108 VALUES LESS THAN(TO_DATE('200108', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200109 VALUES LESS THAN(TO_DATE('200109', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200110 VALUES LESS THAN(TO_DATE('200110', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200111 VALUES LESS THAN(TO_DATE('200111', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200112 VALUES LESS THAN(TO_DATE('200112', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200201 VALUES LESS THAN(TO_DATE('200201', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200202 VALUES LESS THAN(TO_DATE('200202', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200203 VALUES LESS THAN(TO_DATE('200203', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200204 VALUES LESS THAN(TO_DATE('200204', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200205 VALUES LESS THAN(TO_DATE('200205', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200206 VALUES LESS THAN(TO_DATE('200206', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200207 VALUES LESS THAN(TO_DATE('200207', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200208 VALUES LESS THAN(TO_DATE('200208', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200209 VALUES LESS THAN(TO_DATE('200209', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200210 VALUES LESS THAN(TO_DATE('200210', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200211 VALUES LESS THAN(TO_DATE('200211', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200212 VALUES LESS THAN(TO_DATE('200212', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200301 VALUES LESS THAN(TO_DATE('200301', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200302 VALUES LESS THAN(TO_DATE('200302', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200303 VALUES LESS THAN(TO_DATE('200303', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200304 VALUES LESS THAN(TO_DATE('200304', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200305 VALUES LESS THAN(TO_DATE('200305', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200306 VALUES LESS THAN(TO_DATE('200306', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200307 VALUES LESS THAN(TO_DATE('200307', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200308 VALUES LESS THAN(TO_DATE('200308', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200309 VALUES LESS THAN(TO_DATE('200309', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200310 VALUES LESS THAN(TO_DATE('200310', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200311 VALUES LESS THAN(TO_DATE('200311', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200312 VALUES LESS THAN(TO_DATE('200312', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200401 VALUES LESS THAN(TO_DATE('200401', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200402 VALUES LESS THAN(TO_DATE('200402', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200403 VALUES LESS THAN(TO_DATE('200403', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200404 VALUES LESS THAN(TO_DATE('200404', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200405 VALUES LESS THAN(TO_DATE('200405', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200406 VALUES LESS THAN(TO_DATE('200406', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200407 VALUES LESS THAN(TO_DATE('200407', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200408 VALUES LESS THAN(TO_DATE('200408', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200409 VALUES LESS THAN(TO_DATE('200409', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200410 VALUES LESS THAN(TO_DATE('200410', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200411 VALUES LESS THAN(TO_DATE('200411', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200412 VALUES LESS THAN(TO_DATE('200412', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200501 VALUES LESS THAN(TO_DATE('200501', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200502 VALUES LESS THAN(TO_DATE('200502', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200503 VALUES LESS THAN(TO_DATE('200503', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200504 VALUES LESS THAN(TO_DATE('200504', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200505 VALUES LESS THAN(TO_DATE('200505', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200506 VALUES LESS THAN(TO_DATE('200506', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200507 VALUES LESS THAN(TO_DATE('200507', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200508 VALUES LESS THAN(TO_DATE('200508', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200509 VALUES LESS THAN(TO_DATE('200509', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200510 VALUES LESS THAN(TO_DATE('200510', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200511 VALUES LESS THAN(TO_DATE('200511', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200512 VALUES LESS THAN(TO_DATE('200512', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200601 VALUES LESS THAN(TO_DATE('200601', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200602 VALUES LESS THAN(TO_DATE('200602', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200603 VALUES LESS THAN(TO_DATE('200603', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200604 VALUES LESS THAN(TO_DATE('200604', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200605 VALUES LESS THAN(TO_DATE('200605', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200606 VALUES LESS THAN(TO_DATE('200606', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200607 VALUES LESS THAN(TO_DATE('200607', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200608 VALUES LESS THAN(TO_DATE('200608', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200609 VALUES LESS THAN(TO_DATE('200609', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200610 VALUES LESS THAN(TO_DATE('200610', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200611 VALUES LESS THAN(TO_DATE('200611', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200612 VALUES LESS THAN(TO_DATE('200612', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200701 VALUES LESS THAN(TO_DATE('200701', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200702 VALUES LESS THAN(TO_DATE('200702', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200703 VALUES LESS THAN(TO_DATE('200703', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200704 VALUES LESS THAN(TO_DATE('200704', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200705 VALUES LESS THAN(TO_DATE('200705', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200706 VALUES LESS THAN(TO_DATE('200706', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200707 VALUES LESS THAN(TO_DATE('200707', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200708 VALUES LESS THAN(TO_DATE('200708', 'YYYYMM')) SUBPARTITIONS 100,
PARTITION P200709 VALUES LESS THAN(TO_DATE('200709', 'YYYYMM')) SUBPARTITIONS 100,
-- 9400에서 10,000개 파티션에 근접해서 더 늘어나는 구간이라 MAXVALUE 처리함
PARTITION P5 VALUES LESS THAN(MAXVALUE) SUBPARTITIONS 100
);

② 샘플 데이터 넣기
INSERT INTO APPS.PARTITION_TEST_TAB
SELECT ADD_MONTHS(TO_DATE('200001', 'YYYYMM'),
DECODE(MOD(LEVEL, 10000),
0,
TRUNC(LEVEL / 10000),
TRUNC(LEVEL / 10000) + 1) - 1) MON_P1,
DECODE(MOD(LEVEL, 100),
0,
TRUNC(LEVEL / 100),
TRUNC(LEVEL / 100) + 1) * 100 GRP_P2,
LEVEL CNT_N1
FROM DUAL
CONNECT BY LEVEL <= 1000000
;

COMMIT;

③ 통계정보
BEGIN
DBMS_STATS.GATHER_TABLE_STATS('APPS',
'PARTITION_TEST_TAB',
CASCADE => TRUE);
END;
/

PARTITION_TEST_TAB
------------------
Rows=998,668 Blocks=74,646
Empty Blocks=0 Avg Space=0
Chain Count=0 Avg Row Length=16
Avg Space Freelist Blocks=0 Freelist Blocks=0
Sample Size=56,777 Last Analyze=2008/08/24
Partitioned=YES

Column Name Nullable Column Type Distinct Buckets
-------------------------------- -------- ------------- ---------- ----------
MON_P1 DATE 101 1
GRP_P2 NUMBER 9,994 1
CNT_N1 NUMBER 998,668 1

INDEX
-----------------------------------------------------------------------------

Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 11 0.080 0.087 0 3064 0 100
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 13 0.080 0.088 0 3064 0 100

Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
100 TABLE ACCESS FULL NORMAL_TEST_TAB (cr=3064 pr=0 pw=0 time=70247 us)

② 파티션 테이블
SELECT *
FROM PARTITION_TEST_TAB
WHERE MON_P1 = :B1 -- 2007/01/01
AND GRP_P2 = :B2 -- 841000
;

Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 11 0.000 0.001 0 25 0 100
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 13 0.000 0.001 0 25 0 100

Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
100 PARTITION RANGE SINGLE PARTITION: KEY KEY (cr=25 pr=0 pw=0 time=120 us)
100 PARTITION HASH SINGLE PARTITION: KEY KEY (cr=25 pr=0 pw=0 time=96 us)
100 TABLE ACCESS FULL PARTITION_TEST_TAB PARTITION: KEY KEY (cr=25 pr=0 pw=0 time=80 us)

2) 파티션 키가 아닌 것으로 Full Table Scan

① 일반 테이블
SELECT *
FROM NORMAL_TEST_TAB
WHERE CNT_N1 = :B1 -- 900000
;

Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.400 0.412 0 3055 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.400 0.412 0 3055 0 1

Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 TABLE ACCESS FULL NORMAL_TEST_TAB (cr=3055 pr=0 pw=0 time=371933 us)

② 파티션 테이블
-- 2개 파티션 키 모두 없이
SELECT *
FROM PARTITION_TEST_TAB
WHERE CNT_N1 = :B1 -- 900000
;

Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.940 0.936 0 97104 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.940 0.937 0 97104 0 1

Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 PARTITION RANGE ALL PARTITION: 1 94 (cr=97104 pr=0 pw=0 time=873415 us)
1 PARTITION HASH ALL PARTITION: 1 100 (cr=97104 pr=0 pw=0 time=933492 us)
1 TABLE ACCESS FULL PARTITION_TEST_TAB PARTITION: 1 9400 (cr=97104 pr=0 pw=0 time=924367 us)

-- 1개 파티션 키 선두(1블럭당 5개 로우 액세스 - 그나마 효율)
SELECT *
FROM PARTITION_TEST_TAB
WHERE MON_P1 = :B1 -- 2007/01/01
;

Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 1001 0.000 0.046 0 1948 0 10000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 1003 0.000 0.046 0 1948 0 10000

Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
10000 PARTITION RANGE SINGLE PARTITION: KEY KEY (cr=1948 pr=0 pw=0 time=40165 us)
10000 PARTITION HASH ALL PARTITION: 1 100 (cr=1948 pr=0 pw=0 time=40148 us)
10000 TABLE ACCESS FULL PARTITION_TEST_TAB PARTITION: KEY KEY (cr=1948 pr=0 pw=0 time=3704 us)

-- 1개 파티션 키 후발(100블럭당 8개 로우 액세스 - 비효율적)
SELECT *
FROM PARTITION_TEST_TAB
WHERE GRP_P2 = :B2 -- 841000
;

Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 11 0.010 0.013 0 1240 0 100
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 13 0.010 0.013 0 1240 0 100

Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
100 PARTITION RANGE ALL PARTITION: 1 94 (cr=1240 pr=0 pw=0 time=10802 us)
100 PARTITION HASH SINGLE PARTITION: KEY KEY (cr=1240 pr=0 pw=0 time=12343 us)
100 TABLE ACCESS FULL PARTITION_TEST_TAB PARTITION: (cr=1240 pr=0 pw=0 time=11913 us)

-- 파티션 키에서 선두 없이 후발만 들어오게 되면 비효율적

2. Index Ragne Scan
1) 인덱스 생성

① 일반 테이블
CREATE INDEX APPS.NORMAL_TEST_TAB_N1 ON APPS.NORMAL_TEST_TAB (MON_P1)
TABLESPACE APPS_TS_TX_IDX COMPUTE STATISTICS PARALLEL 6;
CREATE INDEX APPS.NORMAL_TEST_TAB_N2 ON APPS.NORMAL_TEST_TAB (MON_P1, GRP_P2)
TABLESPACE APPS_TS_TX_IDX COMPUTE STATISTICS PARALLEL 6;
CREATE INDEX APPS.NORMAL_TEST_TAB_N3 ON APPS.NORMAL_TEST_TAB (CNT_N1)
TABLESPACE APPS_TS_TX_IDX COMPUTE STATISTICS PARALLEL 6;

ALTER INDEX APPS.NORMAL_TEST_TAB_N1 NOPARALLEL;
ALTER INDEX APPS.NORMAL_TEST_TAB_N2 NOPARALLEL;
ALTER INDEX APPS.NORMAL_TEST_TAB_N3 NOPARALLEL;

② 파티션 테이블
-- Local Prefixed Index
CREATE INDEX APPS.PARTITION_TEST_TAB_N1 ON APPS.PARTITION_TEST_TAB (MON_P1)
TABLESPACE APPS_TS_TX_IDX COMPUTE STATISTICS PARALLEL 6 LOCAL;
CREATE INDEX APPS.PARTITION_TEST_TAB_N2 ON APPS.PARTITION_TEST_TAB (MON_P1, GRP_P2)
TABLESPACE APPS_TS_TX_IDX COMPUTE STATISTICS PARALLEL 6 LOCAL;

-- Global Prefixed Index
CREATE INDEX APPS.PARTITION_TEST_TAB_N3 ON APPS.PARTITION_TEST_TAB (CNT_N1)
GLOBAL PARTITION BY RANGE(CNT_N1) (PARTITION P1 VALUES LESS THAN (100000),
PARTITION P2 VALUES LESS THAN (200000),
PARTITION P3 VALUES LESS THAN (300000),
PARTITION P4 VALUES LESS THAN (400000),
PARTITION P5 VALUES LESS THAN (500000),
PARTITION P6 VALUES LESS THAN (600000),
PARTITION P7 VALUES LESS THAN (700000),
PARTITION P8 VALUES LESS THAN (800000),
PARTITION P9 VALUES LESS THAN (900000),
PARTITION P10 VALUES LESS THAN (1000000),
PARTITION P11 VALUES LESS THAN (MAXVALUE))
TABLESPACE APPS_TS_TX_IDX COMPUTE STATISTICS PARALLEL 6;

ALTER INDEX APPS.PARTITION_TEST_TAB_N1 NOPARALLEL;
ALTER INDEX APPS.PARTITION_TEST_TAB_N2 NOPARALLEL;
ALTER INDEX APPS.PARTITION_TEST_TAB_N3 NOPARALLEL;

Column Name Nullable Column Type Distinct Buckets
-------------------------------- -------- ------------- ---------- ----------
MON_P1 DATE 101 1
GRP_P2 NUMBER 9,999 1
CNT_N1 NUMBER 997,141 1

INDEX
-----------------------------------------------------------------------------
NORMAL_TEST_TAB_N1 : MON_P1
Type=NORMAL, Uniq=No, Distinct=100, Rows=1,000,000, Last Analyze=2008/08/24
NORMAL_TEST_TAB_N2 : MON_P1 + GRP_P2
Type=NORMAL, Uniq=No, Distinct=10,000, Rows=1,000,000, Last Analyze=2008/08/24
NORMAL_TEST_TAB_N3 : CNT_N1
Type=NORMAL, Uniq=No, Distinct=1,000,000, Rows=1,000,000, Last Analyze=2008/08/24

② 파티션 테이블
PARTITION_TEST_TAB
------------------
Rows=998,668 Blocks=74,646
Empty Blocks=0 Avg Space=0
Chain Count=0 Avg Row Length=16
Avg Space Freelist Blocks=0 Freelist Blocks=0
Sample Size=56,777 Last Analyze=2008/08/24
Partitioned=YES

Column Name Nullable Column Type Distinct Buckets
-------------------------------- -------- ------------- ---------- ----------
MON_P1 DATE 101 1
GRP_P2 NUMBER 9,994 1
CNT_N1 NUMBER 998,668 1

INDEX
-----------------------------------------------------------------------------
PARTITION_TEST_TAB_N1 : MON_P1
Partition=LOCAL NON_PREFIXED
Type=NORMAL, Uniq=No, Distinct=8, Rows=1,000,000, Last Analyze=2008/08/24
PARTITION_TEST_TAB_N2 : MON_P1 + GRP_P2
Partition=LOCAL PREFIXED
Type=NORMAL, Uniq=No, Distinct=10,000, Rows=1,000,000, Last Analyze=2008/08/24
PARTITION_TEST_TAB_N3 : CNT_N1
Partition=GLOBAL PREFIXED
Type=NORMAL, Uniq=No, Distinct=1,000,000, Rows=1,000,000, Last Analyze=2008/08/24

Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 11 0.000 0.000 0 24 0 100
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 13 0.000 0.001 0 24 0 100

Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
100 TABLE ACCESS BY INDEX ROWID NORMAL_TEST_TAB (cr=24 pr=0 pw=0 time=36 us)
100 INDEX RANGE SCAN NORMAL_TEST_TAB_N2 (cr=13 pr=0 pw=0 time=621 us)(Object ID 2885525)

SELECT *
FROM NORMAL_TEST_TAB
WHERE CNT_N1 = :B1 -- 900000
;

Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.000 0 5 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.000 0.000 0 5 0 1

Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 TABLE ACCESS BY INDEX ROWID NORMAL_TEST_TAB (cr=5 pr=0 pw=0 time=40 us)
1 INDEX RANGE SCAN NORMAL_TEST_TAB_N3 (cr=4 pr=0 pw=0 time=36 us)(Object ID 2885526)

② 파티션 테이블
SELECT *
FROM PARTITION_TEST_TAB
WHERE MON_P1 = :B1 -- 2007/01/01
AND GRP_P2 = :B2 -- 841000
;

Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 11 0.000 0.001 0 23 0 100
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 13 0.000 0.001 0 23 0 100

Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
100 PARTITION RANGE SINGLE PARTITION: KEY KEY (cr=23 pr=0 pw=0 time=71 us)
100 PARTITION HASH SINGLE PARTITION: KEY KEY (cr=23 pr=0 pw=0 time=55 us)
100 TABLE ACCESS BY LOCAL INDEX ROWID PARTITION_TEST_TAB PARTITION: KEY KEY (cr=23 pr=0 pw=0 time=46 us)
100 INDEX RANGE SCAN PARTITION_TEST_TAB_N2 PARTITION: KEY KEY (cr=12 pr=0 pw=0 time=826 us)(Object ID 2895022)

-- Global Prefixed Index
SELECT *
FROM PARTITION_TEST_TAB
WHERE CNT_N1 = :B1 -- 900000
;

Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.000 0 4 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.000 0.000 0 4 0 1

Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 PARTITION RANGE SINGLE PARTITION: KEY KEY (cr=4 pr=0 pw=0 time=49 us)
1 TABLE ACCESS BY GLOBAL INDEX ROWID PARTITION_TEST_TAB PARTITION: ROW LOCATION ROW LOCATION (cr=4 pr=0 pw=0 time=35 us)
1 INDEX RANGE SCAN PARTITION_TEST_TAB_N3 PARTITION: KEY KEY (cr=3 pr=0 pw=0 time=30 us)(Object ID 2904517)
;

-- Local Non-Prefixed Index
DROP INDEX APPS.PARTITION_TEST_TAB_N3;

CREATE INDEX APPS.PARTITION_TEST_TAB_N3 ON APPS.PARTITION_TEST_TAB (CNT_N1) TABLESPACE APPS_TS_TX_IDX COMPUTE STATISTICS PARALLEL 6 LOCAL;

-- FULL COLUMN
SELECT *
FROM PARTITION_TEST_TAB
WHERE MON_P1 = :B1 -- 2007/01/01
AND GRP_P2 = :B2 -- 841000
AND CNT_N1 = :B3 -- 840936
;

Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.000 0 4 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.000 0.000 0 4 0 1

Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 PARTITION RANGE SINGLE PARTITION: KEY KEY (cr=4 pr=0 pw=0 time=99 us)
1 PARTITION HASH SINGLE PARTITION: KEY KEY (cr=4 pr=0 pw=0 time=73 us)
1 TABLE ACCESS BY LOCAL INDEX ROWID PARTITION_TEST_TAB PARTITION: KEY KEY (cr=4 pr=0 pw=0 time=55 us)
1 INDEX RANGE SCAN PARTITION_TEST_TAB_N3 PARTITION: KEY KEY (cr=3 pr=0 pw=0 time=41 us)(Object ID 2904529)

-- 후발 없이(선두키가 없으면 PARALLEL 처리할 떄 보이는 실행계획 나옴)
SELECT *
FROM PARTITION_TEST_TAB
WHERE MON_P1 = :B1 -- 2007/01/01
AND CNT_N1 = :B3 -- 840936
;

Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.018 0 200 0 0
Fetch 2 0.010 0.117 0 0 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.010 0.135 0 200 0 1

Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 PX COORDINATOR (cr=200 pr=0 pw=0 time=37561 us)
0 PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us)
0 PX PARTITION HASH ALL PARTITION: 1 100 (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS BY LOCAL INDEX ROWID PARTITION_TEST_TAB PARTITION: KEY KEY (cr=0 pr=0 pw=0 time=0 us)
0 INDEX RANGE SCAN PARTITION_TEST_TAB_N3 PARTITION: KEY KEY (cr=0 pr=0 pw=0 time=0 us)(Object ID 2904529)

-- 선두 없이(선두키가 없으면 PARALLEL 처리할 떄 보이는 실행계획 나옴)
SELECT *
FROM PARTITION_TEST_TAB
WHERE GRP_P2 = :B2 -- 841000
AND CNT_N1 = :B3 -- 840936
;

Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.009 0 188 0 0
Fetch 2 0.010 0.135 0 0 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.010 0.144 0 188 0 1

Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 PX COORDINATOR (cr=188 pr=0 pw=0 time=46595 us)
0 PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us)
0 PX PARTITION HASH SINGLE PARTITION: KEY KEY (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS BY LOCAL INDEX ROWID PARTITION_TEST_TAB PARTITION: (cr=0 pr=0 pw=0 time=0 us)
0 INDEX RANGE SCAN PARTITION_TEST_TAB_N3 PARTITION: (cr=0 pr=0 pw=0 time=0 us)(Object ID 2904529)

출처 : http://docs.oracle.com/cd/E11882_01/server.112/e10819/intro.htm#BHCHIAFE

번역 : TexasCowboy 

Moving From Standard Edition to Enterprise Edition of Oracle Database

If you have Oracle Database Standard Edition at a release earlier than the new Oracle Database 11g release, then you can change it to Oracle Database Enterprise Edition by installing Oracle Enterprise Edition software and subsequently following the normal upgrade procedures, as described in Chapter 3, "Upgrading to the New Release of Oracle Database.".

To change your existing Standard Edition database to an Enterprise Edition database 

1. Ensure that the release number of your Standard Edition server software is the same release as Enterprise Edition server software.
   사용중인 스탠다드 에디션 릴리즈 번호와 설치하려는 엔터프라이즈 에디션 릴리즈 번호가 동일한지 확인해라. ( 동일하지 않으면 안된다. )

   For example, if your Standard Edition server software is release 11.1.0.6, then you must upgrade to release 11.1.0.6 of Enterprise Edition.
    

2. Shut down your database.
   데이타베이스를 중지시킨다.
    

3. If your operating system is Windows, then stop all Oracle services, including the OracleServiceSID Oracle service, where SID is the instance name.
   윈도우 운영체제를 사용중이라면 OracleServiceSID Oracle Service를 포함한 모든 오라클 서비스를 중지시켜야 한다.
    

4. Make a backup copy of files under $Oracle_Home/network/admin. This ensures that the listener is configured after you complete the steps in this procedure.
   $Oracle_Home/network/admin 폴더 안에 백업파일을 넣는다.
    

5. Back up all database files and data under the current Oracle home that you need to keep.

   • On Linux, back up $Oracle_Home/dbs/.

   • On Windows, back up $Oracle_Home/database/.

    

   
   DB파일을 모두 백업한다.
    

    

6. Edit the orabase_cleanup.lst file found in the Oracle_Home/utl directory and remove the "oradata" and "admin" entries. Refer to "Deinstallation of the Oracle home in the earlier release of Oracle Database may result in the deletion of the old Oracle base that was associated with it".
   Oracle_Home/utl 디렉토리 안에있는 orabase_cleanup.lst 파일을 수정해서 oradata와 admin 부분을 지워준다.
    

7. Deinstall the Standard Edition server software.

   Run the deinstall tool from the Oracle home. The deinstall tool is located in the $ORACLE_HOME/deinstall directory for UNIX platforms, and in the ORACLE_HOME\deinstall directory for Windows platforms.

   IMPORTANT:

   The deinstall tool deletes all existing database files that reside under Oracle home, including data in the database. Therefore, Oracle recommends that you retain a current backup of your data.
   스탠다드 에디션을 deinstall한다. Oracle Home디렉토리 안에 deinstall디렉토리가 있고 그 안에서 deinstall실행파일을 찾을 수 있을 것이다. deinstall명령은 현존하는 Oracle Home밑의 모든 DB파일과 데이타를 삭제하므로 백업을 해두는 것이 좋을 것이다.
    

8. Install Enterprise Edition server software using Oracle Universal Installer (OUI).

   Select the same Oracle home that was used for the Standard Edition that you uninstalled. During the installation, be sure to select Enterprise Edition. When prompted, choose Software Only from the Database Configuration screen.
   OUI를 이용해서 엔터프라이즈 에디션을 설치한다. 스탠다드 에디션이 사용했던 Oracle Home과 동일한 경로를 Oracle Home으로 지정한다.
    

9. Start up your database.
   데이타베이스를 시작한다.

Your database is now upgraded to Enterprise Edition.

Known Issue with the Deinstallation Tool for Release 11.2.0.4

Deinstallation of the Oracle home in the earlier release of Oracle Database may result in the deletion of the old Oracle base that was associated with it

Cause: After upgrading from Oracle Database release 11.2.0.1 or 11.2.0.2 to release 11.2.0.4, deinstallation of the Oracle home in the earlier release of Oracle Database may result in the deletion of the old Oracle base that was associated with it. This may also result in the deletion of data files, audit files, etc., which are stored under the old Oracle base. Important Note: This issue is applicable for upgrades from release 11.2.0.1 or 11.2.0.2 to 11.2.0.4, but is not applicable for upgrading from release 11.2.0.3 to 11.2.0.4.

Action: Before deinstalling the Oracle home in the earlier release, edit the orabase_cleanup.lst file found in the $Oracle_Home/utl directory and remove the "oradata" and "admin" entries. Then proceed with the deinstallation.

뭐 이런 정보들이야 설치할 때 다 알고 설치하지만 나중에 까먹는 경우도 있으니 알아두면 좋은정보라 생각한다.

select * from v$version;


DBA권한을 갖고 있는 사용자가 위 명령어를 입력하면 아래와 같은 결과가 나올 것이다.


경우에 따라


1. 엔터프라이즈 인경우

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
 

2. 스탠다드 인경우

Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

오라클 DB를 백업할때 exp명령어를 이용할 때 아래와 같은 오류메시지를 본 적이 있을 것이다.

EXP-00091 Exporting questionable statistics

이 에러는 data를 export할 때 해당 데이타와 관련된 optimizer statistics를 함께 export하려고 할 때 발생한다고 한다. export를 한다는 것은 단순히 백업용도로 사용하는 경우도 있겠지만 다른 서버로 데이타를 이전 할 때 사용하기도 한다. 데이터를 이전하게되면 오라클은 변경된 환경에 맞게끔 plan을 세워야 하는데 기존의 통계를 함께 export하게되면 plan을 정할 때 최적화된 plan을 세우는데 영향을 미칠 수 있기 때문에 경고메시지를 띄워주는 것이다.

통계를 제외하고 데이타만 export하기 위해서는 exp명령어 옵션 중에 statistics=none이라는 옵션을 추가로 붙여주면 된다. 

좀 더 자세한 사항은 아래 링크를 참조하길 바란다.

http://www.dba-oracle.com/t_exp_00091_exporting_questionable_statistics.htm

계정 생성

권한 부여, 조회

 resource, connect 는 일반적인 것을 다 사용할 수 있다. (DDL, DML 등)

테이블스페이스 지정

계정 삭제

 cascade 를 명시하면 유저네임과 관련된 모든 데이터베이스 스키마가 데이터 사전으로부터 삭제되고 모든 스키마 객체도 물리적으로 삭제된다.