INDEXES

INDEXES

B*Tree index

sql>create index indx_name on table_name (col_name);

Unique index

sql>create unique index ind_name on table_name (col_name);

Composite index

sql>create index ind_name on table_name (col_name,col_name);

Bitmap index

sql>create bitmap index ind_name on table_name (gender);

Functional index

sql>create index ind_name on table_name(upper(col_name));

Reverse Key index

sql>create index ind_name on table_name(col_name) reverse;

IOT (Index Organized Table)

sql>create table table_name (col_name datatype primary key,col_name datatype)

 organization index;

Materialized views: Materialized view store a local copy of the base table records where as normal tables fetch records from the base tables;

SQL> create materialized view mv_emp as select rollno,name from student;  

// materialized view created

SQL> execute DBMS_MVIEW.REFRESH (‘mv_emp’,’c’);

 // refreshing the materialized view

NOTE: Materialized view can also be automatically refreshed by using the START WITh <date> NEXT <date>.

SQL> create materialized view mv1_emp refresh complete start with sysdate next sysdate+1 as select rollno,name,age from student;

// view will be created and refreshed right now (sysdate) and there after once every day at the same time (sysdate+1).

SQL> drop materialized view mv_emp;

 // materialized view dropped SQL> drop view mv_emp; // view dropped

Creating Partition Table:

Range Partition :

sql>create table emp(empno number(10),ename varchar2(10),sal number(10))

  partition by range(empno)

(

partition e1 values less than (100) tablespace tab1,

 partition e2 values less than (200) tablespace tab2,

partition e3 values less than (300) tablespace tab3,

partition e4 values less than (MAXVALUE) tablespace tab4);

Hash Partition

Sql>create table emp(empno number(10),ename varchar2(10),sal number(10))

Partition by hash(empno)

(

Partition p1 tablespace tab1,

Partition p2 tablespace tab2

);  

List Partition

Sql>create table sales_by_region(deptno number(10),deptname varchar2(20),state varchar2(2))

Partition by list(state)

(

Partition north values(‘DL’,’JP’) tablespace tbs1,

Partition south values(‘HY’,’PU’) tablespace tbs2

);

Imp views

dba_tab_partitions

user_tab_partitions

dba_indexes

index_stats

user_indexes