May 052014
 

There’s a neat optimization in Oracle I found while tinkering around (look closely at the predicate section):

select oracle_can_now_use,
       a_function_based_index
from   queries_with_no_functions
where  a_date_col_with_an_fbi = :a_date
and    oracle_version >= '11.2.0.2';

-------------------------------------------------------------------------
| Id  | Operation                   | Name                      | Rows  |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                           |     1 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| QUERIES_WITH_NO_FUNCTIONS |     1 |
|*  2 |   INDEX RANGE SCAN          | QUWF_DATE_FBI             |     1 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ORACLE_VERSION">='11.2.0.2' AND "A_DATE_COL_WITH_AN_FBI"=:A_DATE)
   2 - access(TRUNC(INTERNAL_FUNCTION("A_DATE_COL_WITH_AN_FBI"))=TRUNC(:A_DATE))

Line 2 of the predictates states that the INDEX RANGE SCAN was accessed via TRUNC(INTERNAL_FUNCTION()) – a function-based index was used when no function is present in the where clause!

This optimization appeared in 11.2.0.2 (fix 9263333) – I have to thank the people of Oracle-L (particularly Velikikh Mikhail) identifying when this improvement came in.

This optimization isn’t restricted to TRUNC() on date columns however.
Continue reading »

Jan 312014
 

An SQL magic trick – the table disappeared, just by running a select statement! Can you figure out how it’s done?

select * 
from   the_disappearing_table_trick
/

NOW_YOU_SEE_ME
--------------
Now you don't

--....

select * 
from   the_disappearing_table_trick
/

ORA-00942: table or view does not exist

If you think you know, put your answers in the comments!

Nov 112013
 

If you work with clients with in different timezones, be wary of the CURRENT_DATE/CURRENT_TIMESTAMP functions:

select (current_date - sysdate)
         as greater_than_zero
from   your_database
where  dbtimezone <> sessiontimezone;

There’s posts timezone issues in more detail from Jonathan Lewis and Tony Hasler. For a quick example script, keep reading.
Continue reading »

Nov 012013
 
set sqlterminator ?

select queries
from   your_database?

QUERIES
----------------
Become questions

set sqlterminator !

select queries
from   the_database!

QUERIES
--------------------
Become exclamations!

You need to run in SQL*Plus to use this. I can’t think of a particularly good use – other than playing tricks on your colleagues! ;)

Sep 232013
 
select an_easier_way, 
       to_adjust_for_daylight_savings
from   data_stuck_in_utc_time
where  case 
  when utc_datetime between 
    --clocks forward and back
    next_day(last_day(
      add_months(
        trunc(daylight_savings_datetime, 'y'),
        2)) - 7, 'sunday') and
    next_day(last_day(
      add_months(
        trunc(daylight_savings_datetime, 'y'),
        9)) - 7, 'sunday') 
  then 
    utc_datetime+1/24 -- an hour
  else 
    utc_datetime
  end = daylight_savings_datetime;

AN_EASIER_WAY
----------------------------
There is
TO_ADJUST_FOR_DAYLIGHT_SAVINGS
---------------------------------------
utc_datetime+
  extract(timezone_hour from 
          daylight_savings_datetime)/24
A classic convoluted build your own solution to a problem Oracle's 
already solved for you; figuring out the daylight savings offset 
for a particular date. This is the calculation for the UK (daylight 
savings starts the last Sunday in March and ends the last Sunday in 
October), but it varies in some countries. Must easier to have a 
timestamp with time zone and just extract the hour offset from it!

If you’d like a complete script showing this, keep reading.
Continue reading »

Sep 062013
 
select work_quality
from   me
where  sleep < 'one picosecond'
and    coffee_consumed >= 
           'the contents of the pacific'
and    dealing_with_baby_vomiting 
           like 'the exorcist';

WRK QUALTY
-------------------------------------
Full of speeling erors and zzzzzz.... 

.... *gasp* I'm awake, honest!
Aug 302013
 
select what_i_what
from (
  select *
  from   job_candidates
  where  skills = :the_job_description
  and    personality like:able
  order  by (ability+job_fit) desc
)
where rownum <= 1;

WHAT_I_WANT
---------------------
The perfect candidate

While I'm sure there's hundreds of databases around the world with job seekers in, 
I doubt any are complete enough for this query to find the right people. I guess 
we're stuck with reviewing CVs and interviews until then