Monday, January 26, 2009

Missing Tables in Execution Plan

In Oracle 10g, one of the new features introduced for optimizer, named transformation. Using this features it may be possible that oracle internally removes some of the redundant tables those were involved only in join predicates.

To demonstrate the same following test case created –
1. Table with primary key.
2. Table with foreign key referencing the above created table.
3. Query that eliminates the primary key table and hence leads to faster execution.


SQL> create table pk_table (id number, name varchar2(20));

Table created.



SQL> alter table pk_table add constraint pk_id primary key(id);

Table altered.



SQL> create table fk_table (id number, phone number, addr varchar2(200));

Table created.



SQL> alter table fk_table add constraint fk_id foreign key (id) references pk_table (id);

Table altered.



SQL> begin
2 for x in 1..100 loop
3 insert into pk_table values (x, dbms_random.string('A',20));
4 end loop;
5 commit;
6* end;
SQL> /

PL/SQL procedure successfully completed.




SQL> begin
2 for x in 1..500 loop
3 insert into fk_table values (round(dbms_random.value(1,100)), dbms_random.value(1,100000), dbms_random.string('A',100));
4 end loop;
5 commit;
6 end;
7 /

PL/SQL procedure successfully completed.



SQL> exec dbms_stats.gather_table_stats (user,'PK_TABLE', cascade=>true);

PL/SQL procedure successfully completed.



SQL> exec dbms_stats.gather_table_stats (user,'FK_TABLE', cascade=>true);

PL/SQL procedure successfully completed.



SQL> select b.id, b.phone, b.addr
2 from pk_table a,
3 fk_table b
4 where a.id=b.id;



SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1108179040

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 62500 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| FK_TABLE | 500 | 62500 | 5 (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

1 - filter("B"."ID" IS NOT NULL)

13 rows selected.

As shown above, the primary key table is missing in the execution plan. The table PK_TABLE is redundant. As all of us know, every record of foreign key column must be present in primary key table. Hence to fetch data from foreign key table we need have to check primary table for that record.

Optimizer added one extra filter condition i.e. “B.ID is not null”. This is to ensure the data consistency when transformation takes place.

Starting from Oracle 11g, the optimizer has advanced to eliminate anti joins as well. For e.g. check the plan for following query in 10g and 11g.

Query
select * from fk_table b where not exists (select 1 from pk_table a where a.id=b.id);

Plan in 10g

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 2976039779

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 128 | 5 (0)| 00:00:01 |
| 1 | NESTED LOOPS ANTI | | 1 | 128 | 5 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| FK_TABLE | 500 | 62500 | 5 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN| PK_ID | 100 | 300 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("A"."ID"="B"."ID")

Plan in 11g

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 1108179040

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 125 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| FK_TABLE | 1 | 125 | 5 (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("B"."ID" IS NULL)

This elimination is applicable to view as well. That means, if my view is fetching all columns from primary and foreign key tables and query is written to access only foreign key columns from this view, then also optimizer will eliminate primary table.

Limitations

As this seems to be very interesting changes in optimizer, it is not applicable for the multi-column primary/foreign key columns.

To sum up, now optimizer itself takes care of such unnecessary joins to eliminate table access to improve the query performance.

No comments: