oracle10g Online Table Redefinition testing and related doc
1
Online Table Redefinition Examples
For the following examples, see Oracle Database PL/SQL Packages and Types Reference for descriptions of all DBMS_REDEFINITION subprograms.
Example Description
Example 1 Redefines a table by adding new columns and adding partitioning.
Example 2 Demonstrates redefinition with object datatypes.
Example 3 Demonstrates redefinition with manually registered dependent objects.
Example 4 Redefines a single table partition, moving it to a different tablespace.
Example 1
This example illustrates online redefinition of the previously created table hr.admin_emp, which at this point only contains columns: empno, ename, job, deptno. The table is redefined as follows:
*
New columns mgr, hiredate, sal, and bonus are added. (These existed in the original table but were dropped in previous examples.)
*
The new column bonus is initialized to 0
*
The column deptno has its value increased by 10.
*
The redefined table is partitioned by range on empno.
The steps in this redefinition are illustrated below.
1.
Verify that the table is a candidate for online redefinition. In this case you specify that the redefinition is to be done using primary keys or pseudo-primary keys.
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('hr','admin_emp',
DBMS_REDEFINITION.CONS_USE_PK);
END;
/
2.
Create an interim table hr.int_admin_emp.
CREATE TABLE hr.int_admin_emp
(empno NUMBER(5) PRIMARY KEY,
ename VARCHAR2(15) NOT NULL,
job VARCHAR2(10),
mgr NUMBER(5),
hiredate DATE DEFAULT (sysdate),
sal NUMBER(7,2),
deptno NUMBER(3) NOT NULL,
bonus NUMBER (7,2) DEFAULT(1000))
PARTITION BY RANGE(empno)
(PARTITION emp1000 VALUES LESS THAN (1000) TABLESPACE admin_tbs,
PARTITION emp2000 VALUES LESS THAN (2000) TABLESPACE admin_tbs2);
3.
Start the redefinition process.
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('hr', 'admin_emp','int_admin_emp',
'empno empno, ename ename, job job, deptno+10 deptno, 0 bonus',
dbms_redefinition.cons_use_pk);
END;
/
4.
Copy dependent objects. (Automatically create any triggers, indexes, grants, and constraints on hr.int_admin_emp.)
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('hr', 'admin_emp','int_admin_emp',
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
Note that the ignore_errors argument is set to TRUE for this call. The reason is that the interim table was created with a primary key constraint, and when COPY_TABLE_DEPENDENTS attempts to copy the primary key constraint and index from the original table, errors occurs. You can ignore these errors, but you must run the query shown in the next step to see if there are other errors.
5.
Query the DBA_REDEFINITION_ERRORS view to check for errors.
SQL> select object_name, base_table_name, ddl_txt from
DBA_REDEFINITION_ERRORS;
OBJECT_NAME BASE_TABLE_NAME DDL_TXT
------------- ---------------- ------------------------------
SYS_C005836 ADMIN_EMP CREATE UNIQUE INDEX "HR"."TMP$
$_SYS_C0058360" ON "HR"."INT_A
DMIN_EMP" ("EMPNO")
SYS_C005836 ADMIN_EMP ALTER TABLE "HR"."INT_ADMIN_EM
P" ADD CONSTRAINT "TMP$$_SYS_C
0058360" PRIMARY KEY
These errors are caused by the existing primary key constraint on the interim table and can be ignored. Note that with this approach, the names of the primary key constraint and index on the post-redefined table are changed. An alternate approach, one that avoids errors and name changes, would be to define the interim table without a primary key constraint. In this case, the primary key constraint and index are copied from the original table.
Note:
The best approach is to define the interim table with a primary key constraint, use REGISTER_DEPENDENT_OBJECT to register the primary key constraint and index, and then copy the remaining dependent objects with COPY_TABLE_DEPENDENTS. This approach avoids errors and ensures that the redefined table always has a primary key and that the dependent object names do not change.
6.
Optionally, synchronize the interim table hr.int_admin_emp.
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('hr', 'admin_emp', 'int_admin_emp');
END;
/
7.
Complete the redefinition.
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('hr', 'admin_emp', 'int_admin_emp');
END;
/
The table hr.admin_emp is locked in the exclusive mode only for a small window toward the end of this step. After this call the table hr.admin_emp is redefined such that it has all the attributes of the hr.int_admin_emp table.
8.
Drop the interim table.
Example 2
This example redefines a table to change columns into object attributes. The redefined table gets a new column that is an object type.
The original table, named CUSTOMER, is defined as follows:
Name Type
------------ -------------
CID NUMBER NAME VARCHAR2(30)
STREET VARCHAR2(100)
CITY VARCHAR2(30)
STATE VARCHAR2(2)
ZIP NUMBER(5)
The type definition for the new object is:
CREATE TYPE ADDR_T AS OBJECT (
street VARCHAR2(100),
city VARCHAR2(30),
state VARCHAR2(2),
zip NUMBER(5, 0) );
Here are the steps for this redefinition:
1.
Verify that the table is a candidate for online redefinition. Specify that the redefinition is to be done using primary keys or pseudo-primary keys.
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('STEVE','CUSTOMER',
DBMS_REDEFINITION.CONS_USE_PK);
END;
/
2.
Create the interim table int_customer.
CREATE TABLE INT_CUSTOMER(
CID NUMBER,
NAME VARCHAR2(30),
ADDR ADDR_T);
Note that no primary key is defined on the interim table. When dependent objects are copied in step 5, the primary key constraint and index are copied.
3.
Because CUSTOMER is a very large table, specify parallel operations for the next step.
alter session force parallel dml parallel 4;
alter session force parallel query parallel 4;
4.
Start the redefinition process using primary keys.
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'STEVE',
orig_table => 'CUSTOMER',
int_table => 'INT_CUSTOMER',
col_mapping => 'cid cid, name name,
addr_t(street, city, state, zip) addr');
END;
/
Note that addr_t(street, city, state, zip) is a call to the object constructor.
5.
Copy dependent objects.
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
'STEVE','CUSTOMER','INT_CUSTOMER',DBMS_REDEFINITION.CONS_ORIG_PARAMS,
TRUE, TRUE, TRUE, FALSE, num_errors, TRUE);
END;
/
Note that for this call, the final argument indicates that table statistics are to be copied to the interim table.
6.
Optionally synchronize the interim table.
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('STEVE', 'CUSTOMER', 'INT_CUSTOMER');
END;
/
7.
Complete the redefinition.
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('STEVE', 'CUSTOMER', 'INT_CUSTOMER');
END;
/
8.
Drop the interim table.
Example 3
This example addresses the situation where a dependent object must be manually created and registered.
Consider the case where a table T1 has a column named C1, and where this column becomes C2 after the redefinition. Assume that there is an index Index1 on C1. In this case, COPY_TABLE_DEPENDENTS tries to create an index on the interim table corresponding to Index1, and tries to create it on a column C1, which does not exist on the interim table. This results in an error. You must therefore manually create the index on column C2 and register it. Here are the steps:
1.
Create the interim table INT_T1 and create an index Int_Index1 on column C2.
2.
Ensure that T1 is a candidate for online redefinition with CAN_REDEF_TABLE, and then begin the redefinition process with START_REDEF_TABLE.
3.
Register the original (Index1) and interim (Int_Index1) dependent objects.
BEGIN
DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT(
uname => 'STEVE',
orig_table => 'T1',
int_table => 'INT_T1',
dep_type => DBMS_REDEFINITION.CONS_INDEX,
dep_owner => 'STEVE',
dep_orig_name => 'Index1',
dep_int_name => 'Int_Index1');
END;
/
4.
Use COPY_TABLE_DEPENDENTS to copy the remaining dependent objects.
5.
Optionally synchronize the interim table.
6.
Complete the redefinition and drop the interim table.
Example 4
This example demonstrates redefining a single partition. It moves the oldest partition of a range-partitioned sales table to a tablespace named TBS_LOW_FREQ. The table containing the partition to be redefined is defined as follows:
CREATE TABLE salestable
(s_productid NUMBER,
s_saledate DATE,
s_custid NUMBER,
s_totalprice NUMBER)
TABLESPACE users
PARTITION BY RANGE(s_saledate)
(PARTITION sal03q1 VALUES LESS THAN (TO_DATE('01-APR-2003', 'DD-MON-YYYY')),
PARTITION sal03q2 VALUES LESS THAN (TO_DATE('01-JUL-2003', 'DD-MON-YYYY')),
PARTITION sal03q3 VALUES LESS THAN (TO_DATE('01-OCT-2003', 'DD-MON-YYYY')),
PARTITION sal03q4 VALUES LESS THAN (TO_DATE('01-JAN-2004', 'DD-MON-YYYY')));
The table has a local partitioned index that is defined as follows:
CREATE INDEX sales_index ON salestable
(s_saledate, s_productid, s_custid) LOCAL;
Here are the steps. In the following procedure calls, note the extra argument: partition name (part_name).
1.
Ensure that salestable is a candidate for redefinition.
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE(
uname => 'STEVE',
tname => 'SALESTABLE',
options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
part_name => 'sal03q1');
END;
/
2.
Create the interim table in the TBS_LOW_FREQ tablespace. Because this is a redefinition of a range partition, the interim table is non-partitioned.
CREATE TABLE int_salestable
(s_productid NUMBER,
s_saledate DATE,
s_custid NUMBER,
s_totalprice NUMBER)
TABLESPACE tbs_low_freq;
3.
Start the redefinition process using rowid.
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'STEVE',
orig_table => 'salestable',
int_table => 'int_salestable',
col_mapping => NULL,
options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
part_name => 'sal03q1');
END;
/
4.
Manually create any local indexes on the interim table.
CREATE INDEX int_sales_index ON int_salestable
(s_saledate, s_productid, s_custid)
TABLESPACE tbs_low_freq;
5.
Optionally synchronize the interim table.
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
uname => 'STEVE',
orig_table => 'salestable',
int_table => 'int_salestable',
part_name => 'sal03q1');
END;
/
6.
Complete the redefinition.
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(
uname => 'STEVE',
orig_table => 'salestable',
int_table => 'int_salestable',
part_name => 'sal03q1');
END;
/
7.
Drop the interim table.
The following query shows that the oldest partition has been moved to the new tablespace:
select partition_name, tablespace_name from user_tab_partitions
where table_name = 'SALESTABLE';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1 TBS_LOW_FREQ
SAL03Q2 USERS
SAL03Q3 USERS
SAL03Q4 USERS
4 rows selected.
Privileges Required for the DBMS_REDEFINITION Package
Execute privileges on the DBMS_REDEFINITION package are granted to EXECUTE_CATALOG_ROLE. In addition to having execute privileges on this package, you must be granted the following privileges:
*
CREATE ANY TABLE
*
ALTER ANY TABLE
*
DROP ANY TABLE
*
LOCK ANY TABLE
*
SELECT ANY TABLE
The following additional privileges are required to execute COPY_TABLE_DEPENDENTS:
*
CREATE ANY TRIGGER
*
CREATE ANY INDEX
Auditing Table Changes Using Flashback Transaction Query
2
What Are Index-Organized Tables?
An index-organized table has a storage organization that is a variant of a primary B-tree. Unlike an ordinary (heap-organized) table whose data is stored as an unordered collection (heap), data for an index-organized table is stored in a B-tree index structure in a primary key sorted manner. Each leaf block in the index structure stores both the key and nonkey columns.
The structure of an index-organized table provides the following benefits:
*
Fast random access on the primary key because an index-only scan is sufficient. And, because there is no separate table storage area, changes to the table data (such as adding new rows, updating rows, or deleting rows) result only in updating the index structure.
*
Fast range access on the primary key because the rows are clustered in primary key order.
*
Lower storage requirements because duplication of primary keys is avoided. They are not stored both in the index and underlying table, as is true with heap-organized tables.
Index-organized tables have full table functionality. They support features such as constraints, triggers, LOB and object columns, partitioning, parallel operations, online reorganization, and replication. And, they offer these additional features:
*
Key compression
*
Overflow storage area and specific column placement
*
Secondary indexes, including bitmap indexes.
Index-organized tables are ideal for OLTP applications, which require fast primary key access and high availability. Queries and DML on an orders table used in electronic order processing are predominantly primary-key based and heavy volume causes fragmentation resulting in a frequent need to reorganize. Because an index-organized table can be reorganized online and without invalidating its secondary indexes, the window of unavailability is greatly reduced or eliminated.
Index-organized tables are suitable for modeling application-specific index structures. For example, content-based information retrieval applications containing text, image and audio data require inverted indexes that can be effectively modeled using index-organized tables. A fundamental component of an internet search engine is an inverted index that can be modeled using index-organized tables.
These are but a few of the applications for index-organized tables.
3
Creating Index-Organized Tables
You use the CREATE TABLE statement to create index-organized tables, but you must provide additional information:
*
An ORGANIZATION INDEX qualifier, which indicates that this is an index-organized table
*
A primary key, specified through a column constraint clause (for a single column primary key) or a table constraint clause (for a multiple-column primary key).
Optionally, you can specify the following:
*
An OVERFLOW clause, which preserves dense clustering of the B-tree index by storing the row column values exceeding a specified threshold in a separate overflow data segment.
*
A PCTTHRESHOLD value, which defines the percentage of space reserved in the index block for an index-organized table. Any portion of the row that exceeds the specified threshold is stored in the overflow segment. In other words, the row is broken at a column boundary into two pieces, a head piece and tail piece. The head piece fits in the specified threshold and is stored along with the key in the index leaf block. The tail piece is stored in the overflow area as one or more row pieces. Thus, the index entry contains the key value, the nonkey column values that fit the specified threshold, and a pointer to the rest of the row.
*
An INCLUDING clause, which can be used to specify nonkey columns that are to be stored in the overflow data segment.
Creating an Index-Organized Table
The following statement creates an index-organized table:
CREATE TABLE admin_docindex(
token char(20),
doc_id NUMBER,
token_frequency NUMBER,
token_offsets VARCHAR2(512),
CONSTRAINT pk_admin_docindex PRIMARY KEY (token, doc_id))
ORGANIZATION INDEX
TABLESPACE admin_tbs
PCTTHRESHOLD 20
OVERFLOW TABLESPACE admin_tbs2;
Specifying ORGANIZATION INDEX causes the creation of an index-organized table, admin_docindex, where the key columns and nonkey columns reside in an index defined on columns that designate the primary key or keys for the table. In this case, the primary keys are token and doc_id. An overflow segment is specified and is discussed in "Using the Overflow Clause".
Creating Index-Organized Tables that Contain Object Types
Index-organized tables can store object types. The following example creates object type admin_typ, then creates an index-organized table containing a column of object type admin_typ:
CREATE OR REPLACE TYPE admin_typ AS OBJECT
(col1 NUMBER, col2 VARCHAR2(6));
CREATE TABLE admin_iot (c1 NUMBER primary key, c2 admin_typ)
ORGANIZATION INDEX;
You can also create an index-organized table of object types. For example:
CREATE TABLE admin_iot2 OF admin_typ (col1 PRIMARY KEY)
ORGANIZATION INDEX;
Another example, that follows, shows that index-organized tables store nested tables efficiently. For a nested table column, the database internally creates a storage table to hold all the nested table rows.
CREATE TYPE project_t AS OBJECT(pno NUMBER, pname VARCHAR2(80));
/
CREATE TYPE project_set AS TABLE OF project_t;
/
CREATE TABLE proj_tab (eno NUMBER, projects PROJECT_SET)
NESTED TABLE projects STORE AS emp_project_tab
((PRIMARY KEY(nested_table_id, pno))
ORGANIZATION INDEX)
RETURN AS LOCATOR;
The rows belonging to a single nested table instance are identified by a nested_table_id column. If an ordinary table is used to store nested table columns, the nested table rows typically get de-clustered. But when you use an index-organized table, the nested table rows can be clustered based on the nested_table_id column.
See Also:
*
Oracle Database SQL Reference for details of the syntax used for creating index-organized tables
*
"Creating Partitioned Index-Organized Tables" for information about creating partitioned index-organized tables
*
Oracle Database Application Developer's Guide - Object-Relational Features for information about object types
Using the Overflow Clause
The overflow clause specified in the statement shown in "Creating an Index-Organized Table" indicates that any nonkey columns of rows exceeding 20% of the block size are placed in a data segment stored in the admin_tbs2 tablespace. The key columns should fit the specified threshold.
If an update of a nonkey column causes the row to decrease in size, the database identifies the row piece (head or tail) to which the update is applicable and rewrites that piece.
If an update of a nonkey column causes the row to increase in size, the database identifies the piece (head or tail) to which the update is applicable and rewrites that row piece. If the target of the update turns out to be the head piece, note that this piece can again be broken into two to keep the row size below the specified threshold.
The nonkey columns that fit in the index leaf block are stored as a row head-piece that contains a rowid field linking it to the next row piece stored in the overflow data segment. The only columns that are stored in the overflow area are those that do not fit.
Choosing and Monitoring a Threshold Value
You should choose a threshold value that can accommodate your key columns, as well as the first few nonkey columns (if they are frequently accessed).
After choosing a threshold value, you can monitor tables to verify that the value you specified is appropriate. You can use the ANALYZE TABLE ... LIST CHAINED ROWS statement to determine the number and identity of rows exceeding the threshold value.
See Also:
*
"Listing Chained Rows of Tables and Clusters" for more information about chained rows
*
Oracle Database SQL Reference for syntax of the ANALYZE statement
Using the INCLUDING Clause
In addition to specifying PCTTHRESHOLD, you can use the INCLUDING clause to control which nonkey columns are stored with the key columns. The database accommodates all nonkey columns up to the column specified in the INCLUDING clause in the index leaf block, provided it does not exceed the specified threshold. All nonkey columns beyond the column specified in the INCLUDING clause are stored in the overflow area.
Note:
Oracle Database moves all primary key columns of an indexed-organized table to the beginning of the table (in their key order), in order to provide efficient primary key based access. As an example:
CREATE TABLE admin_iot4(a INT, b INT, c INT, d INT,
primary key(c,b))
ORGANIZATION INDEX;
The stored column order is: c b a d (instead of: a b c d). The last primary key column is b, based on the stored column order. The INCLUDING column can be the last primary key column (b in this example), or any nonkey column (that is, any column after b in the stored column order).
The following CREATE TABLE statement is similar to the one shown earlier in "Creating an Index-Organized Table" but is modified to create an index-organized table where the token_offsets column value is always stored in the overflow area:
CREATE TABLE admin_docindex2(
token CHAR(20),
doc_id NUMBER,
token_frequency NUMBER,
token_offsets VARCHAR2(512),
CONSTRAINT pk_admin_docindex2 PRIMARY KEY (token, doc_id))
ORGANIZATION INDEX
TABLESPACE admin_tbs
PCTTHRESHOLD 20
INCLUDING token_frequency
OVERFLOW TABLESPACE admin_tbs2;
Here, only nonkey columns prior to token_offsets (in this case a single column only) are stored with the key column values in the index leaf block.
Parallelizing Index-Organized Table Creation
The CREATE TABLE...AS SELECT statement enables you to create an index-organized table and load data from an existing table into it. By including the PARALLEL clause, the load can be done in parallel.
The following statement creates an index-organized table in parallel by selecting rows from the conventional table hr.jobs:
CREATE TABLE admin_iot3(i PRIMARY KEY, j, k, l)
ORGANIZATION INDEX
PARALLEL
AS SELECT * FROM hr.jobs;
This statement provides an alternative to parallel bulk-load using SQL*Loader.
Using Key Compression
Creating an index-organized table using key compression enables you to eliminate repeated occurrences of key column prefix values.
Key compression breaks an index key into a prefix and a suffix entry. Compression is achieved by sharing the prefix entries among all the suffix entries in an index block. This sharing can lead to huge savings in space, allowing you to store more keys in each index block while improving performance.
You can enable key compression using the COMPRESS clause while:
*
Creating an index-organized table
*
Moving an index-organized table
You can also specify the prefix length (as the number of key columns), which identifies how the key columns are broken into a prefix and suffix entry.
CREATE TABLE admin_iot5(i INT, j INT, k INT, l INT, PRIMARY KEY (i, j, k))
ORGANIZATION INDEX COMPRESS;
The preceding statement is equivalent to the following statement:
CREATE TABLE admin_iot6(i INT, j INT, k INT, l INT, PRIMARY KEY(i, j, k))
ORGANIZATION INDEX COMPRESS 2;
For the list of values (1,2,3), (1,2,4), (1,2,7), (1,3,5), (1,3,4), (1,4,4) the repeated occurrences of (1,2), (1,3) are compressed away.
You can also override the default prefix length used for compression as follows:
CREATE TABLE admin_iot7(i INT, j INT, k INT, l INT, PRIMARY KEY (i, j, k))
ORGANIZATION INDEX COMPRESS 1;
For the list of values (1,2,3), (1,2,4), (1,2,7), (1,3,5), (1,3,4), (1,4,4), the repeated occurrences of 1 are compressed away.
You can disable compression as follows:
ALTER TABLE admin_iot5 MOVE NOCOMPRESS;
One application of key compression is in a time-series application that uses a set of time-stamped rows belonging to a single item, such as a stock price. Index-organized tables are attractive for such applications because of the ability to cluster rows based on the primary key. By defining an index-organized table with primary key (stock symbol, time stamp), you can store and manipulate time-series data efficiently. You can achieve more storage savings by compressing repeated occurrences of the item identifier (for example, the stock symbol) in a time series by using an index-organized table with key compression.
See Also:
Oracle Database Concepts for more information about key compression
Maintaining Index-Organized Tables
Index-organized tables differ from ordinary tables only in physical organization. Logically, they are manipulated in the same manner as ordinary tables. You can specify an index-organized table just as you would specify a regular table in INSERT, SELECT, DELETE, and UPDATE statements.
Altering Index-Organized Tables
All of the alter options available for ordinary tables are available for index-organized tables. This includes ADD, MODIFY, and DROP COLUMNS and CONSTRAINTS. However, the primary key constraint for an index-organized table cannot be dropped, deferred, or disabled
You can use the ALTER TABLE statement to modify physical and storage attributes for both primary key index and overflow data segments. All the attributes specified prior to the OVERFLOW keyword are applicable to the primary key index segment. All attributes specified after the OVERFLOW key word are applicable to the overflow data segment. For example, you can set the INITRANS of the primary key index segment to 4 and the overflow of the data segment INITRANS to 6 as follows:
ALTER TABLE admin_docindex INITRANS 4 OVERFLOW INITRANS 6;
You can also alter PCTTHRESHOLD and INCLUDING column values. A new setting is used to break the row into head and overflow tail pieces during subsequent operations. For example, the PCTHRESHOLD and INCLUDING column values can be altered for the admin_docindex table as follows:
ALTER TABLE admin_docindex PCTTHRESHOLD 15 INCLUDING doc_id;
By setting the INCLUDING column to doc_id, all the columns that follow token_frequency and token_offsets, are stored in the overflow data segment.
For index-organized tables created without an overflow data segment, you can add an overflow data segment by using the ADD OVERFLOW clause. For example, you can add an overflow segment to table admin_iot3 as follows:
ALTER TABLE admin_iot3 ADD OVERFLOW TABLESPACE admin_tbs2;
Moving (Rebuilding) Index-Organized Tables
Because index-organized tables are primarily stored in a B-tree index, you can encounter fragmentation as a consequence of incremental updates. However, you can use the ALTER TABLE...MOVE statement to rebuild the index and reduce this fragmentation.
The following statement rebuilds the index-organized table admin_docindex:
ALTER TABLE admin_docindex MOVE;
You can rebuild index-organized tables online using the ONLINE keyword. The overflow data segment, if present, is rebuilt when the OVERFLOW keyword is specified. For example, to rebuild the admin_docindex table but not the overflow data segment, perform. a move online as follows:
ALTER TABLE admin_docindex MOVE ONLINE;
To rebuild the admin_docindex table along with its overflow data segment perform. the move operation as shown in the following statement. This statement also illustrates moving both the table and overflow data segment to new tablespaces.
ALTER TABLE admin_docindex MOVE TABLESPACE admin_tbs2
OVERFLOW TABLESPACE admin_tbs3;
In this last statement, an index-organized table with a LOB column (CLOB) is created. Later, the table is moved with the LOB index and data segment being rebuilt and moved to a new tablespace.
CREATE TABLE admin_iot_lob
(c1 number (6) primary key,
admin_lob CLOB)
ORGANIZATION INDEX
LOB (admin_lob) STORE AS (TABLESPACE admin_tbs2);
.
.
.
ALTER TABLE admin_iot_lob MOVE LOB (admin_lob) STORE AS (TABLESPACE admin_tbs3);
5
Altering External Tables
You can use any of the ALTER TABLE clauses shown in Table 15-3 to change the characteristics of an external table. No other clauses are permitted.
Table 15-3 ALTER TABLE Clauses for External Tables
ALTER TABLE Clause Description Example
REJECT LIMIT
Changes the reject limit
ALTER TABLE admin_ext_employees
REJECT LIMIT 100;
PROJECT COLUMN
Determines how the access driver validates rows in subsequent queries:
*
PROJECT COLUMN REFERENCED: the access driver processes only the columns in the select list of the query. This setting may not provide a consistent set of rows when querying a different column list from the same external table. This is the default.
*
PROJECT COLUMN ALL: the access driver processes all of the columns defined on the external table. This setting always provides a consistent set of rows when querying an external table.
ALTER TABLE admin_ext_employees
PROJECT COLUMN REFERNCED;
ALTER TABLE admin_ext_employees
PROJECT COLUMN ALL;
DEFAULT DIRECTORY
Changes the default directory specification
ALTER TABLE admin_ext_employees
DEFAULT DIRECTORY admin_dat2_dir;
ACCESS PARAMETERS
Allows access parameters to be changed without dropping and re-creating the external table metadata
ALTER TABLE admin_ext_employees
ACCESS PARAMETERS
(FIELDS TERMINATED BY ';');
LOCATION
Allows data sources to be changed without dropping and re-creating the external table metadata
ALTER TABLE admin_ext_employees
LOCATION ('empxt3.txt',
'empxt4.txt');
PARALLEL
No difference from regular tables. Allows degree of parallelism to be changed.
No new syntax
ADD COLUMN
No difference from regular tables. Allows a column to be added to an external table.
No new syntax
MODIFY COLUMN
No difference from regular tables. Allows an external table column to be modified.
No new syntax
DROP COLUMN
No difference from regular tables. Allows an external table column to be dropped.
No new syntax
RENAME TO
No difference from regular tables. Allows external table to be renamed.
No new syntax
12
Viewing Information About Tables
The following views allow you to access information about tables.
View Description
DBA_TABLES
ALL_TABLES
USER_TABLES
DBA view describes all relational tables in the database. ALL view describes all tables accessible to the user. USER view is restricted to tables owned by the user. Some columns in these views contain statistics that are generated by the DBMS_STATS package or ANALYZE statement.
DBA_TAB_COLUMNS
ALL_TAB_COLUMNS
USER_TAB_COLUMNS
These views describe the columns of tables, views, and clusters in the database. Some columns in these views contain statistics that are generated by the DBMS_STATS package or ANALYZE statement.
DBA_ALL_TABLES
ALL_ALL_TABLES
USER_ALL_TABLES
These views describe all relational and object tables in the database. Object tables are not specifically discussed in this book.
DBA_TAB_COMMENTS
ALL_TAB_COMMENTS
USER_TAB_COMMENTS
These views display comments for tables and views. Comments are entered using the COMMENT statement.
DBA_COL_COMMENTS
ALL_COL_COMMENTS
USER_COL_COMMENTS
These views display comments for table and view columns. Comments are entered using the COMMENT statement.
DBA_EXTERNAL_TABLES
ALL_EXTERNAL_TABLES
USER_EXTERNAL_TABLES
These views list the specific attributes of external tables in the database.
DBA_EXTERNAL_LOCATIONS
ALL_EXTERNAL_LOCATIONS
USER_EXTERNAL_LOCATIONS
These views list the data sources for external tables.
DBA_TAB_HISTOGRAMS
ALL_TAB_HISTOGRAMS
USER_TAB_HISTOGRAMS
These views describe histograms on tables and views.
DBA_TAB_STATISTICS
ALL_TAB_STATISTICS
USER_TAB_STATISTICS
These views contain optimizer statistics for tables.
DBA_TAB_COL_STATISTICS
ALL_TAB_COL_STATISTICS
USER_TAB_COL_STATISTICS
These views provide column statistics and histogram information extracted from the related TAB_COLUMNS views.
DBA_TAB_MODIFICATIONS
ALL_TAB_MODIFICATIONS
USER_TAB_MODIFICATIONS
These views describe tables that have been modified since the last time table statistics were gathered on them. They are not populated immediately, but after a time lapse (usually 3 hours).
DBA_ENCRYPTED_COLUMNS
USER_ENCRYPTED_COLUMNS
ALL_ENCRYPTED_COLUMNS
These views list table columns that are encrypted, and for each column, lists the encryption algorithm in use.
DBA_UNUSED_COL_TABS
ALL_UNUSED_COL_TABS
USER_UNUSED_COL_TABS
These views list tables with unused columns, as marked by the ALTER TABLE ... SET UNUSED statement.
DBA_PARTIAL_DROP_TABS
ALL_PARTIAL_DROP_TABS
USER_PARTIAL_DROP_TABS
These views list tables that have partially completed DROP COLUMN operations. These operations could be incomplete because the operation was interrupted by the user or a system failure.
22
About Indexes
Indexes are optional structures associated with tables and clusters that allow SQL statements to execute more quickly against a table. Just as the index in this manual helps you locate information faster than if there were no index, an Oracle Database index provides a faster access path to table data. You can use indexes without rewriting any queries. Your results are the same, but you see them more quickly.
Oracle Database provides several indexing schemes that provide complementary performance functionality. These are:
*
B-tree indexes: the default and the most common
*
B-tree cluster indexes: defined specifically for cluster
*
Hash cluster indexes: defined specifically for a hash cluster
*
Global and local indexes: relate to partitioned tables and indexes
*
Reverse key indexes: most useful for Oracle Real Application Clusters applications
*
Bitmap indexes: compact; work best for columns with a small set of values
*
Function-based indexes: contain the precomputed value of a function/expression
*
Domain indexes: specific to an application or cartridge.
23
Table 16-1 To Rebuild or Coalesce ... That Is the Question
Rebuild Index Coalesce Index
Quickly moves index to another tablespace
Cannot move index to another tablespace
Higher costs: requires more disk space
Lower costs: does not require more disk space
Creates new tree, shrinks height if applicable
Coalesces leaf blocks within same branch of tree
Enables you to quickly change storage and tablespace parameters without having to drop the original index.
Quickly frees up index leaf blocks for use.
24
Specifying Storage Options for an Index Associated with a Constraint
You can set the storage options for the indexes associated with UNIQUE and PRIMARY KEY constraints using the USING INDEX clause. The following CREATE TABLE statement enables a PRIMARY KEY constraint and specifies the storage options of the associated index:
CREATE TABLE emp (
empno NUMBER(5) PRIMARY KEY, age INTEGER)
ENABLE PRIMARY KEY USING INDEX
TABLESPACE users;
Specifying the Index Associated with a Constraint
If you require more explicit control over the indexes associated with UNIQUE and PRIMARY KEY constraints, the database lets you:
*
Specify an existing index that the database is to use to enforce the constraint
*
Specify a CREATE INDEX statement that the database is to use to create the index and enforce the constraint
These options are specified using the USING INDEX clause. The following statements present some examples.
Example 1:
CREATE TABLE a (
a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));
Example 2:
CREATE TABLE b(
b1 INT,
b2 INT,
CONSTRAINT bu1 UNIQUE (b1, b2)
USING INDEX (create unique index bi on b(b1, b2)),
CONSTRAINT bu2 UNIQUE (b2, b1) USING INDEX bi);
Example 3:
CREATE TABLE c(c1 INT, c2 INT);
CREATE INDEX ci ON c (c1, c2);
ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1) USING INDEX ci;
If a single statement creates an index with one constraint and also uses that index for another constraint, the system will attempt to rearrange the clauses to create the index before reusing it.
25
Monitoring Space Use of Indexes
If key values in an index are inserted, updated, and deleted frequently, the index can lose its acquired space efficiently over time. Monitor index efficiency of space usage at regular intervals by first analyzing the index structure, using the ANALYZE INDEX...VALIDATE STRUCTURE statement, and then querying the INDEX_STATS view:
SELECT PCT_USED FROM INDEX_STATS WHERE NAME = 'index';
The percentage of index space usage varies according to how often index keys are inserted, updated, or deleted. Develop a history of average efficiency of space usage for an index by performing the following sequence of operations several times:
*
Analyzing statistics
*
Validating the index
*
Checking PCT_USED
*
Dropping and rebuilding (or coalescing) the index
When you find that index space usage drops below its average, you can condense the index space by dropping the index and rebuilding it, or coalescing it.
26
Viewing Index Information
The following views display information about indexes:
View Description
DBA_INDEXES
ALL_INDEXES
USER_INDEXES
DBA view describes indexes on all tables in the database. ALL view describes indexes on all tables accessible to the user. USER view is restricted to indexes owned by the user. Some columns in these views contain statistics that are generated by the DBMS_STATS package or ANALYZE statement.
DBA_IND_COLUMNS
ALL_IND_COLUMNS
USER_IND_COLUMNS
These views describe the columns of indexes on tables. Some columns in these views contain statistics that are generated by the DBMS_STATS package or ANALYZE statement.
DBA_IND_EXPRESSIONS
ALL_IND_EXPRESSIONS
USER_IND_EXPRESSIONS
These views describe the expressions of function-based indexes on tables.
DBA_IND_STATISTICS
ALL_IND_STATISTICS
USER_IND_STATISTICS
These views contain optimizer statistics for indexes.
INDEX_STATS Stores information from the last ANALYZE INDEX...VALIDATE STRUCTURE statement.
INDEX_HISTOGRAM Stores information from the last ANALYZE INDEX...VALIDATE STRUCTURE statement.
V$OBJECT_USAGE Contains index usage information produced by the ALTER INDEX...MONITORING USAGE functionality.
Online Table Redefinition Examples
For the following examples, see Oracle Database PL/SQL Packages and Types Reference for descriptions of all DBMS_REDEFINITION subprograms.
Example Description
Example 1 Redefines a table by adding new columns and adding partitioning.
Example 2 Demonstrates redefinition with object datatypes.
Example 3 Demonstrates redefinition with manually registered dependent objects.
Example 4 Redefines a single table partition, moving it to a different tablespace.
Example 1
This example illustrates online redefinition of the previously created table hr.admin_emp, which at this point only contains columns: empno, ename, job, deptno. The table is redefined as follows:
*
New columns mgr, hiredate, sal, and bonus are added. (These existed in the original table but were dropped in previous examples.)
*
The new column bonus is initialized to 0
*
The column deptno has its value increased by 10.
*
The redefined table is partitioned by range on empno.
The steps in this redefinition are illustrated below.
1.
Verify that the table is a candidate for online redefinition. In this case you specify that the redefinition is to be done using primary keys or pseudo-primary keys.
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('hr','admin_emp',
DBMS_REDEFINITION.CONS_USE_PK);
END;
/
2.
Create an interim table hr.int_admin_emp.
CREATE TABLE hr.int_admin_emp
(empno NUMBER(5) PRIMARY KEY,
ename VARCHAR2(15) NOT NULL,
job VARCHAR2(10),
mgr NUMBER(5),
hiredate DATE DEFAULT (sysdate),
sal NUMBER(7,2),
deptno NUMBER(3) NOT NULL,
bonus NUMBER (7,2) DEFAULT(1000))
PARTITION BY RANGE(empno)
(PARTITION emp1000 VALUES LESS THAN (1000) TABLESPACE admin_tbs,
PARTITION emp2000 VALUES LESS THAN (2000) TABLESPACE admin_tbs2);
3.
Start the redefinition process.
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('hr', 'admin_emp','int_admin_emp',
'empno empno, ename ename, job job, deptno+10 deptno, 0 bonus',
dbms_redefinition.cons_use_pk);
END;
/
4.
Copy dependent objects. (Automatically create any triggers, indexes, grants, and constraints on hr.int_admin_emp.)
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('hr', 'admin_emp','int_admin_emp',
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
Note that the ignore_errors argument is set to TRUE for this call. The reason is that the interim table was created with a primary key constraint, and when COPY_TABLE_DEPENDENTS attempts to copy the primary key constraint and index from the original table, errors occurs. You can ignore these errors, but you must run the query shown in the next step to see if there are other errors.
5.
Query the DBA_REDEFINITION_ERRORS view to check for errors.
SQL> select object_name, base_table_name, ddl_txt from
DBA_REDEFINITION_ERRORS;
OBJECT_NAME BASE_TABLE_NAME DDL_TXT
------------- ---------------- ------------------------------
SYS_C005836 ADMIN_EMP CREATE UNIQUE INDEX "HR"."TMP$
$_SYS_C0058360" ON "HR"."INT_A
DMIN_EMP" ("EMPNO")
SYS_C005836 ADMIN_EMP ALTER TABLE "HR"."INT_ADMIN_EM
P" ADD CONSTRAINT "TMP$$_SYS_C
0058360" PRIMARY KEY
These errors are caused by the existing primary key constraint on the interim table and can be ignored. Note that with this approach, the names of the primary key constraint and index on the post-redefined table are changed. An alternate approach, one that avoids errors and name changes, would be to define the interim table without a primary key constraint. In this case, the primary key constraint and index are copied from the original table.
Note:
The best approach is to define the interim table with a primary key constraint, use REGISTER_DEPENDENT_OBJECT to register the primary key constraint and index, and then copy the remaining dependent objects with COPY_TABLE_DEPENDENTS. This approach avoids errors and ensures that the redefined table always has a primary key and that the dependent object names do not change.
6.
Optionally, synchronize the interim table hr.int_admin_emp.
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('hr', 'admin_emp', 'int_admin_emp');
END;
/
7.
Complete the redefinition.
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('hr', 'admin_emp', 'int_admin_emp');
END;
/
The table hr.admin_emp is locked in the exclusive mode only for a small window toward the end of this step. After this call the table hr.admin_emp is redefined such that it has all the attributes of the hr.int_admin_emp table.
8.
Drop the interim table.
Example 2
This example redefines a table to change columns into object attributes. The redefined table gets a new column that is an object type.
The original table, named CUSTOMER, is defined as follows:
Name Type
------------ -------------
CID NUMBER NAME VARCHAR2(30)
STREET VARCHAR2(100)
CITY VARCHAR2(30)
STATE VARCHAR2(2)
ZIP NUMBER(5)
The type definition for the new object is:
CREATE TYPE ADDR_T AS OBJECT (
street VARCHAR2(100),
city VARCHAR2(30),
state VARCHAR2(2),
zip NUMBER(5, 0) );
Here are the steps for this redefinition:
1.
Verify that the table is a candidate for online redefinition. Specify that the redefinition is to be done using primary keys or pseudo-primary keys.
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('STEVE','CUSTOMER',
DBMS_REDEFINITION.CONS_USE_PK);
END;
/
2.
Create the interim table int_customer.
CREATE TABLE INT_CUSTOMER(
CID NUMBER,
NAME VARCHAR2(30),
ADDR ADDR_T);
Note that no primary key is defined on the interim table. When dependent objects are copied in step 5, the primary key constraint and index are copied.
3.
Because CUSTOMER is a very large table, specify parallel operations for the next step.
alter session force parallel dml parallel 4;
alter session force parallel query parallel 4;
4.
Start the redefinition process using primary keys.
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'STEVE',
orig_table => 'CUSTOMER',
int_table => 'INT_CUSTOMER',
col_mapping => 'cid cid, name name,
addr_t(street, city, state, zip) addr');
END;
/
Note that addr_t(street, city, state, zip) is a call to the object constructor.
5.
Copy dependent objects.
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
'STEVE','CUSTOMER','INT_CUSTOMER',DBMS_REDEFINITION.CONS_ORIG_PARAMS,
TRUE, TRUE, TRUE, FALSE, num_errors, TRUE);
END;
/
Note that for this call, the final argument indicates that table statistics are to be copied to the interim table.
6.
Optionally synchronize the interim table.
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('STEVE', 'CUSTOMER', 'INT_CUSTOMER');
END;
/
7.
Complete the redefinition.
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('STEVE', 'CUSTOMER', 'INT_CUSTOMER');
END;
/
8.
Drop the interim table.
Example 3
This example addresses the situation where a dependent object must be manually created and registered.
Consider the case where a table T1 has a column named C1, and where this column becomes C2 after the redefinition. Assume that there is an index Index1 on C1. In this case, COPY_TABLE_DEPENDENTS tries to create an index on the interim table corresponding to Index1, and tries to create it on a column C1, which does not exist on the interim table. This results in an error. You must therefore manually create the index on column C2 and register it. Here are the steps:
1.
Create the interim table INT_T1 and create an index Int_Index1 on column C2.
2.
Ensure that T1 is a candidate for online redefinition with CAN_REDEF_TABLE, and then begin the redefinition process with START_REDEF_TABLE.
3.
Register the original (Index1) and interim (Int_Index1) dependent objects.
BEGIN
DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT(
uname => 'STEVE',
orig_table => 'T1',
int_table => 'INT_T1',
dep_type => DBMS_REDEFINITION.CONS_INDEX,
dep_owner => 'STEVE',
dep_orig_name => 'Index1',
dep_int_name => 'Int_Index1');
END;
/
4.
Use COPY_TABLE_DEPENDENTS to copy the remaining dependent objects.
5.
Optionally synchronize the interim table.
6.
Complete the redefinition and drop the interim table.
Example 4
This example demonstrates redefining a single partition. It moves the oldest partition of a range-partitioned sales table to a tablespace named TBS_LOW_FREQ. The table containing the partition to be redefined is defined as follows:
CREATE TABLE salestable
(s_productid NUMBER,
s_saledate DATE,
s_custid NUMBER,
s_totalprice NUMBER)
TABLESPACE users
PARTITION BY RANGE(s_saledate)
(PARTITION sal03q1 VALUES LESS THAN (TO_DATE('01-APR-2003', 'DD-MON-YYYY')),
PARTITION sal03q2 VALUES LESS THAN (TO_DATE('01-JUL-2003', 'DD-MON-YYYY')),
PARTITION sal03q3 VALUES LESS THAN (TO_DATE('01-OCT-2003', 'DD-MON-YYYY')),
PARTITION sal03q4 VALUES LESS THAN (TO_DATE('01-JAN-2004', 'DD-MON-YYYY')));
The table has a local partitioned index that is defined as follows:
CREATE INDEX sales_index ON salestable
(s_saledate, s_productid, s_custid) LOCAL;
Here are the steps. In the following procedure calls, note the extra argument: partition name (part_name).
1.
Ensure that salestable is a candidate for redefinition.
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE(
uname => 'STEVE',
tname => 'SALESTABLE',
options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
part_name => 'sal03q1');
END;
/
2.
Create the interim table in the TBS_LOW_FREQ tablespace. Because this is a redefinition of a range partition, the interim table is non-partitioned.
CREATE TABLE int_salestable
(s_productid NUMBER,
s_saledate DATE,
s_custid NUMBER,
s_totalprice NUMBER)
TABLESPACE tbs_low_freq;
3.
Start the redefinition process using rowid.
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'STEVE',
orig_table => 'salestable',
int_table => 'int_salestable',
col_mapping => NULL,
options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
part_name => 'sal03q1');
END;
/
4.
Manually create any local indexes on the interim table.
CREATE INDEX int_sales_index ON int_salestable
(s_saledate, s_productid, s_custid)
TABLESPACE tbs_low_freq;
5.
Optionally synchronize the interim table.
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
uname => 'STEVE',
orig_table => 'salestable',
int_table => 'int_salestable',
part_name => 'sal03q1');
END;
/
6.
Complete the redefinition.
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(
uname => 'STEVE',
orig_table => 'salestable',
int_table => 'int_salestable',
part_name => 'sal03q1');
END;
/
7.
Drop the interim table.
The following query shows that the oldest partition has been moved to the new tablespace:
select partition_name, tablespace_name from user_tab_partitions
where table_name = 'SALESTABLE';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1 TBS_LOW_FREQ
SAL03Q2 USERS
SAL03Q3 USERS
SAL03Q4 USERS
4 rows selected.
Privileges Required for the DBMS_REDEFINITION Package
Execute privileges on the DBMS_REDEFINITION package are granted to EXECUTE_CATALOG_ROLE. In addition to having execute privileges on this package, you must be granted the following privileges:
*
CREATE ANY TABLE
*
ALTER ANY TABLE
*
DROP ANY TABLE
*
LOCK ANY TABLE
*
SELECT ANY TABLE
The following additional privileges are required to execute COPY_TABLE_DEPENDENTS:
*
CREATE ANY TRIGGER
*
CREATE ANY INDEX
Auditing Table Changes Using Flashback Transaction Query
2
What Are Index-Organized Tables?
An index-organized table has a storage organization that is a variant of a primary B-tree. Unlike an ordinary (heap-organized) table whose data is stored as an unordered collection (heap), data for an index-organized table is stored in a B-tree index structure in a primary key sorted manner. Each leaf block in the index structure stores both the key and nonkey columns.
The structure of an index-organized table provides the following benefits:
*
Fast random access on the primary key because an index-only scan is sufficient. And, because there is no separate table storage area, changes to the table data (such as adding new rows, updating rows, or deleting rows) result only in updating the index structure.
*
Fast range access on the primary key because the rows are clustered in primary key order.
*
Lower storage requirements because duplication of primary keys is avoided. They are not stored both in the index and underlying table, as is true with heap-organized tables.
Index-organized tables have full table functionality. They support features such as constraints, triggers, LOB and object columns, partitioning, parallel operations, online reorganization, and replication. And, they offer these additional features:
*
Key compression
*
Overflow storage area and specific column placement
*
Secondary indexes, including bitmap indexes.
Index-organized tables are ideal for OLTP applications, which require fast primary key access and high availability. Queries and DML on an orders table used in electronic order processing are predominantly primary-key based and heavy volume causes fragmentation resulting in a frequent need to reorganize. Because an index-organized table can be reorganized online and without invalidating its secondary indexes, the window of unavailability is greatly reduced or eliminated.
Index-organized tables are suitable for modeling application-specific index structures. For example, content-based information retrieval applications containing text, image and audio data require inverted indexes that can be effectively modeled using index-organized tables. A fundamental component of an internet search engine is an inverted index that can be modeled using index-organized tables.
These are but a few of the applications for index-organized tables.
3
Creating Index-Organized Tables
You use the CREATE TABLE statement to create index-organized tables, but you must provide additional information:
*
An ORGANIZATION INDEX qualifier, which indicates that this is an index-organized table
*
A primary key, specified through a column constraint clause (for a single column primary key) or a table constraint clause (for a multiple-column primary key).
Optionally, you can specify the following:
*
An OVERFLOW clause, which preserves dense clustering of the B-tree index by storing the row column values exceeding a specified threshold in a separate overflow data segment.
*
A PCTTHRESHOLD value, which defines the percentage of space reserved in the index block for an index-organized table. Any portion of the row that exceeds the specified threshold is stored in the overflow segment. In other words, the row is broken at a column boundary into two pieces, a head piece and tail piece. The head piece fits in the specified threshold and is stored along with the key in the index leaf block. The tail piece is stored in the overflow area as one or more row pieces. Thus, the index entry contains the key value, the nonkey column values that fit the specified threshold, and a pointer to the rest of the row.
*
An INCLUDING clause, which can be used to specify nonkey columns that are to be stored in the overflow data segment.
Creating an Index-Organized Table
The following statement creates an index-organized table:
CREATE TABLE admin_docindex(
token char(20),
doc_id NUMBER,
token_frequency NUMBER,
token_offsets VARCHAR2(512),
CONSTRAINT pk_admin_docindex PRIMARY KEY (token, doc_id))
ORGANIZATION INDEX
TABLESPACE admin_tbs
PCTTHRESHOLD 20
OVERFLOW TABLESPACE admin_tbs2;
Specifying ORGANIZATION INDEX causes the creation of an index-organized table, admin_docindex, where the key columns and nonkey columns reside in an index defined on columns that designate the primary key or keys for the table. In this case, the primary keys are token and doc_id. An overflow segment is specified and is discussed in "Using the Overflow Clause".
Creating Index-Organized Tables that Contain Object Types
Index-organized tables can store object types. The following example creates object type admin_typ, then creates an index-organized table containing a column of object type admin_typ:
CREATE OR REPLACE TYPE admin_typ AS OBJECT
(col1 NUMBER, col2 VARCHAR2(6));
CREATE TABLE admin_iot (c1 NUMBER primary key, c2 admin_typ)
ORGANIZATION INDEX;
You can also create an index-organized table of object types. For example:
CREATE TABLE admin_iot2 OF admin_typ (col1 PRIMARY KEY)
ORGANIZATION INDEX;
Another example, that follows, shows that index-organized tables store nested tables efficiently. For a nested table column, the database internally creates a storage table to hold all the nested table rows.
CREATE TYPE project_t AS OBJECT(pno NUMBER, pname VARCHAR2(80));
/
CREATE TYPE project_set AS TABLE OF project_t;
/
CREATE TABLE proj_tab (eno NUMBER, projects PROJECT_SET)
NESTED TABLE projects STORE AS emp_project_tab
((PRIMARY KEY(nested_table_id, pno))
ORGANIZATION INDEX)
RETURN AS LOCATOR;
The rows belonging to a single nested table instance are identified by a nested_table_id column. If an ordinary table is used to store nested table columns, the nested table rows typically get de-clustered. But when you use an index-organized table, the nested table rows can be clustered based on the nested_table_id column.
See Also:
*
Oracle Database SQL Reference for details of the syntax used for creating index-organized tables
*
"Creating Partitioned Index-Organized Tables" for information about creating partitioned index-organized tables
*
Oracle Database Application Developer's Guide - Object-Relational Features for information about object types
Using the Overflow Clause
The overflow clause specified in the statement shown in "Creating an Index-Organized Table" indicates that any nonkey columns of rows exceeding 20% of the block size are placed in a data segment stored in the admin_tbs2 tablespace. The key columns should fit the specified threshold.
If an update of a nonkey column causes the row to decrease in size, the database identifies the row piece (head or tail) to which the update is applicable and rewrites that piece.
If an update of a nonkey column causes the row to increase in size, the database identifies the piece (head or tail) to which the update is applicable and rewrites that row piece. If the target of the update turns out to be the head piece, note that this piece can again be broken into two to keep the row size below the specified threshold.
The nonkey columns that fit in the index leaf block are stored as a row head-piece that contains a rowid field linking it to the next row piece stored in the overflow data segment. The only columns that are stored in the overflow area are those that do not fit.
Choosing and Monitoring a Threshold Value
You should choose a threshold value that can accommodate your key columns, as well as the first few nonkey columns (if they are frequently accessed).
After choosing a threshold value, you can monitor tables to verify that the value you specified is appropriate. You can use the ANALYZE TABLE ... LIST CHAINED ROWS statement to determine the number and identity of rows exceeding the threshold value.
See Also:
*
"Listing Chained Rows of Tables and Clusters" for more information about chained rows
*
Oracle Database SQL Reference for syntax of the ANALYZE statement
Using the INCLUDING Clause
In addition to specifying PCTTHRESHOLD, you can use the INCLUDING clause to control which nonkey columns are stored with the key columns. The database accommodates all nonkey columns up to the column specified in the INCLUDING clause in the index leaf block, provided it does not exceed the specified threshold. All nonkey columns beyond the column specified in the INCLUDING clause are stored in the overflow area.
Note:
Oracle Database moves all primary key columns of an indexed-organized table to the beginning of the table (in their key order), in order to provide efficient primary key based access. As an example:
CREATE TABLE admin_iot4(a INT, b INT, c INT, d INT,
primary key(c,b))
ORGANIZATION INDEX;
The stored column order is: c b a d (instead of: a b c d). The last primary key column is b, based on the stored column order. The INCLUDING column can be the last primary key column (b in this example), or any nonkey column (that is, any column after b in the stored column order).
The following CREATE TABLE statement is similar to the one shown earlier in "Creating an Index-Organized Table" but is modified to create an index-organized table where the token_offsets column value is always stored in the overflow area:
CREATE TABLE admin_docindex2(
token CHAR(20),
doc_id NUMBER,
token_frequency NUMBER,
token_offsets VARCHAR2(512),
CONSTRAINT pk_admin_docindex2 PRIMARY KEY (token, doc_id))
ORGANIZATION INDEX
TABLESPACE admin_tbs
PCTTHRESHOLD 20
INCLUDING token_frequency
OVERFLOW TABLESPACE admin_tbs2;
Here, only nonkey columns prior to token_offsets (in this case a single column only) are stored with the key column values in the index leaf block.
Parallelizing Index-Organized Table Creation
The CREATE TABLE...AS SELECT statement enables you to create an index-organized table and load data from an existing table into it. By including the PARALLEL clause, the load can be done in parallel.
The following statement creates an index-organized table in parallel by selecting rows from the conventional table hr.jobs:
CREATE TABLE admin_iot3(i PRIMARY KEY, j, k, l)
ORGANIZATION INDEX
PARALLEL
AS SELECT * FROM hr.jobs;
This statement provides an alternative to parallel bulk-load using SQL*Loader.
Using Key Compression
Creating an index-organized table using key compression enables you to eliminate repeated occurrences of key column prefix values.
Key compression breaks an index key into a prefix and a suffix entry. Compression is achieved by sharing the prefix entries among all the suffix entries in an index block. This sharing can lead to huge savings in space, allowing you to store more keys in each index block while improving performance.
You can enable key compression using the COMPRESS clause while:
*
Creating an index-organized table
*
Moving an index-organized table
You can also specify the prefix length (as the number of key columns), which identifies how the key columns are broken into a prefix and suffix entry.
CREATE TABLE admin_iot5(i INT, j INT, k INT, l INT, PRIMARY KEY (i, j, k))
ORGANIZATION INDEX COMPRESS;
The preceding statement is equivalent to the following statement:
CREATE TABLE admin_iot6(i INT, j INT, k INT, l INT, PRIMARY KEY(i, j, k))
ORGANIZATION INDEX COMPRESS 2;
For the list of values (1,2,3), (1,2,4), (1,2,7), (1,3,5), (1,3,4), (1,4,4) the repeated occurrences of (1,2), (1,3) are compressed away.
You can also override the default prefix length used for compression as follows:
CREATE TABLE admin_iot7(i INT, j INT, k INT, l INT, PRIMARY KEY (i, j, k))
ORGANIZATION INDEX COMPRESS 1;
For the list of values (1,2,3), (1,2,4), (1,2,7), (1,3,5), (1,3,4), (1,4,4), the repeated occurrences of 1 are compressed away.
You can disable compression as follows:
ALTER TABLE admin_iot5 MOVE NOCOMPRESS;
One application of key compression is in a time-series application that uses a set of time-stamped rows belonging to a single item, such as a stock price. Index-organized tables are attractive for such applications because of the ability to cluster rows based on the primary key. By defining an index-organized table with primary key (stock symbol, time stamp), you can store and manipulate time-series data efficiently. You can achieve more storage savings by compressing repeated occurrences of the item identifier (for example, the stock symbol) in a time series by using an index-organized table with key compression.
See Also:
Oracle Database Concepts for more information about key compression
Maintaining Index-Organized Tables
Index-organized tables differ from ordinary tables only in physical organization. Logically, they are manipulated in the same manner as ordinary tables. You can specify an index-organized table just as you would specify a regular table in INSERT, SELECT, DELETE, and UPDATE statements.
Altering Index-Organized Tables
All of the alter options available for ordinary tables are available for index-organized tables. This includes ADD, MODIFY, and DROP COLUMNS and CONSTRAINTS. However, the primary key constraint for an index-organized table cannot be dropped, deferred, or disabled
You can use the ALTER TABLE statement to modify physical and storage attributes for both primary key index and overflow data segments. All the attributes specified prior to the OVERFLOW keyword are applicable to the primary key index segment. All attributes specified after the OVERFLOW key word are applicable to the overflow data segment. For example, you can set the INITRANS of the primary key index segment to 4 and the overflow of the data segment INITRANS to 6 as follows:
ALTER TABLE admin_docindex INITRANS 4 OVERFLOW INITRANS 6;
You can also alter PCTTHRESHOLD and INCLUDING column values. A new setting is used to break the row into head and overflow tail pieces during subsequent operations. For example, the PCTHRESHOLD and INCLUDING column values can be altered for the admin_docindex table as follows:
ALTER TABLE admin_docindex PCTTHRESHOLD 15 INCLUDING doc_id;
By setting the INCLUDING column to doc_id, all the columns that follow token_frequency and token_offsets, are stored in the overflow data segment.
For index-organized tables created without an overflow data segment, you can add an overflow data segment by using the ADD OVERFLOW clause. For example, you can add an overflow segment to table admin_iot3 as follows:
ALTER TABLE admin_iot3 ADD OVERFLOW TABLESPACE admin_tbs2;
Moving (Rebuilding) Index-Organized Tables
Because index-organized tables are primarily stored in a B-tree index, you can encounter fragmentation as a consequence of incremental updates. However, you can use the ALTER TABLE...MOVE statement to rebuild the index and reduce this fragmentation.
The following statement rebuilds the index-organized table admin_docindex:
ALTER TABLE admin_docindex MOVE;
You can rebuild index-organized tables online using the ONLINE keyword. The overflow data segment, if present, is rebuilt when the OVERFLOW keyword is specified. For example, to rebuild the admin_docindex table but not the overflow data segment, perform. a move online as follows:
ALTER TABLE admin_docindex MOVE ONLINE;
To rebuild the admin_docindex table along with its overflow data segment perform. the move operation as shown in the following statement. This statement also illustrates moving both the table and overflow data segment to new tablespaces.
ALTER TABLE admin_docindex MOVE TABLESPACE admin_tbs2
OVERFLOW TABLESPACE admin_tbs3;
In this last statement, an index-organized table with a LOB column (CLOB) is created. Later, the table is moved with the LOB index and data segment being rebuilt and moved to a new tablespace.
CREATE TABLE admin_iot_lob
(c1 number (6) primary key,
admin_lob CLOB)
ORGANIZATION INDEX
LOB (admin_lob) STORE AS (TABLESPACE admin_tbs2);
.
.
.
ALTER TABLE admin_iot_lob MOVE LOB (admin_lob) STORE AS (TABLESPACE admin_tbs3);
5
Altering External Tables
You can use any of the ALTER TABLE clauses shown in Table 15-3 to change the characteristics of an external table. No other clauses are permitted.
Table 15-3 ALTER TABLE Clauses for External Tables
ALTER TABLE Clause Description Example
REJECT LIMIT
Changes the reject limit
ALTER TABLE admin_ext_employees
REJECT LIMIT 100;
PROJECT COLUMN
Determines how the access driver validates rows in subsequent queries:
*
PROJECT COLUMN REFERENCED: the access driver processes only the columns in the select list of the query. This setting may not provide a consistent set of rows when querying a different column list from the same external table. This is the default.
*
PROJECT COLUMN ALL: the access driver processes all of the columns defined on the external table. This setting always provides a consistent set of rows when querying an external table.
ALTER TABLE admin_ext_employees
PROJECT COLUMN REFERNCED;
ALTER TABLE admin_ext_employees
PROJECT COLUMN ALL;
DEFAULT DIRECTORY
Changes the default directory specification
ALTER TABLE admin_ext_employees
DEFAULT DIRECTORY admin_dat2_dir;
ACCESS PARAMETERS
Allows access parameters to be changed without dropping and re-creating the external table metadata
ALTER TABLE admin_ext_employees
ACCESS PARAMETERS
(FIELDS TERMINATED BY ';');
LOCATION
Allows data sources to be changed without dropping and re-creating the external table metadata
ALTER TABLE admin_ext_employees
LOCATION ('empxt3.txt',
'empxt4.txt');
PARALLEL
No difference from regular tables. Allows degree of parallelism to be changed.
No new syntax
ADD COLUMN
No difference from regular tables. Allows a column to be added to an external table.
No new syntax
MODIFY COLUMN
No difference from regular tables. Allows an external table column to be modified.
No new syntax
DROP COLUMN
No difference from regular tables. Allows an external table column to be dropped.
No new syntax
RENAME TO
No difference from regular tables. Allows external table to be renamed.
No new syntax
12
Viewing Information About Tables
The following views allow you to access information about tables.
View Description
DBA_TABLES
ALL_TABLES
USER_TABLES
DBA view describes all relational tables in the database. ALL view describes all tables accessible to the user. USER view is restricted to tables owned by the user. Some columns in these views contain statistics that are generated by the DBMS_STATS package or ANALYZE statement.
DBA_TAB_COLUMNS
ALL_TAB_COLUMNS
USER_TAB_COLUMNS
These views describe the columns of tables, views, and clusters in the database. Some columns in these views contain statistics that are generated by the DBMS_STATS package or ANALYZE statement.
DBA_ALL_TABLES
ALL_ALL_TABLES
USER_ALL_TABLES
These views describe all relational and object tables in the database. Object tables are not specifically discussed in this book.
DBA_TAB_COMMENTS
ALL_TAB_COMMENTS
USER_TAB_COMMENTS
These views display comments for tables and views. Comments are entered using the COMMENT statement.
DBA_COL_COMMENTS
ALL_COL_COMMENTS
USER_COL_COMMENTS
These views display comments for table and view columns. Comments are entered using the COMMENT statement.
DBA_EXTERNAL_TABLES
ALL_EXTERNAL_TABLES
USER_EXTERNAL_TABLES
These views list the specific attributes of external tables in the database.
DBA_EXTERNAL_LOCATIONS
ALL_EXTERNAL_LOCATIONS
USER_EXTERNAL_LOCATIONS
These views list the data sources for external tables.
DBA_TAB_HISTOGRAMS
ALL_TAB_HISTOGRAMS
USER_TAB_HISTOGRAMS
These views describe histograms on tables and views.
DBA_TAB_STATISTICS
ALL_TAB_STATISTICS
USER_TAB_STATISTICS
These views contain optimizer statistics for tables.
DBA_TAB_COL_STATISTICS
ALL_TAB_COL_STATISTICS
USER_TAB_COL_STATISTICS
These views provide column statistics and histogram information extracted from the related TAB_COLUMNS views.
DBA_TAB_MODIFICATIONS
ALL_TAB_MODIFICATIONS
USER_TAB_MODIFICATIONS
These views describe tables that have been modified since the last time table statistics were gathered on them. They are not populated immediately, but after a time lapse (usually 3 hours).
DBA_ENCRYPTED_COLUMNS
USER_ENCRYPTED_COLUMNS
ALL_ENCRYPTED_COLUMNS
These views list table columns that are encrypted, and for each column, lists the encryption algorithm in use.
DBA_UNUSED_COL_TABS
ALL_UNUSED_COL_TABS
USER_UNUSED_COL_TABS
These views list tables with unused columns, as marked by the ALTER TABLE ... SET UNUSED statement.
DBA_PARTIAL_DROP_TABS
ALL_PARTIAL_DROP_TABS
USER_PARTIAL_DROP_TABS
These views list tables that have partially completed DROP COLUMN operations. These operations could be incomplete because the operation was interrupted by the user or a system failure.
22
About Indexes
Indexes are optional structures associated with tables and clusters that allow SQL statements to execute more quickly against a table. Just as the index in this manual helps you locate information faster than if there were no index, an Oracle Database index provides a faster access path to table data. You can use indexes without rewriting any queries. Your results are the same, but you see them more quickly.
Oracle Database provides several indexing schemes that provide complementary performance functionality. These are:
*
B-tree indexes: the default and the most common
*
B-tree cluster indexes: defined specifically for cluster
*
Hash cluster indexes: defined specifically for a hash cluster
*
Global and local indexes: relate to partitioned tables and indexes
*
Reverse key indexes: most useful for Oracle Real Application Clusters applications
*
Bitmap indexes: compact; work best for columns with a small set of values
*
Function-based indexes: contain the precomputed value of a function/expression
*
Domain indexes: specific to an application or cartridge.
23
Table 16-1 To Rebuild or Coalesce ... That Is the Question
Rebuild Index Coalesce Index
Quickly moves index to another tablespace
Cannot move index to another tablespace
Higher costs: requires more disk space
Lower costs: does not require more disk space
Creates new tree, shrinks height if applicable
Coalesces leaf blocks within same branch of tree
Enables you to quickly change storage and tablespace parameters without having to drop the original index.
Quickly frees up index leaf blocks for use.
24
Specifying Storage Options for an Index Associated with a Constraint
You can set the storage options for the indexes associated with UNIQUE and PRIMARY KEY constraints using the USING INDEX clause. The following CREATE TABLE statement enables a PRIMARY KEY constraint and specifies the storage options of the associated index:
CREATE TABLE emp (
empno NUMBER(5) PRIMARY KEY, age INTEGER)
ENABLE PRIMARY KEY USING INDEX
TABLESPACE users;
Specifying the Index Associated with a Constraint
If you require more explicit control over the indexes associated with UNIQUE and PRIMARY KEY constraints, the database lets you:
*
Specify an existing index that the database is to use to enforce the constraint
*
Specify a CREATE INDEX statement that the database is to use to create the index and enforce the constraint
These options are specified using the USING INDEX clause. The following statements present some examples.
Example 1:
CREATE TABLE a (
a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));
Example 2:
CREATE TABLE b(
b1 INT,
b2 INT,
CONSTRAINT bu1 UNIQUE (b1, b2)
USING INDEX (create unique index bi on b(b1, b2)),
CONSTRAINT bu2 UNIQUE (b2, b1) USING INDEX bi);
Example 3:
CREATE TABLE c(c1 INT, c2 INT);
CREATE INDEX ci ON c (c1, c2);
ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1) USING INDEX ci;
If a single statement creates an index with one constraint and also uses that index for another constraint, the system will attempt to rearrange the clauses to create the index before reusing it.
25
Monitoring Space Use of Indexes
If key values in an index are inserted, updated, and deleted frequently, the index can lose its acquired space efficiently over time. Monitor index efficiency of space usage at regular intervals by first analyzing the index structure, using the ANALYZE INDEX...VALIDATE STRUCTURE statement, and then querying the INDEX_STATS view:
SELECT PCT_USED FROM INDEX_STATS WHERE NAME = 'index';
The percentage of index space usage varies according to how often index keys are inserted, updated, or deleted. Develop a history of average efficiency of space usage for an index by performing the following sequence of operations several times:
*
Analyzing statistics
*
Validating the index
*
Checking PCT_USED
*
Dropping and rebuilding (or coalescing) the index
When you find that index space usage drops below its average, you can condense the index space by dropping the index and rebuilding it, or coalescing it.
26
Viewing Index Information
The following views display information about indexes:
View Description
DBA_INDEXES
ALL_INDEXES
USER_INDEXES
DBA view describes indexes on all tables in the database. ALL view describes indexes on all tables accessible to the user. USER view is restricted to indexes owned by the user. Some columns in these views contain statistics that are generated by the DBMS_STATS package or ANALYZE statement.
DBA_IND_COLUMNS
ALL_IND_COLUMNS
USER_IND_COLUMNS
These views describe the columns of indexes on tables. Some columns in these views contain statistics that are generated by the DBMS_STATS package or ANALYZE statement.
DBA_IND_EXPRESSIONS
ALL_IND_EXPRESSIONS
USER_IND_EXPRESSIONS
These views describe the expressions of function-based indexes on tables.
DBA_IND_STATISTICS
ALL_IND_STATISTICS
USER_IND_STATISTICS
These views contain optimizer statistics for indexes.
INDEX_STATS Stores information from the last ANALYZE INDEX...VALIDATE STRUCTURE statement.
INDEX_HISTOGRAM Stores information from the last ANALYZE INDEX...VALIDATE STRUCTURE statement.
V$OBJECT_USAGE Contains index usage information produced by the ALTER INDEX...MONITORING USAGE functionality.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-625735/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Partitioning a table online with DBMS_REDEFINITION
- 使用DBMS_REDEFINITION包執行線上重定義表(ONLINE TABLE REDEFINITION)
- Online Redefinition
- HOW TO SHRINK A TABLE USING ONLINE REDEFINITION (文件 ID 1357878.1)
- Master Note:Online Redefinition of Tables (DBMS_REDEFINITION)_1357825.1AST
- Shrink a SECUREFILE LOB Using Online Redefinition (DBMS_REDEFINITION)?1394613.1
- oracle online DocOracle
- online website Performance testingWebORM
- Online Redefinition線上重定義（一）
- Oracle Online Redefinition線上重定義（上）Oracle
- Oracle Online Redefinition線上重定義（中）Oracle
- Oracle Online Redefinition線上重定義（下）Oracle
- [doc]How To Efficiently Drop A Table With Many Extents
- 線上重定義表(Redefine Tables Online) - dbms_redefinition
- 11G online redefinition的幾個常用例項
- DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS的一點測試
- Online Redefinition線上重定義（二）--單表複雜案例
- 11G online redefinition的幾個常用例項(轉)
- Oracle10G RMAN Database Duplication (Doc ID 259694.1)OracleDatabase
- Oracle OCP 1Z0-053 Q145(Table Redefinition)Oracle
- Online Redefinition線上重定義（三）--多表關聯重定義案例
- oracle10g刪除Table的困惑Oracle
- MySQL oak-online-alter-table工具使用初探MySql
- Statistics related viewView
- Script: To remove Chained Rows from a Table (Doc ID 1019556.6)REMAI
- oracle 10g online rededination--dbms_redefinition使用小記_part1Oracle 10g
- 指令碼：線上重定義，從普通表到分割槽表，redefinition_table.sh指令碼
- Oracle10g New Feature -- 3.Flashback TableOracle
- Oracle10g 回收站及徹底刪除table : drop table xx purge 以及flashbackOracle
- Testing
- SD T-code related
- Good documentation or books related to OracleGoOracle
- Good Links related OracleGoOracle
- 線上改表工具oak-online-alter-table和pt-online-schema-change的使用限制總結
- best practice of rebuild your partition table local index online by using: "update indexes"RebuildIndex
- Oracle10g中FLASHBACK TABLE語句快速恢復表Oracle
- Oracle10g 回收站及徹底刪除table : drop table xx purge 以及drop flashOracle
- django ORM 中 select_related 與 prefetch_related 的使用場景DjangoORM