Re: query problem - please help

foolishHurts wrote:

>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
>
>

A bit confused here.

1. If it is their query why not throw it back on them?
2. Since it is a vendor query can you modify it?
3. Can you convert all of these CHAR columns to VARCHAR2?
4. Have you tried omitting various clauses to see which one(s) are taking the time?
5. What takes 10+ hours and what takes 8 seconds?
6. Why the RULE hint in the insert statement?

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)