Yes Mr. David, You are right, I did write to TEMP. I have re-written the view using DECODE & found the processing time got reduced to some extent, To be honest I am not aware of using V$SESSION_LONGOPS.....
I will ask my superiors on that.. Anyways thanks a lot for your reply.... On Tue, Dec 16, 2008 at 9:21 PM, ddf <[email protected]> wrote: > > > > On Dec 16, 9:32 am, "karthi keyan" <[email protected]> wrote: > > Hi All, > > > > I have created a view using the following syntax. > > > > CREATE OR REPLACE VIEW tax_information (transaction_key, > > > > transaction_type, > > > > pid, > > > > tax_description_1, > > > > tax_amount1, > > > > tax_description_2, > > > > tax_amount2, > > > > tax_description_3, > > > > tax_amount3, > > > > tax_description_4, > > > > tax_amount4, > > > > tax_description_5, > > > > tax_amount5, > > > > tax_description_6, > > > > tax_amount6 > > > > ) > > > > AS > > > > SELECT DISTINCT a.transaction_key, a.transaction_type, a.pid, > > > > x1.tax_description_1, x1.tax_amount1, x2.tax_description_2, > > > > x2.tax_amount2, x3.tax_description_3, x3.tax_amount3, > > > > x4.tax_description_4, x4.tax_amount4, x5.tax_description_5, > > > > x5.tax_amount5, x6.tax_description_6, x6.tax_amount6 > > > > FROM (SELECT transaction_key, pid, > > > > tax_description AS tax_description_1, > > > > tax_amount AS tax_amount1 > > > > FROM rottaxdetail > > > > WHERE display_order = 1) x1, > > > > (SELECT transaction_key, pid, > > > > tax_description AS tax_description_2, > > > > tax_amount AS tax_amount2 > > > > FROM rottaxdetail > > > > WHERE display_order = 2) x2, > > > > (SELECT transaction_key, pid, > > > > tax_description AS tax_description_3, > > > > tax_amount AS tax_amount3 > > > > FROM rottaxdetail > > > > WHERE display_order = 3) x3, > > > > (SELECT transaction_key, pid, > > > > tax_description AS tax_description_4, > > > > tax_amount AS tax_amount4 > > > > FROM rottaxdetail > > > > WHERE display_order = 4) x4, > > > > (SELECT transaction_key, pid, > > > > tax_description AS tax_description_5, > > > > tax_amount AS tax_amount5 > > > > FROM rottaxdetail > > > > WHERE display_order = 5) x5, > > > > (SELECT transaction_key, pid, > > > > tax_description AS tax_description_6, > > > > tax_amount AS tax_amount6 > > > > FROM rottaxdetail > > > > WHERE display_order = 6) x6, > > > > rottaxdetail a > > > > WHERE a.transaction_key = x1.transaction_key(+) > > > > AND a.transaction_key = x2.transaction_key(+) > > > > AND a.transaction_key = x3.transaction_key(+) > > > > AND a.transaction_key = x4.transaction_key(+) > > > > AND a.transaction_key = x5.transaction_key(+) > > > > AND a.transaction_key = x6.transaction_key(+); > > > > When I run this query my system gets hanged and I am not getting any > result. > > > > Totally there are 25360 records in rottaxdetail. > > > > when I run the following query alone it retreives 128 records however the > > execution time is 11 seconds (!!!) > > > > select TRANSACTION_KEY, TAX_DESCRIPTION as Tax_description_1, TAX_AMOUNT > as > > Tax_Amount1 from ROTTAXDETAIL where display_order = 1 > > > > when I run the following query the system hangs.. > > > > select distinct A.TRANSACTION_KEY, x1.Tax_description_1, x1.Tax_Amount1, > > x2.Tax_description_2, x2.Tax_Amount2 from > > (select TRANSACTION_KEY, TAX_DESCRIPTION as Tax_description_1, TAX_AMOUNT > as > > Tax_Amount1 from ROTTAXDETAIL where display_order = 1)x1, > > (select TRANSACTION_KEY, TAX_DESCRIPTION as Tax_description_2, TAX_AMOUNT > as > > Tax_Amount2 from ROTTAXDETAIL where display_order = 2)x2, ROTTAXDETAIL A > > where A.TRANSACTION_KEY = x1.TRANSACTION_KEY(+) and > > A.TRANSACTION_KEY = x2.TRANSACTION_KEY(+); > > > > Where did I go wrong, can any one, help me out? > > > > Regards, > > Karthik... > > I'm betting you're writing to TEMP and it's taking a while so the > system doesn't 'hang', it's just taking a long while to process what > you've asked for. You could possibly query V$SESSION_LONGOPS to see > what's taking the time, or you could set a 10046 event at level 8 to > see what the query is doing and what resources it may be waiting on. > > Without some sort of trace output (10046, tkprof, statspack) it's > difficult to tell you what is happening and how to possibly fix it. > > > David Fitzjarrell > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en -~----------~----~----~----~------~----~------~--~---
