| How do I optimize this query better (than adding hints) (merged) [message #238918] |
Mon, 21 May 2007 00:34  |
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) |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Can I make this work with MINUS operator??? [message #239238 is a reply to message #238918] |
Mon, 21 May 2007 17:18  |
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 #239346 is a reply to message #239245] |
Tue, 22 May 2007 01:35 |
 |
Maaher
Messages: 7065
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
