retrieving from records from cursor is slow?

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
sakthikumarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
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
PortletPaulfreelancerCommented:
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
Geert GOracle dbaCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PortletPaulfreelancerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark GeerlingsDatabase AdministratorCommented:
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
slightwv (䄆 Netminder) Commented:
>> 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.