one of the pitfalls of inaccurate data types is to construct a table date field, a varchar2, a save date, and a test. I have written two blog posts before:
1. Problem with field type design and actual business mismatch 1
2. Problem with field type design and actual business mismatch 2
sql> drop table test purge;
Sql> CREATE TABLE test as Select
To_char (to_date (' 2014-01-01 ', ' yyyy-mm-dd ') +rownum, ' YYYYMMDD ') s_date,
To_date (' 2014-01-01 ', ' yyyy-mm-dd ') +rownum d_date
From All_objects;
Sql> CREATE index ind_t_sdate on test (s_date) nologging;
Sql> CREATE index ind_t_ddate on test (d_date) nologging;
sql> exec dbms_stats.gather_table_stats (user, ' test ', cascade = TRUE);
Sql> Set Timing on
Sql> Set Autotrace traceonly
Sql> SELECT * FROM Test where s_date between ' 20140201 ' and ' 20140222 ';
22 rows have been selected.
Time used: 00:00:00.00
Execution plan
----------------------------------------------------------
Plan Hash value:953148778
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 51 | 3 (0) | 00:00:01 |
| 1 | TABLE ACCESS by INDEX rowid| TEST | 3 | 51 | 3 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN | Ind_t_sdate | 3 | | 2 (0) | 00:00:01 |
-------------------------------------------------------------------------------------------
--you can see that the CBO evaluates the number of rows is 3, clearly returned is 22
predicate information (identified by Operation ID):
---------------------------------------------------
2-access ("s_date" >= ' 20140201 ' and "s_date" <= ' 20140222 ')
Statistical information
----------------------------------------------------------
1 Recursive calls
0 db Block gets
7 Consistent gets
0 physical Reads
0 Redo Size
944 Bytes sent via sql*net to client
349 bytes received via sql*net from client
3 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
Rows processed
Sql> SELECT * FROM Test
where d_date between To_date (' 20140201 ', ' YYYYMMDD ') and
To_date (' 20140222 ', ' yyyymmdd ');
22 rows have been selected.
Time used: 00:00:00.00
Execution plan
----------------------------------------------------------
Plan Hash value:112387541
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23 | 391 | 3 (0) | 00:00:01 |
| 1 | TABLE ACCESS by INDEX rowid| TEST | 23 | 391 | 3 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN | Ind_t_ddate | 23 | | 2 (0) | 00:00:01 |
-------------------------------------------------------------------------------------------
--you can see that the CBO assessment is basically accurate.
predicate information (identified by Operation ID):
---------------------------------------------------
2-access ("D_date" >=to_date (' 2014-02-01 00:00:00 ', ' syyyy-mm-dd hh24:mi:ss ')
and "D_date" <=to_date (' 2014-02-22 00:00:00 ', ' syyyy-mm-dd hh24:mi:ss '))
Statistical information
----------------------------------------------------------
1 Recursive calls
0 db Block gets
7 Consistent gets
0 physical Reads
0 Redo Size
944 Bytes sent via sql*net to client
349 bytes received via sql*net from client
3 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
Rows processed
Summary: Although the two SQL consumes the same resources, but the SQL1 evaluation results are not correct, in the Multi-table association, this is definitely a hidden danger, it is very easy to lead the execution plan go wrong. In addition to the above reasons, there is still the problem is that using VARCHAR2 to save the date will cause n more storage format, have seen a situation, date format a variety of (days, months, days, months, days, hours, minutes), there are in English:, there are full-width, half-width, there is null, Even undefine (probably from JS). and caused the index to be built up, to change the data type, the script of the light write conversion spent a day more time.
The pitfalls of choosing the wrong type of Oracle database design
