Database Reference
In-Depth Information
PN EMPNO JOB LOC
-- ---------- --------- -------------
p2 7499 SALESMAN CHICAGO
7521 SALESMAN CHICAGO
7654 SALESMAN CHICAGO
7698 MANAGER CHICAGO
7844 SALESMAN CHICAGO
7900 CLERK CHICAGO
p3 7369 CLERK DALLAS
7566 MANAGER DALLAS
7788 ANALYST DALLAS
7876 CLERK DALLAS
7902 ANALYST DALLAS
p4 7782 MANAGER NEW YORK
7839 PRESIDENT NEW YORK
7934 CLERK NEW YORK
14 rows selected.
This shows the distribution of data, by location, into the individual partitions. We can now review some query
plans to see what we could expect performance-wise:
EODA@ORA12CR1> variable x varchar2(30);
EODA@ORA12CR1> begin
2 dbms_stats.set_table_stats
3 ( user, 'EMP', numrows=>100000, numblks => 10000 );
4 end;
5 /
PL/SQL procedure successfully completed.
EODA@ORA12CR1> explain plan for select empno, job, loc from emp where empno = :x;
Explained.
EODA@ORA12CR1> select * from table(dbms_xplan.display(null,null,'BASIC +PARTITION'));
--------------------------------------------------------------------
| Id | Operation | Name | Pstart| Pstop |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| EMP | ROWID | ROWID |
| 2 | INDEX UNIQUE SCAN | EMP_PK | | |
--------------------------------------------------------------------
The plan here shows an
INDEX UNIQUE SCAN
of the nonpartitioned index
EMP_PK
that was created in support of
our primary key. Then there is a
TABLE ACCESS BY GLOBAL INDEX ROWID
, with a
PSTART
and
PSTOP
of
ROWID/ROWID
,
meaning that when we get the
ROWID
from the index, it will tell us precisely which index partition to read to
