Tom,

I'm hoping you can help me with the following.

The background situation is that we're fixing some code to make use of bind variables and in this particular situation we don't know the actual variables until runtime, therefore I'm making use of a ref cursor and application context. Two of the inputs are dates so when writing to the context I 'to_char' the variable to preserve the time element and I've wrapped the sql_text referencing the context in a 'to_date'.

The problem I'm seeing is the use of the to_date function is somehow changing the plan. I'm managed to put together a simple test case to reproduce my findings (no need for dynamic sql in this case)

drop table t
/
create table t as select * from all_objects
/
create index t_idx on t(status,created) <<<<----- low leading edge cardinality to increase a chance of index skip scan
/
exec dbms_stats.gather_table_stats (user,'t',cascade=>true)

ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'
/
DECLARE
l_date DATE := SYSDATE - 7;
l_char VARCHAR2(20) := TO_CHAR(SYSDATE - 7,'ddmmyyyyhh24miss');
l_cnt number;
BEGIN
SELECT max(object_id)
into l_cnt
FROM t WHERE created >= l_date;

SELECT max(object_id)
into l_cnt
FROM t WHERE created >= TO_DATE(l_char,'ddmmyyyyhh24miss');
END;
/
exit
/

The first select shows I'm comparing a date to a date but I would have thought the second is pretty much the same, date to a date. The following are the relevant sections from the tkprof report.

-- first select

SELECT MAX(OBJECT_ID)
FROM
T WHERE CREATED >= :B1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 1 0.00 0.12 2 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.13 2 4 0 1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 64 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=4 pr=2 pw=0 time=128323 us)
3 TABLE ACCESS BY INDEX ROWID T (cr=4 pr=2 pw=0 time=128282 us)
3 #BINDEX SKIP SCAN T_IDX (cr=3 pr=2 pw=0 time=128240 us)(object id 50417)#B


-- second select

SELECT MAX(OBJECT_ID)
FROM
T WHERE CREATED >= TO_DATE(:B1 ,'ddmmyyyyhh24miss')


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 1 0.08 0.36 359 659 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.09 0.36 359 659 0 1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 64 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=659 pr=359 pw=0 time=361152 us)
3 #BTABLE ACCESS FULL T (cr=659 pr=359 pw=0 time=361100 us)#B


This is happening on 10.1.0.2 & 9.2.0.4

Hope you can shed some light on this.

Thanks in advance,
Chris

btw - Predictive discussion - Brilliant, simply brilliant...