How to change an outerjoin query to one without

Some time ago I encountered an issue with an outerjoin query. Although the execution plan was not that bad the respons time was really bad. I found out that the outerjoin in the query was causing the biggest problem. After doing some easy research I checked out the performance of a query with a direct join (a.col = b.col) and one without a join (a.col is not joined). Even if you would execute them seperate you would be much faster as doing the outerjoin. This brought me to the idea of doing these two queries for the sake of the data be retrieved by the outerjoin. By doing a union between these two queries and to get rid of the double records, I would have the same result as with the outerjoin. This is what I did, a collaegae of mine Jorrit Nijssen changed the code to a emp/dept example (thanks Jorrit). The base case looks like: Continue reading

ORA-01422 while drop table (even DUAL looks fine)

Today on the OTN forum a question was comming up about an ORA-01422, in combination of a table drop.

SQL> drop table xxx
2 /
drop table xxx
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows

Due to the fact that this looked a bit weird, I did a small test on my database. The following test case I did: Continue reading