Pl/sql Developer Export Partition Index script

Inaccurate analysis and avoidance methods

1 Case visits

Hardware environment: IBM xseries 3650

Operating system: WINDOWS2003 Standard Version +SP02

Database version: Oracle9.2.0.1

Pl/sql Developer Version: 7.0.2.1076

As a result of business needs, first drop a partition table t_sms, and then rebuild this table, request table structure, index and so on exactly the same. But at that time the opening version was not found, so decided to use the Pl/sql developer tool "View SQL" to obtain the SQL statement to rebuild the partition table. (This partition table is partitioned by day, and because of the large amount of data per day, it truncate last month's data every morning, ensuring that the table can keep only 30 days of data.) )

The second day after the table was rebuilt, the table index was found to be invalid, causing the insert operation to fail. After analysis, the truncate partitioning operation is most likely to result in index invalidation. However, this table establishes a local partitioned index, and Oracle automatically rebuilds the local partition index after the day truncate operation, and no manual intervention is required, and the previous table does not have an index invalidation problem. So the preliminary judgment may be that the new and old table structure is different, immediately view this table index type, found that the index is not a local index, and from the Pl/sql developer tool exported in the build index statement is not a local index.

So why does the index type change when the original table index is a local partitioned index, and the Pl/sql developer tool is used to export the script? Does the Pl/sql developer tool have bugs in exporting index scripts?

2 Case Study

Just now, by looking at the table statement to locate the script problem exported by the Pl/sql Developer tool, we can verify it by following several experiments.

2.1 Add the Local keyword when building the index

(1) The index statement is as follows:

Create INDEX T_sms_idmonthday on t_sms (monthday)

Local tablespace Sms_dat;

(2) using the Pl/sql developer Tool "View SQL" to get the build index script as follows:

Create INDEX T_sms_idmonthday on t_sms (MonthDay);

(3) Analysis:

Visible, the local partition index is built and the Pl/sql Developer tool is used to export the script becomes the global index, the index type has changed.

2.2 Indexing without the local keyword or add global keyword

(1) The index statement is as follows:

Create INDEX T_sms_idmonthday on t_sms (monthday)

[Global] tablespace sms_dat;

(2) using the Pl/sql developer Tool "View SQL" to get the build index script as follows:

createindexT_SMS_IDMONTHDAYonT_SMS(MONTHDAY)
tablespaceSMS_DAT
pctfree10
initrans2
maxtrans255
storage
(
initial64K
minextents1
maxextentsunlimited
);

(3) Analysis:

As you can see, the scripts exported by the Pl/sql developer tool, regardless of the local keyword or the add global keyword, are changed to global indexes while the indexes are indexed, with some storage-related options available.

2.3 Export by using the Pl/sql Developer tool "Oracle export", "SQL Insert", "Pl/sql Developer" three methods respectively

Verify by exporting and then importing, only when the "Oracle export" method has been exported and then imported, the table structure and index structure have not changed, and the index type has been changed in two different ways.