Monday, January 12, 2009

How to improve the performance of a Correlated query ?

›
How to improve the performance of a Correlated query ? In our regular development life, we come across many queries that has correlated sub ...
1 comment:
Thursday, December 11, 2008

Performance Tuning - When to use Bind Variables, when not to ? When histograms work and when they don't ?

›
The usual nightmare of almost every other Java/Oracle developer ... "ooooh...this query is not coming back...what should I do now? How ...
Wednesday, November 12, 2008

Does the columns of the select list impact the query execution plan ?

›
Proceed further, if you fall into any of these categories : "I am a Java/Oracle developer or a junior DBA. I am not sure what columns s...
Tuesday, November 4, 2008

My most favourite Oracle discussions ...

›
Oracle Magazine Archive On joins and sub queries Why my plans keep changing ? On Deduping and Ordering How to update the multiple columns of...
Thursday, July 3, 2008

Sql Tricks : Min(), Max(), ename for min sal, ename for Max sal in the same row

›
Let's say our requirement is to find out the Min() and Max() salaries of employees in each department with their names. So, the output w...
Wednesday, June 25, 2008

How to generate Trace & TKProf analysis ?

›
Autotrace : - Go to sqlplus and login as scott/tiger. Issue the following command at the sqlplus prompt : sql> set autotrace on set autot...
Thursday, June 5, 2008

Oracle Analytics / Analytic Function: How to avoid “referencing the table twice in queries having aggregate functions”.

›
Database : Oracle 10g Schema : Scott/Tiger Table : emp Analytic Functions syntax : Function(arg1,..., argn) OVER ( [PARTITION BY ] [O...

Oracle 10g : Connect by prior

›
Database : Oracle 10g Schema : Scott/Tiger If you have a hiearchial table, the following generic query presents the data in a hierarchica...
1 comment:
Wednesday, June 4, 2008

Using "case ....end" in order by

›
Schema : scott/tiger Version : Oracle 10g Say your manager stops by and asks you to produce a result like this, from the standard "emp...
Thursday, May 29, 2008

Oracle Analytics / Analytic Functions: first_value()

›
Oracle database version : 10g Schema : Scott Analytic Functions Syntax : Function(arg1,..., argn) OVER ( [PARTITION BY ] [ORDER BY ] [ ] ) O...
1 comment:

Insert with check option

›
One of the ways to validate data while insertion, directly at the database level is : INSERT …. WITH CHECK OPTION Let’s take an example and ...
Tuesday, May 6, 2008

Oracle Analytics/Analytic Functions : How to get number of rows(count ) returned by a given query, in the same query ?

›
Often we, the Java/Oracle developers run into the situation, where we need to find the number of rows (count) returned by a query. You can j...
Wednesday, March 5, 2008

Java/Oracle : Should I Encrypt / Hash the passwords ? What is the difference ?

›
The standard question asked by a Java/Oracle developer. Should I Encrypt OR Hash the passwords ? What is the difference ? The BIG difference...
1 comment:
Tuesday, March 4, 2008

Oracle 10g : PL/SQL : Conditional Compilation

›
If you are a pl/sql developer and if you looking for best practices on unit testing, this is for you. Usually we run into situations where w...

Oracle 10g : Case-Insensitive Searching

›
Say in the famous Scott's "emp" table, I have data like : "Ravi", "rAvi", "RAvi" ...etc.,. So, i...

Oracle 10g : How to pass ARRAYS of records from Java/Tomcat to Oracle

›
Environment : JDK 1.5, Tomcat 5.5, Oracle 10gR2. Let's suppose that you have a web app where you get some records from the user inter...
14 comments:
Saturday, March 1, 2008

Oracle 10g : How to pass ARRAYS to Oracle using Java?

›
Environment : JDK 1.5 (did not test on older versions of java but it should work on them !!) Keep the JDBC driver in the classpath : '...
1 comment:
Friday, February 29, 2008

Oracle 10g: Very useful web sites on Oracle for Developers and DBA's

›
Ask Tom -- Mr. Oracle. Knows everything about Oracle. OracleSponge -- interesting articles, experiments. Good Materialized View info. Rit...

Oracle 10g : Alternative Quoting Mechanism

›
If you are blessed and working on Oracle 10g, I believe this would be a useful tip. Say we have a simple string to select : How's Rav...
1 comment:

Oracle 10g : Use Anti-Join to find unmatched data

›
If you are using Oracle, to find un-matched data between two tables use Anti-Join. That performs better than using not in or not exists. Oke...
1 comment:

Oracle 10g : MODEL Clause

›
The following query gets the sum of units and sum of revenue from all regions and subtracts that from the Worldwide units and revenue to cal...