Oracle8: Database Administration 15-23


.
Retrieving Information About Clusters
Note
If you need to use a join on three or more data dictionary views it may be
preferable to use temporary tables based on the views and then use the tables
in a join.
15-24 Oracle8: Database Administration


.
Lesson 15: Using Clusters and Index-Organized Tables
Index-Organized Tables
Storage Structure
An index-organized table keeps all data for a table within a B-tree structure.
The B-tree index, which is based on the primary key of the table, is
organized like an index. The leaf blocks in this structure contain the nonkey
columns, instead of the ROWID, as the second element of the index leaf
entry. Therefore, the use of an index-organized table eliminates the need to
have two separate segments, the table and the index.
Accessing an Index-Organized Table
Index access to a regular table requires that one or more index blocks are
read to retrieve the ROWID and the I/O on the table based on the ROWID. In
contrast, reading an index-organized table requires only index
block reads because the whole row is available in the leaf node.
An index-organized table can be accessed using either the primary key or a
combination of columns that constitute the leading part of the primary key.
Changes to the table data, such as adding new rows, updating rows, or
deleting rows, result only in updating the index.

15-15
Copyright  Oracle Corporation, 1998. All rights reserved.
Indexed access
on table
ROWID
Index-Organized Tables
Accessing index-
organized table
Non-key columns
Key column
Row header
Oracle8: Database Administration 15-25


.
Index-Organized Tables
An index-organized table differs from a regular table in the following
respects:
• ROWID can uniquely identify a row in a regular table, while the rows in
an index-organized table are identified by their primary key.
• A regular table can have many indexes that store the ROWID
corresponding to the index key values. Because an index-organized table
has no ROWID, secondary indexes cannot be created on this type of
table. Any SQL statement that attempts to retrieve the ROWID of an
index-organized table receives the following error:
ORA-02031: no ROWID for fixed tables or for index-organized
tables
• When a full table scan is made on a regular table, the order in which
rows are returned is unpredictable. Full scans on an index-organized
table return rows in a primary key sequence.

• An index-organized table supports all constraints except unique
constraints.
• An index-organized table cannot participate in a distributed transaction.
It cannot be partitioned or replicated.
15-16
Copyright  Oracle Corporation, 1998. All rights reserved.
Index-Organized Table
Identified by primary key
No ROWID
No secondary indexes
Full index scans return
rows in PK order
No support for
unique constraints
Distribution, replication, and
partitioning not supported

Index-Organized Tables
Compared with Regular Tables
Regular Table
Unique identifier—ROWID
ROWID implicit
Supports several indexes
FTS returns rows in no specific
order
Unique constraints allowed
Distribution, replication, and
partitioning supported
15-26 Oracle8: Database Administration



.
Lesson 15: Using Clusters and Index-Organized Tables
Using Index-Organized Tables
An index-organized table is useful for:
• Information retrieval applications
• Spatial applications
• Online analytical processing (OLAP) applications
Index organization is useful for a table that is frequently accessed using the
primary key and has only a few, relatively short nonkey columns.
Syntax
Use the following command to define an index-organized table:
CREATE TABLE [ schema. ] table
(column-definition [, column-definition ]
[, out-of-line-constraint [, out-of-line-constraint ] ] )
ORGANIZATION INDEX
[ PCTFREE integer ]
[ INITRANS integer ]
[ MAXTRANS integer ]
[ storage-clause ]
[ TABLESPACE tablespace ]
[ PCTTHRESHOLD integer
[ INCLUDING column ] ]
[ OVERFLOW segment_attributes_clause ]
15-17
Copyright  Oracle Corporation, 1998. All rights reserved.
Creating Index-Organized Tables
CREATE TABLE scott.sales
( office_cd NUMBER(3),
qtr_end DATE,

revenue NUMBER(10,2),
review VARCHAR2(1000),
CONSTRAINT sales_pk
PRIMARY KEY(office_code, qtr_end))
ORGANIZATION INDEX TABLESPACE data01
PCTTHRESHOLD 20
OVERFLOW TABLESPACE data02;
Oracle8: Database Administration 15-27


