Friday, October 28, 2011
ORACLE SQL TUTORIAL: INDEX PART 3
CONTIUATION....
INDEX-ORGANIZED TABLE
An index-organized table keeps its data sorted according to the
primary key column values for the table. Index-organized tables store their
data as if the entire table was stored in an index.
An index-organized table allows you to store the entire table’s
data in an index.
Ex:
SQL> create table student (sno number(2),sname
varchar(10),smarks number(3) constraint
pk
primary key(sno) organization index;
PARTITION INDEX
Similar to partitioning tables, oracle allows you to partition
indexes too. Like table partitions,
index partitions could be in different tablespaces.
LOCAL INDEXES
Ø Local keyword tells oracle to create a separte index for each
partition.
Ø In the local prefixed index the partition key is specified on the
left prefix. When the underlying table is partitioned baes on, say two columns
then the index can be prefixed on the first column specified.
Ø Local prefixed indexes can be unique or non unique.
Ø Local indexes may be easier to manage than global indexes.
Ex:
SQL> create index stud_index on student(sno) local;
GLOBAL INDEXES
Ø A global index may contain values from multiple partitions.
Ø An index is global prefixed if it is partitioned on the left
prefix of the index columns.
Ø The global clause allows you to create a non-partitioned index.
Ø Global indexes may perform uniqueness checks faster than local
(partitioned) indexes.
Ø You cannot create global indexes for hash partitions or
subpartitions.
Ex:
SQL> create index stud_index on student(sno) global;
Similar to table partitions, it is possible to move them from one
device to another. But unlike table partitions, movement of index partitions
requires individual reconstruction of the index or each partition (only in the
case of global index).
Ex:
SQL> alter
index stud_ind rebuild partition p2
Ø Index partitions cannot be dropped manually.
Ø They are dropped implicitly when the data they refer to is dropped
from the partitioned table.
MONITORING USE OF INDEXES
Once you turned on the monitoring the use of indexes, then we can
check whether the table is hitting the index or not.
To monitor the use of index use the follwing syntax.
Syntax:
alter index index_name
monitoring usage;
then check for the details in V$OBJECT_USAGE view.
If you want to stop monitoring use the following.
Syntax:
alter index index_name
nomonitoring usage;
DATA MODEL
Ø ALL_INDEXES
Ø DBA_INDEXES
Ø USER_INDEXES
Ø ALL_IND-COLUMNS
Ø DBA-IND_COLUMNS
Ø USER_IND_COLUMNS
Ø ALL_PART_INDEXES
Ø DBA_PART_INDEXES
Ø USER_PART_INDEXES
Ø V$OBJECT_USAGE
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
0 Responses to “ORACLE SQL TUTORIAL: INDEX PART 3”
Post a Comment