-- Create a new table for the test
CREATE TABLE order_test
  ( order_id      NUMBER(9)                NOT NULL
  , order_date    TIMESTAMP                NOT NULL
  , order_date_tz TIMESTAMP WITH TIME ZONE NOT NULL );  
  
-- Create a sequence for the test
CREATE SEQUENCE order_id_seq 
  START WITH 1;

DECLARE
  l_timestamp TIMESTAMP;
BEGIN
  -- Create some data spread over the past 3 years
  FOR l_index IN 1..2000000 LOOP
    --
    l_timestamp := SYSTIMESTAMP - NUMTODSINTERVAL(1+ABS(MOD(dbms_random.random, 365*3)));
    --
    INSERT INTO order_test
      ( order_id, order_date, order_date_tz )
    VALUES
      ( order_id_seq.NEXTVAL, l_timestamp, l_timestamp );
    --
  END LOOP;
  --
END;
/

-- Create a index on the order_date columns
CREATE INDEX order_test_idx1 ON order_test ( order_date );
CREATE INDEX order_test_idx2 ON order_test ( order_date_tz );

-- Ensure that there is a nice spread of data
SELECT TO_CHAR(order_date, 'YYYY-Q'), COUNT(1)
FROM    order_test
GROUP BY TO_CHAR(order_date, 'YYYY-Q')
ORDER BY 1;

DELETE FROM plan_table;

-- Index ORDER_TEST_IDX1 is NOT hit using the TO_TIMESTAMP function
EXPLAIN PLAN 
SET statement_id = 'order_date'
FOR
  SELECT * 
  FROM   order_test
  WHERE  order_date >= TO_TIMESTAMP('01-JUN-2010','DD-MON-YYYY') 
  AND    order_date  < TO_TIMESTAMP('01-JAN-2011','DD-MON-YYYY');

SELECT plan_table_output 
FROM   table(dbms_xplan.display('plan_table',null,'serial'));

-- Repeat the query using the TIMESTAMP WITH TIME ZONE column
-- Index ORDER_TEST_IDX2 is hit
EXPLAIN PLAN
SET statement_id = 'order_date_tz'
FOR
  SELECT * 
  FROM   order_test
  WHERE  order_date_tz > TO_TIMESTAMP('01-OCT-2010','DD-MON-YYYY')
  AND    order_date_tz < TO_TIMESTAMP('01-JAN-2011','DD-MON-YYYY');

SELECT plan_table_output 
FROM   table(dbms_xplan.display('plan_table',null,'serial'));


-- Re-execute the query using the TIMESTAMP column but using an in-line view
-- Index ORDER_TEST_IDX1 is now hit
EXPLAIN PLAN
SET statement_id = 'order_date_inline'
FOR
  SELECT * 
  FROM   order_test 
  WHERE  order_date < ( SELECT TO_TIMESTAMP('01-JAN-2011','DD-MON-YYYY') FROM dual )
  AND    order_date > ( SELECT TO_TIMESTAMP('01-OCT-2010','DD-MON-YYYY') FROM dual );

SELECT plan_table_output 
FROM   table(dbms_xplan.display('plan_table',null,'serial'));

-- Create a simple function that takes a TIMESTAMP and RETURNS a TIMESTAMP
CREATE OR REPLACE FUNCTION cast_timestamp(i_timestamp IN TIMESTAMP)
  RETURN TIMESTAMP
IS
BEGIN
  RETURN i_timestamp;
END cast_timestamp;
/

-- Re-execute the query passing the TIMESTAMP into the CAST_TIMESTAMP function
-- Index ORDER_TEST_IDX1 is also hit
EXPLAIN PLAN
  SET statement_id = 'order_date_func'
  FOR
  SELECT * 
  FROM   order_test 
  WHERE  order_date > cast_timestamp(TO_TIMESTAMP('01-OCT-2010','DD-MON-YYYY'))
  AND    order_date < cast_timestamp(TO_TIMESTAMP('01-JAN-2011','DD-MON-YYYY'));

SELECT plan_table_output 
FROM   table(dbms_xplan.display('plan_table',null,'serial'));