Answer Posted / moorthy g

select ename from emp e
where mgr in (select empno from emp whereename = 'KING');

Here's the EXPLAIN PLAN for this query:

OBJECT OPERATION
---------- ----------------------------------------
SELECTSTATEMENT()
NESTEDLOOPS()
EMP TABLEACCESS(FULL)
EMP TABLEACCESS(BY INDEX ROWID)
PK_EMP INDEX(UNIQUESCAN)

This query is virtually equivalent to this:

select e1.ename from emp e1,(select empno from empwhere
ename = 'KING') e2
where e1.mgr = e2.empno;

You can write the same query using EXISTS bymoving the outer
query column to a subquery condition, likethis:

select ename from emp e
where exists (select 0 from emp wheree.mgr = empno and
ename = 'KING');

When you write EXISTS in a where clause, you'retelling the
optimizer that you want the outer query to be runfirst,
using each value to fetch a value from the inner
query(think: EXISTS = outside to inside).

Describe the impact of several join conditions and join order in a joiner transformation?

1116

---

State the differences between sql override and lookup override?

1287

---

What is an aggregator transformation?

1147

---

What are the components of workflow manager?

1159

---

How to extract the informatica rejected data?

1259

---

Which are the different editions of informatica powercenter that are available?

1081

---

How do you load first and last records into target table?

1210

---

What is decode in static cache?

1264

---

How to partition the Session?

1154

---

What is rank transformation in informatica

1233

---

Mention some types of transformation?

1097

---

What is the procedure for creating independent data marts from informatica 7.1?

1120

---

Explain pmcmd command usage in informatica

1189

---

what is informatica metadata?

1090

---

How can we create index after completion of load process?

1165

---