Oracle Pseudo columns

Pseudo columns: Automatically filled by oracle. 

 Ex: sysdate, nextval, currval, rowid, rownum, level,sqlcode, sqlerrm, new, old.ORA_ROWSCN

Rowid:

It is an unique value

It is automatically assigned with every row inserted into table.

It is stored permanently in database

It is an 18 Bit Hexa decimal value.

It comprises of Object id, Data file id, Block id & Record id.

select rowid,dname,loc from dept;

select rowid,empno,ename,sal from emp;

AAA    001

AAB    002                       

AAC    003

AAD    004

AAE    005

Removing Duplicate records :

SQL>Delete from emp where rowid not in

          ( select min(rowid) from emp group by empno);

Retrive all rows except Last Record

  SQL> select empno,ename,sal,deptno from emp

              where rowid not in

             (select max(rowid) from emp);

Update  all rows except First row in Table.

 SQL>Update emp set sal = sal + 2500 where rowid not in

   (select min(rowid) from emp);

Rownum :

It is an unique value

It is an dynamic value automatically retrieved along with Select statement output.

It is only for display purpose.

It is not stored in database.

SQL >select rownum,ename,sal from emp;

SQL >select rownum,dname,loc from dept;

Retrieving Top 5 Highly paid Employees

SQL > select rownum,empno,ename,job,sal from

            (select rownum,empno,ename,job,sal from emp

            order by sal desc )

            where rownum <= 5;

Retrieving  Nth maximum salaried employ details (2 max,...)

SQL > select rownum,empno,ename,job,sal from

          (select rownum,empno,ename,job,sal from emp

          order by sal desc )

          group by rownum,empno,ename,job,sal

          having rownum = &N;

Retrieving Alternate rows

SQL>select rownum,empno,ename,job,sal from emp

group by rownum,empno,ename,job,sal

          having mod(rownum,2) = 0; -- EVEN Rows

          [ having mod(rownum,2) != 0; ] -- ODD Rows

Inline view: Select statement provided in place of table name is known as Inline view.

Level :

It will arrange the select statement output in Inverted tree structure ( Hierarichal Tree ) and gives the position of row in Tree. ( Returns Number )

Retrieving the Hierarichy of employees based on their Superior.

SQL >Select Level,empno,ename,sal,job,mgr from emp

 connect by prior empno = mgr start with mgr is null

 order by Level;

Retrieving Nth maximum salary using Level.

( * Duplicates are eliminated )

SQL >Select Level,max(Sal) from emp where Level = &N

           connect by prior sal > sal group by Level;

SQL Tuning

Sql statements are used to retrieve data from the database. We can get same results by writing different sql queries. But use of the best query is important when performance is considered. So you need to sql query tuning based on the requirement. Here is the list of queries which we use reqularly and how these sql queries can be optimized for better performance.

Sql tuning/sql optimization techniques:       

The sql query becomes faster if you use the actual columns names in select statement instead of than '*'.

For example: write the query as      

Select id, first_name, last_name, age, subject from student_details;

Instead of:

Select * from student_details;

Having clause is used to filter the rows after all the rows are selected. It is just like a filter. Do not use having clause for any other purposes.

For example: write the query as

 Select subject, count(subject)

From student_details

Where subject != 'science'

And subject != 'maths'

Group by subject;

Instead of:

 Select subject, count(subject)

From student_details

Group by subject

Having subject!= 'vancouver' and subject!= 'toronto';

 Sometimes you may have more than one subqueries in your main query. Try to minimize the number of subquery block in your query.

 For example: write the query as

 Select name

From employee

Where (salary, age ) = (select max (salary), max (age)

From employee_details)

And dept = 'electronics';

 Instead of:

 Select name

From employee

Where salary = (select max(salary) from employee_details)

And age = (select max(age) from employee_details)

And emp_dept = 'electronics';

Use operator exists, in and table joins appropriately in your query.

 A) usually in has the slowest performance.

 B) in is efficient when most of the filter criteria is in the sub-query.

C) exists is efficient when most of the filter criteria is in the main query.

For example: write the query as

 Select * from product p

Where exists (select * from order_items o

Where o.product_id = p.product_id)

 Instead of:

 Select * from product p

Where product_id in

     (select product_id from order_items

 Use exists instead of distinct when using joins which involves tables having one-to-many relationship.

 For example: write the query as

 Select d.dept_id, d.dept

From dept d

Where exists ( select 'x' from employee e where e.dept = d.dept);

 Instead of:

 Select distinct d.dept_id, d.dept

From dept d,employee e

Where e.dept = e.dept;

 Try to use union all in place of union.

 For example: write the query as

 Select id, first_name

From student_details_class10

Union all

Select id, first_name

From sports_team;

 Instead of:

 Select id, first_name, subject

From student_details_class10

Union

Select id, first_name

From sports_team;

Be careful while using conditions in where clause.

 For example: write the query as

 Select id, first_name, age from student_details where age > 10;

 Instead of:

Select id, first_name, age from student_details where age != 10;

Write the query as

 Select id, first_name, age

From student_details

Where first_name like 'chan%';

 Instead of:

 Select id, first_name, age

From student_details

Where substr(first_name,1,3) = 'cha';

Write the query as

Select id, first_name, age

From student_details

Where first_name like nvl ( :name, '%');

 Instead of:

 Select id, first_name, age

From student_details

Where first_name = nvl ( :name, first_name);

Write the query as

 Select product_id, product_name

From product

Where unit_price between max(unit_price) and min(unit_price)

 Instead of:

 Select product_id, product_name

From product

Where unit_price >= max(unit_price)

And unit_price <= min(unit_price)

Write the query as

 Select id, name, salary

From employee

Where dept = 'electronics'

And location = 'bangalore';

 Instead of:

 Select id, name, salary

From employee

Where dept || location= 'electronicsbangalore';

Use non-column expression on one side of the query because it will be processed earlier.

Write the query as

 Select id, name, salary

From employee

Where salary < 25000;

 Instead of:

 Select id, name, salary

From employee

Where salary + 10000 < 35000;

Write the query as

 Select id, first_name, age

From student_details

Where age > 10;

 Instead of:

 Select id, first_name, age

From student_details

Where age not = 10;

 Use decode to avoid the scanning of same rows or joining the same table repetitively. Decode can also be made used in place of group by or order by clause.

 For example: write the query as

 Select id from employee

Where name like 'ramesh%'

And location = 'bangalore';

Instead of:

          Select decode(location,'bangalore',id,null) id from employee

Where name like 'ramesh%';

To store large binary objects, first place them in the file system and add the file path in the database.

 To write queries which provide efficient performance follow the general sql standard rules.

Use single case for all sql verbs

Begin all sql verbs on a new line

Separate all words with a single space

Right or left aligning verbs within the initial sql verb