> > 1) try LIKE 'S%' <<======
> > 2) post the plan <<=== for LIKE 'S%'
"Mark Schubert" <Mark.Schubert_at_MyRealBox.com> wrote in message
news:9c9c6d17.0311131124.49d579d4_at_posting.google.com...
> "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)
> /
Received on Thu Nov 13 2003 - 13:29:30 CST
