Home » RDBMS Server » Performance Tuning » Help in tuning a value set query
Help in tuning a value set query [message #506912] Wed, 11 May 2011 15:42 Go to next message
kavitayetakuri
Messages: 7
Registered: May 2011
Location: United States
Junior Member
Hi All,

Can anyone please help me in tuning a value set query, this query has to fetch eligible data for last 6 months.

Below is the query:

select /*+ index(a OE_ORDER_HEADERS_U2) */ ORDER_NUMBER
from OE_ORDER_HEADERS_ALL a
where to_char(order_number) in(
SELECT to_char(ooha.order_number)
FROM oe_order_headers_all ooha
WHERE ooha.cancelled_flag = 'N'
AND ooha.open_flag = 'Y'
AND :ORDER_cancelled_flag = 'N'
AND :ORDER_open_flag = 'Y'
AND NOT EXISTS (
SELECT 1
FROM oe_order_lines_all oola
WHERE oola.header_id = ooha.header_id
AND oola.invoice_interface_status_code = 'YES')
AND ooha.sold_to_org_id = :ORDER_SOLD_TO_ORG_ID
AND ( ooha.attribute16 = to_char(ooha.order_number)
)
AND :SYSTEM_CURRENT_BLOCK='ORDER'
union all
SELECT to_char(ooha.order_number)
FROM oe_order_headers_all ooha
WHERE ooha.cancelled_flag = 'N'
AND ooha.open_flag = 'Y'
AND :ORDER_cancelled_flag = 'N'
AND :ORDER_open_flag = 'Y'
AND NOT EXISTS (
SELECT 1
FROM oe_order_lines_all oola
WHERE oola.header_id = ooha.header_id
AND oola.invoice_interface_status_code = 'YES')
AND ooha.sold_to_org_id = :ORDER_SOLD_TO_ORG_ID
AND ( ooha.order_number = :ORDER_ORDER_NUMBER
)
AND :SYSTEM_CURRENT_BLOCK='ORDER'
union all
select decode(:SYSTEM_CURRENT_BLOCK,'ORDER',(select NVL(to_char(:ORDER_order_number),'123') from dual where :ORDER_cancelled_flag = 'Y'
) )
from dual
union all
select decode(:SYSTEM_CURRENT_BLOCK,'ORDER',(select NVL(to_char(:ORDER_order_number),'123') from dual where :ORDER_open_flag = 'N') )
from dual
union all
select /*+ index(ooh IKN_OE_HEADERS_ALL_PRF_N1) */
to_char(ooh.order_number) from oe_order_headers_all ooh where :SYSTEM_CURRENT_BLOCK <> 'ORDER'
and creation_date > sysdate - 90
)


Also find the explain plan below:


SELECT STATEMENT ALL_ROWSCost: 16,983,189 Bytes: 18,789,118 Cardinality: 507,814
26 HASH JOIN Cost: 16,983,189 Bytes: 18,789,118 Cardinality: 507,814
24 VIEW VIEW SYS.VW_NSO_1 Cost: 400,631 Bytes: 11,171,446 Cardinality: 507,793
23 HASH UNIQUE Cost: 400,631 Bytes: 8,632,497 Cardinality: 507,793
22 UNION-ALL
6 FILTER
5 NESTED LOOPS ANTI Cost: 97 Bytes: 40 Cardinality: 1
2 TABLE ACCESS BY INDEX ROWID TABLE ONT.OE_ORDER_HEADERS_ALL Cost: 93 Bytes: 27 Cardinality: 1
1 INDEX RANGE SCAN INDEX IKNCU.IKN_OE_ORDER_HEADERS_PRF_N3 Cost: 3 Cardinality: 93
4 TABLE ACCESS BY INDEX ROWID TABLE ONT.OE_ORDER_LINES_ALL Cost: 4 Bytes: 404,500,590 Cardinality: 31,115,430
3 INDEX RANGE SCAN INDEX ONT.OE_ORDER_LINES_N1 Cost: 3 Cardinality: 2
12 FILTER
11 NESTED LOOPS ANTI Cost: 8 Bytes: 44 Cardinality: 1
8 TABLE ACCESS BY INDEX ROWID TABLE ONT.OE_ORDER_HEADERS_ALL Cost: 4 Bytes: 31 Cardinality: 1
7 INDEX RANGE SCAN INDEX (UNIQUE) ONT.OE_ORDER_HEADERS_U2 Cost: 3 Cardinality: 1
10 TABLE ACCESS BY INDEX ROWID TABLE ONT.OE_ORDER_LINES_ALL Cost: 4 Bytes: 404,500,590 Cardinality: 31,115,430
9 INDEX RANGE SCAN INDEX ONT.OE_ORDER_LINES_N1 Cost: 3 Cardinality: 2
14 FILTER
13 FAST DUAL Cost: 2 Cardinality: 1
15 FAST DUAL Cost: 2 Cardinality: 1
17 FILTER
16 FAST DUAL Cost: 2 Cardinality: 1
18 FAST DUAL Cost: 2 Cardinality: 1
21 FILTER
20 TABLE ACCESS BY INDEX ROWID TABLE ONT.OE_ORDER_HEADERS_ALL Cost: 400,522 Bytes: 8,632,413 Cardinality: 507,789
19 INDEX RANGE SCAN INDEX ONT.IKN_OE_HEADERS_ALL_PRF_N1 Cost: 2,295 Cardinality: 507,789
25 INDEX FULL SCAN INDEX (UNIQUE) ONT.OE_ORDER_HEADERS_U2 Cost: 105,323 Bytes: 310,485,750 Cardinality: 20,699,050
Re: Help in tuning a value set query [message #506917 is a reply to message #506912] Wed, 11 May 2011 16:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) output from SQL_TRACE & tkprof

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Especially part about FORMAT to improve readibility
Re: Help in tuning a value set query [message #506930 is a reply to message #506917] Wed, 11 May 2011 21:35 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
Focus on these:

24 VIEW VIEW SYS.VW_NSO_1 Cost: 400,631 Bytes: 11,171,446 Cardinality: 507,793
and
25 INDEX FULL SCAN INDEX (UNIQUE) ONT.OE_ORDER_HEADERS_U2 Cost: 105,323 Bytes: 310,485,750 Cardinality: 20,699,050

[Updated on: Thu, 12 May 2011 03:30] by Moderator

Report message to a moderator

Re: Help in tuning a value set query [message #506992 is a reply to message #506912] Thu, 12 May 2011 03:20 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
kavitayetakuri wrote on Wed, 11 May 2011 21:42
this query has to fetch eligible data for last 6 months.

Really? It doesn't appear to be doing that at moment. You've got one section with a date restriction of 90 days and the rest has no date restriction at all as far as I can see.
Re: Help in tuning a value set query [message #507107 is a reply to message #506930] Thu, 12 May 2011 09:20 Go to previous messageGo to next message
kavitayetakuri
Messages: 7
Registered: May 2011
Location: United States
Junior Member
To LKBrwn_DBA,

Can you please elaborate what exactly can I do to those two lines which you mentioned.

Thanks,
Kavita

[Updated on: Thu, 12 May 2011 09:22]

Report message to a moderator

Re: Help in tuning a value set query [message #507109 is a reply to message #507107] Thu, 12 May 2011 09:37 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Its ahrd to read but are you TO_CHAR() wrapping all the order numbers? If so, why?
Re: Help in tuning a value set query [message #507113 is a reply to message #506917] Thu, 12 May 2011 09:58 Go to previous messageGo to next message
kavitayetakuri
Messages: 7
Registered: May 2011
Location: United States
Junior Member
Hi Blackswan,

1. Please find the formatted code and requested data below:

SELECT /*+ index(a OE_ORDER_HEADERS_U2) */
       order_number
  FROM oe_order_headers_all a
 WHERE TO_CHAR (order_number) IN (
          SELECT TO_CHAR (ooha.order_number)
            FROM oe_order_headers_all ooha
           WHERE ooha.cancelled_flag = 'N'
             AND ooha.open_flag = 'Y'
             AND :order_cancelled_flag = 'N'
             AND :order_open_flag = 'Y'
             AND NOT EXISTS (
                    SELECT 1
                      FROM oe_order_lines_all oola
                     WHERE oola.header_id = ooha.header_id
                       AND oola.invoice_interface_status_code = 'YES')
             AND ooha.sold_to_org_id = :order_sold_to_org_id
             AND (ooha.attribute16 = TO_CHAR (ooha.order_number))
             AND :system_current_block = 'ORDER'
          UNION ALL
          SELECT TO_CHAR (ooha.order_number)
            FROM oe_order_headers_all ooha
           WHERE ooha.cancelled_flag = 'N'
             AND ooha.open_flag = 'Y'
             AND :order_cancelled_flag = 'N'
             AND :order_open_flag = 'Y'
             AND NOT EXISTS (
                    SELECT 1
                      FROM oe_order_lines_all oola
                     WHERE oola.header_id = ooha.header_id
                       AND oola.invoice_interface_status_code = 'YES')
             AND ooha.sold_to_org_id = :order_sold_to_org_id
             AND (ooha.order_number = :order_order_number)
             AND :system_current_block = 'ORDER'
          UNION ALL
          SELECT DECODE (:system_current_block,
                         'ORDER', (SELECT NVL (TO_CHAR (:order_order_number),
                                               '123'
                                              )
                                     FROM DUAL
                                    WHERE :order_cancelled_flag = 'Y')
                        )
            FROM DUAL
          UNION ALL
          SELECT DECODE (:system_current_block,
                         'ORDER', (SELECT NVL (TO_CHAR (:order_order_number),
                                               '123'
                                              )
                                     FROM DUAL
                                    WHERE :order_open_flag = 'N')
                        )
            FROM DUAL
          UNION ALL
          SELECT /*+ index(ooh IKN_OE_HEADERS_ALL_PRF_N1) */
                 TO_CHAR (ooh.order_number)
            FROM oe_order_headers_all ooh
           WHERE :system_current_block <> 'ORDER'
             AND creation_date > SYSDATE - 180)


2. Attached the explain plan below:

Plan
SELECT STATEMENT ALL_ROWSCost: 16,470,758 Bytes: 31,470,905 Cardinality: 850,565
26 HASH JOIN Cost: 16,470,758 Bytes: 31,470,905 Cardinality: 850,565
24 VIEW VIEW SYS.VW_NSO_1 Cost: 665,147 Bytes: 18,711,880 Cardinality: 850,540
23 HASH UNIQUE Cost: 665,147 Bytes: 14,459,196 Cardinality: 850,540
22 UNION-ALL
6 FILTER
5 NESTED LOOPS ANTI Cost: 96 Bytes: 40 Cardinality: 1
2 TABLE ACCESS BY INDEX ROWID TABLE ONT.OE_ORDER_HEADERS_ALL Cost: 92 Bytes: 27 Cardinality: 1
1 INDEX RANGE SCAN INDEX ONT.OE_ORDER_HEADERS_N2 Cost: 3 Cardinality: 92
4 TABLE ACCESS BY INDEX ROWID TABLE ONT.OE_ORDER_LINES_ALL Cost: 4 Bytes: 394,319,250 Cardinality: 30,332,250
3 INDEX RANGE SCAN INDEX ONT.OE_ORDER_LINES_N1 Cost: 3 Cardinality: 2
12 FILTER
11 NESTED LOOPS ANTI Cost: 8 Bytes: 44 Cardinality: 1
8 TABLE ACCESS BY INDEX ROWID TABLE ONT.OE_ORDER_HEADERS_ALL Cost: 4 Bytes: 31 Cardinality: 1
7 INDEX RANGE SCAN INDEX (UNIQUE) ONT.OE_ORDER_HEADERS_U2 Cost: 3 Cardinality: 1
10 TABLE ACCESS BY INDEX ROWID TABLE ONT.OE_ORDER_LINES_ALL Cost: 4 Bytes: 394,319,250 Cardinality: 30,332,250
9 INDEX RANGE SCAN INDEX ONT.OE_ORDER_LINES_N1 Cost: 3 Cardinality: 2
14 FILTER
13 FAST DUAL Cost: 2 Cardinality: 1
15 FAST DUAL Cost: 2 Cardinality: 1
17 FILTER
16 FAST DUAL Cost: 2 Cardinality: 1
18 FAST DUAL Cost: 2 Cardinality: 1
21 FILTER
20 TABLE ACCESS BY INDEX ROWID TABLE ONT.OE_ORDER_HEADERS_ALL Cost: 665,039 Bytes: 14,459,112 Cardinality: 850,536
19 INDEX RANGE SCAN INDEX ONT.IKN_OE_HEADERS_ALL_PRF_N1 Cost: 3,839 Cardinality: 850,536
25 INDEX FULL SCAN INDEX (UNIQUE) ONT.OE_ORDER_HEADERS_U2 Cost: 101,120 Bytes: 304,005,150 Cardinality: 20,267,010


3. Also find the DDL and indexes for the table as an attachment

4. Since it is a value set query attached to DFF I'm not sure how to provide tkprof and tracefile for this.

Thanks,
Kavita



  • Attachment: DDL_Index.txt
    (Size: 24.53KB, Downloaded 1835 times)
Re: Help in tuning a value set query [message #507114 is a reply to message #507109] Thu, 12 May 2011 10:09 Go to previous messageGo to next message
kavitayetakuri
Messages: 7
Registered: May 2011
Location: United States
Junior Member
To RoachCoach,

If I don't give To_char() its giving me datatype mismatch error for the union queries.

Regards,
Kavita
Re: Help in tuning a value set query [message #507115 is a reply to message #507113] Thu, 12 May 2011 10:13 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
          SELECT DECODE (:system_current_block,
                         'ORDER', (SELECT NVL (TO_CHAR (:order_order_number),
                                               '123'
                                              )
                                     FROM DUAL
                                    WHERE :order_cancelled_flag = 'Y')
                        )
            FROM DUAL
          UNION ALL
          SELECT DECODE (:system_current_block,
                         'ORDER', (SELECT NVL (TO_CHAR (:order_order_number),
                                               '123'
                                              )
                                     FROM DUAL
                                    WHERE :order_open_flag = 'N')
                        )
            FROM DUAL

Can be as 



          SELECT DECODE (:system_current_block,
                         'ORDER', (SELECT NVL (TO_CHAR (:order_order_number),
                                               '123'
                                              )
                                     FROM DUAL
                                    WHERE :order_cancelled_flag in ('Y','N')
Re: Help in tuning a value set query [message #507118 is a reply to message #507115] Thu, 12 May 2011 10:14 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
        SELECT TO_CHAR (ooha.order_number)
            FROM oe_order_headers_all ooha
           WHERE ooha.cancelled_flag = 'N'
             AND ooha.open_flag = 'Y'
             AND :order_cancelled_flag = 'N'
             AND :order_open_flag = 'Y'
             AND NOT EXISTS (
                    SELECT 1
                      FROM oe_order_lines_all oola
                     WHERE oola.header_id = ooha.header_id
                       AND oola.invoice_interface_status_code = 'YES')
             AND ooha.sold_to_org_id = :order_sold_to_org_id
             [color=red]AND (ooha.attribute16 = TO_CHAR (ooha.order_number))[/color]------------see here
             AND :system_current_block = 'ORDER'
          UNION ALL
          SELECT TO_CHAR (ooha.order_number)
            FROM oe_order_headers_all ooha
           WHERE ooha.cancelled_flag = 'N'
             AND ooha.open_flag = 'Y'
             AND :order_cancelled_flag = 'N'
             AND :order_open_flag = 'Y'
             AND NOT EXISTS (
                    SELECT 1
                      FROM oe_order_lines_all oola
                     WHERE oola.header_id = ooha.header_id
                       AND oola.invoice_interface_status_code = 'YES')
             AND ooha.sold_to_org_id = :order_sold_to_org_id
           [color=red]  AND (ooha.order_number = :order_order_number)[/color]
             AND :system_current_block = 'ORDER'
----see here

What the importance of this ...repeted?

Sriram

[Updated on: Thu, 12 May 2011 10:16]

Report message to a moderator

Re: Help in tuning a value set query [message #507121 is a reply to message #507114] Thu, 12 May 2011 10:37 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
kavitayetakuri wrote on Thu, 12 May 2011 16:09
To RoachCoach,

If I don't give To_char() its giving me datatype mismatch error for the union queries.

Regards,
Kavita

Try removing the to_chars and wrapping the decode in a to_number.
Re: Help in tuning a value set query [message #507122 is a reply to message #507121] Thu, 12 May 2011 10:47 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
SELECT /*+ index(a OE_ORDER_HEADERS_U2) */
       order_number
  FROM oe_order_headers_all a
 WHERE order_number IN
 (
          SELECT ooha.order_number
            FROM oe_order_headers_all ooha
           WHERE ooha.cancelled_flag = 'N'
             AND ooha.open_flag = 'Y'
             AND :order_cancelled_flag = 'N'
             AND :order_open_flag = 'Y'
             AND NOT EXISTS (
                    SELECT 1
                      FROM oe_order_lines_all oola
                     WHERE oola.header_id = ooha.header_id
                       AND oola.invoice_interface_status_code = 'YES')
             AND ooha.sold_to_org_id = :order_sold_to_org_id
	     And((to_number(ooha.attribute16) = ooha.order_number) or (ooha.order_number = :order_order_number))
             AND :system_current_block = 'ORDER'
          UNION ALL
          SELECT DECODE (:system_current_block,
                         'ORDER', (SELECT NVL (TO_CHAR (:order_order_number),
                                               '123'
                                              )
                                     FROM DUAL
                                    WHERE :order_cancelled_flag IN ('Y','N'))
                        )
            FROM DUAL
          UNION ALL
          SELECT /*+ index(ooh IKN_OE_HEADERS_ALL_PRF_N1) */
                 ooh.order_number
            FROM oe_order_headers_all ooh
           WHERE :system_current_block <> 'ORDER'
             AND creation_date > SYSDATE - 180)

Re: Help in tuning a value set query [message #507124 is a reply to message #507122] Thu, 12 May 2011 10:55 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'd remove alll the hints and see what happens.
And I'd change:
SELECT DECODE (:system_current_block,
                         'ORDER', (SELECT NVL (TO_CHAR (:order_order_number),
                                               '123'
                                              )
                                     FROM DUAL
                                    WHERE :order_cancelled_flag IN ('Y','N'))
                        )
            FROM DUAL

to:
            SELECT DECODE (:system_current_block,
                           'ORDER', 
                           NVL (:order_order_number, 123),
                           NULL
                          )
            FROM DUAL
            WHERE :order_cancelled_flag IN ('Y','N')

Or if you are on oracle 9i or higher (and you really should be) then case is easier to follow:
            SELECT CASE WHEN :order_cancelled_flag IN ('Y','N')
                        AND :system_current_block = 'ORDER'
                        THEN NVL (:order_order_number, 123)
                        ELSE NULL
                        END
            FROM dual
Re: Help in tuning a value set query [message #507125 is a reply to message #507122] Thu, 12 May 2011 10:57 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
"oe_order_lines_all" DDL does not exist in your file.
ORA-01471: cannot create a synonym with same name as object.

Quote:
Or if you are on oracle 9i or higher (and you really should be) then case is easier to follow:


But if Op using this in his Report/Forms of version <9i then case wont work.

@ OP
this why Blackswan said Quote:
"It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/ "


Post your Oracle version with 4 digits

Quote:
I'd remove alll the hints and see what happens.

Ok
SELECT 
       order_number
  FROM oe_order_headers_all a
 WHERE order_number IN
 (
          SELECT ooha.order_number
            FROM oe_order_headers_all ooha
           WHERE ooha.cancelled_flag = 'N'
             AND ooha.open_flag = 'Y'
             AND :order_cancelled_flag = 'N'
             AND :order_open_flag = 'Y'
             AND NOT EXISTS (
                    SELECT 1
                      FROM oe_order_lines_all oola
                     WHERE oola.header_id = ooha.header_id
                       AND oola.invoice_interface_status_code = 'YES')
             AND ooha.sold_to_org_id = :order_sold_to_org_id
	     And((to_number(ooha.attribute16) = ooha.order_number) or (ooha.order_number = :order_order_number))
             AND :system_current_block = 'ORDER'
          UNION ALL
        SELECT CASE WHEN :order_cancelled_flag IN ('Y','N')
                        AND :system_current_block = 'ORDER'
                        THEN NVL (:order_order_number, 123)
                        ELSE NULL
                        END
            FROM dual
          UNION ALL
          SELECT 
                 ooh.order_number
            FROM oe_order_headers_all ooh
           WHERE :system_current_block <> 'ORDER'
             AND creation_date > SYSDATE - 180)


Sriram

[Updated on: Thu, 12 May 2011 11:41] by Moderator

Report message to a moderator

Re: Help in tuning a value set query [message #507149 is a reply to message #507125] Thu, 12 May 2011 13:21 Go to previous messageGo to next message
kavitayetakuri
Messages: 7
Registered: May 2011
Location: United States
Junior Member
To Sriram,

Hints came into picture because without hints cost was more, OR Clause was replaced with union because of this reason only and one place you have made cancelled_flag in ('Y','N') thats wrong in one union cancelled_flag = 'Y' another union it is open_flag = 'N'

Regards,
Kavita
Re: Help in tuning a value set query [message #507150 is a reply to message #507121] Thu, 12 May 2011 13:23 Go to previous messageGo to next message
kavitayetakuri
Messages: 7
Registered: May 2011
Location: United States
Junior Member
To RoachCoach,

To_number has reduced the cost, thanks for the advice. Just deployed the valueset have to check how much time it takes.

Thanks,
Kavita
Re: Help in tuning a value set query [message #507154 is a reply to message #507150] Thu, 12 May 2011 14:31 Go to previous messageGo to next message
kavitayetakuri
Messages: 7
Registered: May 2011
Location: United States
Junior Member
Still the value fetching is taking 2 mints, any other suggestions.
Re: Help in tuning a value set query [message #507158 is a reply to message #507154] Thu, 12 May 2011 15:02 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Post the revised explain plan, formatted, in code tags
Re: Help in tuning a value set query [message #507176 is a reply to message #507149] Thu, 12 May 2011 21:27 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
kavitayetakuri wrote on Thu, 12 May 2011 23:51
To Sriram,

Hints came into picture because without hints cost was more, OR Clause was replaced with union because of this reason only and one place you have made cancelled_flag in ('Y','N') thats wrong in one union cancelled_flag = 'Y' another union it is open_flag = 'N'

Regards,
Kavita



I am Sorry.That was a "Parallax".
SELECT /*+ index(a OE_ORDER_HEADERS_U2) */
       order_number
  FROM oe_order_headers_all a
 WHERE order_number IN
 (
          SELECT ooha.order_number
            FROM oe_order_headers_all ooha
           WHERE ooha.cancelled_flag = 'N'
             AND ooha.open_flag = 'Y'
             AND :order_cancelled_flag = 'N'
             AND :order_open_flag = 'Y'
             AND NOT EXISTS (
                    SELECT 1
                      FROM oe_order_lines_all oola
                     WHERE oola.header_id = ooha.header_id
                       AND oola.invoice_interface_status_code = 'YES')
             AND ooha.sold_to_org_id = :order_sold_to_org_id
	     And((to_number(ooha.attribute16) = ooha.order_number) or (ooha.order_number = :order_order_number))
             AND :system_current_block = 'ORDER'
          UNION ALL
       SELECT CASE WHEN (:order_cancelled_flag = 'Y' or :order_open_flag = 'N')
                        AND :system_current_block = 'ORDER'
                        THEN NVL (:order_order_number, 123)
                        ELSE NULL
                        END
            FROM dual
          UNION ALL
          SELECT /*+ index(ooh IKN_OE_HEADERS_ALL_PRF_N1) */
                 ooh.order_number
            FROM oe_order_headers_all ooh
           WHERE :system_current_block <> 'ORDER'
             AND creation_date > SYSDATE - 180)

Re: Help in tuning a value set query [message #507201 is a reply to message #507176] Fri, 13 May 2011 01:58 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Credit where its not due, Cookiemonster advised the to_number Smile
Re: Help in tuning a value set query [message #507215 is a reply to message #507201] Fri, 13 May 2011 03:42 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Just noticed:
SELECT .....
......
AND :system_current_block = 'ORDER'
UNION ALL
SELECT ...........
.............
WHERE :system_current_block <> 'ORDER'


Your first and last select in the union all are mutually exclusive because of the above conditions.
Split the sql into two seperate ones. One where each of the above conditions is true. Then run the appropriate sql depending on what system_current_block is set to. That should speed things up.
Previous Topic: composite index
Next Topic: No row resource operation information in tkprof output
Goto Forum:
  


Current Time: Fri Mar 29 03:58:46 CDT 2024