Transcript Document

Unit 6 Seminar
Indexed Organized Tables
• Definition: Index Organized Tables are tables
that, unlike heap tables, are organized like
B*Tree indexes.
IOT
• An IOT is similar to a regular table with an
index on one or more of its columns. Instead
of two separate segments for the table and
the b-tree index, an index-organized table
maintains a single b-tree index that contains
both the primary key value and the other
column values for the corresponding rows.
• Index-organized tables require less storage
because there is no duplication of the values
for the primary key column. A regular table
and index would both contain the primary key
value.
• An index-organized table stores the primary
key only once and uses it to organize the
table.
• Index-organized tables also provide faster keybased access for queries involving exact match
or range scans involving the key column.
• Index-organized tables work well with spatial
applications that perform spatial queries for
objects residing in a collection of grids. An
example of this is a geographic information
system (GIS).
• Index-organized tables also work well with
Online Analytical Processing (OLAP)
applications that manipulate
multidimensional blocks. Examples of this
class of applications include sales forecasting
and modeling, what-if analysis, and
comparative queries.
• To create an index-organized table, use the
CREATE TABLE command with the
ORGANIZATION INDEX clause. This command
specifies an index-organized table.
IOT Example
SQL> CREATE TABLE dept_iot(
deptno NUMBER(3),
dname VARCHAR2(10),
ddesc CHAR,
emp_sf NUMBER(4),
emp_mg NUMBER(4),
CONSTRAINT dept_iot_pk PRIMARY KEY (deptno))
ORGANIZATION index
INCLUDING ddesc
OVERFLOW TABLESPACE users;
Notes
• The INCLUDING clause specifies a column
where an index-organized table row is divided
into index and overflow portions. In this
example, the columns EMP_SF and EMP_MG
will be stored in the overflow area.
• The OVERFLOW clause specifies that indexorganized table data rows exceeding the
specified threshold have their overflow
portion placed in an overflow data segment.
Overview of ROWID and UROWID Datatypes
• Oracle Database uses a ROWID datatype to
store the address (rowid) of every row in the
database.
• Physical rowids store the addresses of rows in
ordinary tables (excluding index-organized
tables), clustered tables, table partitions and
subpartitions, indexes, and index partitions
and subpartitions.
• Logical rowids store the addresses of rows in
index-organized tables.
• A single datatype called the universal rowid,
or UROWID, supports both logical and physical
rowids, as well as rowids of foreign tables such
as non-Oracle tables accessed through a
gateway.
Temporary table
• A Temporary table is a special type of table in
the Oracle database used for storing
intermediate temporary data.
• Developers often use temporary tables to
store a dataset that will be used in several
queries, under the apparently reasonable
assumption that performing the hard work
only once will benefit performance.
Partitioned Tables
• Partitioning addresses key issues in
supporting very large tables and indexes by
letting you decompose them into smaller and
more manageable pieces called partitions.
• SQL queries and DML statements do not need
to be modified in order to access partitioned
tables.
• After partitions are defined, DDL statements
can access and manipulate individuals
partitions rather than entire tables or indexes.
This is how partitioning can simplify the
manageability of large database objects. Also,
partitioning is entirely transparent to
applications.
• Each partition of a table or index must have
the same logical attributes, such as column
names, datatypes, and constraints, but each
partition can have separate physical attributes
such as pctfree, pctused, and tablespaces.
Types of Partitioning
• Oracle provides the following partitioning
methods:
• Range Partitioning
• List Partitioning
• Hash Partitioning
• Composite Partitioning
Range Partition Example
CREATE TABLE sales_range (salesman_id NUMBER(5),
salesman_name VARCHAR2(30), sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY RANGE(sales_date)
(
PARTITION sales_jan2000 VALUES LESS
THAN(TO_DATE('02/01/2000','DD/MM/YYYY')),
PARTITION sales_feb2000 VALUES LESS
THAN(TO_DATE('03/01/2000','DD/MM/YYYY')),
PARTITION sales_mar2000 VALUES LESS
THAN(TO_DATE('04/01/2000','DD/MM/YYYY')),
PARTITION sales_apr2000 VALUES LESS
THAN(TO_DATE('05/01/2000','DD/MM/YYYY')) );
List Partition Example
CREATE TABLE sales_list (salesman_id NUMBER(5),
salesman_name VARCHAR2(30), sales_state
VARCHAR2(20), sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY LIST(sales_state)
(
PARTITION sales_west VALUES('California', 'Hawaii'),
PARTITION sales_east VALUES ('New York', 'Virginia',
'Florida'),
PARTITION sales_central VALUES('Texas', 'Illinois')
PARTITION sales_other VALUES(DEFAULT) );
Migrating a Row
• If an UPDATE statement increases the amount
of data in a row so that the row no longer fits
in its data block, Oracle tries to find another
block with enough free space to hold the
entire row. If such a block is available, Oracle
moves the entire row to the new block.
Chaining a Row
• If the row is too large to fit into any available
block, Oracle splits the row into multiple
pieces and stores each piece in a separate
block.
• You can identify migrated and chained rows in
a table or cluster by using the ANALYZE
command with the LIST CHAINED ROWS
option.
• The definition of a sample output table named
CHAINED_ROWS appears in a SQL script
available on your distribution medium. The
common name of this script is UTLCHAIN.SQL
Rule Based Optimizer vs. Cost Based
• The cost-based optimizer chooses the best
path for your queries, based on what it knows
about your data and by leveraging Oracle
database features such as bitmap indexes,
function-based indexes, hash joins, indexorganized tables, and partitioning.
• RBO just follows established rules (heuristics).
The functionality is still present but no new
functionality has been included in it and it is
no longer supported by Oracle. It is only
present to provide backwards compatibility
during the migration to the query optimizer
(Cost Based Optimizer). The results of this
osolescence are as follows.
• With the release of Oracle Database 10g, the
RBO's obsolescence is official.