Professional Web Applications Themes

Improve this querry!? - Oracle Server

Is there any way to improve the efficiency of the following query? Currently, this query takes more than 15 min to complete (73 min of records) - and I have to check the whole database which is 7 days!!! (basically, this will take forever). I tried the query without the indexes and it's MUCH slower. It takes just less than a minute (55 secs) to check 5 min between inputserialnumbers. There must be a better way. Here is what I need. A query to return all inputserialnumber from either med or mrd where medstatus <> 'T' and ALL mrdstatus = ...

  1. #1

    Default Improve this querry!?

    Is there any way to improve the efficiency of the following query?
    Currently, this query takes more than 15 min to complete (73 min of
    records) - and I have to check the whole database which is 7 days!!!
    (basically, this will take forever).

    I tried the query without the indexes and it's MUCH slower. It takes
    just less than a minute (55 secs) to check 5 min between
    inputserialnumbers. There must be a better way.

    Here is what I need. A query to return all inputserialnumber from
    either med or mrd where medstatus <> 'T' and ALL mrdstatus = NULL.
    med.inputserialnumbers are unique - mrd.inputserialnumbers are
    "1->many". To clarify, if at least 1 mrdstatus for
    mrd.inputserialnumber <> NULL, then I do not want that
    inputserialnumber.

    SELECT /*+ INDEX(MED_MT_TB IX0101) */
    med.inputserialnumber, inputconnectionnumber, rejectedmessage,
    destinationnumber, mrdstatus, medstatus
    FROM med_mt_tb med, mrd_mt_tb mrd
    WHERE
    mrd.inputserialnumber = med.inputserialnumber(+) AND
    med.inputserialnumber > HEXTORAW('3FA6CDF541000013') AND -- Mon Nov 3
    21:51:49 2003
    med.inputserialnumber < HEXTORAW('3FA6DEF541000013') AND -- Mon Nov 3
    23:04:21 2003
    medstatus <> 'T' AND
    med.inputserialnumber NOT IN (
    SELECT /*+ INDEX(MRD_MT_TB IX0101) */
    med.inputserialnumber FROM mrd_mt_tb
    WHERE mrdstatus IS NOT NULL)
    /


    Oracle Server 8.1.7.3
    IBM DYNIX/ptx
    Mark Guest

  2. #2

    Default Re: Improve this querry!?

    -- Substitute the owner of the tables for 'scott' in the
    -- following two statements

    exec dbms_stats.gather_table_stats('scott', 'med_mt_tb');

    exec dbms_stats.gather_table_stats('scott', 'mrd_mt_tb');

    select inputserialnumber, inputconnectionnumber, rejectedmessage,
    destinationnumber, null as mrdstatus, medstatus
    from med_mt_tb
    where inputserialnumber > to_number('3fa6cdf541000013', 'x')
    and inputserialnumber < to_number('3fa6def541000013',
    'x')
    and medstatus <> 'T'
    and inputserialnumber in
    (select inputserialnumber
    from mrd_mt_tb
    group by inputserialnumber
    having count(mrdstatus) = 0);



    HTH,
    Dave





    Mark Schubert wrote: 

    Dave Guest

  3. #3

    Default Re: Improve this querry!?

    I assumed that all the select columns except mrdstatus came from
    med_mt_tb. If this is not true, please indicate the schema of the two
    tables.

    - Dave





    Dave Hau wrote: 
    >
    >[/ref]

    Dave Guest

  4. #4

    Default Re: Improve this querry!?

    Cool Dave...

    In the original query there's an outer join, maybe Mark needs
    something like this (slight adaption of Dave's solution):

    select med.inputserialnumber, med.inputconnectionnumber,
    med.rejectedmessage,
    null as mrdstatus, medstatus
    from med_mt_tb med
    , (select inputserialnumber, count(mrdstatus) ct
    from mrd_mt_tb
    group by inputserialnumber
    ) mrd
    where med.medstatus <> 'T'
    and med.inputserialnumber = mrd.inputserialnumber(+)
    and nvl(mrd.ct,0) = 0
    ;

    I tested with two tables with each 1mil rows, Dave's query runs in
    16s, this outer join query in 21s.
    Without the outer join it runs in 13s:

    select med.inputserialnumber, med.inputconnectionnumber,
    med.rejectedmessage,
    null as mrdstatus, medstatus
    from med_mt_tb med
    , (select inputserialnumber
    from mrd_mt_tb
    group by inputserialnumber
    having count(mrdstatus) = 0
    ) mrd
    where med.medstatus <> 'T'
    and med.inputserialnumber = mrd.inputserialnumber
    ;

    jan

    om mani padme hum
    Jan Guest

  5. #5

    Default Re: Improve this querry!?

    Mark Schubert wrote:

    [...] 
    Hello Mark,
    maybe you gain some performance using a hash antijoin whith your NOT IN
    clause. Have a look at the docs. Since NOT IN is one of the slowest
    operations one can do with a database, this may boost your performance.
    The HASH_AJ hint has some prerequisites to work properly though.

    Hope that helps,
    Lothar

    --
    Lothar Armbrüster | de
    Hauptstr. 26 | de
    D-65346 Eltville | de
    Lothar Guest

  6. #6

    Default Re: Improve this querry!?

    Lothar Armbruester <de> wrote in message 

    Lothar,

    on my test set with 1m rows in both tables, the CBO chose the hash
    join for each query shown above, including Mark's original query with
    the NOT IN. Have to mention this is on 9.2.0.3 though.

    jan
    Jan Guest

Similar Threads

  1. Multiple insertion querry
    By Eric Kincl in forum PHP Development
    Replies: 2
    Last Post: November 19th, 10:30 PM
  2. MySQL querry
    By Eric Kincl in forum PHP Development
    Replies: 4
    Last Post: October 8th, 10:29 PM
  3. whats wrong with this querry
    By Sundial Services in forum PHP Development
    Replies: 1
    Last Post: August 31st, 12:55 AM
  4. SQL querry vs Access qry
    By Ray in forum Microsoft SQL / MS SQL Server
    Replies: 8
    Last Post: July 18th, 02:18 PM
  5. Querry by date
    By Douglas J. Steele in forum Microsoft Access
    Replies: 2
    Last Post: July 5th, 01:30 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139