Partition

                                                            PARTITIONING

PARTITIONS :

        Dividing  the large tables into small tables known as partitions

• Partition of single table can go in two different tablespaces
• Sorting can be done by partitioning thus we not require temporary tablespace
• Partition can be independently managed
• Backup and recovery can be done on individual partitions
• Partition that may be unavailable (if one disk goes down . that does not stop the show)
• Partition can be moved from one tablespace to another tablespace
• Partition can be dropped, added, truncated at partition level
• Select ,insert, update, delete can be done at the partition level instead of table level
• Export and import can perform at partition level also
• Partitions are use for three reasons performance, availability, manageability

 

TYPES OF PARTITIONS :

• Range partition: in range partition we can mention some range like deptno from 1 to 10 in one partition, 11 to 20 in another partition.

 

• Hash partition: in hash partition we can mention department no wise like 10^th department in one partition and 20^th department in another partition.

 

• List partition: in list partition we mention character values like sales department in one partition, accounting department in another partition.

 

 

SPECIAL INDEXES

 

Types of indexes

• Auto index
• Manual index

 

Auto index : whenever we create primary key constraint or unique key constraint on top of that column system is going to create one index for that column. That is nothing but auto index.

Manual index : it is nothing but whatever the user is creating the index depending on the requirement.

 

Again in manual indexes there are six types of indexes are there.

• Btree index (default index)
• Reverse key index
• Function based index
• Decending index/composite index
• Bit map index
• Index organized table

 

Btree index : in btree index the values will be stored in tree structure format. If you don’t mention anything by default it will create btree index.

>create index ind1 on emp(empno);

Reverse key index : in reverse key index the values will be stored in tree structure format but the values will be in reverse.

7900   0097

Function based index : whenever we create an index with functions like arithmetical operators then we call it as function based index.

>create index fbind on emp(sal+comm);

>select * from emp where sal+comm>5000;

 

Descending index: descending index is also known as composite index. Whenever we create an index with more than one column then we call it as descending index.

>create index dscind on emp(sal,deptno desc);

>select * from emp where sal>5000 and deptno=10;

 

Bitmap index: when compare to all the indexes bitmap index will occupy very much low space in the database. The reason is bitmap index contains the Boolean values like eg: yes or no, true or false, male or female.

 

Index organized table : above five indexes will create after creating the table. But organized index we have to create while creating the table itself only. It must and should contain one primary key. In this the values will be stored in an organized manner link za web stranicu.

 

How to create btree index ?

U1> create index btindex on emp(empno);

 

How to create reverse key index?

U1> create index revindex on emp(deptno) reverse;

 

How to create function based index ?

U1>create index funindex on emp(sal+comm);

 

How to create descending index?

U1>create index descend on emp(sal,comm desc);

 

How to create bitmap index?

U1>create bitmap index btmp on dept(deptno);

 

How to create index organized table ?

U1> create table IOT (no number primary key, name varchar2(10)) organization index;

 

How to rebuild the index?

 Upto 9i

First drop the existing index again create the new index.

 From 10g

U1>alter index  <indexname> rebuild online;