hotnesjpg

Ten questions every Oracle PLSQL Developer should know

Well, if you’re a PLSQL Developer like me, people probably expect a lot of performance and quick solutions from you. These ten topics I’ll present will probably be like “Oracle trivia” for you. But hopefully one of them will surprise you!

1 – What’s the difference between a procedure and a function?

This is an easy one. A function always returns a value to the caller, but the procedure doesn’t.

2 – What’s the difference between truncate and delete ?

The most obvious one is performance ! But there are other differences. TRUNCATE is much faster than delete because it doesn’t allow for rollbacks to occur, and automatically commits data when finishes.Also truncate doesn’t fire any trigger, so if there is a trigger on your table, TRUNCATE won’t fire it but DELETE will. Last but not least, you cannot use the WHERE clause on TRUNCATE.

3 – How to select the Nth most lowest value from a table ?

Actually, there a couple of solutions out there on the web. So nothing better than run some tests to find out the quickest one ! We’ll use the HR schema from Oracle 11g to find out!

Solution 1 (doesn’t work !!)

select level, min(salary)
from employees
where level=2
connect by prior salary < salary
group by level;

Let’s look at the explain plan…

I’ve tried this solution…and 18 minutes after it didn’t produce any result ! So I would discard it…

Solution 2(works !!)

select rownum rank, first_name, salary  from
(select first_name, salary from employees order by salary asc)
group by rownum, first_name, salary
having rownum = 2;

Let’s look at the explain plan…

This is a very quick and dirty one…Yes it’s very very fast(only 0.016 seconds) , but can we do better ?

Solution 3 (works!!)

select *  from
  (select first_name,salary,rank() over(order by salary desc) emp_rank
  from employees)
where emp_rank = 2   and rownum = 1;

Let’s look at the explain plan…

 

I really like analytical functions but this solution took twice the time as solution 2, finishing in 0.032 seconds.

So, I would choose solution 2!

For more info:

http://stackoverflow.com/questions/80706/query-to-find-nth-max-value-of-a-column
http://forums.oracle.com/forums/thread.jspa?messageID=9295439&#9295439
http://aspalliance.com/1557_CodeSnip_Selecting_nth_Highest_Value_of_a_Column_Using_Oracle.all


4 – What’s the difference between an implicit and an explicit cursor ?

Another easy one! Well even if at a first glance you might not know. But, if you think a little, you will certainly recall that, whenever we issue an INSERT,DELETE,UPDATE or single row SELECT INTO, Oracle creates a implicit cursor to deal with this operation. I already talked a bit about it on this post.

An explicit cursor, is a cursor in which the cursor name is explicitly assigned to a SELECT statement via the “CURSOR … IS”  statement.

5 – How to use the FOR UPDATE and the WHERE CURRENT OF clauses?

Well, I’m sure there is much to be said on this one, nevertheless I’ll  stick to the basics.   If you ever wanted to manually change  a range of values in a table you can try and issue a query like this one :

SELECT * FROM employees
FOR UPDATE

and then your in PLSQL Developer you could manually do it.  This query would select all rows from table employees, and lock them. This means that the resultset you are seeing can only be updated by you!  If some other user issued a DML statement on the employees table, the operation would wait until you either issued a commit or a rollback.

The WHERE CURRENT OF clause is of great use if we want to delete or update a statement that was referenced by the SELECT…FOR UPDATE. To understand better how this works issue the following query


  SELECT first_name ,employee-_id
  FROM employees
  WHERE employee_id in (198, 199)
  FOR UPDATE of first_name

This query would return first “Donald”  with id 198 and then “Douglas” with id 199 . Imagine you wanted to update/delete the name of the first of these results. You could write an anonymous PLSQL block like this :


DECLARE
   CURSOR c1 IS
         SELECT first_name, employee_id
         from employees
         where employee_id in (198, 199)
    FOR UPDATE of first_name;

 fname  employees.first_name%TYPE;
 emp_id employees.employee_id%TYPE;

BEGIN

  open c1;
    fetch c1 into fname, emp_id;

    UPDATE employees
    SET first_name = 'Elon'
    WHERE CURRENT OF c1;

    COMMIT;

 close c1;
end;

The above PLSQL block would change the name of “Donald” to “Elon” .

For more info:

http://www.techonthenet.com/oracle/cursors/current_of.php
http://www.geekinterview.com/question_details/4649
http://sql-plsql.blogspot.com/2007/05/oracle-plsql-where-current-of-for.html

6 – What’s the difference between CLOB and BFile?

The Oracle BFile type, is used to store unstructured binary data outside of the database in the operating system. BFiles are read-only and only support random-reads.

CLOB type is stored in the database using two byte code character set. Unlike BFile, CLOB support sequential access and can be modified.

For more info:

http://download.oracle.com/docs/cd/B10501_01/server.920/a96524/c13datyp.htm#13754

7 – How many long columns are allowed in a table?

Another easy one. Since Oracle 8i, it is recommended not to use this type. Why ? Just have a look at some of it’s restrictions :

  • Only one column of type LONG is allowed
  • LONG columns cannot appear in WHERE clauses or in integrity constraints (except that they can appear in NULL and NOT NULL constraints).
  • LONG column can’t appear in GROUP BY, ORDER BY clause, UNIQUE / DISTINCT operator or CONNECT BY clause in SELECT statements.

And you can find lots of more reasons here.  So if by any reason you think you need this datatype, have a look at other options.

8 – Which is faster, “IN” or “EXISTS” ?

Well, the “EXISTS” predicate is always faster because “EXISTS” returns a boolean value, whereas “IN” returns a value.

9 – How do you find the number of rows in a table ?

Actually there are several ways to achieve this. The first most obvious one would be to issue a statement like :

select count(rowid)
from employees e

This would return the number of rows in the employees table. Another way to get this information would be to query the system catalog USER_TABLES. To do it, you could issue

ANALYZE TABLE employees COMPUTE STATISTICS;

This would generate statistics for the employees table. Then we could issue

select ut.TABLE_NAME, ut.NUM_ROWS
from user_tables ut
where ut.TABLE_NAME like 'EMPLOYEES'

For more info:

http://www.thewellroundedgeek.com/2007/09/most-people-use-oracle-count-function.html
http://www.oracle-base.com/articles/8i/CostBasedOptimizerAndDatabaseStatistics.php

10 –  What’s the  DUAL table ?

The DUAL table is a table that Oracle creates with it’s data dictionary consisting in exactly one row. DUAL belongs to the SYS user but all users can query it , and use it’s unique property of returning only one row, to select pseudo-columns .

For more info:

http://www.adp-gmbh.ch/ora/misc/dual.html
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1562813956388

Conclusion

Hope you found these questions a good way to refresh your Oracle SQL and PLSQL knowledge. Feel free to comment and leave further suggestions !!

Cheers,
Carlos