SQL Index

Index

Index :- Index is a data structure like table.Index store column data(On which index created) with row identifier rowid.  Database indexes are used to increase the query performance.
        
Types of Index:-By structure index are 2 types
    1) B-Tree Index
    2) Bitmap Index
B-Tree Index:-By default B-tree index created when type not mentioned.B-Tree index created on column/columns those cardinality (High degree of distinct value) is very high.

Syntax:-Create index on (,,..);
Example:-create index idx_empno on emp1(empno);

Bitmap Index:-Bitmap index is created on column/columns those cardinality is very low.Just like gender column in a table which have only two distinct values "M" & "F" (very low cardinality).
Syntax:-Create bitmap index on (,,..);
Example:-create index idx_gender on student(gender);

Different type of Indexes:-Below are the list of indexes which are available in Oracle

1) Index Organized Table(IOT):-This Index created when most of the columns of table are used in Primary Key. IOT store complete data of tables as B-tree index structure. This index is created at the time of table creation.
Syntax:- Create table ( datatype(size), datatype(size),..,
                                   constraint primary key (,..))
                                   organization index;
Example:-create table item_details(item_id number,item_name varchar2(20)
                                   constraint pk_item primary key (item_id,item_name))
                                   organization index;

2) Unique Index:-This index is created with "unique" keywork .This is a conjustion for Primary Key and Unique Key constraint.
Syntax:-Create unique index on (,,..);
Example:-create unique index idx_regno on student(regno);

3) Reverse Key Index:-Reverse key index created with "reverse" keyword. useful to balance I/O in an index that has many sequential inserts.

Syntax:-Create unique index on (,,..) reverse;
Example:-create unique index idx_regno on student(regno) reverse;

4) Key Compressed Index :-This index created when the leading column has repeated values. It compresses leaf block entries
Example:- create index cust_idx1 on cust(last_name, first_name) compress 2;

5) Desending Index :-
Example:-create index cust_idx2 on cust(cust_id desc);

6) Function Based Index :-When we are using function frequently in a column in query then we create function based index on that column.
Example:-create index cust_idx3 on cust(upper(last_name));

7) Virtual Index :-We can create virtual index by mentioning "nosegment" clause,It is created for tunning purpose.
Example:-create index cust_idx4 on cust(first_name) nosegment;

8) Invisible Index :-Optimizer does not use the invisible index when retrieving data for a query.It is used for test purpose.
Example:-create index cust_idx5 on cust(last_name) invisible;

Partitioned Table Indexes:- There are 2 type of indexes in Partitioned Table.
        1) Global Partitioned Index
        2) Local Partitioned Index

SQL Cluster Table

Cluster Table

Cluster:-Cluster is a method by which we can store data of two or more tables the basis of cluster key

Cluster Key :-Cluster key is a column/columns by which the tables are usually joined. 
Example:- 
1) Create a cluster :- 
SQL>create cluster emp_dept (deptno number(2));

Cluster Index:-Index created on cluster is called cluster index.
Example:-
SQL>create index cls_idx on cluster emp_dept;

2) Creating tables in the cluster:-
SQL>create table dept12 (deptno number(2),
                    name varchar2(20),
                    loc varchar2(20))
                    cluster emp_dept (deptno);

SQL>create table emp12 (empno number(5),
            name varchar2(20),
            sal number(10,2),
            deptno number(2)) 
            cluster emp_dept (deptno) ; 

Drop a Cluster:-   a) If there is no table attached with cluster then we can drop cluster by below way      
SQL>drop cluster emp_dept;

b) If Tables attached with cluster then we can drop cluster with following way

SQL>drop cluster emp_dept including tables; --if tables attached with cluster

Data Dictionary for Cluster :-Below is the Data Dictionary used for getting the information of cluster
SQL>select * from user_clusters;

SQL Sub-Query

Sub-Query

Types of Sub query:-In the basis of place of the subquery, there are three types

1. Nested Subquery:- The subquery appears in the WHERE clause of the SQL.
Example:- select * from emp where deptno in (select deptno from dept);
2. Inline View: The subquery appears in the FROM clause (in place of table name) of the SQL.
Example:- SELECT  a.ename, a.sal, a.deptno, b.salavg FROM emp a, 
          (SELECT  deptno, avg(sal) salavg FROM emp GROUP BY deptno) b 
          WHERE a.deptno = b.deptno AND a.sal > b.salavg;
