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 meant by lookup transformation?

612

---

What is the use of code page?

606

---

What is a sorter transformation?

659

---

Which is the t/r that builts only single cache memory?

710

---

How to elaborate tracing level?

684

---

---

What is primary and backup node?

695

---

can we override a native sql query within informatica? Where do we do it? How do we do it?

597

---

What is dimensional table?

619

---

explain one complex mapping with logic? sales project?

6150

---

Explain how many types of dimensions are available in informatica?

595

---

What are the types of presistent cache in look up tr.

1549

---

How to display session logs based upon particular dates. If I want to display session logs for 1 week from a particular date how can I do it without using unix. ?

1604

---

Why sorter is an active transformation?

749

---

How to partition the Session?

648

---

What is the difference between informatica 7.0 and 8.0?

567

---