To Commit or not to commit.

COMMIT statement in oracle since ages marks the end of transaction and makes the changes permanent in the database.

We can use this statement as much we like in the code and it won't tell us it is wrong, it is perfectly legitimate.







Consider a 10 row insert operation I can have a for loop iterate insert one record at a time and commit it. or I can have all the records commited at one go after the end loop.



Which one is better?



Commit makes your changes permanent in the database in other words it has to write to physical storage, via sync write to a file, when a commit is issued. This will cause a process to wait for the I/O.



In simple words when we learn in our older days batch processing speeds up the tasks , the rule still stays committing 10 records in a batch is much faster than issuing a commit after every transaction.



Frequent commit can also lead to the dreadful ora 1555 snapshot too old error.

How :

Well commits basically releases the space in undo , it frees the space up in undo segment. This freeing up the space might look good , but it is not. It is very unacceptable for our long running queries. where the read consistent view at the point of time the query started may have been lost as the undo records are missing from the segment. So our query won't find the data it needs.
 
Summary perform commit only when your transaction ends i.e. you are done with all logical update delete inserts for the specific logical task you need to perform.

Full table scans.

Full table scans are usually performed when there's no index on the column you are querying , obviouly in a table having large no of rows this would be costly operation.

But Full table scans are not always bad.

They are good when.
1. You are joining 2 tables and one of the table is very small. Ex. Students and courses the index scan of the course table would not be sensible in this case as the table can be read in one go taking all blocks in memory
2. Even on a large table if we are fetching more than 20% of data a full table scan won't be a bad option.

How to instruct oracle to do a full table scan ?

Use a hint /*+ FULL() */

Ex. Select /*+ FULL(dept) */ e.name , d.dept  from emp e , dept d
    where e.dept_id=d.dept_id
    and dept_name='ACCOUNTS';


You can go further and choose to cache the 2nd small table with cache hint.


Instructs the optimizer to place the blocks retrieved for the table at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This hint is useful for small lookup tables.

/*+ CACHE([@queryblock] ) */

Refer PSOUG for more compiler hints.

Nth Highest queries. Or Find Nth Row.

Many a times we are asked to find the nth highest row depending on some criteria.

Here I am trying to compile all the shrewd and crude ways to find the 3rd Highest salary from our employees table.

hr@XE> select * from (select * from employees order by salary desc)
  2  where rownum < 4
  3  Minus
  4  Select * from (select * from employees order by salary desc)
  5  where rownum<3;

Notice that this may not produce the exact 3rd highest salary we should use distinct in that case.

select * from employees e
 where 3=(select count(distinct salary) from employees e1 -- Now this should return the 3rd highest.
          where e.salary<=e1.salary);

Using 10G analytics ---
select * from
(select e.* ,dense_rank() over (order by salary desc) rank_Sal
 from employees e)
where rank_sal=3 -- If you don't want to same ranks for two consecutive same salary values.              -------------------------ROW_NUMBER can be used.



The simplest (no same ranks for the same salary)

select * from (select rownum rank,e.* from employees e order by salary desc)
where rank=3;

Dare to use cast.

Have you ever wanted to convert one data type to other.

Most of the times we would rely on oracle's ability to implicitly convert them for you.

But if you want it fool proof take the task in your hand .

Here's How it's done.

CAST converts one built-in datatype or collection-typed value into another built-in datatype or collection-typed value.

You can cast an unnamed operand (such as a date or the result set of a subquery) or a named collection (such as a varray or a nested table) into a type-compatible datatype or named collection. The type_name must be the name of a built-in datatype or collection type and the operand must be a built-in datatype or must evaluate to a collection value.

Examples :

REP-1814 - Report cannot be formatted

Another nasty error.

This one is hard to workaround particularly when you have complex report structure like several repeating parent child frames and anchoring objects.

If you are lucky then simplest form of error might just mean that the object pointed out in error is going out of page boundary.

If this does not help in resolving then analyze for following possible causes.
1.Find the anchoring objects which are forced to print on the same page but due to size constraints are not allowed to. Try removing the page protect and set the frame object properties to variable and see if error is resolved.

2.Try deleting the object . if the error now moves to another field then it may the case of inconsistent repeating frames. Set all frames "Print object on" to first page and "Base printing on" to enclosing object.

If even that does not help start over :-))
Just kiddin...

REP - 1213 frequency below its group

Allright folks thumbs up here comes our first KED entry for newbies.

REP - 1213 frequency below its group

Now this can be very annoying error to newbies. You can not just put the fields in repeating frames where they don't belong to.
If you have created 2 or more groups in a query and try to put field of say group1 , in a repeating frame of  group2 which lets assume is top level group. You invited this error.

Here lets take an example.

Refer a figure you want your employee table report grouped by department_id and manager_id.

But you only take department id in the upper group and manger id is still in lower group.

While you are creating a parent frame with these 2 fields , you get an error we are talking about just before you run the form

Remedy is simple you have to pull the manager id in upper group. Now real life scenarios wont be that easy. But basics and facts are the only things we should stick to.

Hope I am not confusing though.

The First Post.

Hi Everyone.
I was just thinking whether we should make our blog public or not , may be it is good to maintain a vicinity of our own.
But oh lot of our reusable asset and like projects go fail just because people have to create and maintain accounts and passwords for every darn innovative idea they come with.

Please don't mention the corporate logos and enterprise names here. Lets keep it simple we read and post only technical stuff.

So I made the blog public and also made sure that anybody can comment on the blog.

Hope it serves as a good visiting point then. Happy blogging.