OracleBlog

The general consensus is that Tom Kyte's "Expert One-on-One Oracle" is the best Oracle book available, and most Oracle professionals have a copy on their shelves.

In Chapter 1 of the first edition, you'll see a scheduling algorithm that is supposed to avoid double-bookings. Here is an example of how to double-book a room:

CREATE TABLE resources (resource_name VARCHAR2(25) primary key);
CREATE TABLE schedules (resource_name VARCHAR2(25) references resources, start_time DATE, end_time DATE);

INSERT INTO resources (resource_name) VALUES ('Room A');
INSERT INTO schedules (resource_name, start_time, end_time) VALUES ('Room A', '01-Jan-04', '04-Jan-04');

VARIABLE new_start_time VARCHAR2(32)
EXEC :new_start_time := '02-Jan-04'

VARIABLE new_end_time VARCHAR2(32)
EXEC :new_end_time := '03-Jan-04'

VARIABLE room_name VARCHAR2(25)
EXEC :room_name := 'Room A'

-- If the count comes back 0, the room is yours
SELECT COUNT (*) FROM schedules WHERE resource_name = :room_name
AND (start_time BETWEEN :new_start_time AND :new_end_time
OR
end_time BETWEEN :new_start_time AND :new_end_time);

This returns 0, which means the room is double-booked!!

Here is the fixed version from the 2nd edition:

ops$tkyte@ORA10GR1> select count(*)
2 from schedules
3 where resource_name = :room_name
4 and (start_time <= :new_end_time)
5 and (end_time >= :new_start_time);

According to Tom, "the only [other] notable fix is with regards to function based indexes where I said erroneously that the to_date() function was "broken" with respect to the YYYY format - it is not (can you see why :)"

Here it is:

CREATE TABLE t (y VARCHAR2(32));

CREATE INDEX t2 ON t(to_date(y,'yyyy'));
ORA-01743: only pure functions can be indexed.

As a workaround in the book, Tom created his own "to_date" function. But there is a far simpler reason why this doesn't work (and a far simpler solution). Even though it was staring us all in the very same pages, not very many people could figure out why this function-based index was disallowed:

"My conclusion in the book is wrong because to_date with the 'yyyy' format is not deterministic."

That means that for the same input, you can get a different output. But how can that be?

ops$tkyte@ORA10GR1> select to_date( '2005', 'yyyy' ) from dual;

TO_DATE('
---------
01-JUN-05

"Today, in june, to_date(2005) returns 01-jun, last month, same function, same inputs - would return 01-may"

Wow. Never would have guessed to_date(year) would be non-deterministic.

Tom also clarified that the following function would be deterministic and therefore eligible for a function-based index:

create index t2 on t( to_date( '01'||y, 'mmyyyy') );

That, of course, would force it to choose January no matter what time of year you called the function.

To me, it is very funny that to_date:
1. IS deterministic if you leave out the day (it will always choose the 1st day)
2. IS NOT deterministic if you leave out the month (it will not choose the 1st month, but rather the current month).

Often in Oracle there is method behind the madness, but I do now know why there is this difference.

So I guess this issue is just another interesting footnote in some nerd's blog...