.
Using Index-Organized Tables
where: schema is the owner of the cluster
table is the name of the table
ORGANIZATION INDEX
specifies that it is an index-organized table
PCTTHRESHOLD specifies the percentage of space reserved
in the index block for an index-organized
table row (If a row exceeds the size
calculated based on this value, all columns
after the column named in the
INCLUDING clause are moved to the
overflow segment. If OVERFLOW is not
specified, then rows exceeding the
threshold are rejected. PCTTHRESHOLD
defaults to 50 and must be a value from 0
to 50.)
INCLUDING column
specifies a column at which to divide an
index-organized table row into index and

overflow portions (All columns that follow
column are stored in the overflow data
segment. If this is not specified and a row
size exceeds PCTTHRESHOLD, all
columns except the primary key columns
will be moved to the overflow area. The
column is either the name of the last
primarykeycolumnoranynonprimarykey
column.)
OVERFLOW specifies that index-organized table data
rows exceeding the specified threshold are
placed in the data segment defined by
segment_attributes_clause, which
specifies the tablespace, storage, and block
utilization parameters
15-28 Oracle8: Database Administration


.
Lesson 15: Using Clusters and Index-Organized Tables
Restrictions
A primary key must be specified for an index-organized table. If an attempt
is made to create an index-organized table without the primary key, the
following error is generated:
ORA-25175: no PRIMARY KEY constraint found
If PCTTHRESHOLD is defined and an overflow segment is not specified,
rows exceeding the threshold are rejected with the following error:
ORA-01429: Index-Organized Table: no data segment to store
overflow row-pieces
Oracle8: Database Administration 15-29



.
Using Index-Organized Tables
A large row in an index-organized table might destroy the dense storage of
rows in the index. This problem is overcome by the use of an overflow area.
Segments Created for an Index-Organized Table
When an index-organized table is created specifying the OVERFLOW
clause, the following are created:
• A “logical” table with the name defined in the CREATE TABLE clause.
Because all the rows are stored in the index, there is no segment that
corresponds to this table.
• An index with the same name as the primary key constraint, in the
tablespace defined in the CREATE TABLE statement, using the storage
and block utilization parameters specified.
• A table to accommodate the overflow row pieces. The name of this table
is SYS_IOT_OVER_n, where n is the OBJECT_ID of the index-
organized table as seen from DBA_OBJECTS.
15-18
Copyright  Oracle Corporation, 1998. All rights reserved.
Row Overflow
Segment = PK Name
Type = Index
Segment = SYS_IOT_OVER_n
Type=Table
Row bigger than
PCTTHRESHOLD
Block
Rows within PCTTHRESHOLD
IOT tablespace

Overflow tablespace
15-30 Oracle8: Database Administration


.
Lesson 15: Using Clusters and Index-Organized Tables
Index-Organized Table Operations
An index-organized table can be used like any other table. All the commands, such
as ALTER TABLE, ANALYZE TABLE, TRUNCATE TABLE, and DROP TABLE,
and all the DML commands, are supported against an index-organized table.
Oracle8: Database Administration 15-31


.
Retrieving Information About Index-Organized Tables
Retrieving Information About Index-Organized Tables
Use the following query to list the index-organized tables and information
related to their structure:
SVRMGR> SELECT t.table_name AS "IOT", o.table_name AS
"Overflow",
2> i.index_name AS "Index",
3> o.tablespace_name AS "Overflow TS",
4> i.tablespace_name AS "Index TS", i.pct_threshold
5> FROM dba_tables t, dba_tables o, dba_indexes i
6> WHERE t.owner=o.owner
7> AND t.table_name = o.iot_name
8> AND t.owner = i.owner
9> AND t.table_name = i.table_name
10> AND t.owner= 'SCOTT';
IOT


Overflow

Index

Overflow TS

Index
TS

PCT_THRESHOLD

