Tutorials

Indexes in Oracle


Overview of Indexes :
An index is an optional structure, associated with a table or table cluster, that can sometimes speed data access. By creating an index on one or more columns of a table, you gain the ability in some cases to retrieve a small set of randomly distributed rows from the table. Indexes are one of many means of reducing disk I/O.

If a heap-organized table has no indexes, then the database must perform a full table scan to find a value. For example, without an index, a query of location 2700 in the hr.departments table requires the database to search every row in every table block for this value. This approach does not scale well as data volumes increase.

For example, suppose an HR manager has a shelf of cardboard boxes. Folders containing employee information are inserted randomly in the boxes. The folder for employee Whalen (ID 200) is 10 folders up from the bottom of box 1, whereas the folder for King (ID 100) is at the bottom of box 3. To locate a folder, the manager looks at every folder in box 1 from bottom to top and then moves from box to box until the folder is found. To speed access, the manager could create an index that sequentially lists every employee ID with its folder location:

ID 100: Box 3, position 1 (bottom)
ID 101: Box 7, position 8 
ID 200: Box 1, position 10

Consider creating an index on a column in any of the following situations:

The indexed columns are queried frequently and return a small percentage of the total number of rows in the table.

A referential integrity constraint exists on the indexed column or columns. The index is a means to avoid a full table lock that would otherwise be required if you update the parent table primary key, merge into the parent table, or delete from the parent table.

A unique key constraint will be placed on the table and you want to manually specify the index and all index options.


Index Characteristics :

Indexes are schema objects that are logically and physically independent of the data in the objects with which they are associated. Thus, an index can be dropped or created without physically affecting the table for the index.

An index is a fast access path to a single row of data. It affects only the speed of execution. Given a data value that has been indexed, the index points directly to the location of the rows containing that value.

The database automatically maintains and uses indexes after they are created. The database also automatically reflects changes to data, such as adding, updating, and deleting rows, in all relevant indexes with no additional actions required by users. Retrieval performance of indexed data remains almost constant, even as rows are inserted. However, the presence of many indexes on a table degrades DML performance because the database must also update the indexes.


Indexes properties:

Usability

Indexes are usable (default) or unusable. An unusable index is not maintained by DML operations and is ignored by the optimizer. 

An unusable index can improve the performance of bulk loads. 

Instead of dropping an index and later re-creating it, you can make the index unusable and then rebuild it. Unusable indexes and index partitions do not consume space. When you make a usable index unusable, the database drops its index segment.

Visibility

Indexes are visible (default) or invisible. An invisible index is maintained by DML operations and is not used by default by the optimizer. 

Making an index invisible is an alternative to making it unusable or dropping it. Invisible indexes are especially useful for testing the removal of an index before dropping it or using indexes temporarily without affecting the overall application.

Procedures in Oracle PL/SQL


PROCEDURE

A named database object which executes a compiled block of PL/SQL code on a call.

A procedure cannot be called from an SQL statement.
A procedure cannot be included in the right-hand operand in an assignment statement.
IN parameter: Pass by reference, OUTand IN OUT: Pass by value.
A procedure will never have a RETURN statement in its signature, though it can have it in its body, which would terminate the execution flow.

CREATE [OR REPLACE] PROCEDURE procedure_name [(param1 [IN/OUT/INOUT] DATA_TYPE, param2 DATA_TYPE, ...)]
IS[/AS]
BEGIN

[EXCEPTION]

END;
/

Example:

 

CREATE OR REPLACE PROCEDURE UPDATE_EMP_SALARY (IN_EMP_ID NUMBER, IN_SALARY NUMBER)

IS

BEGIN

UPDATE EMPLOYEES 

SET SALARY = IN_SALARY

WHERE EMP_ID = IN_EMP_ID;

END;

ACID properties in RDBMS database


A transaction can be defined as a group of tasks. A single task is the minimum processing unit which cannot be divided further.

Let’s take an example of a simple transaction. Suppose a bank employee transfers Rs 500 from A's account to B's account. This very simple and small transaction involves several low-level tasks.

A’s Account

Open_Account(A)
Old_Balance = A.balance
New_Balance = Old_Balance - 500
A.balance = New_Balance
Close_Account(A)


B’s Account

Open_Account(B)
Old_Balance = B.balance
New_Balance = Old_Balance + 500
B.balance = New_Balance
Close_Account(B)

ACID Properties
A transaction is a very small unit of a program and it may contain several low-level tasks. A transaction in a database system must maintain Atomicity, Consistency, Isolation, and Durability - commonly known as ACID properties - in order to ensure accuracy, completeness, and data integrity.

Atomicity - This property states that a transaction must be treated as an atomic unit, that is, either all of its operations are executed or none. There must be no state in a database where a transaction is left partially completed. States should be defined either before the execution of the transaction or after the execution/abortion/failure of the transaction.

Consistency - The database must remain in a consistent state after any transaction. No transaction should have an adverse effect on the data residing in the database. If the database was in a consistent state before the execution of a transaction, it must remain consistent after the execution of the transaction as well.

Durability - The database should be durable enough to hold all its latest updates even if the system fails or restarts. If a transaction updates a chunk of data in a database and commits, then the database will hold the modified data. If a transaction commits but the system fails before the data could be written on to the disk, then that data will be updated once the system springs back into action.

Isolation - In a database system where more than one transaction is being executed simultaneously and in parallel, the property of isolation states that all the transactions will be carried out and executed as if it is the only transaction in the system. No transaction will affect the existence of any other transaction.