In Oracle : Oracle Index: Index usage in Oracle

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

Oracle DBA

Real Application Cluster (RAC) Maintenance tasks Backup and Recovery Database Architecture Replication Oracle Performance Tuning Oracle DBA - Other articles Oracle scripts/ Selects for DBA Oracle Errors (ORA-nnnnn)

The last articles in the site (HOT)

The most visited articles in the site

In-Oracle.com  -> Oracle DBA -> Oracle Maintenance Tasks

-> Oracle Index: Index usage in Oracle

 

 Oracle Index

 

 

Here are the main topics for this article:

Create a database table

 

                CREATE TABLE EMP

                                   ( EMPNO NUMBER(4),

                                     ENAME VARCHAR2(10 BYTE),

                                     JOB VARCHAR2(9 BYTE),

                                     MGR NUMBER(4),

                                     HIREDATE DATE,

                                     SAL NUMBER(7,2),

                                     COMM NUMBER(7,2),

                                     DEPTNO NUMBER(2) )

                TABLESPACE USERS

                PCTUSED 0

                PCTFREE 10

                INITRANS 1

                MAXTRANS 255

                STORAGE (

                           INITIAL 64K

                           MINEXTENTS 1

                           MAXEXTENTS 2147483645

                           PCTINCREASE 0

                           BUFFER_POOL DEFAULT )

                LOGGING

                NOCOMPRESS

                NOCACHE;

 


                NOTE: The Automatic Segment Space Tablespace Management (ASSM) is new in Oracle9i and is implemented by adding the

                SEGMENT SPACE MANAGEMENT AUTO clause to the tablespace definition. ASM tablespaces automate freelist management and

                remove the ability to specify PCTFREE, PCTUSED, FREELISTS, and FREELIST GROUPS storage parameters.

 

 

Add a column to an existing table

 

ALTER TABLE EMP add (new_col varchar2(23));

 

 

Drop a column from an existing table

 

ALTER TABLE EMP drop column new_col2;    (drop one column)

ALTER TABLE EMP drop (new_col1, new_col2);   (drop many columns)

 

When you drop a table column the column space remains used inside the data blocks and you may want to reorganize the table (using the imp/exp utility) to reclaim the free spaced from the dropped table column.

 

 

Drop a table

 

            DROP TABLE EMP;

            DROP TABLE EMP cascade constraints;    (Deletes all foreign keys that reference the table to be dropped, then drops the table.)

            DROP TABLE EMP purge  (10g: Normally, a table is moved into the recycle bin, if it is dropped. Using "purge" option the table is not

                                                        moved in recycle bin.)

 

 

Add an index to a table

 

                CREATE UNIQUE INDEX PK_EMP ON EMP  (EMPNO)

                LOGGING

                TABLESPACE USERS

                PCTFREE 10

                INITRANS 2

                MAXTRANS 255

                STORAGE (

                           INITIAL 64K

                           MINEXTENTS 1

                           MAXEXTENTS 2147483645

                           PCTINCREASE 0

                           BUFFER_POOL DEFAULT);

 

            This command creates an unique index on "empno" column on the EMP table and the index data will be stored in USERS tablespace. 

 

 

Index Types

 

The 3 most used indexes are:

 

1)  B-Tree index (the standard index): Used when retrieving a small amount of data from a very large table.

Ex.:

                              CREATE UNIQUE INDEX PK_EMP ON EMP  (EMPNO)

                              LOGGING

                              TABLESPACE USERS

                              PCTFREE 10

                              INITRANS 2

                              MAXTRANS 255

                              STORAGE (

                                          INITIAL 64K

                                          MINEXTENTS 1

                                          MAXEXTENTS 2147483645

                                          PCTINCREASE 0

                                          BUFFER_POOL DEFAULT);

 

2)  Bitmap index: Used on column with 2-4 distinct values on tables which are infrequently updated. This kind of index is very used

                             in data warehouse where are no many updated on the tables. 

                 Ex.:

                       CREATE BITMAP INDEX SCOTT.IDX1 ON SCOTT.EMP(SEX)

                       TABLESPACE USERS

 

3)  Function Based Index: Used on table, where often there is a particular function which use a particular column.

Ex.:

  SELECT ename, empno, sal FROM emp WHERE upper(ename) = 'KING';

 

For this statement the following index will be appropriate:

   

                      CREATE INDEX SCOTT.IND2

                      ON SCOTT.EMP (ENAME, UPPER)

                      TABLESPACE USERS

 

 

Rebuild an index

 

If a table has more than 20% of rows deleted for a better performance the index must be rebuild. More information on this could be found on Metalink notes 122008.1 & 77574.1.

 

            Ex:   ALTER INDEX SCOTT.PK_EMP  REBUILD;   (during the execution  of this statement the INDEX is NOT USED)

                           ALTER INDEX SCOTT.PK_EMP REBUILD ONLINE; (during the execution  of this statement the old INDEX IS USED)

 

 

Drop an index

 

DROP INDEX SCOTT.DEPTNO_IDX;

 

 

In-Oracle.com  -> Oracle DBA -> Oracle Maintenance Tasks

-> Oracle Index: Index usage in Oracle

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

 

     Copyright (c) 2011-2015  www.in-oracle.com  |  Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.