[boot8077]

I have the following code. Both 1 and 2 are basicly the same except that 2 has "UDA_4 = 'DJ' AND" in it. 1 will return data instantly and 2 takes 15 minutes. Does anyone know why the inclusion of "UDA_4 = 'DJ' AND" causes this code to take so much longer to execute.


1)

SELECT t1.unit_key, t1.parent_unit_key, t1.serial_number, t1.uda_4, t1.uda_9, t1.part_revision, t1.part_number,
t1.ship_account_key, t2.customer, t2.customer_Date from (select unit_key, parent_unit_key, serial_number, uda_4, uda_9,
part_revision, shipped_time, part_number, ship_account_key from unit where
(udt_0 > TO_DATE('1/1/1994 12:00:00 AM','MM/DD/YYYY HH:MI:SS AM') AND
udt_0 <= TO_DATE('8/13/2002 10:07:44 AM','MM/DD/YYYY HH:MI:SS AM')) ) t1,
(select tt1.object_name, tt1.object_key, tt1.customer, tt1.customer_date from dc_ge_customerships tt1,
(select distinct object_key, max(creation_time) as MAX_TIME from dc_ge_customerships where Customer = '53146'
group by object_key) tt2 where tt1.creation_time = tt2.MAX_TIME and tt1.object_key = tt2.object_key and
tt1.returned = '0')t2 WHERE t1.unit_key = t2.object_key AND t1.serial_number = t2.object_name


2)

SELECT t1.unit_key, t1.parent_unit_key, t1.serial_number, t1.uda_4, t1.uda_9, t1.part_revision, t1.part_number,
t1.ship_account_key, t2.customer, t2.customer_Date from (select unit_key, parent_unit_key, serial_number, uda_4,
uda_9, part_revision, shipped_time, part_number, ship_account_key from unit where uda_4 = 'DJ' AND
(udt_0 > TO_DATE('1/1/1994 12:00:00 AM','MM/DD/YYYY HH:MI:SS AM') AND
udt_0 <= TO_DATE('8/13/2002 10:09:23 AM','MM/DD/YYYY HH:MI:SS AM'))) t1,
(select tt1.object_name, tt1.object_key, tt1.customer, tt1.customer_date from
dc_ge_customerships tt1, (select distinct object_key, max(creation_time) as
MAX_TIME from dc_ge_customerships where Customer = '53146' group by object_key) tt2
where tt1.creation_time = tt2.MAX_TIME and tt1.object_key = tt2.object_key and tt1.returned = '0')t2
WHERE t1.unit_key = t2.object_key AND t1.serial_number = t2.object_name

[Rockn]

uda_4 = 'DJ' AND AND

[boot8077]

I mistakenly put the extra AND in when I was bolding the code that is not really there.

[Rockn]

OK...LOL

That is the only difference between these two statements is the additional condition?

[boot8077]

Yes this is the only dfference

[Rockn]

Other than no bracketing around the extra condition I can't see anything.