Oracle/PLSQL indexes

What is an Index? Explain how to create an Index.

An index is a object which is used to improve performance during retrieval of records.
CREATE [UNIQUE] INDEX index_name
ON employee[emp_id, emp_name,dept_id]
[COMPUTE STATISTICS]

The UNIQUE keyword is used when combined values of the index should be unique.
The COMPUTE STATISTICS during the creation of index optimizes the plan of execution of the SQL statement and improves performance.

What is Function-Based Index? Create a Function-Based Index

Instead of adding a index to a column, you index the function on that column.
CREATE INDEX sample_index on employee (UPPER(employee_name));

SELECT * FROM employee WHERE Upper(employee_name)='BILL';
Advantages of PL/SQL
PL/SQL is a transaction processing language that offers the following advantages: support for SQL - SQL is flexible, powerful and easy to learn...
Explain block structure of PL/SQL
The order of the parts is quite logical: First comes the declarative part, in which items can be declared. Items can be manipulated in the executable part...
Write a PL/SQL program for a trigger
PL/SQL program for a trigger - PL/SQL program for tracking operation on a emp table...
Post your comment

You may like