Re: Processing different cursor types with the same procedure/function
Date: 13 Aug 2002 16:00:56 -0700
Message-ID: <92eeeff0.0208131500.3b539033_at_posting.google.com>
martinbertolino_at_yahoo.com (Martin Bertolino) wrote in message news:<95be4880.0208130818.7a5e506_at_posting.google.com>...
> If I use one of these two set operators to combine the cursors into
> one wouldn't the resulting operation be more expensive to execute
> given that both cursor plans have to be executed?
>
> Also in the example I posted, if I have the SSN I don't have the
> identifier and vice-versa if I have the identifier I don'h have the
> SSN.
>
> I guess I could just supply null values for each of these when I do
> not have them, but I'm not sure about complicating the execution plan
> (if my understanding about combining the queries is correct) in order
> to simplify code and help manintenace.
>
> Thanks
>
> Martin Bertolino
I had not tested the query but my hunch is that it will be faster then your existing code which is running multiple redundant loops. In any case, the other approach is to create an index on t0.SSN and t0.IDENTIFIER columns and use OR operator,
cursor v_employee_company_cursor is
select
t0.employee_id, t0.payroll_master_record, t0.benefits_master_record, t1.max_as_of_date, t2.data_source_user_id,
rownum
from employee_company t0, work_time t1, data_source t2 where
t0.company_id = p_company_id and
(t0.identifier = p_employee_id OR t0.ssn = p_employee_id) and t0.work_time_list_id = t1.work_time_list_id and t0.data_source_id = t2.data_source_id(+);
You can run both UNION and this query from sqlplus to see which one is more cost effective.
//Rauf Sarwar Received on Wed Aug 14 2002 - 01:00:56 CEST