Query Help --

[james777]

Query Help:

I have three tables which were joined on non-unique index key.

Tables:

Tab1 has 2 Million Rows.
Tab2 has 50K
Tab3 has 1K..

select distinct Tab1.col1 , Tab1.col2
from Tab1 T1
where not exists
( select '1' from Tab2 T2
where T2.col1 = T1.col1 )
and not exists
( select '1' from Tab3 T3
where T3.col1 = T1.col1 )
and T1.col1 is not null
and T1.Col2 is not null

The above query is not coming out at all and hanging.

Tab1 --> Col1 has 30K distinct values --> Has Index on Col1 -> 100K rows are Null values.

Tab2 --> No index on Col1 ( Because this is concatenated field from three derived values )

Tab3 --> Has Index on Col1.

Any Help in writing this query efficeintly helpfull.
1) I tried using NOT IN clause for Tab3
2) Tried selecting COL1 from Tab2 and Tab3 using Union ..

please help.

Thanks
Jim

 

[SantaMufasa]

Jim,

I'd try this:

select distinct  T1.col1, T1.col2
  from Tab1 T1
 where t1.col1 is not null
   and T1.Col2 is not null
   and t1.col1 in
(select Tab1.col1 from Tab1 T1
 minus
  (select T2.col1 from Tab2 T2
   union
   select T3.col1 from Tab3 T3)
);

I believe this will be faster. If you didn't need to display t1.col2, then this would be amazingly fast since you could get away with:

select Tab1.col1 from Tab1 T1
  where t1.col1 is not null
    and T1.Col2 is not null
 minus
  (select T2.col1 from Tab2 T2
   union
   select T3.col1 from Tab3 T3);

Let us know your findings:

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 00:07 (29Oct04) UTC (aka "GMT" and "Zulu"),
@ 17:07 (28Oct04) Mountain Time

 

[james777]

Thanks You..

I haven't tried your query yet but i want change union to union all -- does it impact anything here
1)
select distinct T1.col1, T1.col2
from Tab1 T1
where t1.col1 is not null
and T1.Col2 is not null
and t1.col1 in
(select Tab1.col1 from Tab1 T1
minus
(select T2.col1 from Tab2 T2
union
select T3.col1 from Tab3 T3)
);

TO -->
select distinct T1.col1, T1.col2
from Tab1 T1
where t1.col1 is not null
and T1.Col2 is not null
and t1.col1 in
(select Tab1.col1 from Tab1 T1
minus
(select T2.col1 from Tab2 T2
union all --> does it improve any performance
select T3.col1 from Tab3 T3)
);

2) Regarding the second query..
Can i join the result set..( which got all tab1.col1 that does not exist in tab2 and tab3 ) with again Tab1.COL1 to get TAb1.COL2.. Does it make any faster..

Thanks You for your response


Results :::::::::::+++++++++++ At this Time I tested
I ran the below query .....

select distinct T1.col1, T1.col2
from Tab1 T1
where t1.col1 is not null
and T1.Col2 is not null
and t1.col1 in
(select T1.col1 from Tab1 T1
minus
(select T2.col1 from Tab2 T2
union
select T3.col1 from Tab3 T3)
)

Elapsed: 00:00:23.04 --> Tremendous Improvement..

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (UNIQUE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TAB1'
3 2 NESTED LOOPS
4 3 VIEW OF 'VW_NSO_1'
5 4 MINUS
6 5 SORT (UNIQUE)
7 6 TABLE ACCESS (FULL) OF 'TAB1'
8 5 SORT (UNIQUE)
9 8 UNION-ALL
10 9 TABLE ACCESS (FULL) OF 'TAB2'
11 9 TABLE ACCESS (FULL) OF 'TAB3'
12 3 INDEX (RANGE SCAN) OF 'IDX_COL1' (NON-UNIQUE)


Thanks A Bunch..
Jim--

 

[SantaMufasa]

Jim,

Q1. "I want change union to union all -- does it impact anything here"

A1. It could in that it avoids a SORT.

Q2. "Can i join the result set?...Does it make any faster?

A2. Yes you can and yes it could.

Dave's First Rule of Advice: "One test is worth 10 expert opinions."

Dave's First Rule of Tuning: "One test is worth 100 expert opinions."

My reaction to your 23-second result: "Wow! That's great!"

Please try the coding permutations that you thought of, logging the performance time, of course. Please post your findings here.

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 03:43 (29Oct04) UTC (aka "GMT" and "Zulu"),
@ 20:43 (28Oct04) Mountain Time

 

[SantaMufasa]

Jim,

Here is another alternative that I believe should run even faster for you:

select distinct  T1.col1, T1.col2
  from Tab1 T1
     , (select Tab1.col1 from Tab1 T1
        minus
          (select T2.col1 from Tab2 T2
           union
           select T3.col1 from Tab3 T3)
        ) T4
 where t1.col1 = t4.col1
   and T1.Col2 is not null

Please test and post your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 22:12 (29Oct04) UTC (aka "GMT" and "Zulu"),
@ 15:12 (29Oct04) Mountain Time