Database Reference
In-Depth Information
30-DEC-14 20141230 20141230
31-DEC-14 20141231 20141231
SQL> EXPLAIN PLAN SET STATEMENT_ID = 'd' FOR
2 SELECT *
3 FROM t
4 WHERE d BETWEEN to_date('20140201','YYYYMMDD') AND to_date('20140228','YYYYMMDD');
SQL> EXPLAIN PLAN SET STATEMENT_ID = 'n' FOR
2 SELECT *
3 FROM t
4 WHERE n BETWEEN 20140201 AND 20140228;
SQL> EXPLAIN PLAN SET STATEMENT_ID = 'c' FOR
2 SELECT *
3 FROM t
4 WHERE c BETWEEN '20140201' AND '20140228';
SQL> SELECT statement_id, cardinality FROM plan_table WHERE id = 0;
STATEMENT_ID CARDINALITY
------------ -----------
d 29
n 11
c 11
The fifth problem is also related to the query optimizer. This one, however, is because of implicit conversion
(as a general rule, you should always avoid implicit conversion). What might happen is that an implicit conversion
prevents the query optimizer from choosing an index. To illustrate this problem, the same table as in the previous
example is used. For this table, an index based on the column of datatype
VARCHAR2
is created. If a
WHERE
clause
contains a restriction on that column that uses a character string, the query optimizer picks out the index. However, if
the restriction uses a number (the developer “knows” that only numeric values are stored in it. . .), a full table scan is
used (notice, in the second SQL statement, that the implicit conversion based on the
to_number
function prevents the
index from being used) and as a result the query optimizer correctly ignores the index.
SQL> CREATE INDEX i ON t (c);
SQL> SELECT /*+ index(t) */ *
2 FROM t
3 WHERE c = '20140228';
--------------------------------------------
| Id | Operation | Name |
--------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| T |
|* 2 | INDEX RANGE SCAN | I |
--------------------------------------------
2 - access("C"='20140228')
