Solved

script change to use cursors

Posted on 2011-03-22
3
253 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

717 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