Thanks for the question, Harpreet.

Asked: October 25, 2016 - 1:11 pm UTC

Last updated: October 26, 2016 - 8:53 am UTC

Version: Oracle 11G

Viewed 1000+ times

You Asked

Dear All,

Ques 1. Please advise if the use of minus clause is best when I am looking to exclude certain specific conditions or shall I be using Not IN?

I am in situation where I know what to avoid, not sure what all to select and hence I needed to use minus clause.

Ques 2. We use a GTT to store data coming out of various tables in a procedure, which is then used to prepare data in a cursor which is then passed as an object to the application. Lately we have started noticing that the database is getting hammered with the insert queries on GTT, please advise if there is way to boost up performance in this case as the procedure can be called simulatenously by different users depending on the activity.

Thanks,
H

and Chris said...

Minus and not in aren't really equivalent.

Minus is a set operation. It's for when you have a table and you want to subtract from this the results of another table. So you have to have matching columns.

Not in is when you want to compare specific columns. So in general you'll get different results using them:

create table t1 as
  select rownum x, 
         case mod(rownum, 2) 
           when 0 then 'this'
           else 'that'
         end y
  from dual connect by level <= 10;

create table t2 as
  select rownum x, 
         'that' y
  from   dual connect by level <= 5;
  
select * from t1
minus 
select * from t2
order  by 1;

X   Y     
2   this  
4   this  
6   this  
7   that  
8   this  
9   that  
10  this  

select * from t1
where  x not in (select x from t2)
order  by x;

X   Y     
6   this  
7   that  
8   this  
9   that  
10  this

You can do multi-column comparisons with not in, making it similar to minus:

select * from t1
where  (x, y) not in (select x, y from t2)
order  by x;

X   Y     
2   this  
4   this  
6   this  
7   that  
8   this  
9   that  
10  this

And you could emulate not in using in and minus:

select * from t1
where  x in (
  select x from t1
  minus 
  select x from t2
)
order  by x;

X   Y     
6   this  
7   that  
8   this  
9   that  
10  this

But that's a bit daft!

They also behave differently when it comes to null:

insert into t1 values (11, 'this');
insert into t2 values (null, 'this');

select x from t1
minus 
select x from t2
order  by 1;

X   
6   
7   
8   
9   
10  
11  


 6 rows selected 

select * from t1
where  x not in (select x from t2)
order  by x;

 0 rows selected

If you can have nulls in your "not in" subquery/list, you almost certainly want to use not exists instead:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:442029737684

Rating

(2 ratings)

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

Comments

Many thanks - please assist with Ques 2

Harpreet Randhawa, October 25, 2016 - 4:38 pm UTC

Dear Chris,

Many thanks, for my particular case, this is what I have -

Table A -
Column A - Emp ID
Column B - Skills

Table B -
Column A - Emp ID
Column B - Emp Name

I need to extract the Emp ID & Name of all employees who do not have a particular skill, for ex -

Suppose A.A has got values 1,2,3,4,5 while A.B has got DBA,SysAdm, Developer, PM, BA

I am also not having the full list of all different values that can come in A.B, however, I need to get emp id and emp name for all who haven't got PM as skills, here is the query that I use -

Insert into GTT_temp1 ( select unique(A.EmpID, B.Skill) from Table A, Table B where A.Empid = B.Empid
MINUS
select unique(A.EmpID, B.Skill) from Table A, Table B where
A.Empid = B.Empid
and B.Skill in ('DBA'));

Both the table A and B have large amounts of data and this procedure can be invoked simultaneously from different application users. We have seen this query putting in maximum load on the database and hence the questions one and 2.

October 25, 2016 - 4:57 pm UTC

It sounds to me like you need a not exists:

select * 
from   a
where  not exists (
  select null from b
  where  a.emp_id = b.emp_id
  and    b.skill in ('PM', 'DBA') -- which emps don't have these skills
)

I'm not sure why you're using a gtt...

Thanks Once Again - please help more

Harpreet Randhawa, October 25, 2016 - 5:15 pm UTC

Dear Chris,

I am using GTT because the procedure is invoked by the application and it passes an object which is an array of emp IDs and based on the skill identified above, I now need to calculate the % hike, so I use GTT to first get the skills and then use a cursor to run for the specific emp IDs in loop so that the whole cursor can be passed back.

I was earlier using not exists but someone advised me to use minus clause to gain performance and hence I asked the query to you.

Further, this procedure is invoked may be 2 million times a day and sometimes by 40000+ users simultaneously and thats when we say the insert into GTT throttling the database spikes,

Please advise what is the best way forward.

Thanks a million,
H

October 26, 2016 - 8:53 am UTC

I'm still not sure why you need the GTT. Why can't you do everything in one query?

someone advised me to use minus

Did they have some metrics to backup that assertion?

It some cases it may be better. For your insert, probably not. This insert:

select unique(A.EmpID, B.Skill) from Table A, Table B where A.Empid = B.Empid 
MINUS 
select unique(A.EmpID, B.Skill) from Table A, Table B where 
A.Empid = B.Empid 
and B.Skill in ('DBA')

accesses both tables twice. The not exists only uses each once.

I suspect the real problem is having so many inserting into the GTT though. I still don't see why you need this.

But rather than guess, measure! Get the execution plan for the not exists query and the minus and compare. For instructions on how to do this, see:

https://blogs.oracle.com/sql/entry/how_to_create_an_execution

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Many thanks - please assist with Ques 2

Thanks Once Again - please help more

More to Explore

PL/SQL demos

PL/SQL docs

Query on Stored Procedure - MINUS clause

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Many thanks - please assist with Ques 2

Thanks Once Again - please help more

More to Explore

PL/SQL demos

PL/SQL docs