Tips to Performance tuning in all db's

JOIN or  Correlated subquery with exists clause, which one is better

select *

from ContactInformation c

where exists (select * from Department d where d.Id = c.DepartmentId )

select *

from ContactInformation c

inner join Department d on c.DepartmentId = d.Id 

Generally, the EXISTS clause because you may need DISTINCT for a JOIN for it to give the expected output. For example, if you have multiple Department rows for a Contact Information row.

In your example above, the SELECT *:

means different output too so they are not actually equivalent

less chance of a index being used because you are pulling all columns out

Saying that, even with a limited column list, they will give the same plan: until you need DISTINCT... which is why I say "EXISTS"

Difference between in IN and EXISTS in Teradata SQL

Performance wise both should be same with less no of records.

If no of records will be more, EXISTS is faster than IN.

Mostly IN is used in case of subqueries and EXISTS is used in case of correlated subqueries.

Difference between subquery and correlated subquery in SQL?

Subquery :- The inner query is executed only once The inner query will get executed first and the output of the inner query used by the outer query.The inner query is not dependent on outer query.

Eg:-  SELECT cust_name, dept_no FROM Customer WHERE cust_name IN (SELECT cust_name FROM Customer);

Correlated sub query:-The outer query will get executed first and for every row of outer query, inner query will get executed. So the inner query will get executed as many times as number of rows in result of the outer query. The outer query output can use the inner query output for comparison. This means inner query and outer query dependent on each other.

Eg:- SELECT cust_name,dept_id FROM Cust

WHERE cust_name in (SELECT cust_name FROM dept WHERE cust.dept_id=dept.dept_id);

Oracle: Hints based on perf

/*+ INDEX(e1 ENC_REG_PK_IDX)
           USE_MERGE(ag) FULL(ag) */

--/*+ PARALLEL(6) */  -222 secs.

/*+ INDEX_COMBINE(er ENC_REG_PK_IDX ENC_REG_ACCT_REC_PK_IDX ENC_PMT_ADJ_PK_IDX ) */  --196

/*+ INDEX_FFS(er ENC_REG_PK_IDX) */  ---232

/*+ STAR_TRANSFORMATION */ ---218