DATATYPES in Font

Drawer DataMatrix in Font DATATYPES

To truncate that date down to the year, all the database had to do was put 1s in the last 5 bytes a very fast operation. We now have a sortable, comparable DATE field that is truncated to the year level, and we got it as efficiently as possible. What many people do instead of using TRUNC, however, is use a date format in the TO_CHAR function. For example, they will use Where to_char(date_column,'yyyy') = '2005' instead of Where trunc(date_column,'y') = to_date('01-jan-2005','dd-mon-yyyy') The latter is a far more performant and less resource-intensive approach. If we make a copy of ALL_OBJECTS and save out just the CREATED column ops$tkyte@ORA10G> create table t 2 as 3 select created from all_objects; Table created. ops$tkyte@ORA10G> exec dbms_stats.gather_table_stats( user, 'T' ); PL/SQL procedure successfully completed. and then, with SQL_TRACE enabled, we repeatedly query this table using both techniques, we see the following results: select count(*) from t where to_char(created,'yyyy') = '2005' call count ------- -----Parse 4 Execute 4 Fetch 8 ------- -----total 16 cpu elapsed disk query current -------- ---------- ---------- ---------- ---------0.01 0.05 0 0 0 0.00 0.00 0 0 0 0.41 0.59 0 372 0 -------- ---------- ---------- ---------- ---------0.42 0.64 0 372 0 rows ---------0 0 4 ---------4

select count(*) from t where trunc(created,'y') = to_date('01-jan-2005','dd-mon-yyyy') call count ------- -----Parse 4 Execute 4 Fetch 8 ------- -----total 16 cpu elapsed disk query current -------- ---------- ---------- ---------- ---------0.00 0.00 0 0 0 0.00 0.00 0 0 0 0.04 0.16 0 372 0 -------- ---------- ---------- ---------- ---------0.04 0.16 0 372 0 rows ---------0 0 4 ---------4

You can see the obvious difference. Using TO_CHAR consumed an order of magnitude more CPU than using TRUNC. That is because TO_CHAR must convert the date to a string, using a much larger code path, taking all of the NLS we have in place to do so. Then we had to compare a string to a string. The TRUNC, on the other hand, just had to set the last 5 bytes to 1. Then it compared 7 binary bytes to 7 binary bytes, and it was done. So, you should never use TO_CHAR on a DATE column simply to truncate it. Additionally, avoid applying a function at all to the DATE column when possible. Taking the preceding example one step further, we can see that the goal was to retrieve all data in the year 2005. Well, what if CREATED had an index on it and a very small fraction of the values in that table were in the year 2005 We would like to be able to use that index by avoiding a function on the database and column using a simple predicate: select count(*) from t where created >= to_date('01-jan-2005','dd-mon-yyyy') and created < to_date('01-jan-2006','dd-mon-yyyy'); We would achieve two things: An index on CREATED could be considered. The TRUNC function would not have to be invoked at all, and that overhead would go away entirely. This technique of using a range comparison instead of TRUNC or TO_CHAR applies equally to the TIMESTAMP type discussed shortly. When you can avoid applying a function to a database column in a query, you should. In general, avoiding the function will be more performant and allow the optimizer to choose from a wider variety of access paths.

A question I am frequently asked is, How do I add time to or subtract time from a DATE type For example, how do you add one day to a DATE, or eight hours, or one year, or one month, and so on There are three techniques you ll commonly use: Simply add a NUMBER to the DATE. Adding 1 to a DATE is a method to add 1 day. Adding 1/24 to a DATE therefore adds 1 hour, and so on. You may use the INTERVAL type, as described shortly, to add units of time. INTERVAL types support two levels of granularity, years and months, or days/hours/minutes/seconds. That is, you may have an interval of so many years and months or an interval of so many days, hours, minutes, and seconds. Add months using the built-in ADD_MONTHS function. Since adding a month is generally not as simple as adding 28 to 31 days, a special-purpose function was implemented to facilitate this. Table 12-3 demonstrates the techniques you would use to add N units of time to a date (or subtract N units of time from a date, of course).