Oracle 11g new feature system partition table

Oracle 11g new feature system partition table

A new feature in Oracle 11g is the Partition Table of the system. Here is an experiment:

SQL> select * from v $ version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
PL/SQL Release 11.2.0.1.0-Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0-Production
NLSRTL Version 11.2.0.1.0-Production

SQL> drop table S_P_TEST purge;

SQL> CREATE TABLE S_P_TEST
(
Col1 number,
Name varchar2 (100)
)
PARTITION BY SYSTEM
(
PARTITION p1,
PARTITION p2,
PARTITION p3,
PARTITION p4
);

-- Specify partitions when inserting
SQL> insert into S_P_TEST select object_id, object_name from dba_objects;
Insert into S_P_TEST select object_id, object_name from dba_objects
*
Row 3 has an error:
ORA-14701: For tables partitioned by system method, you must use a partition extension or bind a variable to DML
SQL> insert into S_P_TEST partition (p1) select object_id, object_name from dba_objects
Where object_type = 'table ';
You have created 2115 rows.
SQL> insert into S_P_TEST partition (p2) select object_id, object_name from dba_objects
Where object_type = 'index ';
You have created 2888 rows.
SQL> commit;
SQL> select count (1) from S_P_TEST;
COUNT (1)
----------
5003
SQL> select count (1) from S_P_TEST partition (p1 );
COUNT (1)
----------
2115
SQL> select count (1) from S_P_TEST partition (p2 );
COUNT (1)
----------
2888
-- The same data can be inserted into different partitions, which indicates that the data has no relationship with the partition.
SQL> insert into S_P_TEST partition (p3) values (1, 'A ');
SQL> insert into S_P_TEST partition (p4) values (1, 'A ');
SQL> commit;
SQL> select * from S_P_TEST partition (p3 );
COL1 NAME
----------------------------------------------------
1 aa
SQL> select * from S_P_TEST partition (p4 );

COL1 NAME
---------------------------------------------------
1 aa

-- Traditional partition pruning and smart partition Association are ineffective and local indexes cannot be created.
SQL> ALTER TABLE S_P_TEST SPLIT PARTITION p1 at (1000)
Into (partition p3, partition p4 );
Alter table S_P_TEST split partition p1 at (1000)
*
Row 3 has an error:

ORA-14255: tables are not partitioned by range, list, combined range, or combined list method

 

-- If you know which table the data is in, make sure that you want to set partition conditions.
SQL> set autotrace traceonly
SQL> select * from S_P_TEST where col1 = 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2020968526
Bytes -------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop |
Bytes -------------------------------------------------------------------------------------------------
| 0 | select statement | 2 | 130 | 10 (0) | 00:00:01 |
| 1 | partition system all | 2 | 130 | 10 (0) | 00:00:01 | 1 | 4 |
| * 2 | table access full | S_P_TEST | 2 | 130 | 10 (0) | 00:00:01 | 1 | 4 |
Bytes -------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-filter ("COL1" = 1)
Note
-----
-Dynamic sampling used for this statement (level = 2)
Statistics
----------------------------------------------------------
5 recursive cballs
0 db block gets
70 consistent gets
0 physical reads
0 redo size
414 bytes sent via SQL * Net to client
338 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL> select * from S_P_TEST partition (p3) where col1 = 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1626227678
Bytes ----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop |
Bytes ----------------------------------------------------------------------------------------------------
| 0 | select statement | 1 | 65 | 4 (0) | 00:00:01 |
| 1 | partition system single | 1 | 65 | 4 (0) | 00:00:01 | 3 | 3 |
| * 2 | table access full | S_P_TEST | 1 | 65 | 4 (0) | 00:00:01 | 3 | 3 |
Bytes ----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-filter ("COL1" = 1)
Note
-----
-Dynamic sampling used for this statement (level = 2)
Statistics
----------------------------------------------------------
5 recursive cballs
0 db block gets
15 consistent gets
0 physical reads
0 redo size
385 bytes sent via SQL * Net to client
338 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)

1 rows processed

I don't think much about the use cases of system partition tables. If you understand the features of traditional partition table data: 1. data and partitions are related; 2. partitions are used to manage many tables together. The system partition table has only the second feature.

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Sharing pool for Oracle Performance Optimization