On Dec 16, 9:32 am, "karthi keyan" <karthi...@gmail.com> 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 Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