3. Scalar Subquery: The subquery appears in the SELECT clause of the SQL.
Example:-
select a.*,(select round(avg(b.sal),2) from emp b where a.deptno=b.deptno) avg_sal 
from emp a 
where a.sal>(select round(avg(b.sal),2) from emp b where a.deptno=b.deptno);


In the basis of execution of subquery, there are two categories of subqueries

1. Simple Subquery: A simple subquery can be exceuted independently. 
                    It is executed first then parent query executed on the basis of Subquery result.
Example:-select * from emp where deptno in (select deptno from dept);
2. Correlated Subquery: A correlated subquery can not be excuted independently.
                       In Correlated Subquery, parent query executed first then Correlated Subquery
                       executed on the basis of parent query record.                       
Example:-select a.* from emp a where a.sal>(select avg(b.sal) from emp b where a.deptno=b.deptno);

Multiple-Column Subquery:-
Syntax:-
SELECT ,,... FROM  
WHERE (,, ...) IN (SELECT ,,... FROM 
WHERE condition);

Example:- Display the employee's details whose job and deptno matched with 'WARD' 
          and  'WARD' details should not be displayed 

Pair-wise Comparison:-
select * from emp where (job,deptno) in (select job,deptno from emp where ename='WARD')
and ENAME<>'WARD';

Non Pair-wise Comparison:-
select * from emp 
where (job) in (select job from emp where ename='WARD')
and (deptno) in (select deptno from emp where ename='WARD')
and ENAME<>'WARD';

Sub query in FROM clause (Inline View or Logical Table):-
Example:-
SELECT  a.ename, a.sal, a.deptno, b.salavg FROM emp a, 
(SELECT  deptno, avg(sal) salavg FROM emp GROUP BY deptno) b 
WHERE a.deptno = b.deptno AND a.sal > b.salavg;


Question for Practice :-

Q1 :- What is the Sub-Query ?
Q2 :- What are types of Sub-Query ?
Q3 :- Why Sub-Query is required ?
Q4 :- What is the difference between Main Sub-Query and Co-related Sub-Query ?
Q5 :- Can we use Sub-Query in place of Column Name as well ? 
Q6 :- What is the In-line View ?

SQL Join

Join

Joins:-We are using joins when we need data from two are more tables

Note:- By "exists" we can select columns of outer table only , same way we can select outer table columns by "in" as well but by "Join" we can select columns from all joined tables (Which is possible in join only).  

 Types of Joins:- There are below types joins in Oracle

1) Cross Join or Cartesian Join
2) Inner Join
3) Natural Join
4) Outer Join
             a) Left Outer Join
             b) Right Outer Join
             c) Full Outer Join
5) Self Join

Note:- There are 2 standard syntax for Join
           1) ANSI Standard
           2) Oracle Standard

1) ANSI Standard:- In ANSI standard Join type need to mentioned and joining condition supply with "on" clause.

2) Oracle Standard :-In Oracle Standard, Join type not mentioned and joining condition supply with "where" clause.

1) Cross Join or Cartesian Join:-This is the worst join where no join condition mentioned. In this join, Oracle joins every record of first table with all records of second table .

Note:-After this join record count will be (m X n) where m is record count of first table and n is the record count of second table.


Example:- 
select a.*,b.* from emp a,dept b;--Oracle Standard
select a.*,b.* from emp a cross join dept b; --ANSI standard

2) Inner Join:-In this join result depend on join condition.

Note:-After this join record count will be (<= m )  where m is the record count of that table which have the highest records.


Example:- 
ANSI standard:-
select a.*,b.* from emp a  inner join dept b on a.deptno=b.deptno;  
                                OR
select a.*,b.* from emp a  join dept b on a.deptno=b.deptno;

Oracle standard:-
select a.*,b.* from emp a , dept b where  a.deptno=b.deptno;

3) Natural Join :- This join is the same as Inner Join but here no need to supply any joining condition. Oracle automatically decide joining condition on the basis of columns name in Tables.There should be at-least one column name should be same in both tables.

Note:- You can't select that column on which system joined both tables in select query and when you are selecting all columns by "*" system will display that column in first position.  

select * from emp a  natural join dept b ; --ANSI standard
select a.EMPNO,a.ENAME,b.DNAME from emp a  natural join dept b ;

Note:-You can not take join column in select list.

4) Outer Join:- This join is used when we need records from joining table that are not satisfying join condition with records that are satisfying matching condition.

a) Left Outer Join:- In Left Outer Join, All records from left table will be part of result and where not matching records of right table, NULL will be displayed. 

