Home » SQL & PL/SQL » SQL & PL/SQL » query long time execution (10.2.0.3.0)
query long time execution [message #403219] Thu, 14 May 2009 05:31 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All,

I have written the following query. But it's taking more than 1 hour for retrieving 10000 records.can you please look int this and give me your suggestions please.

SELECT    dn_num
       || CHR (10)
       || (SELECT prm_txt
             FROM tmpo.prm
            WHERE SUBSTR (prm_id_d, 1, 2) = l.lng_shdes
              AND prm_id_h = 'INSSURANCE')
  FROM bsc.ccontact_all cc,
       bsc.customer_all cu,
       bsc.LANGUAGE l,
       bsc.contr_services_cap cos_c,
       bsc.contr_services COS,
       bsc.directory_number dn,
       bsc.contract_all ca
 WHERE COS.co_id = cos_c.co_id
   AND cos_c.dn_id = dn.dn_id
   AND COS.co_id = ca.co_id
   AND ca.customer_id = cu.customer_id
   AND cc.customer_id = ca.customer_id
   AND COS.sncode IN (7130, 7131, 7132)
   AND COS.cs_stat_chng LIKE ('%a')
   AND LENGTH (COS.cs_stat_chng) = 7
   AND TO_DATE (SUBSTR (COS.cs_stat_chng, 1, 6), 'YYMMDD')
          BETWEEN (SELECT TO_DATE (prm_txt, 'YYYYMMDD')
                     FROM tmp.prm
                    WHERE prm_id_d = 'ACTIV_DT' AND prm_id_h = 'INSSURANCE')
              AND TRUNC (SYSDATE - 1)
   AND cu.prgcode IN (1, 2, 7)
   AND cc.ccbill = 'X'
   AND cos_c.sncode = 1
   AND cos_c.cs_deactiv_date IS NULL
   AND l.lng_id = cc.cclanguage;


Thank you in advance...!!
Re: query long time execution [message #403226 is a reply to message #403219] Thu, 14 May 2009 05:54 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
That (as usual) would depend on a lot of things you (as usual) don't tell us.

Can you tell me what's on the bottom shelf on the left in my cellar? No? Why not? If you can figure that one out, you will perhaps finally see why all your questions are UNANSWERABLE


icon10.gif  Re: query long time execution [message #403230 is a reply to message #403226] Thu, 14 May 2009 06:33 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
It must be the masochist in me, but I truly enjoy 71408's (I feel that we're familiar enough that I can use an abbreviated name) posts. It is a constant source of amusement between me and my colleagues.
Re: query long time execution [message #403235 is a reply to message #403230] Thu, 14 May 2009 06:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I truly enjoy 71408's (I feel that we're familiar enough that I can use an abbreviated name)

./fa/449/0/

Re: query long time execution [message #403250 is a reply to message #403230] Thu, 14 May 2009 07:54 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
pablolee wrote on Thu, 14 May 2009 07:33
It must be the masochist in me, but I truly enjoy 71408's (I feel that we're familiar enough that I can use an abbreviated name) posts. It is a constant source of amusement between me and my colleagues.


Me too. I enjoy laughing the first thing in the morning. Puts me in a good mood for the rest of the day. People come in here and ask me why I have this crazy-a** grin so early in the morning.
Re: query long time execution [message #403259 is a reply to message #403250] Thu, 14 May 2009 08:37 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
./fa/451/0/

And I would DEFINETLY like to be there and to the cheerleading when his employer finds out that he posted tons of probably copyrighted and perhaps even confidential code "as is" here (and as it seems a few other forums all over the internet), because he isn't even able to create test cases for his problems.

./fa/1963/0/

Re: query long time execution [message #403269 is a reply to message #403219] Thu, 14 May 2009 09:19 Go to previous message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
How do you know that the result set is correct?

I suspect delay involves a Cartesian Product.
Previous Topic: Read CSV file in PLSQL
Next Topic: select statement in collections
Goto Forum:
  


Current Time: Sat Dec 03 18:16:13 CST 2016

Total time taken to generate the page: 0.15947 seconds