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--------
