ORACL SQL PART II

JOIN

1. Equi - Joins                  1. Cross join
2. Cartesion Join                2. Natural Join
3. Non equi  join                3.Join …. Using
4. Outer join                    4.Join On
5. Self join                     5.Left outer join
1. 6. Right outer join 

                              7. Left  outer join

                              8. Full outer join

To join ‘n’ tables you need minimum of n-1 condition

The above rule may not apply if you have concatenated primary key, in which more than one column is required to uniquely identify each row

Equi Join

SQL > select eid,ename,dept_id,location from emp,dept where emp.eid=dept.id

NON Eequi join

SQL > select e.name,e.sal.j.grade from emp e,jobgrade j where e.sal between j.lowsal and j.highsal

Outer JOIN

It is reverse of equi join. Those which ever not meet the condition can be seen using outer join

Outer join can be implemented either using (+) symbol or the keyword outer join

Types of Outer join

1. Left outer join
2. Right outer join
3. Full outer join

Right Outer Join

SQL > select t1.column,t2.column from t1,t2 where t1.column (+) = t2.column

                        (or)

SQL > select t1.column,t2.column from t1 right outer join t2 on (t1.column=t2.column)

Left Outer Join

SQL > select t1.column,t2.column from t1,t2 where t1.column = t2.column (+)

                        (or)

SQL > select t1.column,t2.column from t1 left outer join t2 on (t1.column=t2.column)

Full Outer Join

Display all

SQL > select t1.column,t2.column from t1 full outer join t2 on (t1.column=t2.column)

Inner Join

Displays only two columns matched value

SQL > select t1.id,t2.id from t1 inner join t2 on (t1.id=t2.id)

Self join

A self join is a join in which a table is joined with itself. For example, when you require details about an employee and his manager (also an employee).

SQL > select e1.ename || ‘ works under’ || e2.ename ‘manager’ from emp e1, emp e2 where e1.mgr=e2.empno

                              (or)

SQL > SELECT e1.ename||' works for '||e2.ename "Employees and their Managers"    FROM emp e1  JOIN emp e2   ON (e1.mgr = e2.empno);

Cartesian join

A cartesian join is a join of every row of one table to every row of another table.

 SQL > SELECT * FROM emp, dept;

                              (or)

SQL > SELECT *    FROM emp, dept WHERE dept.deptno = 10   AND emp.sal > 10000;

                              (or)

SQL > SELECT * FROM emp CROSS JOIN dept;

Natural join

It is based on all column in the two table that have the same name

It select rows from the two tables that have equal value in all matched column

A natural join is a join statement that compares the common columns of both tables with each other. One should check whether common columns exist in both tables before doing a natural join.

Natural joins may cause problems if columns are added or renamed. Also, no more than two tables can be joined using this method. So, it is best to avoid natural joins as far as possible.

SQL > SELECT dname, ename FROM dept NATURAL JOIN emp

                              (or)

SQL > SELECT department_name, first_name||' '||last_name FROM departments NATURAL JOIN employees;

SQL > SELECT  dept_id,dept_name,location_id,city from dept natural join location

Creating Join with using clause

If  several coumn have the same name but different data type the natural join clause can be modified with the ‘USING’ clause to specify the column that should be used for all equi join

Do not use the table name or alias in the referenced column

The  natural join and ‘USING’ clause are mutually exclusive

SQL > select l.city,d.deptname from location l,dept d using (location_id) where location_id=1400

SUB query

1.Single rwo subquery

2. Multiple row sub query

Single row sub query

Operator in single row sub query

=,<,<=,>,>=,<>

SQL > select name,dept,sal from emp where dept =(select dept from emp where name like ‘sam’)

SQL > select last_name,job_id,sa; from emp where sal=(select min(sal) from emp)

SQL > select dept_id,min(sal) from emp group by dept_id having min(sal) >(select mins(sal) from emp where dept_id=50)

Multiple row sub query

Operators in multiple row subquery

IN,ANY,ALL

IN – equal to any member in list

ANY – compare value to each value returned by sub query

ALL – compare value to every value returned by sub query (not with each value)

SQL > select name,sal from emp where sal > in (1000,1600) – Output will be what ever value equal to  1000 and 1600 will be displayed

