query problem - please help

From: foolishHurts <silverback_at_photobooks.com>
Date: Fri, 24 Oct 2003 08:03:38 -0400
Message-ID: <e23ipvc3t2citd22n97qva71pm0mfh204u@4ax.com>

Hi gurus.

I have a query which goes into never never land. I need a fast answer as this goes live a week from today and it is just one of a bunch.

Here is the situation:
QUERY from vendor:
insert into ids
select /*+RULE*/ <---- See below

     lpad(trim(e.id_number),10,'0'),        'CSI',    trim(stdn_i),
     to_date('19010101','YYYYMMDD'),     
     to_date('19010101','YYYYMMDD'),     
     'conversion',  'CO'                    

from cvt_rltv_dat, entity e
where lpad(trim(indv_i),10,'0') = lpad(trim(e.id_number),10,'0')

    and rltv_c_rltn in ('C','I')

    and trim(stdn_i) is not null
    and trim(stdn_i) <> '00000'
    and trim(stdn_i) <> '0'
    and not exists (select b.id_number from ids b
            where lpad(trim(indv_i),10,'0') = b.id_number
            and b.ids_type_code = 'CSI'
            and b.other_id = trim(stdn_i))

    group by e.id_number, trim(stdn_i);

EXPLAIN PLAN - first query (7:21:02) shows RULE, second (7:24:17) shows without RULE. It is too wide to post, but I can e-mail to any who want it.

first - RULE has 13 steps with two full reads of the entity table. second - optimizer has 7 steps and a cost of 418, one full table read of cvt_rltv_dat table.

ENVIRONMENT:
Oracle 8i - 8.1.7.4 (can not patch due to vendor) on SunOS 5.8 480
Lots of disk. nice memory, but several databases are shareing. SGA is
Shared pool= 76, buffer cache= 25, Java= 19, total SGA=120MB No large pool specified
This is a test database, so logging is off.

This puppy can go away for 10 hours (I kill it at that point). select count(*)
from cvt_rltv_dat, entity e

    where lpad(trim(indv_i),10,'0') = e.id_number     and rltv_c_rltn in ('C','I')

    and trim(stdn_i) is not null
    and trim(stdn_i) <> '00000'
    and trim(stdn_i) <> '0'
    and not exists (select b.id_number from ids b
            where indv_i = b.id_number
            and b.ids_type_code = 'CSI'
            and b.other_id = trim(stdn_i))

    group by e.id_number, trim(stdn_i)

returns 16786 in 8 seconds.

Feel free to e-mail me at silverNObackSPAM_at_photobooks.com

Thanks,

Evan Received on Fri Oct 24 2003 - 07:03:38 CDT