particionar tablas

mapao
15 de Diciembre del 2004
Como hago para particionar una tabla ya existente, el cual tiene un campo de fecha. Mi pregunta es como es la consulta que debo ejcutar y el proceso de seguimiento ó donde puedo conseguir información al respecto.

set
15 de Diciembre del 2004
que quiere decir con "particionar" una tabla

sys
15 de Diciembre del 2004
PARTITION TABLES ================ This FAQ discusses what partition tables are all about, how to create them and how to perform ddl and dml operations on them. Q) What is partitioning? --> Partitioning allows users to decompose tables and indexes into smaller and more manageable pieces called partitions. Q) What kinds of objects can be partitioned? --> Oracle8 supports only table and index level partitioning. Clustered tables/indexes and snapshots are not supported. Q) What is the relationship between a partition and a segment? --> Each partition is stored in a separate segment. --> DBA_SEGMENTS will give an idea of which segment stores which partition. Q) How can the physical and logical attributes of a partition be defined? --> Logical attributes: all the partitions of a table/index should contain the same columns and constraint definitions --> Physical attributes: each partition can have it's own storage parameters and each partition can reside in a separate tablespace --> DBA_TAB_PARTITIONS will list the storage parameters for each partition as well as give the tablespace name associated with each partition. Q) How can partitions be defined? --> There are 4 types of partitioning methods: Range - maps data based on the range of values of the partitioning key available from 8.0 Hash - uses a hash function to distribute the data into the partitions available from 8.1.7 List - maps data based on list of discrete values that you specify available from 9.0.1 Composite - is a combination of any of the above partitioning methods available from 8.1.7 Q) How are partitions named? --> When a partition is created, you have the option to specify a name for the partition. If the name is not specified then Oracle generates a name of the format SYS_Pn for table level partitions and SYS_Cn for index level partitions where 'n' is an integer that makes the name unique within the database. Q) How to create a partitioned table? --> An example would be as follows: create table sales( acct_no number(5) unique, person varchar2(30) not null, sales_amount number(8) not null, week_no number(2) not null) partition by range(week_no) (partition p1 values less than(4) tablespace data0, partition p2 values less than(8) tablespace data1, partition p3 values less than(12) tablespace data2, partition p4 values less than(16) tablespace data3, partition p5 values less than(20) tablespace data4, partition p6 values less than(24) tablespace data5, partition p7 values less than(28) tablespace data6, partition p8 values less than(32) tablespace data7, partition p9 values less than(36) tablespace data8, partition p10 values less than(40) tablespace data9, partition p11 values less than(44) tablespace data10, partition p12 values less than(48) tablespace data11, partition p13 values less than(52) tablespace data12); where week_no = partition column p1..p13 = partitions in the table for the column week_no values less than (x) = x is the upper partition bound for that partition data0..data12 = different tablespaces for the different partitions Notes 105317.1 and 149116.1 provides additional examples for creating the different partition types. Q) How are partition keys allocated to a particular partition? --> They are allocated based on the upper partition bound for that partition. So if I insert a row with the values such as: insert into sales values (1000, 'abc', 10, 30); --> this row should reside in partition #p8 in tablespace data7 since the partition keys in that partition should compare less than and NOT equal to the partition bound of that partition. Q) How can I select data out of a particular partition in the table? --> select * from sales partition (p8); ACCT_NO PERSON SALES_AMOUNT WEEK_NO ---------- ------------------------------ ------------ ---------- 1000 abc 10 30 Q) How can I insert data into a particular partition? --> insert into sales partition (p8) values (7000, 'bcd', 10, 30); Q) How can I delete data from a particular partition? --> delete from sales partition (p8); Q) How can I update a particular partition? --> update sales partition (p8) set sales_amount = 20; Q) Can I update the value of a partition key column? With 8.0 --> No, you will get ora-14402 --> The only way to go about doing this is to manually delete the old value and then insert the new value. From 8.1 on You can update the value of a partition key column sql>alter table <table_name> enable row movement; See [NOTE:73474.1] Update Partition Keys for example. Row Movement is disabled by default on all tables. You may check the status of ROW_MOVEMENT on dba and user_tables. Q) Can I add a partition to a non-partitioned table? --> No, you cannot add a partition to a non-partitioned table but can add a new partition to a partitioned table. In order to add a partition to a non-partitioned table you have to drop and recreate the non-partitioned table as a partitioned table. Q) Can partitioned tables contain LOB (large object) datatypes? --> Yes, but the LOB column cannot be specified as a partitioning key column. See examples below: SQL> create table lob_tab ( 2 a integer, 3 b blob, 4 c clob) partition by range (b) 5 (partition b1 values less than('hello') tablespace data0, 6 partition b2 values less than('world') tablespace data1); b blob, * ERROR at line 3: ORA-14113: partitioned table cannot have column with LOB datatype SQL> create table lob_tab ( 2 a integer, 3 b char(5), 4 c blob) partition by range (b) 5 (partition b1 values less than('hello') tablespace data0, 6* partition b2 values less than('world') tablespace data1); Table created. Q) What data dictionary view will give the storage parameters for the indiviual partitions of the partitioned table? --> DBA_TAB_PARTITIONS Q) How can I find out the upper partition bound for a partition table? --> select high_value, partition_position from sys.dba_tab_partitions where table_name = 'SALES'; Q) How different is OBJECT_ID from DATA_OBJECT_ID in the data dictionary view DBA_OBJECTS? --> OBJECT_ID DATA_OBJECT_ID SUBOBJECT_NAME ---------- -------------- ------------------------------ 1697 1697 P1 1706 1706 P10 1707 1707 P11 1708 1708 P12 1709 1709 P13 1698 1698 P2 1699 1699 P3 1700 1700 P4 1701 1701 P5 1702 1702 P6 1703 1703 P7 OBJECT_ID DATA_OBJECT_ID SUBOBJECT_NAME ---------- -------------- ------------------------------ 1704 1704 P8 1705 1705 P9 1696 From the above results OBJECT_ID is the dictionary object# of the partition. DATA_OBJECT_ID is the data object# of the partition. The partitioned table does not have a DATA_OBJECT_ID, only it's individual partitions do. Therefore the unique object number for this partitioned table is 1696. DATA_OBJECT_ID was introduced in 8.0 to track versions of the same segment (certain operations change the version). It is used to discover stale ROWIDs and stale undo records.