SQL > select name,sal from emp where sal > any (1000,1600) – Output will be what ever value greater thatn 1000 and 1600 will be displayed

SQL > select name,sal from emp where sal > all (1000,1600) – Output will be all operator first compare two argument if the given operator is ‘>’ the all will take highest value from argument which is 1600 then value which ever greator that 1600 will be displayed.

Using sub query in insert statement

SQL > insert into  (select emp_id,last_name,mail from emp where dept_id=50) values (999,’aaaa’,’abc@mail’)

Difference between Nested & Correlated Subqueries

There are two main types of subqueries - nested and correlated. Subqueries are

nested, when the subquery is executed first,and its results are inserted into

Where clause of the main query. Correlated subqueries are the opposite case,

where the main query is executed first and the subquery is executed for every

row returned by the main query

Nested Subqueries

A subquery is nested when you are having a subquery in the where or having

clause of another subquery.

Scenario

Get the result of all the students who are enrolled in the same course as the

student with ROLLNO 12.

SQL > Select * From result where rollno in (select rollno

       from student where courseid = (select courseid from student

 where rollno = 12));

The innermost subquery will be executed first and then based on its result the

next subquery will be executed and based on that result the outer query will be

executed. The levels to which you can do the nesting is

implementation-dependent.

Correlated Subquery

A Correlated Subquery is one that is executed after the outer query is executed.

So correlated subqueries take an approach opposite to that of normal subqueries.

The correlated subquery execution is as follows:

 -The outer query receives a row.

  -For each candidate row of the outer query, the subquery (the correlated

  subquery) is executed once.

  -The results of the correlated subquery are used to determine whether the

  candidate row should be part of the result set.

  -The process is repeated for all rows.

Correlated Subqueries differ from the normal subqueries in that the nested

SELECT statement referes back to the table in the first SELECT statement.

Scenario

To find out the names of all the students who appeared in more than three papers

of their opted course, the SQL will be

SQL > Select name from student A Where 3 < (select count (*)

      from result b where b.rollno = a.rollno);

In other words, a correlated subquery is one whose value depends upon some

variable that receives its value in some outer query. A non-correlated subquery

as said before is evaluted in a bottom-to-up manner, i.e. the inner most query

is evaluated first. But a correlated subquery is resolved in a top-to-bottom

fashion. The top most query is analyzed and based on that result the next query

is initiated. Such a subquery has to be evaluated repeatedly, once for each

value of the variable in question, instead of once and for all.

Correlated subquery

SQL > select last_name,salary,dept_id from emp xx where salary> (

      Select avg(salary) from emp where dept_id=xx.dept_id)

ALIAS

SQL > select e.id,e.name from emp e

View

A view is simply the representation of a SQL statement that is stored in memory so that it can easily be re-used.Compilation code is stored in view

Oracle views offer some compelling benefits. These include:

* Commonality of code being used. Since a view is based on one common set of SQL, this means that when it is called it’s less likely to require parsing. This is because the basic underlying SQL that is called is always the same. However, since you can add additional where clauses when calling a view, you still need to use bind variables. Additional where clauses without a bind variable can still cause a hard parse!

Security. Views have long been used to hide the tables that actually contain the data you are querying. Also, views can be used to restrict the columns that a given user has access to. Using views for security on less complex databases is probably not a bad thing. As databases become more complex, this solution becomes harder to scale and other solutions will be needed.

Creating View

-- View One

SQL > CREATE VIEW vw_layer_one  AS SELECT * FROM emp;

-- view two

SQL > CREATE VIEW vw_layer_two_ dept_100 AS SELECT * FROM vw_layer_one WHERE deptno=100;

 

SQL > Create or replace view eg_view as select * from <table> with read only

 

SQL > create force view eg_force as select * from  emp <Non existing table>

 

SQL > create view  as select name from emp where dept=’IT’ with check option – Will allow  to either insert or update only  department IT rows

TO see the View

SQL > select * from <view name>

 

To see the Views created in local database

SQL > select * from user_objects where objects_name=’View’

SQL > select * from user_view where view_name=’<name>’ – To check whether read only or not

To see the source code of the view

SQL > select text from user_view where view_name=’Name’

Note: It is not advisable to insert or update record through views. To overcome we can make it read only

Note : It is possible to create view for non existing table

