Tuesday, February 27, 2007
oracle tricky sort
Why rownum works here? Rownum is assigned to the row before any explicite sorting (as a result of ORDER BY clause) is done. But when oracle has to perform an implicit sorting (for example, because of the GROUP BY or merge join) then rownum will be assigned to the presorted result set. For this reason I outer joined the table emp with the small table - the smaller, the better for performace sake. It could be any table, but sys.dual is the most appropriate, as it is ususaly the smallest table and publicly available. The joining condition is irrelevant, since in any case all the rows from the emp will be included in the result set.
What is the purpose of the decode function? The columns emp.sal and dual.dummy are of different datatypes and the direct comparison would result in some ORA-error, complaining about incompatible datatypes. With the use of decode I ensure that the result is allways NULL, which is compatible with all datatypes. So in this particular join with use of decode, it doesn't realy matter if sal is of type numbe, varchar, date,...
Btw, when oracle performs an implicit sort, it allways sorts in ascending order, so my sollution works on BOTTOM_N type of problems. If you wan't to apply it to TOP_N, you must use another trick. For example, we wan't to select 5 records from emp with *highest* salaries:
SQL> SELECT /*+ USE_MERGE */ a.empno, a.ename, a.sal
2 FROM emp a, dual b
3 WHERE -1*sal = DECODE(b.dumMY(+),'X', NULL, NULL)
4 AND rownum <= 5
5 ORDER BY sal DESC
EMPNO ENAME SAL
--------- ---------- ---------
7839 KING 5000
7788 SCOTT 3000
7902 FORD 3000
7566 JONES 2975
7698 BLAKE 2850
5 rows selected.
The trick here is to make oracle perform implicit sorting in ascending order, but on *negative values* of the sorted column, hence the "-1*sal" in the WHERE clause. This results in the descending order of the salary amounts!
What is the purpose of the decode function? The columns emp.sal and dual.dummy are of different datatypes and the direct comparison would result in some ORA-error, complaining about incompatible datatypes. With the use of decode I ensure that the result is allways NULL, which is compatible with all datatypes. So in this particular join with use of decode, it doesn't realy matter if sal is of type numbe, varchar, date,...
Btw, when oracle performs an implicit sort, it allways sorts in ascending order, so my sollution works on BOTTOM_N type of problems. If you wan't to apply it to TOP_N, you must use another trick. For example, we wan't to select 5 records from emp with *highest* salaries:
SQL> SELECT /*+ USE_MERGE */ a.empno, a.ename, a.sal
2 FROM emp a, dual b
3 WHERE -1*sal = DECODE(b.dumMY(+),'X', NULL, NULL)
4 AND rownum <= 5
5 ORDER BY sal DESC
EMPNO ENAME SAL
--------- ---------- ---------
7839 KING 5000
7788 SCOTT 3000
7902 FORD 3000
7566 JONES 2975
7698 BLAKE 2850
5 rows selected.
The trick here is to make oracle perform implicit sorting in ascending order, but on *negative values* of the sorted column, hence the "-1*sal" in the WHERE clause. This results in the descending order of the salary amounts!
Thursday, February 22, 2007
bind variable and shared pool relationship
When I was trying to diagnose a problem in ADO, where a 'group-by' query fails after CURSOR_SHARING is set to FORCE.
I read a wonderful article on bind variable/shared pool in oracle. It made me realize why the programmers are not a database developer.
See more at:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:528893984337
I read a wonderful article on bind variable/shared pool in oracle. It made me realize why the programmers are not a database developer.
See more at:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:528893984337
Labels: oracle
