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



0 Responses to “ORACLE SQL TUTORIAL: INDEX PART 3”

Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.