Inline view (or) TOP ‘N’ analysis

Instead of giving table name after the from statement if we use another select statement it is called inline view

To check like top 3 performer or least 3 performer

SQL > select  rownum,sal from select sal from emp orderby sal desc) where rownum<3

Inline view insert statement

SQL > insert into (select * from <tabl> where sno=10) values (&no,’&name’)

Inline view insert statement with check option

To conform user only should enter 10 as sno follwing query should be executed

SQL > insert into (select * from <table> where sno=10) with check option values (&no,’&name’)

Index

Rowid in index

Rowid is pseudo column it  indicate Data object,data file no,block no, row number

Creating INDEX

SQL > create index <name> on table (column)

Creating an Indextype: Example

The following statement creates an indextype named position_indextype and specifies the position_between operator that is supported by the indextype and the position_im type that implements the index interface. Please refer to "Using Extensible Indexing " for an extensible indexing scenario that uses this indextype:

CREATE INDEXTYPE position_indextype

   FOR position_between(NUMBER, NUMBER, NUMBER)

   USING position_im;

To See user created objects

SQL > select tablespace_name from user_table where table_name=’TABLE’

To See the current table space

SQL > select tablespace_name from user_table where table_name=’TABLE’

To see the index

SQL > select index_name from user_indexes where table_name=’tb_sample’

SQL > select * from user_ind_columns

Note : When table is droped index also droped automatically

Note: While creating Primary key & unique constraint index will created automatically

When to create index

Table is too large

Frequently searching column

Index wont include NULL values

When not to create index

Table is too small

Frequently updated column

Equivalent record/same data

Introduction

If you are new to databases, or perhaps new to Oracle, you may find the discussion on indexes and indexing strategy complicated.  Don't fret.  To get started it's fairly straightforward, and as long as you pay attention to the options relevant to day-to-day dba needs, it should remain fairly simple.

Common Usage Indexes

b-tree index

The most common index type is the b-tree index.  It is named b-tree after a computer science construct of the same name.  Whenever you issue the basic CREATE INDEX statement without further modifications, you're creating a b-tree index.  Without going into a lot of depth about b-trees which you can investigate on your own, basically these store the values of the column you have created the index on, and pointers to the actual table data to find the row itself.  Keep in mind that also means multiple lookups, one for various nodes and the leaf node of the index, and then the table row itself.  That's why Oracle's optimizer will choose in certain circumstances to do full table scans rather than index lookup, because it may actually be faster.  Also note that if your index is on more than one column, the leading column is very important.  For example if you have a multi-column (called concatenated) index on columns c and d in that order, you can also do a query on column c alone and use that index. In some other cases using Oracle's skip- scan technology, one can do a query on non-leading columns as well. Do a google search on "skip scan index access" for details.

function-based index

Related Articles

Oracle: Unusable Indexes

Partition Pitfalls in Oracle

The Globalization of Language in Oracle - Index Requirements

We mentioned that Oracle would choose not to use an index sometimes, if you're reading a lot of rows, or your index is not selective, or you're using a column other than the leading one in a concatenated index.  What about if you want to do a case-insensitive search?  Something like:

WHERE UPPER(first_name) = 'JOHN'

This won't use an index on first_name.  Why?  Because Oracle would have to go and apply the UPPER function on ALL values in the index, so it might as well do the full table scan.  This was such a common need that Oracle created the function-based index for this purpose.

reverse key indexes

You also may see these indexes, or want to use them from time to time.  Consider a column, which includes names like "restaurant A", "restaurant B", "restaurant C" and so on.  Perhaps a not very glamorous example, but the point is a column with many unique values but not much variation at the front.  Using a reverse-key index would be ideal here, because Oracle will simple REVERSE the string before throwing it into the b-tree.  So, the result will be a more balanced, useful, and ultimately fast index.

More Exotic Index Types on Offer

Oracle offers quite a few more sophisticated types of indexes as well.  Please note, these should be used after you've fully read the docs as they fill very specific niches. 

bitmap indexes

Have a column, which is not very selective, such as gender?  You might consider using a bitmap index on it.  That's what they were created for.  But also consider what's happening behind the scenes.  Generally bitmap indexes become useful when you have a whole bunch of them on different columns so that they can all be used together to be more selective on rows that otherwise you'd need a full table scan for.  So one, use them when you can have quite a few on different columns.  Secondly, these indexes were designed for data warehouses, so the presumption is data that does not change much.  They are not meant for transactional or high update databases.  Updates on tables with bitmap indexes are, shall we say, less than efficient. 

