INDEX & MERGE

Index In Oracle

1.Its a Schema Object
2.Its One of the Performance tuning method.
3.It improves the performance of the query.
4.Index created by user and maintained by Database.

Types Of Index::

1.Unique Index
2.Non Unique Index

1.Unique Index::

It's crated by automatically by oracle,When you create Primary Key or Unique Key.

2.Non Unique Index::

It's Created by the programmer to speed up the retrieval of the query.

2.Types of Non Unique Index::

1.B-Tree Index
2.Bitmap Index
3.Functional Based Index
4.Reverse Index.

--------------------------------------------------------------------------------------------
Data Dictionary Table ---For Index Is ---->(USER_INDEX)
---------------------------------------------------------------------------------------------

WHEN TO CREATE INDEX::

1.When a table size is Large.
2.When a column is most often used as a condition in a query.
3.When column Contain wide range of Values.
4.When most of your queries are expected to retrieve less than 4% of the row from the table.

Example::

SELECT * from Employees Where First_Name='Steven';

In this table the Column "First_Name" is mostly used  So We can create Index for the Column.

WHEN NOT TO CREATE INDEX::

1.When the table size is small.
2.When a column is not used as a condition in a query.
3.When a column contains large number of duplicate values.
4.When your query are expect to retrieve more than 10% of the ROW from the table.
5.When a table is frequently Updated.

Example::

CREATE INDEX Dept_Id ON Employees(Department_Id);
[This index in known as Normal index or B-Tree Index]

Functional Index::

When performing a function on an indexed column in the where clause of a query is Known as Functional Index.

Example::

SELECT * FROM Employees WHERE First_Name='neena';
In this the First name is 'Neena' in the table,So its not return the output in this case,we can create Fun Index.

CREATE INDEX First_Nm ON Employees(LOWER(First_Name);

BITMAP INDEX::

When our Column having more duplicate records then we prefer the BITMAP index.

Example::

GENDER Column
CREATE BITMAP INDEX Gender_Ind ON Employees(Gender);

REVERSE INDEX::
 
A reverse key Index can be useful if it e.g. is the primary key (or other Index ) of an column that is filled by a sequence.

When generating new records in the base table, because of the used sequence you'll get a high contention on the same Index branches and thus database blocks.
It can have a high impact on Insert on that table.

SYNTAX::

CREATE INDEX <<INDEX_NAME>>ON <<TABLE_NAME(Col_Name)>> REVERSE;

Example::

CREATE INDEX Rev_Indx ON Bank_Master(Acc_num) REVERSE;

WHEN INDEX IS NOT PERFORM::
If we perform any action with the Index column then the Index is not perform.

Example::

If The Salary having Index,

Select Salary*12 As "Yearly Salary" From Employees;

In this it's not Working.

MERGE

MERGE is nothing but it's combined INSERT + UPDATE
When the Target table match with the Source table then the "UPDATE" will happen
When the target table is unmatched with source table then "INSERT" will happen.

EXAMPLE::

Consider Target_Table & Source Table,Merge [Insert + Update] data's from Source to Target Table

MERGE INTO table_Trg t
USING table_src s
ON(s.Department_Id=t.Department_Id)
WHEN NOT MATCHED THEN
INSERT(t.Department_Id,t.Department_Name,t.Manager_Id,t.location_Id)
VALUES(s.Department_Id,s.Department_Name,s.Manager_Id,s.location_Id)
WHEN MATCHED THEN
UPDATE SET t.Department_Name=s.Department_Name
  t.Manager_Id=s.Manager_Id
  t.Location_Id=s.Location_Id

--------END--------