Session altered. Table dropped. Table created. Table altered. Index created. Index created. Index created. Index created. PL/SQL procedure successfully completed. 293 rows updated. 986 rows updated. Commit complete. ' distribution of current records' HIGH_YEAR NULL_YEAR CHAR NUM_ COUNT(*) ----------- ----------- ---- ---- ---------- 01-jan-9999 0 1279 01-jan-1999 01-jan-1999 1999 1999 2581 01-jan-2000 01-jan-2000 2000 2000 3477 01-jan-2001 01-jan-2001 2001 2001 3467 01-jan-2002 01-jan-2002 2002 2002 3468 01-jan-2003 01-jan-2003 2003 2003 3467 01-jan-2004 01-jan-2004 2004 2004 2261 7 rows selected. ' my range scan christmas 2003 to liz's birthday' COUNT(*) ---------- 104 1 row selected. PL/SQL procedure successfully completed. Explained. 'Using a high date to find current records' PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ Plan hash value: 2242812449 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 11 | 143 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 11 | 143 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_HIGH | 11 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------- 1 - SEL$1 / T1@SEL$1 2 - SEL$1 / T1@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("HIGH_DATE"=TO_DATE('9999-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "ID"[NUMBER,22] 2 - "T1".ROWID[ROWID,10] 29 rows selected. Explained. 'Using a null date to find current records' PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ Plan hash value: 1931397137 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1179 | 15327 | 29 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T1 | 1179 | 15327 | 29 (0)| 00:00:01 | -------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ 1 - SEL$1 / T1@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("NULL_DATE" IS NULL) Column Projection Information (identified by operation id): ----------------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ 1 - "ID"[NUMBER,22] 24 rows selected. Explained. 'using a character date to find current records' PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ Plan hash value: 1697806504 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 11 | 154 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 11 | 154 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_CHAR | 11 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------- 1 - SEL$1 / T1@SEL$1 2 - SEL$1 / T1@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("CHAR_DATE"=' ') Column Projection Information (identified by operation id): PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------- 1 - "ID"[NUMBER,22] 2 - "T1".ROWID[ROWID,10] 28 rows selected. Explained. 'Using a numeric date to find current records' PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ Plan hash value: 2573521682 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 11 | 121 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 11 | 121 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_NUM | 11 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------- 1 - SEL$1 / T1@SEL$1 2 - SEL$1 / T1@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("NUM_DATE"=0) Column Projection Information (identified by operation id): PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------- 1 - "ID"[NUMBER,22] 2 - "T1".ROWID[ROWID,10] 28 rows selected. Explained. 'Using a high date for a range scan' PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ Plan hash value: 2242812449 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 21 | 273 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 21 | 273 | 3 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_HIGH | 21 | | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------- 1 - SEL$1 / T1@SEL$1 2 - SEL$1 / T1@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("HIGH_DATE">=TO_DATE('2003-12-25 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "HIGH_DATE"<=TO_DATE('2004-01-05 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "ID"[NUMBER,22] 2 - "T1".ROWID[ROWID,10] 30 rows selected. Explained. 'Using a null date for a range scan' PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ Plan hash value: 2454602383 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 124 | 1612 | 5 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 124 | 1612 | 5 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_NULL | 132 | | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------- 1 - SEL$1 / T1@SEL$1 2 - SEL$1 / T1@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("NULL_DATE">=TO_DATE('2003-12-25 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "NULL_DATE"<=TO_DATE('2004-01-05 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "ID"[NUMBER,22] 2 - "T1".ROWID[ROWID,10] 30 rows selected. Explained. 'using a character date for a range scan' PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ Plan hash value: 1697806504 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 11 | 154 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 11 | 154 | 3 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_CHAR | 11 | | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------- 1 - SEL$1 / T1@SEL$1 2 - SEL$1 / T1@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("CHAR_DATE">='20031225' AND "CHAR_DATE"<='20040105') Column Projection Information (identified by operation id): PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------- 1 - "ID"[NUMBER,22] 2 - "T1".ROWID[ROWID,10] 28 rows selected. Explained. 'Using a numeric date for a range scan' PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ Plan hash value: 2573521682 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 20 | 220 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 20 | 220 | 3 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_NUM | 20 | | 2 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------- 1 - SEL$1 / T1@SEL$1 2 - SEL$1 / T1@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("NUM_DATE">=20031225 AND "NUM_DATE"<=20040105) Column Projection Information (identified by operation id): PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------- 1 - "ID"[NUMBER,22] 2 - "T1".ROWID[ROWID,10] 28 rows selected. 'Now try histograms' PL/SQL procedure successfully completed. Explained. 'Using a high date to find current records with histograms' PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ Plan hash value: 1931397137 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1404 | 18252 | 29 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T1 | 1404 | 18252 | 29 (0)| 00:00:01 | -------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ 1 - SEL$1 / T1@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("HIGH_DATE"=TO_DATE('9999-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) Column Projection Information (identified by operation id): ----------------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ 1 - "ID"[NUMBER,22] 25 rows selected. Explained. 'Using a null date to find current records with histograms' PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ Plan hash value: 1931397137 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1331 | 17303 | 29 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T1 | 1331 | 17303 | 29 (0)| 00:00:01 | -------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ 1 - SEL$1 / T1@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("NULL_DATE" IS NULL) Column Projection Information (identified by operation id): ----------------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ 1 - "ID"[NUMBER,22] 24 rows selected. Explained. 'using a character date to find current records with histograms' PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ Plan hash value: 1931397137 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1248 | 17472 | 30 (4)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T1 | 1248 | 17472 | 30 (4)| 00:00:01 | -------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ 1 - SEL$1 / T1@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("CHAR_DATE"=' ') Column Projection Information (identified by operation id): ----------------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ 1 - "ID"[NUMBER,22] 24 rows selected. Explained. 'Using a numeric date to find current records with histograms' PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ Plan hash value: 1931397137 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1248 | 13728 | 30 (4)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T1 | 1248 | 13728 | 30 (4)| 00:00:01 | -------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ 1 - SEL$1 / T1@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("NUM_DATE"=0) Column Projection Information (identified by operation id): ----------------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ 1 - "ID"[NUMBER,22] 24 rows selected. Explained. 'Using a high date for a range scan with histograms' PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ Plan hash value: 2242812449 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 113 | 1469 | 5 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 113 | 1469 | 5 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_HIGH | 113 | | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------- 1 - SEL$1 / T1@SEL$1 2 - SEL$1 / T1@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("HIGH_DATE">=TO_DATE('2003-12-25 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "HIGH_DATE"<=TO_DATE('2004-01-05 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "ID"[NUMBER,22] 2 - "T1".ROWID[ROWID,10] 30 rows selected. Explained. 'Using a null date for a range scan with histograms' PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ Plan hash value: 2454602383 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 42 | 546 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 42 | 546 | 3 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_NULL | 45 | | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------- 1 - SEL$1 / T1@SEL$1 2 - SEL$1 / T1@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("NULL_DATE">=TO_DATE('2003-12-25 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "NULL_DATE"<=TO_DATE('2004-01-05 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "ID"[NUMBER,22] 2 - "T1".ROWID[ROWID,10] 30 rows selected. Explained. 'using a character date for a range scan with histograms' PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ Plan hash value: 1697806504 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 195 | 2730 | 8 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 195 | 2730 | 8 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_CHAR | 195 | | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------- 1 - SEL$1 / T1@SEL$1 2 - SEL$1 / T1@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("CHAR_DATE">='20031225' AND "CHAR_DATE"<='20040105') Column Projection Information (identified by operation id): PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------- 1 - "ID"[NUMBER,22] 2 - "T1".ROWID[ROWID,10] 28 rows selected. Explained. 'Using a numeric date for a range scan with histograms' PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ Plan hash value: 2573521682 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 195 | 2145 | 7 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 195 | 2145 | 7 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_NUM | 195 | | 2 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------- 1 - SEL$1 / T1@SEL$1 2 - SEL$1 / T1@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("NUM_DATE">=20031225 AND "NUM_DATE"<=20040105) Column Projection Information (identified by operation id): PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------- 1 - "ID"[NUMBER,22] 2 - "T1".ROWID[ROWID,10] 28 rows selected.