Solved

script change to use cursors

Posted on 2011-03-22
3
249 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
make null the repeated levels 2 36
update statement in oracle 9 40
oracle differnce between two timestamps 5 38
error starting form builder in 11g 2 25
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 …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

756 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