ITPub博客

首页 > Linux操作系统 > Linux操作系统 > EXPLAIN PLAN WHEN USING PARTITIONS

EXPLAIN PLAN WHEN USING PARTITIONS

原创 Linux操作系统 作者:cc59 时间:2008-01-15 00:30:22 0 删除 编辑

EXPLAIN PLAN WHEN USING PARTITIONS

Table partitions yield different outputs for their EXPLAIN PLANs (as shown in the following listing).Here, we create a  table with three partitions and a partitioned index. Broadly speaking, partitions are tables stored in multiple places in the database. For more information on partitioning tables

 

SQL> create table dept1(deptno number(2), dept_name varchar2(30))

  2  partition by range(deptno)

  3  (partition d1 values less than (10),

  4   partition d2 values less than (20),

  5   partition d3 values less than (maxvalue));

 

Table created.

 

SQL> insert into dept1 values(1,'dept1');

 

1 row created.

 

SQL> insert into dept1 values(7,'dept7');

 

1 row created.

 

SQL> insert into dept1 values(10,'dept10');

 

1 row created.

 

SQL> insert into dept1 values(15,'dept15');

 

1 row created.

 

SQL> insert into dept1 values(22,'dept22');

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> create index dept_index on dept1(deptno)

  2  local

  3  (partition d1,

  4   partition d2,

  5   partition d3);

 

Index created.

 

SQL>

 

We now generate an EXPLAIN PLAN that forces a full table scan to access the first two partitions.

 

SQL> explain plan for

  2      select  * from dept1

  3       where deptno||''=1

  4          or deptno||''=15;

 

Explained.

 

 

SQL>  select * from table(dbms_xplan.display());

 

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 2415070041

 

---------------------------------------------------------------------------------------------

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

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |       |     2 |    60 |     2   (0)| 00:00:01 |       |       |

|   1 |  PARTITION RANGE ALL|       |     2 |    60 |     2   (0)| 00:00:01 |     1 |     3 |

|*  2 |   TABLE ACCESS FULL | DEPT1 |     2 |    60 |     2   (0)| 00:00:01 |     1 |     3 |

-----

 

SQL>

 

The preceding examples shows that a full table scan on the DEPT1 table is performed. All three partitions are scanned. The starting partition is 1 and the ending partition is 3.

 

Next, an EXPLAIN plan is generated in the following listing for an index range scan of partition2 only(ensure that you delete from the plan table to clear it).

 

explain plan for

    select  * from dept1

     where deptno=1;

 

We now generate an EXPLAIN PLAN  for an index range scan accessing only the second partition:

 

-----------------------------------------------------------------------------------------------------------------

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

-----------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                   |            |     1 |    30 |     3   (0)| 00:00:01 |       |       |

|   1 |  PARTITION RANGE SINGLE            |            |     1 |    30 |     3   (0)| 00:00:01 |     1 |     1 |

|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| DEPT1      |     1 |    30 |     3   (0)| 00:00:01 |     1 |     1 |

|*  3 |    INDEX RANGE SCAN                | DEPT_INDEX |     1 |       |     2   (0)| 00:00:01 |     1 |     1 |

 

 

This output shows that the only partition of the table OR index that is accessed is the second partition. This is because the value for deptno=1 is within the second partition of the DEPT1 table.

The DEPTNO column is also indexes, and this value is also within the second partition of the index.

 

TIPPartition can also be viewed by the EXPLAIN PLAN by accessing the columns PARTITION_STOP and PARTITION_START in the PLAN_TABLE table.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/104152/viewspace-144743/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Using dbms_monitor
请登录后发表评论 登录
全部评论

注册时间:2007-12-21

  • 博文量
    132
  • 访问量
    288304