Solved

script change to use cursors

Posted on 2011-03-22
3
247 Views
Last Modified: 2012-05-11
Hi,

I need to modify the below mentioned script to use cursors (perferably cursor for loop ) to replace the temp tables. Any help on this will be appriciated. Thans

delete from kanreports.br_traffic_first_worksite
where transfer_date >= to_char (sysdate-4,'yyyy/mm/dd')
and transfer_date < to_char (sysdate,'yyyy/mm/dd');
commit;



--get all transfers and their different carriers
create global temporary table kanreports.br_first_tsfs on commit preserve rows
as
--AUTO KTX count all companies as 1
select distinct t.transfer_date, substr(t.quote_ref_id,1,14) as quote_ref_id, t.province,
case when t.carrier in  ( select distinct company_id
                          from live.company_info@KANDBRAC.WAREHOUSE.HO.KANETIX.COM
                          where supplier_id = 'inssolutions'
                          and company_id != 'ELT'
                        )
then 'KTX' else t.carrier end as carrier, product
from kanreports.distinct_transfers t
where t.transfer_date >= to_char (sysdate-4,'yyyy/mm/dd')
and transfer_date < to_char (sysdate,'yyyy/mm/dd')
and t.product = 'Auto'
UNION
--V2C
select distinct t.transfer_date, substr(t.quote_ref_id,1,14) as quote_ref_id,
t.province,t.carrier, product
from kanreports.distinct_transfers t
where t.transfer_date >= to_char (sysdate-4,'yyyy/mm/dd')
and transfer_date < to_char (sysdate,'yyyy/mm/dd')
and t.product = 'Travel Visitor'
UNION
--OTHER PRODUCTS
select distinct t.transfer_date, substr(t.quote_ref_id,1,14) as quote_ref_id,
t.province,t.carrier, product
from kanreports.distinct_transfers t
where t.transfer_date >= to_char (sysdate-4,'yyyy/mm/dd')
and transfer_date < to_char (sysdate,'yyyy/mm/dd')
and t.product in ('Property','Life','Moto','Mortgage','Credit Card','Travel','Health');


-- get all the min times for the transfer QRNs
create global temporary table kanreports.br_first_qrn_min_time on commit preserve rows
as
select ql.quote_ref_id, min(ql.quote_time) as min_time
from kanreports.quote_logger ql, kanreports.br_first_tsfs f
where ql.quote_ref_id = f.quote_ref_id

group by ql.quote_ref_id;


-- Transfer's first employee id
create global temporary table kanreports.br_first_qrn_employee on commit preserve rows
as
select distinct ql.quote_ref_id, ql.employee_id
from kanreports.quote_logger ql, kanreports.br_first_qrn_min_time b
where ql.quote_ref_id = b.quote_ref_id
and ql.quote_time = b.min_time
;

--transfer's employee id' mintime
create global temporary table kanreports.br_first_emp_min_time on commit preserve rows
as
select ql.employee_id, min(ql.quote_time) as min_time
from kanreports.quote_logger ql, kanreports.br_first_qrn_employee f
where ql.employee_id = f.employee_id
group by ql.employee_id;


-- Transfer's mintime employer id
create global temporary table kanreports.br_first_emp_emper on commit preserve rows
as
select distinct ql.employee_id, ql.employer_id, e.worksite_name, e.domain
from kanreports.quote_logger ql, kanreports.br_first_emp_min_time b,
live.employer@KANDBRAC.WAREHOUSE.HO.KANETIX.COM e
where ql.employee_id = b.employee_id
and ql.quote_time = b.min_time
and ql.employer_id = e.employer_id;


insert into br_traffic_first_worksite
select distinct t.transfer_date, t.quote_ref_id, t.province, t.carrier, t.product,
w.worksite_name, w.domain
from kanreports.br_first_tsfs t
left join kanreports.br_first_qrn_employee e
on t.quote_ref_id = e.quote_ref_id
left join kanreports.br_first_emp_emper w
on e.employee_id = w.employee_id
where ( (w.worksite_name <> 'BA_compare' and w.worksite_name <> 'BA_compare_fr')
        or w.worksite_name is null);
commit;
0
Comment
Question by:nocinfospan
  • 2
3 Comments
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 35199024
Why you want Cursor approach?
This approach uses more resource and provides very bad performance.

You code is collecting distinct XXX_ID, YYYY_ID and then find minimum value based on that, which you can do it in one step as well using MIN() OVER(PARTITION ... ).

I would suggest to minimize your code and avoid cursor.
If you want some kind of error reporting, then you can use
dbms_errlog.create_error_log(dml_table_name =>... err_log_table_name  => ....)
0
 
LVL 1

Author Comment

by:nocinfospan
ID: 35206616
Thanks for the reply. The reason I want to switch over to cursors is that in my code I m creating multiple temp table that requires access to dictionary and perform i/o. So my understanding is that if i conver the whole thing into cursors and sub queries I can avoid unnecessary calls to disk / data dictionary and if required i can pin the code in the memory. Any advise on this concept will be appriciated

0
 
LVL 15

Accepted Solution

by:
Devinder Singh Virdi earned 500 total points
ID: 35207290
Cursors always used maximum resources. Consider the following example.
1.  Code will be in PL block which will run under PL engine.
2.  You have FOR loop that uses cursor.
3.  Now you have some IF--ELSE--END conditions etc.
4.  This time you are trying to fetch the records. THIS IS MOST EXPENSIVE.
     Oracle internally will hard parse your statement.
     ie find if there is syntax error, current lock on object, grant permission, its previous execution plan, which plan is good plan, if required create another plan(Hard Parse) etc.
5.  Go to 2.

So step 4 will take more resources, whereas during temp table, step 4 will runs one with full capacity and therefore it will save your time/resource.
Moreover if you there are index scan that is causing slowness, you can think of Parallel and Full hint.
Right now I cannot say Parallel, Full Table scan or Index can is good for your queries without any analysis.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

806 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question