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
Solved

retrieving from records from cursor is slow?

Posted on 2014-09-15
6
217 Views
Last Modified: 2014-10-14
Hi,

I have attached my query here. when I can executing from plsql developer , it hardly takes 2 secs.

but when putting in a ref cursor and executing through test window.

It is taking 10 secs to get results to from cursor handle in test window of plsql developer.

Experts, please help.
qry.txt
0
Comment
Question by:sakthikumar
6 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40323177
To start: There is NEVER a reason to use "SELECT DISTINCT" and UNION in a single query.

You are using DISTINCT in 2 places, both should be removed as they are redundant.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40323181
NULL cannot, ever, equal anything.

In other words these do not work:
      AND (NULL IS NULL OR CC.BOE_NO = NULL)
      AND (NULL IS NULL OR CC.RECEIPT_NO = NULL)
      AND (NULL IS NULL OR (CD.BRN_NO = NULL OR CD.BOL_NO = NULL))
      AND (NULL IS NULL OR CC.CUSTOMER_CODE = NULL)

use IS NULL instead
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 40323202
use bindvariables
i guess your generating this query somehow ?
>> or is rotation_no always this specific value ?
(677278 IS NULL OR CC.ROTATION_NO = 677278)

and fwiw ... 677278 IS NUL will always be false
0
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.

 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40323228
I don't understand why you run row_number() over a table and join it back to itself. You can avoid that join.

For the rest I'm not too keen on the correlated subquery to achieve ROTATION_NO (lines 33-39)  but I don't know enough about your data model to suggest an alternate.

I suggest you try UNION ALL. I cannot tell if you really need to remove unwanted repetition from the result, but UNION ALL is faster than UNION.
SELECT
      NVL(CD.BOL_NO, CD.BRN_NO) "BRN/BOL"
    , CC.ROTATION_NO
    , CC.DOC_NO
    , CC.TOTAL_AMOUNT
    , 'GC'
    , CC.RECEIPT_NO
    , CC.COLLECT_DATE
    , CC.CLR_NO
FROM (
           SELECT
                 G.CHARGE_COLLECTION_ID
               , G.ROTATION_NO
               , G.DOC_NO
               , G.TOTAL_AMOUNT
               , G.RECEIPT_NO
               , G.COLLECT_DATE
               , ROW_NUMBER() OVER (PARTITION BY G.BOE_NO ORDER BY G.COLLECT_DATE ASC) CLR_NO
           FROM GCB_DBA.GCB_CHARGE_COLLECTION G
     ) CC
INNER JOIN GCB_DBA.GCB_CHARGE_DTLS CD
      ON CD.CHARGE_COLLECTION_ID = CC.CHARGE_COLLECTION_ID
WHERE CC.STATUS <> 'R'
      AND CD.IS_VALID = 1
      AND (677278 IS NULL OR CC.ROTATION_NO = 677278)
      AND (NULL IS NULL OR CC.BOE_NO IS NULL)
      AND (NULL IS NULL OR CC.RECEIPT_NO IS NULL)
      AND (NULL IS NULL OR CD.BRN_NO IS NULL OR CD.BOL_NO IS NULL)
      AND (NULL IS NULL OR CC.CUSTOMER_CODE IS NULL)
UNION ALL
      SELECT
            NULL      "BRN/BOL"
          , (
                  SELECT
                        BC.ROTN
                  FROM GCB_DBA.OPBE_BOL_CHARGES BC
                  WHERE C.PAY_SR_NO = BC.PAY_SR_NO
                        AND ROWNUM = 1
            ) ROTATION_NO
          , C.BOE_NO  DOC_NO
          , C.TOT_AMT TOTAL_AMOUNT
          , 'OPBE'
          , C.RCPT_NO RECEIPT_NO
          , C.COLLECT_DATE
          , NULL      CLR_NO
      FROM GCB_DBA.OPBE_CHARGE_COLLECTION C
      WHERE C.OTHER_BOE_TYPE = 'N'
            AND NVL(C.DEL_IND, 'N') = 'N'
            AND TXN_SOURCE IS NULL
            AND EXISTS (
                  SELECT
                        1
                  FROM GCB_DBA.OPBE_BOL_CHARGES BC
                  WHERE BC.PAY_SR_NO = C.PAY_SR_NO
                        AND NVL(BC.DEL_IND, 'N') = 'N'
                        AND (677278 IS NULL
                        OR BC.ROTN = 677278)
                        AND (NULL IS NULL
                        OR BC.IMP_CODE = NULL)
            )
            AND (NULL IS NULL
            OR C.BOE_NO = 677278)
            AND (NULL IS NULL
            OR C.RCPT_NO = NULL)
UNION ALL
      SELECT
            NULL      "BRN/BOL"
          , BC.ROTN   ROTATION_NO
          , C.BOE_NO  DOC_NO
          , C.TOT_AMT TOTAL_AMOUNT
          , 'MDS'
          , C.RCPT_NO RECEIPT_NO
          , C.CLR_DATE
          , C.CLR_NO  CLEARANCE_NO
      FROM GCB_DBA.OPBE_BOE_CLEARANCES C
         , GCB_DBA.OPBE_BOE_CHARGES BC
      WHERE C.BOE_NO = BC.BOE_NO
            AND C.CLR_NO = BC.CLR_NO
            AND C.BOE_TYPE = BC.BOE_TYPE
            AND NOT EXISTS (
                  SELECT
                        1
                  FROM GCB_DBA.GCB_CHARGE_COLLECTION CC
                  WHERE CC.BOE_NO = C.BOE_NO
                        AND CC.RECEIPT_NO = C.RCPT_NO
                        AND CC.STATUS <> 'R'
            )
            AND (677278 IS NULL
            OR BC.ROTN = 677278)
            AND (NULL IS NULL
            OR C.BOE_NO = NULL)
            AND (NULL IS NULL
            OR C.RCPT_NO = NULL);

Open in new window

You may need to revert back to UNION
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40323598
I agree that "UNION ALL" is faster then "UNION", because "UNION" will take the time and resources to do a sort operation and remove possible duplicates.  But, if the data from the three different secions of your query are already distinct, you can skip the extra sort and use "UNION ALL".

And, I agree that lines like this look very strange and not at all helpful:
AND NULL IS NULL OR CC.BOE_NO = NULL
1. This part will never be true: "CC.BOE_NO = NULL"
2. This part may always be true "NULL IS NULL" , so why include it?  (I have never tested this syntax, but I don't see what advantage it may possibly add.)
3. If you change that line to this: "AND CC.BOE_NO IS NULL" then it makes sense.  This doesn't help Oracle execute the query efficiently though - it forces Oracle to read every record that meets the other criteria in this section of the query.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40324157
>> it hardly takes 2 secs.  but when putting in a ref cursor and executing through test window.  It is taking 10 secs to get results to from cursor handle in test window of plsql developer.

I think the SQL piece has been covered.  I wanted to comment on the time differences:
Don't know a lot about pl/sql developer but most GUIs like it and SQL Deverloper buffer the results so it will start returning them as soon as it can and does not return the ENTIRE result set at once.

When calling from cursor, it is likely returning ALL rows at once.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
selective queries 7 37
error in my cursor 5 41
Fill Null values 5 28
Loading flat file data in tables 2 39
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

766 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