What must be considered when modifying Oracle DATE column to TIMESTAMP
The system I'm working on uses an oracle 11 db and has a java implemented backend which uses JDBC.
The system has one main performance problem:
The oracle db has indexed DATE columns but the java backend access the db like this
String sql = "SELECT foo FROM bar WHERE a_date > ?";
// ... prepare the statement
statement.setTimestamp(1, new Timestamp());
then Oracle will not use the index and fall back to full table scan because of statement.setTimestamp.
First Solution:
So I have changed the access of the backend from statement.setTimestamp to statement.setDate. The performance has been improved and the DATE index was now used, but now I lost the accuracy of the timestamp data type, and only got a day-accuracy of the java.sql.Date type.
Thus, the first solution was unusable for my system.
Second Solution:
I don't change the java backend thus using statement.setTimestamp and change all DATE columns of the oracle db like the following sql command example:
ALTER TABLE <table_1> MODIFY <date_column> TIMESTAMP;
Now my question:
What must be considered when modifying Oracle DATE column to TIMESTAMP
I think a list of the important aspects would be perfect.
Here my first draft of the "CHECKLIST of Migrating Oracle DATE columns to TIMESTAMP
1. CREATE / INSERT
2. READ / SELECT
3. UPDATE / UPDATE
4. DELETE / DELETE
5. TO_DATE( )
select * from table where crea_time between TO_DATE( '10.2014', 'MM.yyyy' ) and TO_DATE( '12.2014', 'MM.yyyy' ) ;
6. TRUNC( )
select TRUNC( crea_time ) from table;
output of crea_time data typ DATE 05.11.2014 00:00:00 output of crea_time data typ TIMESTAMP 05.11.2014 00:00:00
7. TO_CHAR( )
select to_char( crea_time,'hh24miss' ) from table;
output data typ DATE 140612 output data typ TIMESTAMP 140612
8. NESTED - TRUNC( TO_DATE() )
select TRUNC(TO_DATE( crea_time), 'YEAR') from table;
output of crea_time data typ DATE 01.01.2014 00:00:00 output of crea_time data typ TIMESTAMP ORA-01830: 01830. 00000 - "date format picture ends before converting entire input string" *Cause:
*Action: Solution >>> select TRUNC( TO_TIMESTAMP( crea_time), 'YEAR' ) from table; output of crea_time data typ TIMESTAMP 01.01.2014 00:00:00
9. Add A DAY/HOUR/MINUTE/SECOND
select crea_time+1 from table;
output data typ DATE 06.11.2014 14:06:12
output data typ TIMESTAMP 06.11.2014 14:06:12
10. MIN() / MAX()
SELECT MIN(crea_time) FROM table;
output data typ DATE 05.11.2014 14:06:12 output data typ TIMESTAMP 05.11.2014 14:06:12,000000000
Please excuse my bad English.
链接地址: http://www.djcxy.com/p/6302.html上一篇: 如何在oracle中传递日期值？