Solved

script change to use cursors

Posted on 2011-03-22
3
245 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
Comment Utility
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
Comment Utility
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
Comment Utility
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.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
oracle query help 18 74
EXECUTE IMMEDIATE 5 33
What is the version of ojdbc6.jar 2 23
dates - loop 12 39
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 …
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to recover a database from a user managed backup

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now