To explain the indexing problem on a partitioned table

Today, after the partition cut query, found that the index is invalid! See a Good article! Sharing learning! [@more @] To explain the indexing problem on the partition table
Question:
Partitioned tables are really easy to manage and perform well in terms of performance.
But just one thing is not ideal: deleting data from partitions and truncate partition tables causes primary keys and global indexes to fail. If the datasheet is very large, it will take a long time to rebuild the index.
Is there a suitable solution to this problem? The Complete solution:
Finally, we'll tidy it up, please.
1. Use and advantages of partitioned tables: A, enhanced availability: If one partition of a table is not usable due to a system failure, the rest of the table's partitions can still be used;
b, reduce the shutdown time: If the system failure affects only part of the table partition, then only this part of the partition needs to be repaired, so can be repaired than the entire large table to spend less time;
C, easy to maintain: if you need to rebuild the table, it is much easier to manage each partition independently than to manage a single large table;
D, balanced I/O: You can allocate different partitions of the table to different disks to balance I/O performance improvement;
E, improve performance: the large table query, add, modify and other operations can be decomposed into the table of different partitions to execute in parallel, can make running faster;
F, the partition is transparent to the user, the end user does not feel the partition exists. 2. Problems in the course of use:
Deleting data in partitions and truncate partition tables causes primary keys and global indexes to fail. If the datasheet is very large, it will take a long time to rebuild the index. 3. Solution: First, according to the application as far as possible to modify the index as a partitioned index (individual indexes may not be modified), delete primary key also modified to unique partition index.
The test shows that if the partitioning field is reasonable, the efficiency of using the partitioned index is higher than the global index, while the efficiency of the primary key and the unique partition index is roughly the same, so the optimization is worthwhile, if the efficiency of individual application is reduced because of optimization, it can be optimized by the adjustment of the application.
Several aspects that require special attention:
A. If an individual index is not appropriate to use a partitioned index, it is preferable to increase the update global indexes clause when performing a delete partition, as follows: ALTER TABLE XXX DROP PARTITION yyy update global indexes to Ensure global index synchronization update to avoid impact on application.
B. Maintenance operations such as partition creation and deletion it is best to use manual methods in the database relatively idle time period, especially the partition of the deletion operation, due to the need to free disk space and synchronized update index, easy to produce some surprises.
C. A unique index created on a partitioned table must contain a partition field, or an error (ORA-14039) may be prompted, which also requires special attention.
D. Partitioning indexes are updated synchronously when partitions are added or split on a partitioned table, do not require index rebuilding and profiling, and can be dynamically created if necessary to meet the needs of the application. 3. Create an instance of a partitioned table:
CREATE TABLE EDU. Tj_result_partition
(
ID Number (8) is not NULL,
msg_id Number (8) is not NULL,
Ainserviceid VARCHAR2 (Ten) is not NULL,
State number (1) DEFAULT 0 is not NULL,
Msgmode Number (1) NULL,
Serviceid VARCHAR2 (Ten) is not NULL,
Srctermid VARCHAR2 is not NULL,
Desctermid VARCHAR2 is not NULL,
Feeterminalid VARCHAR2 is not NULL,
Src_mobile VARCHAR2 (one) DEFAULT ' 0 ' not NULL,
Src_accountid Number (8) DEFAULT 0 not NULL,
Src_personid Number (8) DEFAULT 0 not NULL,
Src_orgid Number (6) DEFAULT 0 not NULL,
Validtime DATE NULL,
Attime DATE NULL,
FinishDate DATE DEFAULT sysdate not NULL
)
Tablespace Education
Nologging
PCTFREE 10
Pctused 40
Initrans 10
Maxtrans 255
STORAGE (pctincrease 0
Freelists 5
Freelist GROUPS 2
Buffer_pool KEEP)
Noparallel
NoCache
PARTITION by RANGE (finishdate)
(
PARTITION partition_200605 VALUES Less THAN (to_date (' 2006-06-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), ' nls_calendar= Gregorian '))
Tablespace Eduspace,
PARTITION partition_200606 VALUES Less THAN (to_date (' 2006-07-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), ' nls_calendar= Gregorian '))
Tablespace Eduspace,
PARTITION partition_200607 VALUES Less THAN (to_date (' 2006-08-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), ' nls_calendar= Gregorian '))
Tablespace Eduspace,
PARTITION partition_200608 VALUES Less THAN (to_date (' 2006-09-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), ' nls_calendar= Gregorian '))
Tablespace Eduspace,
PARTITION partition_200609 VALUES Less THAN (to_date (' 2006-10-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), ' nls_calendar= Gregorian '))
Tablespace Eduspace,
PARTITION partition_200610 VALUES Less THAN (to_date (' 2006-11-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), ' nls_calendar= Gregorian '))
Tablespace Eduspace,
PARTITION partition_200611 VALUES Less THAN (to_date (' 2006-12-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), ' nls_calendar= Gregorian '))
Tablespace Eduspace,
PARTITION partition_200612 VALUES Less THAN (to_date (' 2007-01-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), ' nls_calendar= Gregorian '))
LOGGING)
/
4. Create an instance of a unique partitioned index:
CREATE UNIQUE INDEX EDU. Partition_primary
On EDU. Tj_result_partition
(ID, FinishDate)
LOGGING
Local (
PARTITION partition_200605
LOGGING
Nocompress,
PARTITION partition_200606
LOGGING
Nocompress,
PARTITION partition_200607
LOGGING
Nocompress,
PARTITION partition_200608
LOGGING
Nocompress,
PARTITION partition_200609
LOGGING
Nocompress,
PARTITION partition_200610
LOGGING
Nocompress,
PARTITION partition_200611
LOGGING
Nocompress,
PARTITION partition_200612
LOGGING
Nocompress
)
5. Create an instance of a normal partitioned index:
CREATE INDEX EDU. Partition_finishdate
On EDU. Tj_result_partition
(finishdate)
LOGGING
Local (
PARTITION partition_200605
LOGGING
Nocompress,
PARTITION partition_200606
LOGGING
Nocompress,
PARTITION partition_200607
LOGGING
Nocompress,
PARTITION partition_200608
LOGGING
Nocompress,
PARTITION partition_200609
LOGGING
Nocompress,
PARTITION partition_200610
LOGGING
Nocompress,
PARTITION partition_200611
LOGGING
Nocompress,
PARTITION partition_200612
LOGGING
Nocompress
)
6. Partition Maintenance Example:
A. Delete partitions:
ALTER TABLE EDU. Tj_result_partition DROP PARTITION partition_200610;
B. Additional partitions:
ALTER TABLE EDU. Tj_result_partition ADD PARTITION partition_200701 VALUES less THAN
(To_date (' 2007-02-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS ', ' Nls_calendar=gregorian ')]
LOGGING nocompress;
C. Split partitions:
ALTER TABLE EDU. Tj_result_partition
SPLIT PARTITiON partition_200608 at (to_date (' 2006-08-15 ', ' yyyy-mm-dd '))
into (partition partition_20060801, partition partition_20060802)
Oracle Partitioning table learning and application-create table (creating a partition Table)
CREATE TABLE Bill_monthfee_zero
(
SERV_ID number is not NULL,
Billing_cycle_month Number (6) is not NULL,
Date_type number (1),
ACC_NBR VARCHAR2 (80)
)
Partition by range (Billing_cycle_month)
(partition p_200407 values less than (200407)
Tablespace Ts_ziken
Storage (initial 100k next 100k minextents 1 maxextents Unlimited pctincrease 0),
Partition p_200408 values less than (200408)
Tablespace Ts_ziken
Storage (initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0))
;
Create INDEX idx_bill_monthfee_zero_idx01 on Bill_monthfee_zero (billing_cycle_month)
Tablespace Ts_ziken_idx
Storage (initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0) nologging;
Grant all on Bill_monthfee_zero to Dxsq_dev;

--Increase the partition table

ALTER TABLE Bill_monthfee_zero add Partition p_200409
Values less than (200409) tablespace Ts_ziken; --Delete a partition
ALTER TABLE PART_TBL drop Partition part_tbl_08;

--divides a partition into two partitions
ALTER TABLE Bill_monthfee_zero split Partition p_200409 at (200409)
Into (Partition p_200409_1 tablespace Ts_ziken,
Partition p_200409_2 tablespace ts_ziken_idx);

--Merge partitions
ALTER TABLE Bill_monthfee_zero
MERGE partitions p_200408, p_200409 into PARTITION p_all

--Renaming the partition
ALTER TABLE Bill_monthfee_zero rename Partition p_200408 to p_fee_200408

--Change the partition to table space
ALTER TABLE Bill_monthfee_zero move Partition p_200409
Tablespace ts_ziken_01 nologging

--Querying specific partitions
Select COUNT (*) from Bill_monthfee_zero partition (p_200407);

--Add data
INSERT INTO Bill_monthfee_zero SELECT * Bill_monthfee_zero partition (p_200407)

--Export of partitioned tables
userid=dxsq/teledoone@jndxsq154
buffer=102400
tables=bill_monthfee:p_200401,
File=e:exp_paraexp_dxsq_tables.dmp
Log=e:exp_paraexp_dxsq_tables.log


Skills:
To delete a field from a table:
ALTER TABLE Bill_monthfee_zero set unused column date_type;
Add a field: Alter TABLE Bill_monthfee_zero add date_type number (1);
Today, when you delete a partition in a ORALCE partition table, the partition is deleted directly, but the index of the table is not reset, which leads to the application problem.
In fact, the problem is that I don't know much about Oracle's partitioned tables.
Error 1. When you create this partition table, all of the indexes for this table are built into global, which is the index of the entire table, and if you build an index for each partition, that is, local will not present the problem today.
Error 2. When deleting a partitioned table without adding a statement that updates the index, causing all index unusable, my statement is ALTER TABLE hs_app_visitlog drop Partition hs_app_visitlog_0701;
Here if you write ALTER TABLE Hs_app_visitlog drop Partition hs_app_visitlog_0701 Update global indexes; it's okay.
No way, the error has been caused, had to put the table of all the index rebuild
Alter index hsidx_visitlog_version rebuild;
The time of rebuild is long ....
In addition, you can query the index state of a table by using the following statement:
Select Index_name,status from user_indexes where table_name= ' hs_app_visitlog ';
The results of valid are valid and unusable unused.
Remember it later! The partition of the original table, as the partition of the new month is forgotten, and when some of the data is already stored in the Max region
Forcibly rebuild the partition, and as a result, when inserting new data into the table, it prompts:
Index ' USER. Table_aaa_idx4 ' or the partition of such an index is in an unavailable state
Query index status (also available via EMC managed Terminal View Library-"scenario-" user-"index):
Select Index_name, status from User_indexes;
Found this index status is N/A, the original partition's index can not be viewed through the user dictionary,
Need to view with DBA's data dictionary, log in with SYS user, query:
Select Index_name,partition_name,status from Dba_ind_partitions order by status;
The index of the newly-inserted data in April was invalidated, and the index for the April partition was rebuilt.
ALTER index TABLE_AAA_IDX4 rebuild partition table_aaa_200704;
After rebuilding, the index is in effect and inserting new data is normal. Create INDEX WWWW1 on H_link_info1 (date_id)
Global partition by range (DATE_ID)
(partition h_lk200803 values less than (' 20080331 ')
Tablespace Rpt_tbs_h,
Partition h_lk200806 values less than (' 20080630 ')
Tablespace Rpt_tbs_h,
Partition h_lk200809 values less than (' 20080930 ')
Tablespace Rpt_tbs_h,
Partition h_lk200810 values less than (MAXVALUE)
Tablespace rpt_tbs_h)