Tag: sql

Translating SQL (a migration tool)

When you are moving from another database to Oracle, in a perfect world, you’d set aside plenty of time to refactor all of your code and re-engineer your applications to get the maximum benefit out of the new database technology.  But there are not…

Locked rows and lunch breaks ? A simple fix

Even in the new world of stateless web applications, from time to time we still come across the “lunch break locking” problem.  That is, someone or something, commences a transaction which locks some rows, and then their session goes idle, without committing or rolling…

Transaction subtleties

This came in from an AskTom question recently, and I thought it worth a blog mention because it could easily catch people out. Lets set the scene with a simple procedure that commences a transaction, but then always fails SQL> drop table test$tab purge;…

The simple fix to date queries

We had question in the OpenWorld panel about why queries on date columns are “always slow”.  Well….they aren’t but here’s a common cause of that misconception. Let’s create a table as a copy of DBA_OBJECTS, and index the CREATED column (which is  a date)….

LAG / LEAD quick tip

As most of us know, with LAG and LEAD or more generally, any analytic function that may extend “past” the boundary of window it is operating on, you can get null as a result. Here’s a trivial example SQL> create table t as 2…

Granular detail from a summary

We had an interesting question on AskTom a few days ago.  Given a set of 12 values (forecasts in this case), one for each month of the year , can we manufacture a set of weekly forecasts for the same period.  Now it is…

Parsing … no big deal eh ?

Most of us have probably seen the standard demo when it comes to emphasizing the need for sharable SQL, aka, using bind variables where appropriate.  The demo traditionally compares two similar scripts, where one of them generates a lot of SQL statements with literals,…

12c FETCH PERCENT

A nice little feature in 12c is the FETCH FIRST n ROWS syntax, which is a simple shorthand to avoid using inline views and the like to get a subset of the rows from what would normally be a larger resultset. Here’s a simple…

SQL statements using literals

16 years ago, someone “Ask-ed Tom” how to find those SQL statements that were not using bind variables.   You can see the question here (because we don’t delete stuff ever ) but I’ll paraphrase the answer below: Tom took the following approach take a…

MERGE vs UPDATE/INSERT revisited

I wrote a few years back that for single row operations, MERGE might in fact have a large overhead than the do-it-yourself approach (ie, attempt an update, if it fails, then do an insert). Just to show that it’s always good to revisit things…

Datatypes for DATES

Richard Foote has written a post about not using the DATE datatype for storing dates. So I’ve come up with a revolutionary system to store dates as well…using the very efficient RAW datatype. Here’s a demo SQL> create table t ( x raw(7) );…

Those pesky dates as strings

You might be thinking “Nothing is more frustrating that encountering a string column that is full of dates”. But there is something worse than that…and that is, when that string column is full of potential dates, and your job is to sift out the…