Logo 
Search:

Oracle Articles

Submit Article
Home » Articles » Oracle » IndexRSS Feeds

INDEXES

Posted By: Maddison Hughes     Category: Oracle     Views: 17004

This article explains about indexes in oracle.

WHAT HAPPENS WITHOUT INDEX

Query : SELECT * FROM Customers WHERE cname='ram';

- When a user fires a SELECT query,the DBMS first locates the table Customers on Hard-disk(using the system information).
- DBMS then performs a SEQUENTIAL-SEARCH on cname column.
- as the cname column values are not stored in Sorted order,DBMS has to search the entire column in the table.

WITH INDEX

- An index is an data-structure(2 col table) created by DBMS which contains the ordered list of the contents of a column of a table (on which index is created).
- indexing a table is an access strategy,a way to sort and search records in table.
- Indexes improve the speed with which records can be located and retrieved from a table.

- INDEX is a System-table containing 2 columns : 
column 1 : holds the sorted data of the column on which index is created.
column2 : ROWID,contains the physical address of the record in the table.

e.g.
Index created for Customers table.

cno   ROWID
-------------------------
1     00000240.0000.0004
2     00000240.0001.0004
3     00000241.0001.0004

select rowid from Customers;

DRAWBACK OF INDEXING

i)MORE MEMORY IS REQUIRED FOR creating index.
ii)DATA UPDATIONS ARE COSTLIER,LESS EFFICIENT.

ROWID format is BBBBBBB.RRRR.FFFF

DATA FILE --> DATA BLOCK --> RECORDS
FFFF = unique number of Data file in which the table is physically stored(Oracle stores date in data-files).
- Each data-file is divided into DATA-BLOCKS and each block is given a unique number.
BBBBBBB = is the block number in which the record is stored.
- Each data-block stores 1 or more RECORDS.Each record in a DATA-BLOCK is given a unique record number.
RRRR = is a unique record number in a DATA-BLOCK.

WHAT HAPPENS DURING INSERT IN A TABLE ?   

- for each inserted record in table,DBMS makes an entry in INDEX table.

WHY SPPEDIER SEARCH in INDEX?

- Since the data in index is sorted,the sequential search ends as soon as index data value is greater than the value searched.
- No need to search the entire indexed column.

CREATE INDEX

Syntax : 
CREATE INDEX <indexname> ON <tablename> (<columnname>);

SIMPLE INDEX 

index created on single column
CREATE INDEX idx_cname ON Customers(cname);

COMPOSITE INDEX 

Index created on muliple columns
CREATE INDEX idx_cnum_snum ON Orders(cnum,snum);

DROP INDEX <indexname>;

WHEN ORACLE USES INDEX FOR DATA EXTRACTION

i)SELECT statement with WHERE clause specified on the column on which INDEX exists.
ii)SELECT statement with ORDER BY clause specified on the column on which INDEX exists.

DRAWBACK OF INDEXES

- While indexes speeds up data-retrieval,data inserts,update,deletes are slowed down considerably
- Because for each insert,update,delete INDEX processing must be done.

USING ROWID TO DELETE DUPLICATE ROWS FROM A TABLE 

- ROWID is a pseudo-column of table which contains the physical address of each record.

CREATE TABLE Customer (
cnum  INTEGER ,
cname CHAR(10) NOT NULL
);

DROP TABLE Customers;
INSERT INTO Customer VALUES (1,'paresh');
INSERT INTO Customer VALUES (1,'paresh');
INSERT INTO Customer VALUES (1,'paresh');
INSERT INTO Customer VALUES (2,'aresh');
INSERT INTO Customer VALUES (11,'jayesh');

INNER SELECT statement 

- to create a record set of identical records from a table,the records must be grouped on all the columns in the table by using a GROUP BY clause in the SELECT statement.
- inner query selects min(rowid) FROM EACH GROUP OF IDENTICAL RECORDS.

DELETE FROM Customer WHERE rowid NOT IN
(SELECT min(rowid) FROM Customer GROUP BY cnum,cname); 

USING ROWNUM IN SQL STATEMENTS

- for each row returned by a query,ROWNUM pseduo column contains the order in which ORACLE selects the row from a table.

Query : Retrieve first 2 records from Customers table.

SELECT ROWNUM,Customers.* FROM Customers WHERE ROWNUM <3;

DECODE

SELECT DECODE(RTRIM(city),'bombay','mumbai',city) FROM Customers;
SELECT DECODE(RTRIM(city),'bombay','mumbai','ahmd','amdavad','no city') FROM Customers;
  
Share: 

 
 

Didn't find what you were looking for? Find more on INDEXES Or get search suggestion and latest updates.

Maddison Hughes
Maddison Hughes author of INDEXES is from London, United Kingdom.
 
View All Articles

 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
No Comment Found, Be the First to post comment!