Tuesday, October 14, 2008

More about EXISTS and IN

This is a very useful link that will definitely answer your questions regarding whether to use EXISTS or IN on your SQL statements.


http://asktom.oracle.com/pls/asktom/f?p=100:11:3263397840860471::::P11_QUESTION_ID:953229842074

Basically the rule of thumb is:

Small query, large subquery = EXISTS
Large query, small subquery = IN
Large query, large subquery = either

Friday, September 26, 2008

Oracle Spool command

The Oracle Spool command is a nice little feature that allows you to record what goes on in your sqlplus session on the fly. Enabling it is simple and can be a very good logging/debugging tool.

To record your sqlplus session commands, simply type:

SQL> spool /tmp/myfile.txt

where myfile.txt is the name you want to give to your log file.
The file will not appear or be usable until you turn off spooling:

SQL> spool off


Voila!

Monday, September 8, 2008

EXISTS is your friend

I recenty came across a problem that required me to retrieve data from a table based on certain criteria. I decided to use the SQL 'IN' condition. This, as I learned later, was a poor choice given the fact that the subquery in the 'IN' clause returned several hundred thousand records. 

My query construct was the following: 

SELECT foo, bar FROM table1 
WHERE foo IN (SELECT foo FROM table2 WHERE condition1 AND condition2)
AND bar IN (SELECT bar FROM table2 WHERE condition1 AND condition2);

Each of those two subqueries returned ~260,000 records, which, needless to say, took forever and a day to complete. I later discovered that the IN clause should mostly be used for small result sets, basically for when the subquery returns less than 1024 records. For result sets as massive as the ones I was getting, it is better to use the EXISTS clause. My query above could easily be modified to use EXISTS in the following way:

SELECT table1.foo, table1.bar FROM table1
WHERE EXISTS (SELECT table2.foo, table2.bar from table2 
                                WHERE  table1.foo = table2.foo 
AND table1.bar = table2.bar
AND condition1
AND condition2);

As you can see, you have to join the two tables being used in order for it to work, otherwise EXISTS will return true and the query will just return the results of the master query.

Remember, when dealing with large result sets, EXISTS is your friend!

Thursday, July 24, 2008

Random numbers in Oracle

Oracle provides a set of neat functions to generate and manipulate random numbers and strings (Yes, random strings!). Here's an example of the generation of a random number:

select dbms_random.random from dual;

Result: 688382086

The following example allows you to limit the generated number between 1 and 1000:

select dbms_random.value(1,1000) num from dual;

Result: 800.627594089089637807565678078832430488

Yikes! This number has a gazillion digits after the decimal point. So let's round it up:

select round(dbms_random.value(1,1000),0) num from dual;

Result: 709


Conclusion:
Random number generation is a useful tool that can have many applications in your SQL code.

A more complete list of things you can do with this is here:
http://www.databasejournal.com/features/oracle/article.php/3341051

Wednesday, June 4, 2008

Chuck Norris Facts

A lot of people with time on their hands have been adding "Chuck Norris facts" to this ongoing list, which contains several things Chuck Norris can do that you can't.

This is some serious hilarity:

'Chuck Norris’ hand is the only hand that can beat a Royal Flush.'

See the rest at http://chucknorrisfacts.com/

Tuesday, June 3, 2008

Connecting to an Oracle database using JDBC

Oracle has a proprietary JDBC driver that allows for native Java connections to an Oracle Database. First, you must download Oracle's JDBC Driver here and then include the jar archive into your project.

The code to connect to the database would be as follows:

public void createDBConn() {
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@//server.local:1521/prod";
Connection conn = DriverManager.getConnection(url,"scott", "tiger");
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery("select BANNER from SYS.V_$VERSION");

while (rset.next()) {
System.out.println (rset.getString(1));
}
stmt.close();
System.out.println ("Ok.");
}

Thursday, May 29, 2008

Select INTO!

When coding in PL/SQL, remember that if you want to add a query's returned value into a variable, you must use the format:

SELECT value INTO variable FROM table
/*The rest of the query goes here*/

I made the mistake of doing it this way:

variable = SELECT value FROM table ...

Don't make the same mistake I made!

Query to display duplicate values

This query will display how many times a certain value occurs in a table.

SELECT a, COUNT(a) AS numoccurences FROM table WHERE a IS NOT NULL
GROUP BY a;

It's useful to create a view with this query and then use the values in other queries.

Oracle Database Schema Viewer

Schemester is a sweet little tool that allows Oracle database schema discovery in case you don't have the database diagram handy (which is usually the case). After connecting to the database through Schemester, you'll have a list of all the tables in the database, and you're able to drag a table into the "page" and you'll have a diagramatic representation of this table, displaying all its columns and appropriately identifying keys. Awesome!

http://www.download.com/Schemester/3000-2210_4-10225039.html

Wednesday, May 28, 2008

PL/SQL on Oracle 9i

I came across a problem or two at work in which I was forced to use PL/SQL commands to solve it. I researched a bit and found some cool information that'll help those of us that are a bit rusty on Oracle and need a refresher on PL/SQL.

Here's a pretty good tutorial for creating and using PL/SQL cursors.

http://www.exforsys.com/tutorials/oracle-9i/oracle-cursors.html

A good explanation of several PL/SQL commands:

http://infolab.stanford.edu/~ullman/fcdb/oracle/or-plsql.html#variables%20and%20types

The PL/SQL FAQ

http://www.orafaq.com/wiki/PL/SQL_FAQ

Thursday, May 22, 2008

Self-modifying code

You can do awesome things by using Java's Reflection APIs and combining it with the Javac and JVM utility classes. You can make objects of the Java compiler and compile and run code AT RUNTIME. I'll update this later.

Monday, February 11, 2008

Ruby resources

I've pretty much spent this past week learning Ruby, and here are some rather useful links to get you up to speed:

Ruby official page
http://www.ruby-lang.org/en/

Ruby Standard Library Reference
http://www.ruby-doc.org/stdlib/

Ruby Core Reference
http://www.ruby-lang.org/en/documentation/

Ruby Learning tutorials - Excellent tutorials that include code samples that are ready to run.
http://rubylearning.com/satishtalim/tutorial.html