SALES SYS_IOT_OVER_2049 SALES_PK DATA02 DATA01 20
15-19
Copyright  Oracle Corporation, 1998. All rights reserved.
Retrieving IOT Information from
Data Dictionary
DBA_INDEXES
OWNER
TABLE_NAME
INDEX_NAME
INDEX_TYPE
PCT_THRESHOLD
INCLUDE_COLUMN
DBA_TABLES
OWNER
TABLE_NAME
IOT_TYPE
IOT_NAME
TABLESPACE_NAME

15-32 Oracle8: Database Administration


.
Lesson 15: Using Clusters and Index-Organized Tables
Summary
15-20
Copyright  Oracle Corporation, 1998. All rights reserved.
Summary
• Identifying situations where clusters are
useful
• Using index-organized tables
Oracle8: Database Administration 15-33


.
Summary
Quick Reference
Context Reference
Initialization parameters None
Dynamic performance views None
Data dictionary views DBA_CLUSTERS
DBA_TAB_COLUMNS
DBA_CLU_COLUMNS
DBA_CLUSTER_HASH_EXPRESSIONS
DBA_TABLES
DBA_INDEXES
Commands CREATE CLUSTER
CREATE INDEX ON CLUSTER
CREATE CLUSTER HASHKEYS HASH IS

ALTER CLUSTER
TRUNCATE CLUSTER
DROP CLUSTER
CREATE TABLE ORGANIZATION INDEX
15-34 Oracle8: Database Administration


.
Lesson 15: Using Clusters and Index-Organized Tables

16
Loading and
Reorganizing Data
16-2 Oracle8: Database Administration


.
Lesson 16: Loading and Reorganizing Data
Instructor Note
Topic Timing
Lecture 75 minutes
Practice 45 minutes
Total 120 minutes
Oracle8: Database Administration 16-3


.
Objectives
Objectives
16-2

Copyright  Oracle Corporation, 1998. All rights reserved.
Objectives
• Loading data using direct-load insert
• Loading data into Oracle tables using
SQL*Loader conventional and direct
paths
• Reorganizing data using export and
import
16-4 Oracle8: Database Administration


.
Lesson 16: Loading and Reorganizing Data
Overview
Several methods are available for loading data into tables in an Oracle
database. The methods that will be discussed here are:
• Direct-load insert
• SQL*Loader
• Export and Import utilities
Direct-Load Insert
Direct-load inserts can be used to copy data from one table to another table
within the same database. It speeds up the insert operation, by passing the
buffer cache, and writing data directly into the data files.
SQL*Loader
SQL*Loader is a utility that is used to load data from external files into
Oracle tables. It provides a means of migration from other systems to
Oracle.
16-3
Copyright  Oracle Corporation, 1998. All rights reserved.
SQL*Loader

Other
applications
Overview
Oracle
database
Oracle
database
Export
Import
Direct-load insert
Oracle8: Database Administration 16-5


.
Overview
Export and Import Utilities
Export utility enables users to extract dictionary information and data from
an Oracle database and move them into an operating system file in
Oracle-binary format. The files generated by export can be read by the
Import utility into the same Oracle database or a different database. The
Export and Import utilities have several uses that will be discussed in a
subsequent section in this lesson.
16-6 Oracle8: Database Administration


.
Lesson 16: Loading and Reorganizing Data
Loading Data Using Direct-Load Insert
Syntax
Direct-load insert can be invoked by using the APPEND hint as shown in the

command below:
INSERT /*+APPEND */ INTO [ schema. ] table
[ [NO]LOGGING ]
sub-query;
where: schema is the owner of the table
table is the name of the table
sub-query is the subquery used to select the required
columns and rows for insert
Direct-Load inserts are only available when the INSERT INTO SELECT
command is used. This option is not available when INSERT INTO
VALUES command is used. The direct-load insert can be used both on
nonpartitioned and partitioned tables. This operation maintains indexes and
also enforces all enabled constraints. It also enables other users to
concurrently modify other rows in the table.
16-4
Copyright  Oracle Corporation, 1998. All rights reserved.
Free space after delete
Blocks used by inserted rows
High water mark
Used block
Using Direct-Load Inserts
EMP table
INSERT /*+APPEND */ INTO scott.emp
NOLOGGING
SELECT * FROM scott.old_emp;
Server
process
Oracle8: Database Administration 16-7