sys
15 de Diciembre del 2004
Purpose: ======== The purpose of this article is to explain how to implement partitioning in Oracle Versions 8 and 8i. How to Implement Partitioning in Oracle Versions 8 and 8i: ========================================================== I. Introduction: ----------------- I.1 Version 7: --------------- Partitioning is available in V7 through the use of partitioned views. This type of view is based on a set of tables that have the same structure; specifically, the columns and data types are identical and in the same order. Moreover, for each table there must exist an index based on the 'partitioning key'. If this is not the case, then the optimizer fails to take the partitioned view into account when it builds its execution plan. Then is built as a UNION ALL type view of the different tables: Create view partitioned_emp as select * from emp1 union all select * from emp2 union all select * from emp3; A new parameter, PARTITION_VIEW_ENABLED, is available starting in version 7.3. When it is set to TRUE, it allows the optimizer to only take into account the partitions associated with the select predicate. This partitioning method has certain negative aspects: - DDL and DML orders are not taken into account. - Export, import, and loader tools are not taken into account. - There is no possibility to use global indexes. I.2 Version 8: --------------- Partitioning is offered as an option in Oracle V8 Enterprise Server Edition. It is an option that requires specific selection during installation of the RDBMS that enables it to be linked into the kernel. This new option is useful in addressing performance problems and database administration issues in Very Large Database (VLDB) environments. Administrators of databases with historical data are particularly interested in this option. Administrators of OLTP databases concerned the performance and/or availability also benefit with the implementation of this option. Advantages: - Improved database administration as several maintenance operations can now be done at a partition level rather than at a table or index level. Moreover, the independence between partitions allows the maintenance on those very partitions to take place simultaneously. - Database unavailability reduced. Finer grained recovery as it can be done at the partition level. - Better distribution of data as it is possible to spread it out on several partitions and different disks. - Performance enhancement in the dispatching of queries, particularly for data warehousing or decision making. The optimizer is capable of selecting a limited quantity of rows associated with one or several partitions specified in the query. This functionality is available for both indexes and tables, but cannot be implemented in clusters. The RULE-based optimizer does not take the partitioning of tables and indexes into account. II. The different partitioning types: -------------------------------------- The only partitioning mode available in version 8.0 is: - Range Partitioning. Version 8.1 also includes two new partitioning modes: - Hash. - Composite. II.1 Range-type partitioning: ------------------------------ Table and index partitions are based on a list of columns allowing to store each occurrence in a given partition. CREATE TABLE emp_range (empno NUMBER(4) NOT NULL, ename VARCHAR2(10), sal NUMBER(7,2)) PARTITION BY RANGE(EMPNO) (partition emp_p1 VALUES LESS THAN (50), partition emp_p2 VALUES LESS THAN (100), partition emp_p3 VALUES LESS THAN (150), partition emp_p4 VALUES LESS THAN (MAXVALUE)); Each partition is defined with an upper boundary. The storage location of each occurrence is then found by comparing the partitioning key of the occurrence with this upper boundary. This upper boundary is non-inclusive; in other words, the key of each occurrence must be less than this limit for the record to be stored in this partition. The storage options are set a table level and are directly inherited by the partition level if they are not redefined by each partition. The following views enable you to gather information on the partitions: DBA_PART_KEY_COLUMNS DBA_PART_COL_STATISTICS DBA_PART_HISTOGRAMS DBA_PART_INDEXES DBA_IND_PARTITIONS DBA_PART_TABLES DBA_TAB_PARTITIONS DBA_PART_LOBS DBA_LOB_PARTITIONS The only possible functions in the partition clause: 'VALUE LESS THAN (value1, value2 ..., valueN)' are the TO_DATE and RPAD functions. The NULL value can be associated to a partitioning key column and is considered to be superior to any other possible value on this column except the MAXVALUE column. This is a constraint. This implies the upper limit of the top-level partition must be set to MAXVALUE; otherwise the occurrences containing a NULL value on this column would not be able to fit in this last partition, generating an ORA-14400 error. II.2 Hash-type partitioning: ---------------------------- This type of partitioning allows a better mastering of the distribution of the data in the different partitions. These partitions are defined with the help of a hashing function offered by Oracle. This function is then applied to a list of columns. CREATE TABLE emp_hpart (empno NUMBER(4) NOT NULL, ename VARCHAR2(10), sal NUMBER(7,2)) STORAGE (INITIAL 50k NEXT 50k) PARTITION BY HASH(sal) PARTITIONS 4 STORE IN (DATA01, DATA02, DATA03, DATA04); This partitioning method is recommended in the following cases: - It is impossible to have criteria for the distribution of data. - It is difficult to anticipate the quantity of data for a given partition. - It is hard to balance the load in each partition. This partitioning method requires that the number of partitions allocated be a power of two in order to ensure a uniform distribution of data in the different partitions. Specific storage clauses cannot be specified for each partition. These clauses are inherited from the tablespace in which the partitions reside. The administration of these HASH type partitions is similar to that of the RANGE ones except for DROP, SPLIT, and MERGE. The ADD and COALESCE commands are used to add and drop partitions. II.3 Composite-type partitioning: ---------------------------------- This method combines the two sorting methods described previously. The first level is based on the RANGE method and the second, finer grained, HASH is based on a hashing function. The benefit obtained is the first level sorts the data on a logical basis while the second balances its distribution among each one of the partitions. CREATE TABLE emp_composite (empno NUMBER(4) NOT NULL, ename VARCHAR2(10), sal NUMBER(6)) STORAGE (INITIAL 12k NEXT 12k) PARTITION BY RANGE(empno) SUBPARTITION BY HASH(sal) SUBPARTITIONS 4 STORE IN (DATA01, DATA02, DATA03, DATA04) (PARTITION p1 VALUES LESS THAN (50), PARTITION p2 VALUES LESS THAN (100), PARTITION p3 VALUES LESS THAN (150), PARTITION p4 VALUES LESS THAN (MAXVALUE)); This presents a RANGE type first level partitioning based on a NUMBER type column (EMPNO). The second level, HASH-type partitioning, is also based on a NUMBER type column (SAL), and each of these partitions is divided into four sub-partitions. At the RANGE level the partitioning is a logical sort. The data is then physically stored in the sub-partitions (HASH). The storage at this level can be visualized in the DBA_TAB_SUBPARTITIONS view. PARTITION_NAME SUBPARTITION_NAME POSITION TABLESPACE_NAME --------------- -------------------- ---------- ---------------- P1 SYS_SUBP27 1 DATA01 P1 SYS_SUBP28 2 DATA02 P1 SYS_SUBP29 3 DATA03 P1 SYS_SUBP30 4 DATA04 P2 SYS_SUBP31 1 DATA01 P2 SYS_SUBP32 2 DATA02 P2 SYS_SUBP33 3 DATA03 P2 SYS_SUBP34 4 DATA04 P3 SYS_SUBP35 1 DATA01 P3 SYS_SUBP36 2 DATA02 P3 SYS_SUBP37 3 DATA03 P3 SYS_SUBP38 4 DATA04 P4 SYS_SUBP39 1 DATA01 P4 SYS_SUBP40 2 DATA02 P4 SYS_SUBP41 3 DATA03 P4 SYS_SUBP42 4 DATA04 Version 8.1 offers new views to administer this level of granularity: DBA_SUBPART_COL_STATISTICS DBA_SUBPART_HISTOGRAMS DBA_SUBPART_KEY_COLUMNS DBA_IND_SUBPARTITIONS DBA_LOB_SUBPARTITIONS DBA_TAB_SUBPARTITIONS COMPOSITE partitioning preserves the advantages of the two previous methods: - Ease of administration tied to the RANGE type. - Finer granularity level in the storage of data. - Possibility of setting local indexes at subpartition level, combined with global indexes at RANGE level. - The HASH mode enables PDML operations on a parallel mode. III Implementation: -------------------- All these combinations are theoretically possible: - Partitioned table and non-partitioned index. - Partitioned table and partitioned index. - Non-partitioned table and non-partitioned index. - Non-partitioned table and partitioned index. Restrictions: Table level: - Clustered tables cannot be partitioned. - A LONG or LONG RAW column in a table prevents it from being partitioned. BLOB-type columns, on the other hand, may be partitioned (cf. chapter V). - Index Only Tables (IOT) can only be RANGE partitioned. Index level: - Indexes for clusters cannot be partitioned. - Indexes for clustered tables cannot be partitioned. - Bitmap indexes must be locally partitioned indexes. - Bitmap indexes associated to non-partitioned tables cannot be partitioned. - Non-prefixed global indexes are not supported. - Local non-prefixed unique indexes must have a partitioning key included in the index key. Multi-column partition keys are limited to 16. The following types of columns cannot be part of a partition key: - LEVEL or ROWID pseudo-types. - Columns of the following types: o Nested table o Varray o Object type o Ref o Rowid - Different types of LOB (BLOB, CLOB, NCLOB, BFILE). IV. Index partitioning: ------------------------ Oracle Version 8 offers two main types of indexes: Local and Global. Local indexes are equi-partitioned with the table they are based on. That means that for each index entry residing in partition A there is a row of the table which also resides in that same partition A. A local index linked to a table has the following properties: - Same number of partitions/sub-partitions. - Same partition limits. - Same partitioning keys. The index partitioning is automatically maintained along with that of the associated table in case of partition/subpartition addition, subtraction, splitting, or compacting. The method used to partition the index is the same as that used for the corresponding table. Global indexes are partitioned on columns which can be different than those used for the base table. When you create this type of index, it is necessary to define, in a precise manner, the types and limits for the partitions. Oracle does not keep up to date the index partitions in case of modification of the base tables' partitions. The index partitions then get an UNUSABLE status and the index has to be built. Three index types are supported in the two groups mentioned above: - Local prefixed. - Local non prefixed. - Global prefixed. Prefixed vs. Non prefixed; what is the difference? An index is considered prefixed if its partitioning key/keys constitute the left part of the index key, columns being kept in the same order. Non prefixed global indexes are not supported. Non-partitioned indexes are considered to be global prefixed indexes. We will use the following base table for the examples of each type of index: CREATE TABLE emp (empno NUMBER NOT NULL, ename VARCHAR2(10) NOT NULL, job VARCHAR2(14), deptpno NUMBER) PARTITION BY RANGE (empno) (PARTITION part1 VALUES LESS THAN (30), PARTITION part2 VALUES LESS THAN (60), PARTITION part3 VALUES LESS THAN (MAXVALUE)); IV.1 Prefixed Local Index: --------------------------- CREATE INDEX emp_idx1 ON emp(empno) LOCAL; The index is considered as prefixed because the index key 'empno' is identical to the partitioning key. This index is defined as 'local'. It is thus partitioned automatically by Oracle on the same key as the emp table, namely 'empno'. There is, therefore, no need to define again the partitioning criteria tied to the index. Oracle automatically creates three index partitions based on the same partition limits as those of the emp table. Each of the index keys is stored in a partition whose limits are identical to those of the base table. The main advantage of the prefixed mode is that it enables the optimizer to ignore the index partitions that do not match the search criteria tied to the index key. Look at the following query: select * from emp where empno = 62; The optimizer avoids the first two partitions and goes directly to the third index partition. emp_idx1 Index empno empno empno partitions -> 0-29 30-59 60-MAXVALUE Index key -> empno 0-29 30-59 60-MAXVALUE table emp -> empno IV.2 Non-prefixed Local Index: ------------------------------- CREATE INDEX emp_idx2 ON emp(deptno) LOCAL; In the above example, Oracle creates three index partitions, equi- partitioned with the base table. The physical storage of the index keys, on the other hand, does not follow the partitioning rule. This implies the spreading of the index keys in the different partitions. Thus queries that include index key based predicates will force the optimizer to scan all the index partitions in order to retrieve the selected values. In the following query: Select * from emp where deptno = X; the optimizer must scan all three partitions to the index emp_idx2 in order to locate the particular index key. The value specified in the query's predicate can be in any one of the three index partitions. emp_idx2 empno partitions -> 0-29 30-59 60-MAXVALUE Index Key -> deptno 55-95 56-18 57-82 Emp table -> empno For non-prefixed unique key local indexes, it is mandatory that the index key be a subset of the partitioning key in order for an index key to only belong to one partition at a time. IV.3 Global prefixed index: ---------------------------- CREATE INDEX emp_idx3 ON emp(ename) GLOBAL PARTITION BY RANGE (ename) (PARTITION p1 VALUES LESS THAN ('N'), PARTITION p2 VALUES LESS THAN (MAXVALUE)); Using Global Prefixed Indexes allows a partitioning key different from that of the underlying table. This type of index must be prefixed. The index key is based on the partitioning key, which enables the optimizer to select which of the partitions are concerned by the query's predicate. Consider the following query: select * from emp where ename like 'B%'; The optimizer automatically selects the first index partition. On the other hand, rows that make up this partition have ROWIDs referencing rows in other table partitions. emp_Idx3 partitions -> ename A-M Index key -> ename A-M N-MAXVALUE emp -> empno Spreading index access over the different table partitions induces performance loss as disk I/O increases. Global indexes are harder to manage than local indexes since Oracle does not maintain the index partitioning automatically in case of table reorganization. A change in the table's storage causes all the index's partitions to be put in UNUSABLE status. Index rebuild is then necessary, and its duration is proportional to the size of the table rather than to the size of one of its partitions. IV.4 Guidelines to Partitioned Indexes: ---------------------------------------- The choice of a particular type of index is tied to the constraints of your Oracle applications. A trade-off has to be found between performance of the jobs, availability, and ease of administration. The main rules to remember are as follows: - Local indexes are easier to maintain and offer a higher availability than global indexes. - Prefixed indexes enable the optimizer to limit the probe to the partitions directly concerned by the query predicates. - Local prefixed indexes and global indexes are more adapted to an OLTP styles of use whereas local non-prefixed indexes are better adapted to data-warehousing and decision making environments. Ask yourself the following questions before you choose a particular type of partitioned index: If your index is partitioned on a left prefix of the index columns, then ===> Local prefixed If you want to create a unique index, whose columns do not include your partitioning keys ===> Global prefixed If you can use parallel access to the different partitions and mainly work in a DSS environment ===> Local non-prefixed If you work in an OLTP environment ===> Global prefixed Local non-prefixed indexes are particularly useful for historical type databases in which the data is partitioned on a DATE type criteria. This index is useful in efficiently probing the database with particular criteria, different from a date. Maintenance operations tied to adding or dropping a partition are simpler. V. LOB partitioning: --------------------- Partitioned tables may have LOB type columns as of 8.1, but these columns may not be used as partitioning keys. All types of partitioning support the different types of LOBs: - BLOB - CLOB - NCLOB - BFILE In the case of BFILE type LOBs, only the reference of these columns is stored in the tables. All LOB type columns automatically generate a data segment along with an associated index that enables the access to the different parts of the LOB. The storage clauses tied to the LOB as well as to its associated index can be different from those of the associated table. For a partitioned table with a LOB type column, Oracle creates as many data and index segments as there are table partitions or sub-partitions. These different segments are equi-partitioned with the corresponding table partitions. CREATE TABLE emp_lob (empno NUMBER(4) NOT NULL, ename VARCHAR2(10), sal NUMBER(7,2), image BLOB) LOB (image) STORE AS IMAGE_LOB (CHUNK 1 PCTVERSION 20 NOCACHE LOGGING TABLESPACE T_LOB STORAGE (INITIAL 12k NEXT 12K PCTINCREASE 0)) TABLESPACE USERS PARTITION BY RANGE(empno) (PARTITION emp_p1 VALUES LESS THAN (50), PARTITION emp_p2 VALUES LESS THAN (100), PARTITION emp_p3 VALUES LESS THAN (150), PARTITION emp_p4 VALUES LESS THAN (MAXVALUE)); In the above example, the table is partitioned in four partitions. Each one of these partitions is associated with a LOB partition. These LOB partitions are themselves broken into a data partition and an index partition. This organization is visible in the Oracle dictionary via the following queries: select partition_position, tablespace_name, partition_name from dba_tab_partitions where table_name = 'EMP_LOB'; PARTITION_POSITION TABLESPACE_NAME PARTITION_NAME ------------------ ------------------------------ --------------- 1 USERS EMP_P1 2 USERS EMP_P2 3 USERS EMP_P3 4 USERS EMP_P4 select column_name, segment_name, index_name from dba_lobs where table_name = 'EMP_LOB'; COLUMN_NAME SEGMENT_NAME INDEX_NAME --------------- --------------- ------------------------- IMAGE IMAGE_LOB SYS_IL0000012665C00004$$ The index segment name is implicitly generated by ORACLE. The two objects, IMAGE_LOB and SYS_IL0000012665C00004$$, only exist virtually in the Oracle dictionary, but physically exist in the form of partitions. select column_name, lob_name, partition_name, lob_partition_name, lob_indpart_name, tablespace_name from dba_lob_partitions where table_name = 'EMP_LOB'; COLUMN_NAME LOB_NAME PARTITION LOB_PARTITION LOB_INDPART TABLESPACE ----------- ---------- --------- ------------- ----------- ---------- IMAGE IMAGE_LOB EMP_P1 SYS_LOB_P63 SYS_IL_P67 T_LOB IMAGE IMAGE_LOB EMP_P2 SYS_LOB_P64 SYS_IL_P68 T_LOB IMAGE IMAGE_LOB EMP_P3 SYS_LOB_P65 SYS_IL_P69 T_LOB IMAGE IMAGE_LOB EMP_P4 SYS_LOB_P66 SYS_IL_P70 T_LOB select column_name, lob_name, lob_index_name, def_chunk, def_pctversion from dba_part_lobs where table_name = 'EMP_LOB'; COLUMN LOB_NAME LOB_INDEX_NAME DEF_CHUNK DEF_PCTVERSION ------ ---------- ------------------------ ---------- -------------- IMAGE IMAGE_LOB SYS_IL0000012665C00004$$ 1 20 select index_name, partitioning_type "TYPE", partition_count "COUNT", locality, alignment from dba_part_indexes where table_name = 'EMP_LOB'; INDEX_NAME TYPE COUNT LOCALITY ALIGNMENT ------------------------ ----------- ------- -------- ---------- SYS_IL0000012665C00004$$ RANGE 4 LOCAL NON_PREFIXED select high_value, partition_name "NAME", partition_position "POSITION", status, tablespace_name "TNAME" from dba_ind_partitions where index_name = 'SYS_IL0000012665C00004$$'; HIGH_VALUE NAME POSITION STATUS TABLESPACE ---------- ----------- -------- ------ ---------- 50 SYS_IL_P67 1 USABLE T_LOB 100 SYS_IL_P68 2 USABLE T_LOB 150 SYS_IL_P69 3 USABLE T_LOB MAXVALUE SYS_IL_P70 4 USABLE T_LOB As for the other types of partitions, the storage clauses may be defined at the level of the table or directly in the definition of each partition. Each LOB partition can have its own storage clauses as well as specific tablespace. Index partitions for LOBs are always stored in the same tablespace as the partitions for the LOB data partitions. It is not possible to specify a storage clause for partitions concerning index LOBS as those are always linked to the corresponding data partitions or to the tablespace in which they reside. In the case of BFILE type columns, Oracle only stores the value of the LOCATOR that references an external file. Oracle therefore only partitions the BFILE column in the same manner it would use for a VARCHAR2 type column. VI. IOT partitioning: ---------------------- Index Organized Tables (IOT) can now be partitioned as of version 8.1. BACKGROUND: IOTs have a BTREE index structure where the leaves of this index contain the actual rows of the table. The rows of the table are sorted on the primary key, which is essential to the creation of each IOT. It is possible to leave an overflow zone when you create an IOT containing any part of the row that does not fit in the primary key and that has to be stored in a particular area. The aim of this additional segment is to limit the size of the rows stored in each IOT block. IOT partitioning is only possible under certain conditions: - The partitioning must be of RANGE type. - The partitioning key(s) must be a subset of the primary key. - The overflow zones must be equi-partitioned with IOT partitions. Oracle imposes that the partitioning key be a subset of the primary key or included in the latter, which allows unique constraint checking amid a partition. Moreover if this partitioning key is prefixed, then the queries based on the partitioning predicate directly retrieve the rows stored on the primary key. Consider for example the following IOT: CREATE TABLE emp_iot (empno NUMBER(4) PRIMARY KEY, ename VARCHAR2(10), sal NUMBER(7,2)) ORGANIZATION INDEX INCLUDING ename OVERFLOW PARTITION BY RANGE(empno) (partition emp_p1 VALUES LESS THAN (50) TABLESPACE data01, partition emp_p2 VALUES LESS THAN (100) TABLESPACE data02, partition emp_p3 VALUES LESS THAN (150) TABLESPACE data03, partition emp_p4 VALUES LESS THAN (MAXVALUE) TABLESPACE data04); Each IOT partition generates two partitions: a data partition and an overflow partition. This overflow partition contains the data associated to the rows beyond the 'ename' column including that very column. This organization can be visualized with the following dictionary views: select table_name, iot_name, iot_type, partitioned from dba_tables where table_name like '%IOT%'; TABLE_NAME IOT_NAME IOT_TYPE PARTITIONED -------------------- ---------- ------------ ----------- EMP_IOT IOT YES SYS_IOT_OVER_12770 EMP_IOT IOT_OVERFLOW YES select table_name, partitioning_type "TYPE", partition_count "COUNT", def_tablespace_name from dba_part_tables where table_name like '%IOT%'; TABLE_NAME TYPE COUNT DEF_TABLESPACE_NAME ------------------ ----------- ------- ------------------- SYS_IOT_OVER_12770 RANGE 4 USERS EMP_IOT RANGE 4 select index_name, index_type, table_name, uniqueness, partitioned from dba_indexes where table_name like '%IOT%'; INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENES PARTITIONED ------------------ ----------- ----------- --------- ----------- SYS_IOT_TOP_12770 IOT - TOP EMP_IOT UNIQUE YES select index_name, partition_name, high_value, tablespace_name from dba_ind_partitions where index_name like '%IOT%'; INDEX_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME ----------------- -------------- ---------- --------------- SYS_IOT_TOP_12770 EMP_P1 50 DATA01 SYS_IOT_TOP_12770 EMP_P2 100 DATA02 SYS_IOT_TOP_12770 EMP_P3 150 DATA03 SYS_IOT_TOP_12770 EMP_P4 MAXVALUE DATA04 select table_name, partition_position, partition_name, tablespace_name from dba_tab_partitions where table_name like '%IOT%'; TABLE_NAME PARTITION_POSITION PARTITION_NAME TABLESPACE_NAME -------------------- ------------------ --------------- --------------- SYS_IOT_OVER_12770 1 EMP_P1 USERS SYS_IOT_OVER_12770 2 EMP_P2 USERS SYS_IOT_OVER_12770 3 EMP_P3 USERS SYS_IOT_OVER_12770 4 EMP_P4 USERS EMP_IOT 1 EMP_P1 EMP_IOT 2 EMP_P2 EMP_IOT 3 EMP_P3 EMP_IOT 4 EMP_P4 In this case, the view DBA_TAB_PARTITIONS shows that the data segments tied to the overflow are stored in the creator's default tablespace. This was due to no tablespace being specified for the overflow segment, and the table has no default tablespace specified. When there is a default tablespace for the table, then the overflow segments are created in that tablespace. Additional indexes may be specified on other columns of the IOT (local prefixed, local non prefixed, local prefixed, or global prefixed). VII. Partition Maintenance: ---------------------------- Consider the following Test table: CREATE TABLE test (empno NUMBER(4) NOT NULL, ename VARCHAR2(10), sal NUMBER(7,2)) PARTITION BY RANGE(EMPNO) (partition emp_p1 VALUES LESS THAN (50), partition emp_p2 VALUES LESS THAN (100), partition emp_p3 VALUES LESS THAN (150), partition emp_p4 VALUES LESS THAN (200)); select partition_position, partition_name, tablespace_name from dba_tab_partitions where table_name = 'TEST'; PARTITION_POSITION PARTITION_NAME TABLESPACE_NAME ------------------ ----------------------- ---------------------- 1 EMP_P1 USERS 2 EMP_P2 USERS 3 EMP_P3 USERS 4 EMP_P4 USERS VII.1 Moving partitions: ------------------------- This ALTER TABLE option allows the transfer of the table partition from one tablespace to another. The status of any index partitions tied to this partition become UNUSABLE. In the case of a global index, the whole index has to be rebuilt. ALTER TABLE test MOVE PARTITION emp_p1 tablespace tbs1; Querying on DBA_TAB_PARITIONS shows the moved partition: PARTITION_POSITION PARTITION_NAME TABLESPACE_NAME ------------------ ----------------------- ---------------------- 1 EMP_P1 TBS1 2 EMP_P2 USERS 3 EMP_P3 USERS 4 EMP_P4 USERS VII.2 Adding partitions: ------------------------- The ADD PARITION clause allows you to add an extra partition beyond the last partition as long as the upper limit is not equal to MAXVALUE. Should you specify a value that is equal, the add partition statement would fail. Let's add an extra partition at the end of the TEST table using the ADD PARTITION: ALTER TABLE test ADD PARTITION emp_p5 values less than (300); Querying on DBA_TAB_PARTITIONS, this time for the high value of each partition: select partition_name, high_value, tablespace_name from dba_tab_partitions where table_name = 'TEST'; PARTITION_NAME HIGH_VALUE TABLESPACE_NAME ----------------------- ---------------------- --------------- EMP_P1 50 TBS1 EMP_P2 100 USERS EMP_P3 150 USERS EMP_P4 200 USERS EMP_P5 300 USERS Note the tablespace for the new partition. In the case of indexes, partitions can only be added to global indexes. The upper limit of a global index always being MAXVALUE implies that SPLIT is the only possible command (see section VII.5). VII.3 Dropping partitions: --------------------------- The DROP PARTITION clause allows the withdrawal of a table or global index partition. The DROP of a table partition causes the status of all the partitions of the global index to become UNUSABLE. A complete rebuild of the index has to occur to modify this status. ALTER TABLE test DROP PARTITION emp_p1; PARTITION_NAME HIGH_VALUE TABLESPACE_NAME ----------------------- ---------------------- --------------- EMP_P2 100 USERS EMP_P3 150 USERS EMP_P4 200 USERS EMP_P5 300 USERS This functionality is not available for HASH partitioned tables, which have to use the COALESCE command instead (see section VII.8). VII.4 Truncate partitions: --------------------------- Discards all the rows of a table partition while the storage allocated is preserved. This option is not available for indexes. Local index partitions are automatically kept up to date by Oracle. In the case of global indexes, the status of all the partitions becomes UNUSABLE. ALTER TABLE test TRUNCATE PARTITION emp_p2; VII.5 Splitting partitions: ---------------------------- The SPLIT PATITION clause enables you to create from an original partition two new partitions, each with a new segment and new physical attributes, and new initial extents. The segment associated with the old partition is discarded. The associated index partitions, global and local, become UNUSABLE. ALTER TABLE test SPLIT PARTITION EMP_P5 AT ( 250 ) INTO (partition EMP_P5, partition EMP_P6); Looking at the values now: PARTITION_NAME HIGH_VALUE TABLESPACE_NAME ----------------------- ---------------------- --------------- EMP_P2 100 USERS EMP_P3 150 USERS EMP_P4 200 USERS EMP_P6 300 USERS EMP_P5 250 USERS The EMP_P5 partition is divided into two distinct partitions, EMP_P5 and EMP_P6. The two partitions are redefined on the following values: 200-249 and 250-300. This functionality also works on global indexes. The SPLIT command cannot be used on HASH type partitions; the ADD command has to be used instead. Consider the emp_hpart table described previously in section II.2, which is distributed over four partitions: PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME --------------- ------------------ --------------- SYS_P69 1 DATA01 SYS_P70 2 DATA02 SYS_P71 3 DATA03 SYS_P72 4 DATA04 A query on DBA_SEGMENTS shows the location of the partitions: select segment_name, partition_name, tablespace_name, header_file, header_block from dba_segments where segment_name='TEST'; SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME HEADER_FILE HEADER_BLOCK --------------- --------------- --------------- ----------- ------------ EMP_HPART SYS_P69 DATA01 8 21 EMP_HPART SYS_P70 DATA02 9 21 EMP_HPART SYS_P71 DATA03 10 2 EMP_HPART SYS_P72 DATA04 11 2 The contents of the first partition P1 are the following: EMPNO ENAME SAL ---------- ---------- ---------- 303 TEST 10302 306 TEST 10305 310 TEST 10309 325 TEST 10324 326 TEST 10325 332 TEST 10331 338 TEST 10337 341 TEST 10340 343 TEST 10342 347 TEST 10346 348 TEST 10347 349 TEST 10348 350 TEST 10349 360 TEST 10359 361 TEST 10360 365 TEST 10364 368 TEST 10367 370 TEST 10369 373 TEST 10372 380 TEST 10379 385 TEST 10384 386 TEST 10385 396 TEST 10395 400 TEST 10399 401 TEST 10400 25 rows selected. The following command adds a new partition P5 and redistributes the rows of partition P1 between P1 and P5. ALTER TABLE emp_hpart ADD PARTITION P5; SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME HEADER_FILE HEADER_BLOCK --------------- --------------- --------------- ----------- ------------ EMP_HPART SYS_P69 DATA01 8 2 EMP_HPART SYS_P70 DATA02 9 21 EMP_HPART SYS_P71 DATA03 10 2 EMP_HPART SYS_P72 DATA04 11 2 EMP_HPART P5 USERS 5 1292 SQL> select * from emp_hpart partition(P1); EMPNO ENAME SAL ---------- ---------- ---------- 306 TEST 10305 310 TEST 10309 325 TEST 10324 326 TEST 10325 338 TEST 10337 341 TEST 10340 343 TEST 10342 347 TEST 10346 350 TEST 10349 370 TEST 10369 373 TEST 10372 385 TEST 10384 400 TEST 10399 401 TEST 10400 14 rows selected. SQL> select * from emp_hpart partition(P5); EMPNO ENAME SAL ---------- ---------- ---------- 303 TEST 10302 332 TEST 10331 348 TEST 10347 349 TEST 10348 360 TEST 10359 361 TEST 10360 365 TEST 10364 368 TEST 1036 380 TEST 10379 386 TEST 10385 396 TEST 10395 11 rows selected. Notice that partition P1 has been erased and recreated. VII.6 Merging partitions: -------------------------- This 8.1 functionality enables adjacent partitions to coalesce. The resulting partitions inherit the upper boundary of the highest partition. ALTER TABLE test MERGE PARTITIONS emp_p2, emp_p3 into emp_p2_p3; select partition_position, partition_name, tablespace_name from dba_tab_partitions where table_name = 'TEST'; PARTITION_POSITION PARTITION_NAME TABLESPACE_NAME ------------------ ------------------------------ --------------------- 2 EMP_P4 USERS 4 EMP_P6 USERS 3 EMP_P5 USERS 1 EMP_P2_P3 USERS This option is not available on HASH type partitioned tables where the COALESCE command must be used instead. VII.7 Partition exchanges: --------------------------- Partition exchange allows the transfer of non-partitioned tables into partitions as well as the reverse. Alternatively, you can transfer partitions into non-partitioned tables. This option is particularly useful in migrating V7 partitioned views into table partitions. Consider the following partitioned view based on four tables: less50, less100, less150, less200. CREATE VIEW test_view AS SELECT * FROM less50 UNION ALL SELECT * FROM less100 UNION ALL SELECT * FROM less150 UNION ALL SELECT * FROM less200; An empty partitioned table needs to be created, on the same schema as the underlying tables of the partitioned views: CREATE TABLE new_test (empno NUMBER(4) NOT NULL, ename VARCHAR2(10), sal NUMBER(7,2)) PARTITION BY RANGE(EMPNO) (partition emp_p1 VALUES LESS THAN (50), partition emp_p2 VALUES LESS THAN (100), partition emp_p3 VALUES LESS THAN (150), partition emp_p4 VALUES LESS THAN (200)); Then you need to transfer each underlying table of the view in the corresponding partition of the partitioned table: ALTER TABLE new_test EXCHANGE PARTITION emp_p1 WITH TABLE less50 WITH VALIDATION; This takes a very short time as the updates only take place in the Oracle dictionary. There is no physical movement of the segments. The structure of the tables to swap partitioned as well as non-partitioned must be identical in terms of types, columns, and sizes, as well as the number of columns. VII.8 Coalesce partitions: --------------------------- This is 8.1 functionality dedicated to HASH type partitions. This indicates to the Oracle Kernel that it must choose a HASH type partition and redistribute the rows on the remaining partitions. Oracle then drops the partition selected for the operation. Look again at the emp_hpart table: PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME --------------- ------------------ --------------- SYS_P69 1 DATA01 SYS_P70 2 DATA02 SYS_P71 3 DATA03 SYS_P72 4 DATA04 select segment_name, partition_name, tablespace_name, header_file, header_block from dba_segments where segment_name='EMP_HPART'; SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME HEADER_FILE HEADER_BLOCK --------------- --------------- --------------- ----------- ------------ EMP_HPART P1 DATA01 8 21 EMP_HPART P2 DATA02 9 21 EMP_HPART P3 DATA03 10 2 EMP_HPART P4 DATA04 11 2 Issue the command to coalesce the HASH partitions: ALTER TABLE emp_hpart COALESCE PARTITION; SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME HEADER_FILE HEADER_BLOCK --------------- --------------- --------------- ----------- ------------ EMP_HPART P1 DATA01 8 21 EMP_HPART P2 DATA02 9 2 EMP_HPART P3 DATA03 10 2 Notice that Oracle has redistributed the rows in the P2 and P4 partitions as well as recreated a new P2 partition. VIII. Conclusion: ------------------ Partitioning in Oracle 8i has been greatly enhanced. This change in functionality provides flexibility in the organization of large amounts of data, as well as noticeable improvement in performance and administration.