Mega Code Archive

Analyze index on varchar2 value and date type value

SQL> SQL> create table t 2 as 3 select to_char( to_date('01-jan-1995','dd-mon-yyyy')+rownum, 'yyyymmdd' ) str_date, 4 to_date('01-jan-1995','dd-mon-yyyy')+rownum date_date 5 from all_objects 6 where rownum < 20 7 / Table created. SQL> SQL> create index i1 on t(str_date); Index created. SQL> SQL> create index t_date_date_idx on t(date_date); Index created. SQL> SQL> analyze table t compute statistics 2 for table 3 for all indexes 4 for all indexed columns; Table analyzed. SQL> SQL> set autotrace on explain SQL> select * from t 2 where str_date between '20001231' and '20100101' and rownum < 20; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 508354683 ----------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ----------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 20 | 1 | |* 1 | COUNT STOPKEY | | | | | |* 2 | TABLE ACCESS FULL| T | 1 | 20 | 1 | ----------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<20) 2 - filter("STR_DATE">='20001231' AND "STR_DATE"<='20100101') Note ----- - cpu costing is off (consider enabling it) SQL> SQL> select * from t where date_date between to_date('20001231','yyyymmdd') and to_date('20100101','yyyymmdd') and rownum<20; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 508354683 ----------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ----------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 20 | 1 | |* 1 | COUNT STOPKEY | | | | | |* 2 | TABLE ACCESS FULL| T | 1 | 20 | 1 | ----------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<20) 2 - filter("DATE_DATE">=TO_DATE('2000-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "DATE_DATE"<=TO_DATE('2010-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) Note ----- - cpu costing is off (consider enabling it) SQL> SQL> SQL> select * from t 2 where str_date between '20001231' and '20100101' and rownum<20; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 508354683 ----------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ----------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 20 | 1 | |* 1 | COUNT STOPKEY | | | | | |* 2 | TABLE ACCESS FULL| T | 1 | 20 | 1 | ----------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<20) 2 - filter("STR_DATE">='20001231' AND "STR_DATE"<='20100101') Note ----- - cpu costing is off (consider enabling it) SQL> SQL> select * from t where date_date between to_date('20001231','yyyymmdd') and to_date('20100101','yyyymmdd') 2 and rownum < 20; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 508354683 ----------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ----------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 20 | 1 | |* 1 | COUNT STOPKEY | | | | | |* 2 | TABLE ACCESS FULL| T | 1 | 20 | 1 | ----------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<20) 2 - filter("DATE_DATE">=TO_DATE('2000-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "DATE_DATE"<=TO_DATE('2010-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) Note ----- - cpu costing is off (consider enabling it) SQL> SQL> set autotrace off SQL> SQL> drop table t; Table dropped.