.
Loading Data Using Direct-Load Insert
Logging Mode
When inserting using the LOGGING option, which is the default, the
operation generates redo log entries, making complete recovery possible in
case of failures.
If the NOLOGGING option is used, changes to data are not recorded in the
redo log buffer. Some minimal logging is still carried out, for operations
such as extent allocation. The NOLOGGING mode is used if this attribute
has been set for the table.
If several online modifications to the data in the table are likely to occur
subsequently, it might be advisable to set the NOLOGGING attribute before
the load and reset it to LOGGING once the load completes.
Other Considerations
Direct-load inserts allow other transactions to concurrently make changes to
the table.
All data inserted using this method is loaded above the high water mark. If
the table contains many blocks where rows have been deleted, space may be
wasted, and full table scans may be slower.
Instructor Note
You may want to inform the participants that hints are suggestions that
they give the optimizer for optimizing a SQL statements. They are always
specified after the first keyword in a statement block, preceded by a “+”
sign, delimited by “/*” and “*/.” You may want to refer interested
participants to the lesson “Optimization Modes and Hints” in the manual
Oracle8 Server Tuning.
16-8 Oracle8: Database Administration


.

Lesson 16: Loading and Reorganizing Data
Direct-load inserts can be made in parallel using one of the following
methods:
• Using a PARALLEL hint in the INSERT statement as in the example
• Creating the table or altering it to specify the PARALLEL clause
When parallel direct-load inserts are made, the Oracle server uses several
processes, known as parallel query slaves, to insert data into the table.
Temporary segments are allocated to store the data inserted by each slave
process. When the transaction commits, the extents in these individual
segments become a part of the table in which records are inserted.
Note
• The ALTER SESSION ENABLE PARALLEL DML command must be
executed at the beginning of a transaction.
• An object that is modified using parallel direct-load insert cannot be
queried or modified again within the same transaction.
• For a detailed discussion of parallel direct-load inserts, see the chapter
“Parallel Execution” in Oracle8 Server Reference manual.
Instructor Note
You can demonstrate the use of parallel direct-load insert using the scripts
dli_set.sql and dli_par.sql.
16-5
Copyright  Oracle Corporation, 1998. All rights reserved.
Free space after delete
Temporary segments
High water mark
Used block
Parallel Direct-Load Insert
EMP table
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+PARALLEL(scott.emp,2) */

INTO scott.emp NOLOGGING
SELECT * FROM scott.old_emp;
Slave
process
Slave
process
Oracle8: Database Administration 16-9


.
Loading Data Using SQL*Loader
Loading Data Using SQL*Loader
SQL*Loader loads data from external files into tables in an Oracle database.
SQL*Loader Features
SQL*Loader has the following features:
• One or more input files can be used.
• Several input records can be combined into one logical record for
loading.
• Input fields can be fixed or variable length.
• Input data can be in any format—character, binary, packed decimal, date,
and zoned decimal.
• Data can be loaded from different types of media such as disk, tape, or
named pipes.
• Data can be loaded into several tables in one run.
• Options are available to either replace or to append to existing data in the
tables.
• SQL functions can be applied on the input data before the row is stored
in the database.
16-6
Copyright  Oracle Corporation, 1998. All rights reserved.

Database files
Data files
Control file
SQL*Loader
Log file
Inserted
Oracle server
Selected
SQL*Loader
Parameter file
(optional)
Rejected
Field processing
Bad
file
Rejected
Record selection
Accepted
Discard file
(optional)
Discarded
16-10 Oracle8: Database Administration


