If i have column emp_id as an index. and if u use it in 'in' clause or exists clause will i get the beiifit of PI as in = clause
sel * from table where emp_id in ( select id from table_b where <condition>)
sel * from table where emp_id exists( select id from table_b where <condition>)
Will this query takes PI for fetching data.
it will depend if id is the pi of table_b and id has the same data type as emp_id.
you can veryfy be your own by reading the explain.
"Exists' will be more efficient than "IN" Clause. Nulls will not be handled properly when we use the IN Clause.
Thanks & Regards,
Adharssh Hospet Srinivasa Rao.
So Ulrich, You mean if its data type matches it will be helpful and will giv good performance.
the datatype need to match and both columns need to be PI on the table.
And again - check the explain. Is one of the tables redistributed? In case yes, no PI join.
You could also rewirte your query
from table_a a
on a.emp_id = b.id