Re: Improve this querry!?

"mcstock" <mcstockspamplug_at_spamdamenquery.com> wrote in message news:<D4adnaFGlJ7IIC-iRVn-tg_at_comcast.com>...
> 'AND medstatus NOT in ('T')' will very likely not use an index
>
> there's no reason why
> 'AND medstatus like 'S%')' won't work -- looks like you weren't using the
> 'LIKE' operator, but the IN operator
>
> 1) try LIKE 'S%'
> 2) post the plan

19:14:08 SQL> select operation, options, object_name, id, parent_id, position from plan_table;

OPERATION                      OPTIONS                       
OBJECT_NAME                            ID  PARENT_ID   POSITION
------------------------------ ------------------------------
------------------------------ ---------- ---------- ----------
SELECT STATEMENT                                                      
                               0                     6
NESTED LOOPS                                                          
                               1          0          1
TABLE ACCESS                   BY INDEX ROWID                
MED_MT_TB                               2          1          1
INDEX                          RANGE SCAN                     PKYMT01 
                               3          2          1
FILTER                                                                
                               4          3          1
TABLE ACCESS                   FULL                          
MRD_MT_TB                               5          4          1
TABLE ACCESS                   BY INDEX ROWID                
MRD_MT_TB                               6          1          2
INDEX                          RANGE SCAN                     PKYMT03 
                               7          6          1

8 rows selected.

That's the plan for this query that took 104 seconds to run. Way too slow.

explain plan for
SELECT /*+ INDEX(MED_MT_TB IX0101) */
   med.inputserialnumber,
   inputconnectionnumber,
   rejectedmessage,
   destinationnumber,
   mrdstatus,
   medstatus
   FROM

      mrd_mt_tb mrd,
      med_mt_tb med
   WHERE
      mrd.inputserialnumber = med.inputserialnumber(+)
      AND med.inputserialnumber > HEXTORAW('3FB0349000000000')  --    
Mon Nov 11 01:00:00
      AND med.inputserialnumber < HEXTORAW('3FB036E800000000')  --    
Mon Nov 11 01:10:00
      AND medstatus <> 'T'
      AND med.inputserialnumber NOT IN 
         (SELECT /*+ HASH_AJ */
            med.inputserialnumber
         FROM 
            mrd_mt_tb
         WHERE 
            mrdstatus IS NOT NULL)