Home » SQL & PL/SQL » SQL & PL/SQL » How do I optimize this query better (than adding hints) (merged)
How do I optimize this query better (than adding hints) (merged) [message #238918] Mon, 21 May 2007 00:34 Go to next message
concorde800
Messages: 52
Registered: May 2007
Member
This query takes forever to run, I've added hints to each of the SELECT statments. How do I restructure/optimize this query??? What I seek to do is to obtain all records in Table A that are not in Table B.


The columns vbeln (Table a) is equivalent to the invc_doc_hdr_num_code (Table b).


select /*+ Parallel (a,8) */
distinct VBELN 
from
TMP_BW_2LIS13_BE_05182007 a
where a.vbeln NOT IN (select /*+ Parallel (b,10) */
 distinct invc_doc_hdr_num_code from ops_stg.invc_fact b)
Re: How do I optimize this query better (than adding hints) [message #238964 is a reply to message #238918] Mon, 21 May 2007 01:31 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

Can u post the table,table structure with some sample data?
Re: How do I optimize this query better (than adding hints) [message #238980 is a reply to message #238918] Mon, 21 May 2007 01:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you have parallel hints?
Are you sure they are appropriate?

If you don't know.
Better remove them.

Regards
Michel
Re: How do I optimize this query better (than adding hints) [message #239099 is a reply to message #238918] Mon, 21 May 2007 08:36 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
"What I seek to do is to obtain all records in Table A that are not in Table B."

Sounds like you should use a union set operator.
Re: How do I optimize this query better (than adding hints) [message #239105 is a reply to message #238918] Mon, 21 May 2007 08:56 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
A MUCH faster way to do this is


select VBELN
from TMP_BW_2LIS13_BE_05182007
minus
select invc_doc_hdr_num_code
from ops_stg.invc_fact;

Re: How do I optimize this query better (than adding hints) [message #239106 is a reply to message #239099] Mon, 21 May 2007 08:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A "minus" you meant, didn't it?

[Gosh! Bill beat me]

Regards
Michel

[Updated on: Mon, 21 May 2007 08:58]

Report message to a moderator

Re: How do I optimize this query better (than adding hints) [message #239158 is a reply to message #239105] Mon, 21 May 2007 10:49 Go to previous messageGo to next message
concorde800
Messages: 52
Registered: May 2007
Member
Thanks BILL.

These tables are very large (millions of rows) and I am thinking of using parallel hints (may be give it at least 4-6 threads). Would that work?? Hints on large tables scans should make queries will execute faster, correct??
Re: How do I optimize this query better (than adding hints) [message #239159 is a reply to message #238918] Mon, 21 May 2007 10:51 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Have you tried it yet, Oracle has heavily optimized the minus join logic to run very quickly.
Re: How do I optimize this query better (than adding hints) [message #239162 is a reply to message #239159] Mon, 21 May 2007 10:54 Go to previous messageGo to next message
concorde800
Messages: 52
Registered: May 2007
Member
Do you advise using the DISTINCT on either of 2 selects??? As there may be more than one occurence of vbeln or invc_doc_hdr_num_code on the tables.
Re: How do I optimize this query better (than adding hints) [message #239167 is a reply to message #239159] Mon, 21 May 2007 11:34 Go to previous messageGo to next message
concorde800
Messages: 52
Registered: May 2007
Member
BILL, the query is still running (4 threads on First Select (VBELN) and (6 threads on Second Select). Its running for about 20 min now
Re: How do I optimize this query better (than adding hints) [message #239168 is a reply to message #239167] Mon, 21 May 2007 11:51 Go to previous messageGo to next message
concorde800
Messages: 52
Registered: May 2007
Member
Query ran with success in approx 30 min..thanks for the tip
Can I make this work with MINUS operator??? [message #239238 is a reply to message #238918] Mon, 21 May 2007 17:18 Go to previous messageGo to next message
concorde800
Messages: 52
Registered: May 2007
Member
I tried to create table with a result set from the select statement and it fails with an invalid operator syntax error.

I am trying not to use the NOT IN operator as it takes forever (table has millions of rows). Is there an efficient way to do create this table using the MINUS operator or any other alternative operator???

The code I have:

Create table TMP_BW_VDITM_CIMGB4LOAD nologging parallel 4 tablespace STAGE_EMEA_TAB_LG
AS
select /*+ parallel (a,6) */
*
from TMP_BW_VDITM_BECIMG052107 a
where a.vbeln MINUS
select /*+ parallel (b,6) */
invc_doc_hdr_num_code b
from ops_stg.invc_fact;
Re: Can I make this work with MINUS operator??? [message #239241 is a reply to message #239238] Mon, 21 May 2007 17:35 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Unwilling or incapable of Reading The Fine SQL Reference Manual on proper use of MINUS & understanding same?

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/queries004.htm#i2054381
Re: How do I optimize this query better (than adding hints) [message #239242 is a reply to message #239159] Mon, 21 May 2007 17:36 Go to previous messageGo to next message
concorde800
Messages: 52
Registered: May 2007
Member
How do I go create a table based on this select statement with all columns for TMP_BW_2LIS13_BE_05182007??

I tried, MINUS operator and it does not work syntatically, the NOT IN operator takes forever to run. Any other options??
Re: Can I make this work with MINUS operator??? [message #239245 is a reply to message #239241] Mon, 21 May 2007 17:40 Go to previous messageGo to next message
concorde800
Messages: 52
Registered: May 2007
Member
OK, SO MISS PERFECT. How do you suggest it be done (other than NOT IN operator)?
Re: Can I make this work with MINUS operator??? [message #239246 is a reply to message #239238] Mon, 21 May 2007 17:42 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
For those incapable/unwilling to RTFM...

MINUS Example The following statement combines results with the MINUS operator, which returns only unique rows returned by the first query but not by the second:

[code]
SELECT product_id FROM inventories
MINUS
SELECT product_id FROM order_items;
[code]

Do NOT cross/multi-post
http://www.orafaq.com/forum/t/82025/74940/

[Updated on: Mon, 21 May 2007 17:44] by Moderator

Report message to a moderator

Re: Can I make this work with MINUS operator??? [message #239346 is a reply to message #239245] Tue, 22 May 2007 01:35 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
concorde800 wrote on Tue, 22 May 2007 00:40
OK, SO MISS PERFECT.

Wow, at ease! You ask for advice, you get it. There's no need to get all excited. The MINUS tip you got is an good one.

And doesn't MINUS apply the DISTINCT for you (a DISTINCT operation can be a bottleneck).

SQL> WITH table_a AS
  2   ( SELECT 1 x FROM dual UNION ALL
  3     SELECT 1 x FROM dual UNION ALL
  4     SELECT 2 x FROM dual UNION ALL
  5     SELECT 3 x FROM dual UNION ALL
  6     SELECT 4 x FROM dual UNION ALL
  7     SELECT 5 x FROM dual UNION ALL
  8     SELECT 5 x FROM dual UNION ALL
  9     SELECT 5 x FROM dual UNION ALL
 10     SELECT 4 x FROM dual
 11   ), table_b AS
 12   ( SELECT 1 x FROM dual UNION ALL
 13     SELECT 3 x FROM dual UNION ALL
 14     SELECT 3 x FROM dual UNION ALL
 15     SELECT 3 x FROM dual UNION ALL
 16     SELECT 4 x FROM dual UNION ALL
 17     SELECT 4 x FROM dual UNION ALL
 18     SELECT 4 x FROM dual
 19   )
 20  SELECT x
 21  FROM   table_a
 22  MINUS
 23  SELECT x
 24  FROM   table_b
 25  /

         X
----------
         2
         5


MHE
Previous Topic: Function Overloading
Next Topic: import command
Goto Forum:
  


Current Time: Sun Dec 11 06:18:20 CST 2016

Total time taken to generate the page: 0.07414 seconds