SQL Set Operator

Set Operator 

Set Operator:-Set Operator are used to get the result from different queries.
Below are set operator in SQL
 1) union
 2) union all
 3) intersect
 4) minus

Constraint to use Set Operator :- There are below constraint to use Set Operator
               1) All queries should have same number of columns
               2) All columns datatype should be sequentially matched
               3) Order by clause can be used only with last query

1) Union :-Union displays the combined result from all the compounded SELECT queries,after removing all duplicates and in sorted order
           (ascending by default), without ignoring the NULL values.

select empno,ename,job,deptno from emp where deptno=10
union
select empno,ename,job,deptno from emp where deptno in (10,20)
order by empno;








2) Union all:-Union all gives the result set without removing duplicate and sorting the data.

select empno,ename,job,deptno from emp where deptno=10
union all
select empno,ename,job,deptno from emp where deptno in (10,20)
order by empno;





3) Intersect:-Intersect displays the common rows from both the all queries, with no duplicates and in sorted order (ascending by default).

select empno,ename,job,deptno from emp where deptno =20
intersect
select empno,ename,job,deptno from emp
order by empno;






4) Minus :-Minus operator displays the rows which are present in the first query but not present in the second query, with no duplicates and data arranged in ascending order by default.

select empno,ename,job,deptno from emp
minus
select empno,ename,job,deptno from emp where deptno in (20,30)
order by empno;




select empno,ename,job,deptno from emp where deptno in (20,30)
minus
select empno,ename,job,deptno from emp
order by empno;

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