Home » SQL & PL/SQL » SQL & PL/SQL » Query re-writing help (oracle,9.2.0.8,window)
Query re-writing help [message #444711] Tue, 23 February 2010 11:46 Go to next message
vickey_sagar1
Messages: 77
Registered: August 2007
Location: pune
Member

Hi All,

Mentioned below query is taking more then 8 hours. Could you please advice some alternate way to re-write the query to get the faster result?

SELECT *
  FROM master_part_table empa
 WHERE NVL ((SELECT DISTINCT supp.mst_supplier_id
                        FROM table_a supp, table_b MAP, table_c avl
                       WHERE supp.enabled_flag = 'Y'
                         AND supp.mst_supplier_id = MAP.mst_supplier_id
                         AND avl.inventory_item_id = empa.inventory_item_id
                         AND MAP.manufacturer_id = avl.manufacturer_id
                         AND ROWNUM < 2),
            1
           ) IN
          (NVL (convertsupplierstostring ('160'),
                NVL ((SELECT DISTINCT supp.mst_supplier_id
                                 FROM table_a supp, table_b MAP, table_c avl
                                WHERE supp.enabled_flag = 'Y'
                                  AND supp.mst_supplier_id =
                                                           MAP.mst_supplier_id
                                  AND avl.inventory_item_id =
                                                        empa.inventory_item_id
                                  AND MAP.manufacturer_id =
                                                           avl.manufacturer_id
                                  AND ROWNUM < 2),
                     1
                    )
               )
          )
Re: Query re-writing help [message #444712 is a reply to message #444711] Tue, 23 February 2010 11:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remove the following:
convertsupplierstostring ('160'),

Regards
Michel
Re: Query re-writing help [message #444713 is a reply to message #444712] Tue, 23 February 2010 12:04 Go to previous messageGo to next message
vickey_sagar1
Messages: 77
Registered: August 2007
Location: pune
Member

Thanks for the reply. I have removed the function and it is working fine. Could you let me know, what was the problem with convertsupplierstostring ('160') user define function.
Re: Query re-writing help [message #444714 is a reply to message #444711] Tue, 23 February 2010 12:05 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'm not at all convinced that query is correct.
1) Why are you using in when your sub-query and function can only return 1 value. Surely that should be =.
2) Why are you using distinct with a rownum < 2 clause? Restricting it to 1 row makes distinct pointless (but still time consuming).
3) Why are you using rownum without an order by?
Re: Query re-writing help [message #444715 is a reply to message #444713] Tue, 23 February 2010 12:08 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
vickey_sagar1 wrote on Tue, 23 February 2010 18:04
Thanks for the reply. I have removed the function and it is working fine. Could you let me know, what was the problem with convertsupplierstostring ('160') user define function.


It was being run once for every row in master_part_table.
Call the function before the query.
Assign the result to a variable.
Use the variable in the query.
Re: Query re-writing help [message #444716 is a reply to message #444713] Tue, 23 February 2010 12:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
vickey_sagar1 wrote on Tue, 23 February 2010 19:04
Thanks for the reply. I have removed the function and it is working fine. Could you let me know, what was the problem with convertsupplierstostring ('160') user define function.

Whatever it does (and I bet it queries some tables) it is called at least once for each row.
I advice you to put the result inside a package or a context variable, there Oracle knows it is a constant (if as I assume the function is deterministic and it is really a constant) and can optimize the access.

Regards
Michel

[Updated on: Tue, 23 February 2010 12:11]

Report message to a moderator

Re: Query re-writing help [message #444717 is a reply to message #444714] Tue, 23 February 2010 12:13 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
2) Why are you using distinct with a rownum < 2 clause? Restricting it to 1 row makes distinct pointless (but still time consuming).

You can have several rows with "distinct". [Edit: Doh! I read it the wrong way, of course with rownum<2 "distinct" is useless.]

@vickey_sagar1
The most important point is that if it is the case (you can have several rows) then the result of the query (the whole one) is undeterministic.
This mean you have a flaw somewhere.

Regards
Michel

[Updated on: Tue, 23 February 2010 12:15]

Report message to a moderator

Previous Topic: Sql -Formatting Data
Next Topic: Cultivation in PL/SQL
Goto Forum:
  


Current Time: Sun Dec 04 12:39:06 CST 2016

Total time taken to generate the page: 0.15196 seconds