Oracle SQL - left Join on 2 tables does not run

Question

Anybody have any idea why when I try to run this query it just runs forever and does not stop??

The queries within the 'With' clauses run fine on their own. It's just when I try joining them I can't get it to run properly??

--Process name and input
With ProcessIn as
(
select Data.Scan_time, equipment.equipment_desc, data.serial_number

from Data, equipment

where Data.equipment = equipment.equipm_barcode and data.quality_plan = 'N/A' and        data.error_code = 'N/A' 
)

--Total process defects
,ProcessDefect as
(
select Data.Scan_time, equipment.equipment_desc, data.serial_number

from Data, equipment

where Data.equipment = equipment.equipm_barcode and (data.quality_plan <> 'N/A' or     data.error_code <> 'N/A')
)

Select concat(to_char(ProcessIn.SCAN_TIME, 'mm'), to_char(ProcessIn.SCAN_TIME, 'dd')) as "Date", ProcessIn.equipment_desc, count(ProcessIn.serial_number), count(ProcessDefect.serial_number)

from ProcessIn 
Left Join ProcessDefect
On concat(to_char(ProcessIn.SCAN_TIME, 'mm'),to_char(ProcessIn.SCAN_TIME, 'dd')) = concat(to_char(ProcessDefect.SCAN_TIME, 'mm'),to_char(ProcessDefect.SCAN_TIME, 'dd')) 
and ProcessIn.equipment_desc = ProcessDefect.equipment_desc

group by concat(to_char(ProcessIn.SCAN_TIME, 'mm'), to_char(ProcessIn.SCAN_TIME, 'dd')), ProcessIn.equipment_desc
;

---

UPDATE 20110816 I've used some of the suggestions to refine my query with no success. Does anybody have any other suggestion? Also, anyways to check that my index is being used properly as I am new to creating/using them. I just used the Oracle interface to create an index based on serial_number and smalldate.

Thanks

With ProcessIn as
(
select data.smalldate, mip.mip_step_description, data.part_serial_number

from Data, MIP

where Data.equipment = MIP.equipment and data.quality_plan is null and data.error_code is null
)

,ProcessDefect as
(
select data.smalldate, mip.mip_step_description, data.part_serial_number

from Data, MIP

where Data.equipment = MIP.equipment and (data.quality_plan is not null or data.error_code is not null)
)

Select ProcessIn.smalldate, ProcessIn.mip_step_description, count(ProcessIn.part_serial_number),  count(ProcessDefect.part_serial_number)

from ProcessIn 
Left Join ProcessDefect
On ProcessIn.smalldate = ProcessDefect.smalldate
and ProcessIn.mip_step_description = ProcessDefect.mip_step_description

group by ProcessIn.smalldate, ProcessIn.mip_step_description

UPDATE 110820

So I've been making some progress but need help figuring one last thing. I changed the queries structure to look like this now

select data.smalldate, mip.mip_step_description, error_code.error_code_en, count(case when (error_code is null and quality_plan is null) then data.part_serial_number end) as "Input", count(case when error_code is not null then data.part_serial_number end) as "Defects"

from Data

left join MIP
On data.equipment = mip.equipment

left join error_code
on data.error_code = error_code.error_code_sn 

group by data.smalldate, mip.mip_step_description, error_code.error_code_en

order by data.smalldate, mip.mip_step_description, count(data.part_serial_number) desc

As you can see in the select statement, I'm using case statements within my count functions. This works fine. Data output looks like this

Date    MIP_Desc    Error_Code    Input    Defects
1/1/2011    MIP Z    (null)       100      0
1/1/2011    MIP Z    A            0        10
1/1/2011    MIP Z    B            0        15

I'd like to fill in the same input value in the input column throughout all the rows that have the same date and MIP.

Output should look like this

Date    MIP_Desc    Error_Code    Input    Defects
1/1/2011    MIP Z    (null)       100      0
1/1/2011    MIP Z    A            100      10
1/1/2011    MIP Z    B            100      15

Any tips?? Thanks again for the help

Answer 1

Another way to simplify this

concat(to_char(ProcessIn.SCAN_TIME, 'mm'),to_char(ProcessIn.SCAN_TIME, 'dd')) = concat(to_char(ProcessDefect.SCAN_TIME, 'mm'),to_char(ProcessDefect.SCAN_TIME, 'dd'))

would be to say something like

to_char(ProcessIn.SCAN_TIME, 'ddmm') = to_char(ProcessDefect.SCAN_TIME, 'ddmm').

To improve performance you could create function based indexes

create index scan_time_idx on ProcessIn(to_char(ProcessIn.SCAN_TIME, 'ddmm'));

create index scan_time_idx on ProcessIn(to_char(ProcessDefect.SCAN_TIME, 'ddmm'));

See http://www.akadia.com/services/ora_function_based_index_2.html for more information on function based indexes.

Alternatively you could store scan_time in a separate column called monthday on the 'data' table in the to_char(tablename.SCAN_TIME, 'ddmm') format, and index the new column.

Once created, your query would then look like this :-

With ProcessIn as
(
select Data.Scan_time, equipment.equipment_desc, data.serial_number,Data.monthday

from Data, equipment

where Data.equipment = equipment.equipm_barcode and data.quality_plan = 'N/A'    and        data.error_code = 'N/A' 
)

--Total process defects
,ProcessDefect as
(
select Data.Scan_time, equipment.equipment_desc, data.serial_number,Data.monthday

from Data, equipment

where Data.equipment = equipment.equipm_barcode and (data.quality_plan <> 'N/A' or         data.error_code <> 'N/A')
)

Select monthday as "Date", ProcessIn.equipment_desc,                                     count(ProcessIn.serial_number),     count(ProcessDefect.serial_number)

from ProcessIn 
Left Join ProcessDefect
On ProcessIn.monthday = ProcessDefect.monthday
and ProcessIn.equipment_desc = ProcessDefect.equipment_desc
group by ProcessIn.monthday, ProcessIn.equipment_desc
;

Answer 2

I am not an Oracle developer, but a huge red flag to me is the use of nested function invocations on both sides of your JOIN statement:

On concat(to_char(ProcessIn.SCAN_TIME, 'mm'),to_char(ProcessIn.SCAN_TIME, 'dd')) =
    concat(to_char(ProcessDefect.SCAN_TIME, 'mm'),to_char(ProcessDefect.SCAN_TIME, 'dd')) 

In SQL Server, this is a pretty strong indicator that the optimizer is going to have trouble using indexes to perform the join operation. If your subqueries yield large record sets, the JOIN could take a very, very long time to complete.

If you can modify the ON clause to not require function invocations (on both sides if possible, but at least on one) it will probably help performance quite a bit.