Oracle Partition Common Usage

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
--create tablespaces
CREATE tablespace cus_area_1 datafile '/mnt/oracle/oradata/zuobian/cus_area_1.dbf' SIZE 5m autoextend ON NEXT 10m maxsize unlimited;
CREATE tablespace cus_area_2 datafile '/mnt/oracle/oradata/zuobian/cus_area_2.dbf' SIZE 5m autoextend ON NEXT 10m maxsize unlimited;
CREATE tablespace cus_area_3 datafile '/mnt/oracle/oradata/zuobian/cus_area_3.dbf' SIZE 5m autoextend ON NEXT 10m maxsize unlimited;
--create table with partition declaration
CREATE TABLE CUSTOMER
(
CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY,
CUST_AREA INT NOT NULL,
FIRST_NAME VARCHAR2(30) NOT NULL,
LAST_NAME VARCHAR2(30) NOT NULL,
PHONE VARCHAR2(15) NOT NULL,
EMAIL VARCHAR2(80),
SEX VARCHAR2(10),
STATUS VARCHAR2(10),
CREATE_DATE DATE
)
PARTITION BY LIST (CUST_AREA) --partition declaration
(
PARTITION area_1 VALUES(1) TABLESPACE cus_area_1,
PARTITION area_2 VALUES(2) TABLESPACE cus_area_2,
PARTITION area_3 VALUES(3) TABLESPACE cus_area_3
);
-- test data
INSERT INTO CUSTOMER(CUSTOMER_ID,CUST_AREA,FIRST_NAME,LAST_NAME,PHONE,CREATE_DATE) VALUES(1,1,'geln','zhang','18912386146',sysdate);
INSERT INTO CUSTOMER(CUSTOMER_ID,CUST_AREA,FIRST_NAME,LAST_NAME,PHONE,CREATE_DATE) VALUES(2,2,'eric','zhang','18912386146',sysdate);
INSERT INTO CUSTOMER(CUSTOMER_ID,CUST_AREA,FIRST_NAME,LAST_NAME,PHONE,CREATE_DATE) VALUES(3,3,'tom','zhang','18912386146',sysdate);
INSERT INTO CUSTOMER(CUSTOMER_ID,CUST_AREA,FIRST_NAME,LAST_NAME,PHONE,CREATE_DATE) VALUES(4,3,'tony','zhang','18912386146',sysdate);
commit;
-- query by partition
SELECT CUSTOMER_ID,CUST_AREA,FIRST_NAME,LAST_NAME FROM CUSTOMER;
SELECT CUSTOMER_ID,CUST_AREA,FIRST_NAME,LAST_NAME FROM CUSTOMER partition (area_1) ;
SELECT /*+rowid(CUSTOMER)*/ ROWID,LAST_NAME,FIRST_NAME,PHONE FROM CUSTOMER partition (AREA_1) WHERE ROWID>='AAASrfAAIAAAACAAAA' AND ROWID<='AAASrfAAIAAAACHCcQ';
-- find all partitioin data for a table
SELECT e.owner,e.segment_name,e.partition_name,o.data_object_id,
e.RELATIVE_FNO,
e.BLOCK_ID MIN_BLOCK,
e.BLOCK_ID + e.BLOCKS - 1 MAX_BLOCK
FROM dba_extents e, dba_objects o
WHERE e.segment_name = 'CUSTOMER' -- table name
AND o.object_name = e.segment_name
AND e.owner = 'MASK' -- schema name
AND o.OWNER = e.owner
AND NVL (e.partition_name, 0) = NVL (o.SUBOBJECT_NAME, 0)
AND o.data_object_id IS NOT NULL;