select a.*,b.* from emp111 a  left outer join dept b on a.deptno=b.deptno; 

ANSI standard:-
select a.*,b.* from emp111 a  left join dept b on a.deptno=b.deptno; --ANSI standard
select a.*,b.* from emp111 a , dept b where a.deptno=b.deptno(+);--Oracle Standard

b) Right Outer Join:- In Right Outer Join, All records from right table will be part of result and where not matching records of left table, NULL will be displayed. 

select a.*,b.* from emp a  right outer join dept b on a.deptno=b.deptno;

ANSI standard:-
select a.*,b.* from emp a  right join dept b on a.deptno=b.deptno; --ANSI standard
select a.*,b.* from emp a , dept b where a.deptno(+)=b.deptno;--Oracle Standard

c) Full Outer Join:- In Full Outer Join, All records from both tables will be part of result and where not matching records of left/right table, NULL will be displayed. 

select a.*,b.* from emp111 a  full outer join dept b on a.deptno=b.deptno; --ANSI standard
select a.*,b.* from emp a  full join dept b on a.deptno=b.deptno; --ANSI standard
select a.*,b.* from emp a , dept b where a.deptno(+)=b.deptno
union
select a.*,b.* from emp a , dept b where a.deptno=b.deptno(+);--Oracle Standard

Practice Questions :-

Q1 :- What is Join ?
Q2 :- Why Join is necessary ?
Q3 :- What are types of Join ?
Q4 :- What is the difference between Inner Join and Outer Join ?
Q5 :- What types of Outer Joins available in Oracle ?

SQL Database Link

Database Link 

Database Link:- Database Link is a Database Object ,that is used for accessing one database from another database.In other word database link is a schema object in one database permit you to access objects on another database.

Type of Database Link:- There are 2 types of Database Link

1) Private Database Link
2) Public Database Link

Where we can Use:- We can access table,view and other PLSQL objects by using database link.Also we can perform DML operations by using database link.
                                     
1) Private Database Link:- Private Database Link are database link that can be accessed only by creator or you can say visible for only creator.
Syntax :- 

CREATE DATABASE LINK 
CONNECT TO IDENTIFIED BY 
USING '';

Example :- There are 2 Database have below tns entries

prod1db =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.81.128)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = ORCL))
  )


prod2db =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.81.129)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = ORCL))
  )  
  

Below create statement will create database link from prod1db to prod2db

CREATE DATABASE LINK prod2ln
CONNECT TO system IDENTIFIED BY 
USING 'prod2db';

same can be created by below statement as 

CREATE DATABASE LINK prod2ln
CONNECT TO system IDENTIFIED BY 
USING '(DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.81.129)(PORT = 1521))
       (CONNECT_DATA = (SERVICE_NAME = ORCL))
       )';

                        
2) Public Database Link :-Public Database Link  are database link that can be accessed by any user or you can say visible to all users.

Syntax :- 

CREATE PUBLIC DATABASE LINK 
CONNECT TO IDENTIFIED BY 
USING '';
Example:- 

CREATE PUBLIC DATABASE LINK prod2pln
CONNECT TO system IDENTIFIED BY 
USING 'prod2db';

Example of using DATABASE Link:-
Example1:- Create a table on primary database with same structure and data available on remote database using database link.

CREATE TABLE EMPLOYEE AS SELECT * FROM EMPLOYEE@prod2pln;

Example2:- How can I see tables available in remote database using database link.

SELECT * FROM TAB@prod2pln;

Example3:- Delete records from EMPLOYEE table which is available at remote database using database link.

DELETE FROM EMPLOYEE@prod2pln;

Example3:-Update salary by 10000 of HR department employee , table is available at remote database

UPDATE EMPLOYEE@prod2pln set salary=10000 where department='HR';

ALTER DATABASE LINK :- Alter database link statement is used to update the change password.

Syntax :- ALTER DATABASE LINK 
          CONNECT TO IDENTIFIED BY ;

Example:-
ALTER DATABASE LINK prod2ln
  CONNECT TO system IDENTIFIED BY system_new_password;

ALTER PUBLIC DATABASE LINK prod2pln
  CONNECT TO system IDENTIFIED BY system_new_password;
 

DROP PRIVATE DATABASE Link :- Below statement is used to drop the private database link.

DROP DATABASE LINK prod2ln;

DROP PUBLIK DATABASE Link :- below statement is used to drop the public database link.

DATA DICTIONARIES for DATABASE LINK :- Below are the DATA DICTIONARIES for DATABASE LINK.