Re: Vanishing table in 11.2.0.3

From: Peter Schneider <pschneider1968_at_googlemail.com>
Date: Thu, 02 Feb 2012 23:20:52 +0100
Message-ID: <jgf284$kqu$1_at_online.de>

Am 02.02.2012 05:08, schrieb Mladen Gogala:
> Query is extremely simple and the result is expected:
>
> select ename,job,mgr,deptno,sal from emp
> where deptno in (select deptno from dept)
> /
> ENAME JOB MGR DEPTNO SAL
> ---------- --------- ---------- ---------- ----------
> SMITH CLERK 7902 20 800
> ALLEN SALESMAN 7698 30 1600
> WARD SALESMAN 7698 30 1250
> JONES MANAGER 7839 20 2975
> MARTIN SALESMAN 7698 30 1250
> BLAKE MANAGER 7839 30 2850
> CLARK MANAGER 7839 10 2450
> SCOTT ANALYST 7566 20 3000
> KING PRESIDENT 10 5000
> TURNER SALESMAN 7698 30 1500
> ADAMS CLERK 7788 20 1100
> JAMES CLERK 7698 30 950
> FORD ANALYST 7566 20 3000
> MILLER CLERK 7782 10 1300
>
> 14 rows selected.
>
> Elapsed: 00:00:00.01
>
>
> That is nothing unusual. What is unusual is the execution plan:
> SQL> select * from table(dbms_xplan.display_cursor);
>
> PLAN_TABLE_OUTPUT
> --------------------------------------------------------------------------------
> SQL_ID fbqjucf3xdmjb, child number 0
> -------------------------------------
> select ename,job,mgr,deptno,sal from emp where deptno in (select deptno
> from dept)
>
> Plan hash value: 3956160932
>
> --------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
> --------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | | | 4 (100)| |
> |* 1 | TABLE ACCESS FULL| EMP | 14 | 350 | 4 (0)| 00:00:01 |
> --------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 1 - filter("DEPTNO" IS NOT NULL)
>
>
> 19 rows selected.
>
> Elapsed: 00:00:00.04
> SQL>
>
> Question: where the _at_#$%! is the table DEPT? The original query contains
> 2 tables, the execution plan is only showing me only one.

Hi Mladen,

the IN subquery implies that only non-NULL values for emp.deptno are selected. Now when there is an enabled validated FK constraint emp.deptno->dept.deptno in place, every non-NULL emp.deptno value is guaranteed to exist in dept.

So with FK_DEPTNO enabled we have

---

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

---

|   0 | SELECT STATEMENT  |	 |    14 |   350 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |    14 |   350 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

and with

SQL> alter table emp disable constraint fk_deptno;

Tabelle wurde geändert.

we now have the expected

---

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

---

|   0 | SELECT STATEMENT   |	     |	  14 |	 392 |	   3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS	   |	     |	  14 |	 392 |	   3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP     |	  14 |	 350 |	   3   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK_DEPT |	   1 |	   3 |	   0   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---

    3 - access("DEPTNO"="DEPTNO")

SQL> So still no table access to dept necessary, as the existance can be validated by index scan on dept PK index. But now you have the nested loop you wanted to see.

This is 11.2.0.1 on my laptop.

Regards
Peter Received on Thu Feb 02 2012 - 16:20:52 CST