SELECT DISTINCT order_id FROM ITEM
WHERE EXISTS
(SELECT DISTINCT order_id FROM TRANS_TBL
WHERE updt_dt BETWEEN TO_DATE('01/01/2008','MM/dd/yyyy') AND
TO_DATE('08/12/2008','MM/dd/yyyy')
)
AND current_loc_cd='OHIO' AND destination_loc_cd='OBMA'
ORDER BY order_id
/

SELECT DISTINCT order_id 
	FROM ITEM
WHERE EXISTS
(
SELECT  DISTINCT order_id 
	FROM TRANS_TBL
	WHERE updt_dt BETWEEN TO_DATE('01/01/2008','MM/dd/yyyy') 
	AND TO_DATE('08/12/2008','MM/dd/yyyy')
)
AND current_loc_cd='OHIO' 
AND destination_loc_cd='OBMA'
ORDER BY order_id
/

INDEX USING:

ITEM:
_______

INDEX XIFITEM_1 ON (UPD_DT, DESTINATION_LOC_CD) OF ITEM
UNIIQUE INDEX XPK_ITEM  ON (ORDER_ID, ORDER_TYPE_CODE, STATUS_CODE, UPD_DT) OF ITEM

TRANS_TBL:
__________


UNIQUE INDEX TRANS_TBL_PKEY ON TRANS_TBL
(ORDER_ID, TRANS_SEQ_NBR)
INDEX  XI1_TRANS_TBL ON  UPD_DT

Without Hint:
_________________

83 rows selected.
Elapsed: 00:00:12.06

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=151193 Card=1 Bytes=
          42)

   1    0   SORT (UNIQUE) (Cost=151192 Card=1 Bytes=42)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TRANS_TBL'
           (Cost=4 Card=2 Bytes=36)

   3    2       NESTED LOOPS (Cost=151190 Card=1 Bytes=42)
   4    3         TABLE ACCESS (FULL) OF 'ITEM' (Cos
          t=151186 Card=1 Bytes=24)

   5    3         INDEX (RANGE SCAN) OF 'TRANS_TBL_PKEY' (
          UNIQUE) (Cost=2 Card=2)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      15855  consistent gets
      15673  physical reads
          0  redo size
        868  bytes sent via SQL*Net to client
        303  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         63  rows processed
		 
		 
		 
		 
		 
		 
----

Using Rule hint:

83 rows selected.

Elapsed: 00:00:03.02

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=151193 Card=1 Bytes=
          42)

   1    0   SORT (UNIQUE) (Cost=151192 Card=1 Bytes=42)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TRANS_TBL'
           (Cost=4 Card=2 Bytes=36)

   3    2       NESTED LOOPS (Cost=151190 Card=1 Bytes=42)
   4    3         TABLE ACCESS (FULL) OF 'ITEM' (Cos
          t=151186 Card=1 Bytes=24)

   5    3         INDEX (RANGE SCAN) OF 'TRANS_TBL_PKEY' (
          UNIQUE) (Cost=2 Card=2)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      15855  consistent gets
      15671  physical reads
          0  redo size
        869  bytes sent via SQL*Net to client
        303  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         63  rows processed
		 
		 

SELECT DISTINCT order_id 
	FROM ITEM
WHERE EXISTS
(
SELECT  DISTINCT order_id 
	FROM TRANS_TBL
	WHERE updt_dt BETWEEN TO_DATE('02/01/2008','MM/dd/yyyy') 
	AND TO_DATE('08/12/2008','MM/dd/yyyy')
)
AND current_loc_cd='OHIO' 
AND destination_loc_cd='OBMA'
ORDER BY order_id
/

67 rows selected.

Elapsed: 00:00:06.04

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=151193 Card=1 Bytes=
          42)

   1    0   SORT (UNIQUE) (Cost=151192 Card=1 Bytes=42)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TRANS_TBL'
           (Cost=4 Card=1 Bytes=18)

   3    2       NESTED LOOPS (Cost=151190 Card=1 Bytes=42)
   4    3         TABLE ACCESS (FULL) OF 'ITEM' (Cos
          t=151186 Card=1 Bytes=24)

   5    3         INDEX (RANGE SCAN) OF 'TRANS_TBL_PKEY' (UNIQUE) 
   (Cost=2 Card=2)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      15855  consistent gets
      15668  physical reads
          0  redo size
        869  bytes sent via SQL*Net to client
        303  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         67  rows processed


Using rule hint:
____________________

Elapsed: 00:00:03.01

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   SORT (UNIQUE)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'ITEM'

   3    2       NESTED LOOPS
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'TRANS_TBL'

   5    4           INDEX (RANGE SCAN) OF 'XI1_TRANS_TBL'
           (NON-UNIQUE)

   6    3         INDEX (RANGE SCAN) OF 'XPK_ITEM' (UNIQUE)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        276  consistent gets
          3  physical reads
          0  redo size
        869  bytes sent via SQL*Net to client
        303  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         67  rows processed
		 

SELECT DISTINCT order_id 
	FROM ITEM
WHERE EXISTS
(
SELECT  order_id 
	FROM TRANS_TBL
	WHERE updt_dt BETWEEN TO_DATE('02/01/2008','MM/dd/yyyy') 
	AND TO_DATE('08/12/2008','MM/dd/yyyy')
        AND trans_tbl.order_id = item.order_id   --- *** THIS LINE ***
)
AND current_loc_cd='OHIO' 
AND destination_loc_cd='OBMA'
ORDER BY order_id
/

select	pname, pval1 
from 	sys.aux_stats$ 
where 	sname = 'SYSSTATS_MAIN';

ERROR at line 2:
ORA-00942: table or view does not exist