Database Reference
In-Depth Information
10 LOC VARCHAR2(13) NOT NULL
11 )
12 partition by range(loc)
13 (
14 partition p1 values less than('C') tablespace p1,
15 partition p2 values less than('D') tablespace p2,
16 partition p3 values less than('N') tablespace p3,
17 partition p4 values less than('Z') tablespace p4
18 )
19 /
Table created.
We alter the table to add a constraint on the primary key column:
EODA@ORA12CR1> alter table emp add constraint emp_pk
2 primary key(empno)
3 /
Table altered.
A side effect of this is that there exists a unique index on the
EMPNO
column. This shows we can support and
enforce data integrity, one of our goals. Finally, we create two more global indexes on
DEPTNO
and
JOB
to facilitate
accessing records quickly by those attributes:
EODA@ORA12CR1> create index emp_job_idx on emp(job)
2 GLOBAL
3 /
Index created.
EODA@ORA12CR1> create index emp_dept_idx on emp(deptno)
2 GLOBAL
3 /
Index created.
EODA@ORA12CR1> insert into emp
2 select e.*, d.loc
3 from scott.emp e, scott.dept d
4 where e.deptno = d.deptno
5 /
14 rows created.
Let's see what is in each partition:
EODA@ORA12CR1> break on pname skip 1
EODA@ORA12CR1> select 'p1' pname, empno, job, loc from emp partition(p1)
2 union all
3 select 'p2' pname, empno, job, loc from emp partition(p2)
4 union all
5 select 'p3' pname, empno, job, loc from emp partition(p3)
6 union all
7 select 'p4' pname, empno, job, loc from emp partition(p4)
8 /