bitmap join indexes

These indexes take bitmap indexes one step further.  They completely take the bitmapped columns out of the table data, and store it in the index.  The presumption is that those sets of columns will always be queried together.  Again, these are meant for data warehousing databases.  The create statement looks like a CREATE BITMAP INDEX except it has a WHERE clause at the end!

compressed indexes

This is really an option to a normal b-tree index.  It results in fewer leaf nodes, so less overall I/O and less to cache.  All of this means Oracle's optimizer will be just a little more likely to use these than a normal uncompressed index.  There is a cost to all of this, in CPU to uncompress when you access these.  Also, in reading about how the optimizer uses these, and choosing the proper compression level, it begins to look daunting.  Your mileage may vary.

descending

These are a special type of function-based index.  They are obviously optimized for ORDER BY x, y, z DESC clauses.

partitioned indexes

If you have a partitioned table, a whole world of new index types opens up to you, from ones that index across all the partitions (global) to ones that are focused on each partition individually (local).  Check the documentation for details.

index organized tables

Imagine you take your concatenated index, and extend it to all the columns in the table.  Then you can remove the table itself.  That's what an index organized table is.

cluster indexes

I personally have never seen these in the wild.  All I've read is they have performance issues left and right.  Basically, you take two tables with one column in common, and that column has a cluster index on it.

domain indexes

These indexes are used when creating custom indextypes for user defined datatypes.

invisible indexes

These are new in 11g.  They are created as a normal index, but invisible to the cost based optimizer.  This can allow you to test out performance of large queries, without impacting the live running application.

virtual (no segment) indexes

Another tool for the testers and developers.  They allow you to test new indexes and their effect on query plans, without actually building them.  On gigabyte tables, the index build can be very resource intensive, and take a lot of time.   See also the Virtual Index Wizard of OEM.

miscellaneous

There are other types of indexes as well, such as Oracle TEXT for indexing CLOB or other large text data, and Oracle Spatial.  Investigation of those is left as an exercise to the reader.

It's All About the Optimizer

Having worked extensively with MySQL, and some other databases, I can tell you it is not Oracle's user-friendliness that makes it the world leader.  I could go digress on this point, but primarily Oracle's bread and butter is it's optimizer.  This is the special sauce.  And it keeps getting better and better.  There are whole books written on the topic of the Oracle's CBO (Cost Based Optimizer) discussing hints (comments embedded in SQL to push the optimizer one way or another), strategies for analyzing your tables and indexes, and histograms for those finicky columns where data distribution is not balanced. 

Besides keeping your statistics up to date, you'll want to always test your new queries.  Use the explain plan mechanism, and optimize to reduce overall I/O and computational sorting and merging of data, and you will be on the path to better performance.

Sequence

To generate integer value. Can be used where ever needed

Caching of sequence number improve the performance. Because a set of numbers is pre allocated in memory for faster access.

If there is an instance failure all cached numbers are lost & there will be a gap

SQL > create sequence <seq name>

      Minvalue 10

Maxvalue 100

Increment by 10

No cyle/cycle

Cache 50 – is byte for internale storage

Start with 100

SQL > insert into <table> values (sequ.nextval) – To insert sequence value

Alter sequence

Note : Start with value cannot be altered

SQL > alter sequence <name>

      Minvalue <new value>

      Maxvalue xx

     

To view the sequence

SQL > select * from user_sequences where sequence_name = ‘<name>’

Synonym

is alias for an object that you own or you have given access to.

Types of synonym

1. Private synonym
2. public synonym

How to create synonym

Private access

SQL > create synonym <name> for <table name>

SQL > grant select on <synonym name> to <user>

Public synonym

SQL > create public synonym <synonym name> for <table>

SQL > grant select on <synonym name> to public

To view the synonym

SQL > select * from user_synonym where synonym_name=’syn name’

To view the public synonym

SQL > select * from all_synonym where synonym_name=’syn name’

To Drop synonym

SQL > drop synonym <name> - For private synonym

SQL > grant drop public synonym to <user> - Only Sys can drop public synonym

