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).

What is exclusive and normal mode for repository services?

1149

---

Target increases every day even though no new record was inserted

1589

---

What is the session task and command task?

1064

---

What are the features of complex mapping?

1162

---

What is the main purpose of Unconnected lookup other than updating slowly changing dimensions? or In which case u use Unconnected lookup?

7462

---

Can we get 1st record through Informatica(without sequence number).

1483

---

What are some examples of informatica etl programs?

947

---

Under what condition selecting sorted input in aggregator may fail the session?

1128

---

Briefly describe lookup transformation?

1047

---

What are the new features of Power Center 5.0?

1784

---

What is native users?

1174

---

What is the scenario which compels informatica server to reject files?

1075

---

Can some one explain me about Telecommunications(wireless) project in Informatica? Thanks in advance

1983

---

Explain what is informatica metadata and where is it stored?

1052

---

Can any one give me a real time example for FACT TABLE & DIMENSIONAL TABLE?

2129

---