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.
TIP：Partition can also be viewed by the EXPLAIN PLAN by accessing the columns PARTITION_STOP and PARTITION_START in the PLAN_TABLE table.
