Home » SQL & PL/SQL » SQL & PL/SQL » how to rewrite the query the sub query. (oracle 10g)
how to rewrite the query the sub query. [message #431062] Sat, 14 November 2009 03:51 Go to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
WHEN i remove subquery its executing fast.EACH DOCUMENT_ID may exist either in REFDOCNO or in ALLOCATIONNUMBER in the DC_INVOICE_ALLOCATION table;WHEN i append the sub query its taking lot of time;

how to rewrite the query the sub query.



SELECT DOCUMENT_ID DOCUMENTREFRENCENO,
            (SELECT COUNT(*)
                     FROM DC_INVOICE_ALLOCATION IA
                    WHERE DOC.DOCUMENT_ID IN
                          (IA.REFDOCNO,IA.ALLOCATIONNUMBER)) COUNT
  FROM DC_DOCUMENT DOC
 WHERE DOCUMENT_ID LIKE '%'
   AND DOCUMENT_TYPE_CODE IN ('MAIN_INVOICE', 'SUB_INVOICE')
   AND DOCUMENT_DATE >= '30-Oct-2009'
   AND DOCUMENT_DATE <= '14-Nov-2009'
   AND COMPANY_ID = 'IND'
 ORDER BY DOCUMENT_ID DESC;


Re: how to rewrite the query the sub query. [message #431063 is a reply to message #431062] Sat, 14 November 2009 04:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The "subquery" is executed for each row of the outer one.
Put it in FROM clause and add a group by clause.

'30-Oct-2009' and '14-Nov-2009' are strings and not DATE, this implies implicit conversion (which may not work) AND prevent from some index usage AND may lead to wrong results.

Regards
Michel

[Updated on: Sat, 14 November 2009 04:03]

Report message to a moderator

Re: how to rewrite the query the sub query. [message #431064 is a reply to message #431062] Sat, 14 November 2009 04:17 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
AS you pointed i changed into group by clause.Is this ok?
SELECT DOCUMENT_ID DOCUMENTREFRENCENO,count(*)

FROM DC_DOCUMENT DOC,DC_INVOICE_ALLOCATION IA
WHERE DOCUMENT_ID LIKE '%'
AND DOCUMENT_TYPE_CODE IN ('MAIN_INVOICE', 'SUB_INVOICE')
AND DOCUMENT_DATE >= '30-Oct-2009'
AND DOCUMENT_DATE <= '14-Nov-2009'
AND COMPANY_ID = 'IND'
AND (DOC.DOCUMENT_ID = IA.REFDOCNO OR DOC.DOCUMENT_ID =IA.ALLOCATIONNUMBER)
GROUP BY DOCUMENT_ID;
ORDER BY DOCUMENT_ID DESC;
Re: how to rewrite the query the sub query. [message #431084 is a reply to message #431064] Sat, 14 November 2009 10:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is ok but for:
Quote:
'30-Oct-2009' and '14-Nov-2009' are strings and not DATE, this implies implicit conversion (which may not work) AND prevent from some index usage AND may lead to wrong results.

Regards
Michel
Re: how to rewrite the query the sub query. [message #431129 is a reply to message #431084] Sun, 15 November 2009 16:13 Go to previous message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member

  1. There is WITH in Oracle - you might want to use it from time to time...
    WITH x AS SELECT 1 FROM DUAL
    SELECT 2 FROM x

  2. Use "explain plan" and check how your query is executed.
  3. Avoid "SELECT COUNT(...)" unless you need to know how many items are there in whole table (and you are aware that the query might take a long while to execute).
  4. Use EXISTS if you wish to just check if an item exists - Oracle SHOULD finish the search after first item if match is found.
  5. Imagine the following query execution:
    SELECT 1 FROM DC_DOCUMENT WHERE DOCUMENT_DATE = '2009 15 listopad 59:23:59'

    That query would execute perfectly with my locales... Why it would fail for you? Don't you know what is listopad? or maybe you don't know that weird date format? This is what Michel pointed out twice.
    Never use hard-coded date/time format.
    Never assume that comma or dot is your decimal separator.
    Do not use weird US data format MMDDYY or any other weird local date formats. Read about ISO-8601 and use local date format only if the user specified it so (or you have other reason for such thing).
  6. Use normalization rules for your database.
  7. Read wiki of the OraFAQ.
  8. You query scans the data range in 2 steps (which you might notice when you check the query execution plan and statistics): first it selects everything that has date >='2009-10-30' and then filters out the results - this is how Oracle's "date" works like.
  9. Your table has weird values - usually ID is a number and things like DOCUMENT_CODE would be kept in a separate table.
  10. DOCUMENT LIKE '%' is doing nothing and is not using indexing.
  11. TO_DATE('2009-10-30','YYYY-MM-DD') is a date.

[Updated on: Sun, 15 November 2009 16:16]

Report message to a moderator

Previous Topic: Oracle 9i (merged)
Next Topic: Oracle DATE Problem
Goto Forum:
  


Current Time: Sun Dec 04 08:25:12 CST 2016

Total time taken to generate the page: 0.14325 seconds