date or timestamp literals
Yesterday I commented on a post on the French developer forums http://www.developpez.net about using DATE and TIMESTAMP literals.
A date literal is DATE '2000-01-01'. It is not possible to have a date literal at 6am, like DATE '2000-01-01 06:00:00', the syntax simply does not exist. You can either use TO_DATE function, or if you are really hooked on litterals, maybe DATE '2000-01-01'+INTERVAL '+0 06:00:00' DAY TO SECOND.
One suggestion was to use TIMESTAMP, because TIMESTAMP literals do have time in it.
However, comparing a date with a timestamp will result in an implicit conversion to timestamp, and if the date colum is indexed, the index will not be used.
SQL> create table lsc_t(x number, d date)
Table created.
SQL> create index lsc_i on lsc_t(d)
Index created.
SQL> insert into lsc_t(d) values (to_date('2000-01-01 06:00:00',
'YYYY-MM-DD HH24:MI:SS'))
1 row created.
SQL> select * from lsc_t where d>=timestamp '2000-01-01 06:00:00'
X D
---------- -------------------
2000-01-01 06:00:00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer Mode=CHOOSE
1 0 TABLE ACCESS FULL TGPOWNER.LSC_T
SQL> select * from lsc_t where d>=to_date('2000-01-01 06:00:00',
'YYYY-MM-DD HH24:MI:SS')
X D
---------- -------------------
2000-01-01 06:00:00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer Mode=CHOOSE
1 0 TABLE ACCESS BY INDEX ROWID TGPOWNER.LSC_T
2 1 INDEX RANGE SCAN TGPOWNER.LSC_I
As shown above, the index is not used when comparing to timestamp.
Why does Oracle convert the date to timestamp ? Because timestamp may contain nanoseconds.
SQL> select * from lsc_t where
d>=timestamp '2000-01-01 06:00:00.000000001';
no rows selected
SQL> select * from lsc_t where
cast(d as timestamp)>=timestamp '2000-01-01 06:00:00.000000001';
no rows selected
SQL> select * from lsc_t where
d>=cast(timestamp '2000-01-01 06:00:00.000000001' as date);
X D
---------- -------------------
2000-01-01 06:00:00
Converting to date would deliver wrong result.
this reminds me of one of my Oracle SR 7059297.994: ORA-00600:[17059] CAUSED BY CURSOR WITH 32768 CHILDS opened for Oracle 11.1.0.6.
if you do a lot of inserts with timestamp literals (timestamp ‘..’) with cursor_sharing=similar or force, you can get ORA-00600:[17059] depending on your size of the SGA. the usage of the timestamp cast created nonshared cursors until the limit of 32768 child cursors was reached and the ORA-600 was thrown. the workaround is to use to_timestamp().
thanks for the info!
Maybe off topic comment. Andreas, I have searched oracle metalink for your SR 7059297.994 but not found any. One similar is SR 7021131.992: ORA-00600: INTERNAL ERROR CODE, ARGUMENTS: [17059]. Could you verify the SR number? Thanks Tomas