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;