Merge statement

Either we can update / insert using merge to avoid duplicate

SQL > merge into emp as xx using emp yy  On (xx.e_id=yy.e_id)

When mached then

Update set xx.name=yy.name,xx.id=yy.id

When not matched then

Insert values (yy.id,yy.name)

Partition

To improve the performance

syntax

SQL> create table <name> (sno,name)

      partition by range (sno)

      partition p1 values less than (10)

      partition p1 values less than (20)

      partition p1 values less than (40));

SQL> insert into <name> values (&sno,'&name')

SQL> select * from <name> partition (p1)

SQL> select * from <name> partition (p2)

Add partion using alter

SQL> alter table <name> add partition p5 values less than (100);

To see the partition in table

SQL> select partition_name,high_value from user_table_partition where table_name='<name>'

Split partition

SQL> alter table <name>  split partition p3 at (50) into (partition p2, partition p3);

Merge partition

SQL> alter table <table> merge partition p2,p3

partition with character data type

SQL> create table <name> (city varchar(6))

      partition by list(city)

      (partition p1 values ('CBE','CHN')

      partition p2 values('HYD','JK'));

Note : in the above table only mentioned city name can be inserted\

Producing readable output

&  - To store the temporary value

&& - To re use the variable value with out prompting the user each time

      SQL > select  emp_id,lastname,job_id,&&column from emp order by & column

Define – To pre define value in iSQl

      SQL > define variable = value

      SQL > define variable – to see the stored value

      SQL > undefined variable – To clear the variable from buffer

Column & breack

SQL > Column <column> heading ‘Heading of column’

SQL > break on job_id – To suppress the duplicate

SQL > Ttitle ‘Salary report’

SQL > Btitle ‘Confidential’

SQL > column lastname – To display column heading

SQL > column lastname clear – clears any column format

Set Commands

SQL > set heading off/on

SQL > set arraysize {20|n} – Data fetching size

SQL > set feedback on/off

SQL > set long {80|n}|on|text

SQL > set verify on

SQL > set echo on

SQL > show echo

SQL > show all – to see all environment variables

Set Operations

The following list briefly describes the four set operations supported by Oracle SQL:

UNION ALL

Combines the results of two SELECT statements into one result set.

SQL > select * from <table1> union select * from <table2>

UNION

Combines the results of two SELECT statements into one result set, and then eliminates any duplicate rows from that result set.

SQL > select * from <table1> union all select * from <table2>

MINUS

Takes the result set of one SELECT statement, and removes those rows that are also returned by a second SELECT statement.

SQL > select * from <table1> minus select * from <table2>

INTERSECT

Returns only those rows that are returned by each of two SELECT statements.

SQL > select * from <table1> intersect select * from <table2>

SQL > select emp_id,dept_id from emp where (emp_id,dept_id) in

      (select emp_id,dept_id from emp union select emp_id,dept_id

      From job_histroy)

SQL > select dept_id,to_number(null) as “location”,hire_date from emp union

      Select dept_id,location_id,to_date(null) from dept

External Table

Giving permission to create external tables

SQL > show parameter utl_file_dir

SQL > alter system set utl_file_dir=’*’ scope=spfile – To give permission in server to store in any drive

SQL > startup

SQL > grant create any directory

SQL > create directory <dr> as ‘d:/’

Create text file in D: drive

SQL > create table <name> (empno number,name varchar(10))

Organization external

Type oracle_loader

Default directory <dr>

Access parameter

(records delimited by newline

Badfile ‘xxx.bad’

Logfile ‘xxx’log’

Fields terminated by ‘,’

(empno char

Name char))

Location (‘sample.txt’))

Parallel 5-[To take no. of line parallel]

Reject limit 200 –[Up to 200 rejects]

DATABASE link

To create Database link

SQL > create public database link www.abc.com using ‘<service name>’

To view the created link

SQL > select * from <db>.<table>@<address>

SQl Vs SQLplus*

An language	An environment
Ansi standard	Oracle proprietary
Keyword cannot abbreviated	Can be
Statement manipulate data & table definition	Command do not allow manipulation of value in database
SQLstatement - > SQL buffer	SQLPLUS Not stroed in SQl buffer

SQLPLUS File command

v      Save <filename>

