When like is not what you like

I’ve been always using PreparedStatement in JDBC query operations for two main reasons:

• it manages for you all data translations between types
• it automatically calculates the best query path to recover your data

Unfortunately there’s a little canvas you should know about: we know, like is evil, but when you use like in a query using a PreparedStatement, data path is calculated before having information about what you would like, so resulting query is very unoptimized! In such cases you should proceed handling like parameters with string composition/substitution, before creating PreparedStatement object.

That’s not a JDBC or database fault, it’s just how things work. But you should be aware of it…

Oracle Flashback!

In these days I’m struggling (studying? fighting? googling?) with Oracle recovery system. I’m trying to recover a database to a precise moment in time, using “recover database until time” clause. Well, apart the fact I’m not able to make it work while two weeks passed along (!!!), today I’ve discovered an interesting functionality, available since version 9i, called Oracle Flashback. It allows you to see a consistent view of the database as it was at a point in the past: while in 10g version is quite simple to use, in 9i it’s a little bit weird , but it works.

Imagine you have a table called “project” created by the user “test”: someone kindly executed such command: “DELETE FROM PROJECT” (oops!). All your data is lost! (yeah, he was also in autocommit!). What can you do? No panic, simply connect as system/yourpassword and type:

EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME(TO_TIMESTAMP(‘2006-09-05 11:00:00′,’YYYY-MM-DD HH24:MI:SS’));

SELECT * FROM TEST.PROJECT;

All your stuff is there! Unfortunately, in readonly mode, but you can export and reimport data easily. Then, remember to type:

EXECUTE DBMS_FLASHBACK.DISABLE;

Just to get out from flashback mode Hope this helps!