January 25, 2012

Use Timestamp type instead of Date type

Today I noticed another problem caused by Date type.

select page3_.*
from sb2.NEWS_ITEM this_ inner join sb2.CONTENT_FETCHER cf1_ on this_.news_id=cf1_.ID
 inner join sb2.CONTENT content2_ on cf1_.content_id=content2_.id 
  inner join sb2.PAGE page3_ on content2_.page_id=page3_.id
where ( this_.discriminator = 'calendar_item') and page3_.LAST_UPDATED_DATE>:1 
order by page3_.LAST_UPDATED_DATE asc, this_.publication_date asc

The SQL Tunning Advisor suggested that “page3_.LAST_UPDATED_DATE>:1 ” has an implicit data type conversion prevent the optimizer from selecting indices.

The LAST_UPDATED_DATE column is of “DATE” type.

From V$SQL_MONITOR, the bind variable is:
<binds>
<bind name=":1" pos="1" dty="180" dtystr="TIMESTAMP" maxlen="11" len="7" format="hexdump">78640101010101</bind>
</binds>

After inspecting the source code, it is appear bind as java.util.Date.

I think it should be bound as java.sql.Date, not java.sql.Timestamp or java.util.Date. See http://databaseperformance.blogspot.com/2009/01/java-data-types-and-oracle-index-usage.html


Oracle saw the value it was getting was of type TIMESTAMP, which has sub-second values in it. And rather than lose precision by truncating its value, it was instead converting the DATE value in each data row to a TIMESTAMP value before comparing.

Better to use Timestamp type instead of Date type. The index should work properly whether you bind java.sql.Date or java.sql.Timestamp.

Data Type Conversion
Oracle will convert the value to most precise type:

Column type DATE, predicate value is TIMESTAMP => Right size is more precise => Convert every row to timestamp
Column type Timestap, predicate value is DATE => Left size is more precise => convert predicate to Timestap
Column type varchar, predicate value is number => Right size is more precise => convert every row to varchar
Column type is number, predicate value is varchar => Left size is more precise => convert predicate value to number


- No comments Not publicly viewable


Add a comment

You are not allowed to comment on this entry as it has restricted commenting permissions.

Search this blog

Search Warwick Blogs

 

Tags

Most recent comments

  • Oracle 11g is the clear leader in this area. Oracle has invested heavily into self–tuning capabiliti… by shaane on this entry
  • All the features are very nice.I like Repair Advisors the most.Oracle 10G introduced some of the dat… by lucy on this entry
  • "logical standby failed to re–start, I am also getting the same error. I have search on internet and… by anemia on this entry
  • Thanks for the nice information because from last three days i am stuck on this problem…. by robot vacuum on this entry
  • So what happen City speed dating when you tried this coding once in your PC.. Please tell me so i am… by John Bergeron on this entry
Not signed in
Sign in

Powered by BlogBuilder
© MMXXI