Skip to Main Content
  • Questions

  • what we can use to replace minus set operator with something like rollup or cube

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, sudhakar.

Asked: October 08, 2012 - 10:00 am UTC

Last updated: September 07, 2016 - 9:12 am UTC

Version: 10.2.1

Viewed 1000+ times

You Asked

Hi Tom,

is there any thing like rollup and cube to replace minus and intersect set operator to tune query.

Thanks,

Sudhakar Pawar

and Tom said...

MINUS and INTERSECT are brutally efficient.

rollup and cube would be not useful here.


you can simulate an intersect with

select c1, c2, c3, ...
from
(
select 1 id, c1, c2, c3, ... from t1
union all
select 2 id, c1, c2, c3, ... from t2
)
group by c1, c2, c3, ...
having count(distinct id) = 2;

but I don't think you'd like it in general:


ops$tkyte%ORA11GR2> /*
ops$tkyte%ORA11GR2> drop table t1;
ops$tkyte%ORA11GR2> drop table t2;
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create table t1 as select * from all_objects;
ops$tkyte%ORA11GR2> create table t2 as select * from t1 where mod(object_id,5) <= 2;
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T1' );
ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T2' );
ops$tkyte%ORA11GR2> */

select * from t1 intersect select * from t2


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     2919      0.49       0.69          0       1674          0       43762
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     2921      0.49       0.69          0       1674          0       43762

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
     43762      43762      43762  INTERSECTION  (cr=1674 pr=0 pw=0 time=596927 us)
     72938      72938      72938   SORT UNIQUE (cr=1045 pr=0 pw=0 time=303797 us cost=2982 size=12207080 card=77260)
     72938      72938      72938    TABLE ACCESS FULL T1 (cr=1045 pr=0 pw=0 time=49926 us cost=292 size=12207080 card=77260)
     43762      43762      43762   SORT UNIQUE (cr=629 pr=0 pw=0 time=173100 us cost=1783 size=7280956 card=46082)
     43762      43762      43762    TABLE ACCESS FULL T2 (cr=629 pr=0 pw=0 time=28327 us cost=177 size=7280956 card=46082)
********************************************************************************
select
 OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID,
 DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME,
 TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY,
 NAMESPACE, EDITION_NAME
from
(select 1 id, t1.* from t1 union all select 2 id, t2.* from t2)
group by
 OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID,
 DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME,
 TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY,
 NAMESPACE, EDITION_NAME
having count(distinct id) = 2

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     2919      0.99       1.09          0       1674          0       43762
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     2921      0.99       1.09          0       1674          0       43762

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
     43762      43762      43762  FILTER  (cr=1674 pr=0 pw=0 time=993619 us)
     72938      72938      72938   HASH GROUP BY (cr=1674 pr=0 pw=0 time=941263 us cost=474 size=19858062 card=123342)
    116700     116700     116700    VIEW  VM_NWVW_1 (cr=1674 pr=0 pw=0 time=707346 us cost=474 size=19858062 card=123342)
    116700     116700     116700     HASH GROUP BY (cr=1674 pr=0 pw=0 time=617145 us cost=474 size=19858062 card=123342)
    116700     116700     116700      VIEW  (cr=1674 pr=0 pw=0 time=349322 us cost=469 size=19858062 card=123342)
    116700     116700     116700       UNION-ALL  (cr=1674 pr=0 pw=0 time=257460 us)
     72938      72938      72938        TABLE ACCESS FULL T1 (cr=1045 pr=0 pw=0 time=43696 us cost=292 size=12207080 card=77260)
     43762      43762      43762        TABLE ACCESS FULL T2 (cr=629 pr=0 pw=0 time=24571 us cost=177 size=7280956 card=46082)






Minus gets even uglier looking - you'd need a distinct, you'd need an outer join based on a complex where clause (you have to deal with nulls). I'm not even going there.


minus and intersect are brutally efficient at what they do.



Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Helena Marková, October 12, 2012 - 7:48 am UTC


Minus Operator is slow

Maha, September 07, 2016 - 7:17 am UTC

I am using a minus operator to fetch data from tables A and B. i.e I want
select A_Col1 from A minus select B_Col1 from B;

My B Table is very huge(some 20,00,00,000 rows) and Table A can contain anywhere between 3,00,000 to 10,00,000 rows and so the query is taking a really long time to execute. How to replace it with NOT IN operator or any other operator to improve the performace?
Connor McDonald
September 07, 2016 - 9:12 am UTC

You could try:

select *
from   a
where  not exists (
  select null from b
  where  a.col1 = b.col1
  and    <all other cols in your minus>
)


But it's likely you're going to have full scans of both tables whichever method you use. So this is going to take a while...

Chris
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

DBMS_STATS

More on PL/SQL routine DBMS_STATS here