1. What are the partition tables (indexes )?A partition table is a kind of data storage mechanism. It stores the data of a table in different memory segments, these different storage partitions are partitions ). Why? One of the reasons is that when the data volume of a table does not increase significantly. If these data records are stored in the same data zone (the same physical magnetic field or data file, when there are multiple data records for this table and are asked, it is easy to cause I/O bottle crashes. However, if the table data is stored in different magnetic fields according to certain distribution rules, the problems that may occur in the above conditions can be solved well, this improves the performance of data processing. The same is true for partition indexes. The effect of regional technologies in super-large data centers is even more obvious.
Ii. Partitioning ModeOracle has four partitioning methods: · range is divided by the range of the value of a field · hash is divided by the result of the HA Letter of the value of a field · List is divided by the Value List of a specified column · range -The hashrange sub-region is then divided by hash (sub-region) · range-listrange, and then press list (subarea)
3. region-based table Creation1. Create Table sales (invoice_no number, sale_year int not null, sale_month int not null, sale_day int not null) Storage (initial 100 K next 50 k) Logging
Partition by range (sale_year, sale_month, sale_day)
(Partition sales_q1 values less than (1999, 04, 01)
Tablespace TSA storage (initial 20 K, next 10 K ),
Partition sales_q2 values less than (1999, 07, 01)
Tablespace TSB,
Partition sales_q3 values less than (1999, 10, 01)
Tablespace TSC,
Partition sales
_
Q4 values less than (2000, 01, 01)
Tablespace TSD
,
Partition sales_q5 values less than (maxvalue)
)The preceding DML function is to create a table named sales with the fields invoice_no, sale_year, sale_money, and sale_day. The partition mode of the table is based on the range partition of the three fields sale_year, sale_money, and sale_day. The partition mode is as follows: sale_year is less than 1999, and sale_money is less than 04, sale_day is less than the value of 01 in the partition sale_q1. The partition is saved in the TSA table space.
Initial 20 K, next 10 K
;Sale_year is less than 1999, sale_money is less than 07, and sale_day is less than 01, which is divided into sale_q2, Which is saved in the blank space of the TSB table; sale_year is less than 1999, sale_money is less than 10, and sale_day is less than 01, which is divided into sale_q3, Which is saved in the blank space of the TSC table; sale_year is less than 2000, sale_money is less than 01, and sale_day is less than 01, which is divided into sale_q4, Which is saved in the blank space of the TSD table; if the last row is not specified, oracle will generate a region when the database is not distributed to different regions. 2. Create Table dept (deptno number, deptname varchar (32) Storage (initial 10 K)
Partition by hash (deptno)
(Partition P1 tablespace ts1, partition P2 tablespace ts2,
Partition P3 tablespace ts1, partition P4 tablespace ts3,
Partition P5 tablespace ts4 );Or create table dept (deptno number, deptname varchar (32) Storage (initial 10 K)
Partition by hash (deptno)
Partitions 5
Store in (ts1, ts2, ts3, ts4, ts5)
Overflow store in ts6
;
3. create Table sales_by_region (item # integer, qty integer, store_name varchar (30), state_code varchar (2), sale_date date) storage (initial 10 K next 20 k) tablespace tbs5
Partition by list (state_code)
(
Partition region_east
Values ('M', 'ny', 'ct ', 'nh', 'me', 'md', 'va ', 'pa', 'nj ')
Storage (initial 20 K next 40 k pctincrease 50)
Tablespace tbs8,
Partition region_west
Values ('CA', 'az', 'nm ', 'or', 'wa', 'ut ', 'nv', 'co ')
Pctfree 25 nologging,
Partition region_south
Values ('tx ', 'ky', 'tn ', 'La', 'Ms ', 'ar', 'al', 'ga '),
Partition region_central
Values ('Oh', 'nd', 'sd', 'mo', 'Il ', 'mi', 'A '),
Partition region_null
Values (null ),
Partition region_unknown
Values (default)
);
4. Create Table EMP (deptno number, empname varchar (32), grade number) partition by range (deptno)
Subpartition by hash (empname)
Subpartitions 8 store in (ts1, ts3, ts5, TS7)(Partition P1 values less than (1000) pctfree 40, partition P2 values less than (2000) store in (ts2, ts4, ts6, TS8), partition P3 values less than (maxvalue) (subpartition p3_s1 tablespace ts4, subpartition p3_s2 tablespace ts5); 5. create Table sample_regional_sales (deptno number, item_no varchar2 (20), txn_date date, txn_amount number, State varchar2 (2) partition by range (txn_date)
Subpartition by list (state)(Partition q1_1999 values less than (to_date ('1-APR-1999 ', 'dd-MON-YYYY') tablespace tbs_1
(
Subpartition q1_5o_northwest values ('or', 'wa '),
Subpartition q1_5o_southwest values ('az', 'ut', 'nm '),
Subpartition q1_5o_northeast values ('ny ', 'vm', 'nj '),
Subpartition q1_5o_southeast values ('fl ', 'ga '),
Subpartition q1_others values (default) tablespace tbs_4
),Partition q2_1999 values less than (to_date ('1-JUL-1999 ', 'dd-MON-YYYY') tablespace tbs_2
(
Subpartition q2_1999_northwest values ('or', 'wa '),
Subpartition q2_1999_southwest values ('az', 'ut', 'nm '),
Subpartition q2_1999_northeast values ('ny ', 'vm', 'nj '),
Subpartition q2_1999_southeast values ('fl ', 'ga '),
Subpartition q2_1999_northcentral values ('sd', 'wi '),
Subpartition q2_1999_southcentral values ('OK', 'tx ')
),Partition q3_1999 values less than (to_date ('1-OCT-1999 ', 'dd-MON-YYYY') tablespace tbs_3
(
Subpartition q3_1999_northwest values ('or', 'wa '),
Subpartition q3_1999_southwest values ('az', 'ut', 'nm '),
Subpartition q3_others values (default) tablespace tbs_4
),Partition q4_1999 values less than (to_date ('1-Jan-2000 ', 'dd-MON-YYYY') tablespace tbs_4 );
Iv. Partition TableOpinion: Oracle administrator is taken as the "alter table administrator" in the Oracle official document"
V. Regional correlationDBA_PART_TABLESALL_PART_TABLESUSER_PART_TABLES-----------------------DBA_TAB_PARTITIONSALL_TAB_PARTITIONSUSER_TAB_PARTITIONS-----------------------DBA_TAB_SUBPARTITIONSALL_TAB_SUBPARTITIONSUSER_TAB_SUBPARTITIONS-----------------------DBA_PART_KEY_COLUMNSALL_PART_KEY_COLUMNSUSER_PART_KEY_COLUMNS-----------------------DBA_SUBPART_KEY_COLUMNSALL_SUBPART_KEY_COLUMNSUSER_SUBPART_KEY_COLUMNS-----------------------DBA_PART_COL_STATISTICSALL_PART_COL_STATISTICSUSER_PART_COL_STATISTICS-----------------------DBA_SUBPART_COL_STATISTICSALL_SUBPART_COL_STATISTICSUSER_SUBPART_COL_STATISTICS-----------------------DBA_PART_HISTOGRAMSALL_PART_HISTOGRAMSUSER_PART_HISTOGRAMS-----------------------DBA_SUBPART_HISTOGRAMSALL_SUBPART_HISTOGRAMSUSER_SUBPART_HISTOGRAMS-----------------------DBA_PART_INDEXESALL_PART_INDEXESUSER_PART_INDEXES-----------------------DBA_IND_PARTITIONSALL_IND_PARTITIONSUSER_IND_PARTITIONS-----------------------DBA_IND_SUBPARTITIONSALL_IND_SUBPARTITIONSUSER_IND_SUBPARTITIONS