.
Lesson 16: Loading and Reorganizing Data
• Column values can be autogenerated based on rules. For example, a
sequential key value can be generated and stored in a column.
• Data can be loaded directly into the table, bypassing the database buffer
cache.

Files Used by SQL*Loader
SQL*Loader uses the following files:
• Control file: specifies the input format, output tables, and optional
conditions that may be used to load only part of the records found in the
input data files
• Data files: contain the data in the format defined in the control file
• Parameter file: is an optional file that can be used to define the command
line parameters for the load
• Bad file: is used by the utility to write the records that are rejected during
the load (This can occur during input record validation by the utility or
during record insertion by the Oracle server.)
• Log file: is created by SQL*Loader and contains a record of the load
• Discard file: is a file that can be created, if necessary, to store all records
that did not satisfy the selection criteria
Oracle8: Database Administration 16-11


.
Loading Data Using SQL*Loader
SQL*Loader provides two methods for loading data:
• Conventional path
• Direct path
Conventional Path Load
Conventional path load builds an array of rows to be inserted and uses the
SQL INSERT statement to load the data. During conventional path loads,
input records are parsed based on field specifications, and an array of records
is built and inserted into the table specified in the control file. Records that
do not conform to the field specifications are rejected and those records that
do not satisfy the selection criteria are discarded.
Conventional path loads can be used to load data into both clustered and

unclustered tables. Redo log generation is controlled by the logging attribute
for the table being loaded.
16-7
Copyright  Oracle Corporation, 1998. All rights reserved.
Conventional and Direct Loads
Conventional
Direct
path
Array
insert
Extent
management
Data
save
Table
High water
mark
Space used only by conventional load
Instance
SGA
Shared pool
16-12 Oracle8: Database Administration


.
Lesson 16: Loading and Reorganizing Data
Direct Path Load
A direct path load builds blocks of data in memory and saves these blocks
directly into the extents allocated for the table being loaded. Redo log entries
are not generated unless the database is in an archiving mode. Direct path

loads use the field specifications to build whole Oracle blocks of data, and
write the blocks directly to Oracle data files. It bypasses the database buffer
cache and accesses the SGA only for extent management and adjustment of
the high water mark.
Direct path load is generally faster than conventional path load, but cannot
be used in all situations. The next section compares conventional path to
direct path loading, and gives examples of situations in which each of them
can be used.
Note
The script catldr.sql, supplied by Oracle, creates views that are used by
direct path load. It is automatically invoked when the script catalog.sql is
run.
Oracle8: Database Administration 16-13


.
Loading Data Using SQL*Loader
Method of Saving Data
Conventional path loads use SQL processing and database COMMITS for
saving data. The insertion of an array of records is followed by a commit
operation. Each data load might involve several transactions.
Direct path loads use data saves to write blocks of data to Oracle data files.
The following features differentiate a data save from a commit:
• During a data save, only full database blocks are written to the database.
• They are written after the high water mark of the table, and then after the
data save the high water mark is moved.
• Internal resources are not released after a data save.
• A data save does not end the transaction.
• Indexes are not updated at each data save.
Logging of Changes

Conventional path loading generates redo log entries just as any DML
statement. When using a direct path, load redo log entries are not generated
if:
• Database is in NOARCHIVELOG mode
• Database is in ARCHIVELOG mode, but logging is disabled (Logging
can be disabled by setting the NOLOG attribute for the table or by using
the UNRECOVERABLE clause in the control file.)
16-8
Copyright  Oracle Corporation, 1998. All rights reserved.
Comparing Direct and
Conventional Path Loads
Conventional Load
Uses COMMITs to make
changes permanent
Redo log entries always
generated
Enforces all constraints
INSERT triggers fire
Can load into clustered
tables
Other users can make
changes to tables
Direct Path Load
Uses
data saves
Generates redo only under
specific conditions
Enforces only primary key,
unique, and NOT NULL
INSERT triggers do not fire

Cannot load into
clustered tables
Other users cannot
make changes to tables