v      Get <filename>

v      Start <filename>

v      @ <filename>

v      Edit <filename>

v      Spool <filename>

v      Exit

PL/SQl

Structure

      Decalre

            ---------

            Variable declaration

            ---------

      begin

            ----------

            ----------

      exception

            ----------

      end

Example to insert using PL/SQl

      declare

            v_sno number

            v_sname varchar(10)

            v_sub1 number

            v_sub2 number

            v_total number

            v_gtotal number

      begin

            v_sno:=&v_sno

            v_name:=&v_name

            v_sub1:=&v_ssub1

            v_sub2:=&v_ssub2

            v_total:=v_sub1+v_sub2

            if (v_sub1 >40 and v_sub2>40) then

                  v_grade:='pass'

            else

                  v_grade:='fail'

      end if;

      insert into table values (v_sno,v_name,v_sub1,v_sub2,v_total,v_grade);

      commit;

      end;

To execute immediately

SQL> /

Example to read using PL/SQl

      declare

            v_total number

            v_gtotal number

      begin

            select total,grade into v_total,v_grade from <table> where sno=001

            dbms_output.put_line(v_total||'      '""v_grade)

      end;

Example to update & delete using PL/SQl

      declare

            v_sno number

            v_sname varchar(10)

            v_sub1 number

            v_sub2 number

            v_total number

            v_gtotal number

      begin

            v_name:=&v_name

            v_sub1:=&v_ssub1

            v_sub2:=&v_ssub2

            v_total:=v_sub1+v_sub2

            update emp set sub1=v_sub1,sname=v_sname where sno=&sno

      commit;

      end;

PL/SQl for coumn & row data type

      declare

            v_sno emp.sno%type

            v_sname emp.name%type

      begin

            select sno,name into v_sno,b_sname from emp

            dbms_output.put_line (v_sno,v_sname)

      end;

PL/SQl for row data type

      declare

            v_row  emp%rowtype

      begin

            for v_row in (select * from emp)

            loop

                  dbms_output.put_line (v_row.sno,v_row.sname)

            end loop;

      end;

For Loop

      declare

            x number

      begin

            for x in 1..10 loop

                  dbms_output.put_lin (x)

            end loop;

      end;

Named block or sub program

1.Procedure

2.Function

Procedure

SQL> create or replace procedure <p1> (sno number, name varchar,p_sub1 number,p_sub2 number) is

v_total number

v_grade varchar(10)

begin

      v_total:=v_sub1+v_sub2

      if (p_sub1>40 and p_sub2>40) then

            v_grade:='pass'

      else

            v_grade:='fail'

      end if;

      insert into <table> values (p_sno,p_name,…….)

commit;

end;

Executing procedure

SQL> exec <procedure name> (argument)

To see the description

SQL> desc <procedure name>

To see the source code

SQL> select text from user_source where name='<procedure>'

Procedure example 2(with in and out parameters

SQL> create or replace procedure <p2> (psno number,p_total out number,pgrade out varchar) is

begin

      select total,grade into p_total,p_grade from table where sno=p_sno;

end;

Note : before executing above procedure we have to declare bind or global variable for out parameter

SQL> variable xx number

SQL> variable yy varchar(10)

SQL> exec <p2> (103,:xx,:yy)

SQL> print xx,yy;

function

SQL> create or replace function <name>(p_sno number) return varchar is

yy varchar(10)

begin

      select grade into yy from table where sno=p_sno;

return y;

end;

To call function

SQL> exec yy:=f1(101)

SQL> print yy

Package

1. Package specification

2. Package body

package specification

SQL> create or replace packgae <name> is

      procedure p_insert (p_no number,p_name varchar)

      procedure p_update (p_no number,p_name varchar)

      procedure p_delete (p_no number,p_name varchar)

 end;

Package body

SQL> create or replace package body pkg_sample is

procedure p_insert (p_sno number,p_name varchar) is

begin

      insert into table values (p_sno,p_name);

commit;

end p_insert

procedure p_update (p_sno number,p_name varchar) is

begin

      update table set name=p_name where sno=p_sno

commit;

end p_update

procedure p_delete (p_sno number) is

begin

      delete from table where sno=p_sno

commit

      commit;

end p_delete

end;

To call package

SQL> exec packagename.p_delete(20);