[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


script change to use cursors

Posted on 2011-03-22
Medium Priority
Last Modified: 2012-05-11

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');

--get all transfers and their different carriers
create global temporary table kanreports.br_first_tsfs on commit preserve rows
--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'
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'
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
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
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
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
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,
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);
Question by:nocinfospan
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
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  => ....)

Author Comment

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

LVL 15

Accepted Solution

Devinder Singh Virdi earned 2000 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.

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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 setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

650 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