nice new feature in 9

From: Telemachus <telemachus_at_ulysseswillreturn.net>
Date: Wed, 17 Jul 2002 15:51:12 +0100
Message-ID: <BvfZ8.2792$zX3.2372@news.indigo.ie>

Using the Scott tables and the **NEW** plan table... rerun utlxplan to get it...

From Toad or SQL*PLUS

explain plan for select ename from emp e , dept d where e.deptno=d.deptno

select * from table(dbms_xplan.display('PLAN_TABLE',null,'ALL'))

gives

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|

---

| 0 | SELECT STATEMENT | | 14 | 168 | 3 (34)|
| 1 | NESTED LOOPS | | 14 | 168 | 3 (34)|
| 2 | TABLE ACCESS FULL | EMP | 14 | 126 | 3 (34)|
|* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 3 | |

---

Predicate Information (identified by operation id):

---

   3 - access("E"."DEPTNO"="D"."DEPTNO")

(probably pastes terrible but in an editor is perfectly formatted)

and ....

explain plan for select 1 from dual

select * from table(dbms_xplan.display('PLAN_TABLE',null,'ALL'))

---

| Id | Operation | Name | Rows | Bytes | Cost |

---

| 0 | SELECT STATEMENT | | | | |
| 1 | TABLE ACCESS FULL | DUAL | | | |

---

Note: rule based optimization

it's a lot more informative and easier to use than earlier incarnations... Received on Wed Jul 17 2002 - 09:51:12 CDT