A new feature in 11g is the partition table for the system, and here's 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 partition 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
*
An error occurred on line 1th:
ORA-14701: For a table partitioned by the system method, you must use a partition extension or a binding variable for DML
sql> INSERT INTO S_p_test partition (p1) Select Object_id,object_name from Dba_objects
where object_type= ' TABLE ';
2115 rows have been created.
sql> INSERT INTO S_p_test partition (p2) select Object_id,object_name from Dba_objects
where object_type= ' INDEX ';
2888 rows have been created.
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, indicating that the data and partition have no relationship
sql> INSERT INTO S_p_test partition (P3) VALUES (1, ' AA ');
sql> INSERT INTO S_p_test partition (P4) VALUES (1, ' AA ');
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 cropping and partition smart associations are invalid and cannot establish localized indexes
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)
*
An error occurred on line 1th:
ORA-14255: Table is not partitioned by range, list, combination range, or combined list method
-If you know which table the data is in, make the partitioning criteria
Sql> Set Autotrace traceonly
Sql> SELECT * from S_p_test where col1=1;
Execution plan
----------------------------------------------------------
Plan Hash value:2020968526
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
| 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 |
-------------------------------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
2-filter ("COL1" =1)
Note
-----
-Dynamic sampling used for this statement (level=2)
Statistical information
----------------------------------------------------------
5 Recursive calls
0 db Block gets
Consistent gets
0 physical Reads
0 Redo Size
414 Bytes sent via sql*net to client
338 Bytes received 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
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 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 |
----------------------------------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
2-filter ("COL1" =1)
Note
-----
-Dynamic sampling used for this statement (level=2)
Statistical information
----------------------------------------------------------
5 Recursive calls
0 db Block gets
Consistent gets
0 physical Reads
0 Redo Size
385 Bytes sent via sql*net to client
338 Bytes received via sql*net from client
2 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
1 rows processed
usage scenarios for system partition Tables I don't think much of it, if the characteristic of the traditional partitioned table data is: 1. Data and zoning are linked; 2. Partitioning is the management of a lot of tables sticking together. The System partition table has only a second feature.
Oracle 11g new feature system partition table
