In-depth understanding of Oracle partitions (3): Partition Table and partition index Overview

In-depth understanding of Oracle partition (3): Partition table and partition index Overview (I) Partition table technology Overview (1) Range partition ① example create table t (... column definition ...) partition by range (week_num) (partition p1 values less than (4) tablespace data0, partition p2 values less than (5) tablespace data1 ,....); ② applicable Scenarios Range partitioning is generally suitable for data storage by time period ③ advantage users know which partition the specific data falls into. Therefore, through the partition, You can effectively implement a variety of data management operations such as, delete management of historical data within a specified period of time, backup and recovery of a specified partition, or import and export. ④ disadvantages ● data in a partition may be uneven. ● Range partition is related to the record value, and implementation difficulty and maintainability are relatively poor. (2) list partition ① example: CRES Ate table t (... column definition ...) partition by list (city) (partition p1 values ('beijing') tablespace data0, partition p2 values ('shanghai') tablespace data1 ,....); the difference between Range and List is that the former is continuous, while the latter is discrete. Therefore, in terms of advantages and disadvantages and applicable scenarios, the difference is almost the same. We will not go into details here (3) HASH partition ① example create table t (. .. column definition ..) partition by hash (customer_no) partition 8 store in (data0, data1); reminder: oracle suggests that the number of HASH partitions is generally 2 power. ② applicable scenarios HASH partitions are suitable for static data. What is static data? This type of data is usually stored in the database forever and does not require historical data migration. For example: user Data Tables, account information, and other such information are accessed through user IDs or accounts. If HASH partitions are performed based on these fields and local prefix partition indexes are created, the access efficiency is quite high. ③ advantage ● uniform Data Distribution ● implementation is very simple ④ disadvantages users do not know which partition a record will be placed in. Therefore, HASH partitions are not suitable for mass data management operations such as historical data cleaning and import and export of mass data. (4) combined partitioning Oracle combined partitioning to some extent is a kind of concentrated performance, such as, in most cases, partitioning by the time field of the first dimension, which is suitable for large batches at the partition level the two-dimensional HASH or List of data management operations can further improve the access performance or reduce the implementation difficulty before only: range-HASH or Range-List (ii) Partition Index Technology overview in the production environment, sometimes we will encounter: we have already made a partition table, how does the performance not improve? Even slower? One of the reasons is that the partition index is not properly designed or even not designed. First, let's take a general look at the relationship between the table and the index on the partition: (1) The local prefix partition index assumes that the partition table is a transaction flow meter t and is partitioned by Range based on the transaction date. If you want to create an index on the date field, we can: create index idx_t on t (date) local; idx_t is called a local prefix index. The partitioning method of the Local index is the same as the partitioning method of the corresponding table. The so-called prefix indicates that the partition field is the prefix advantage of the index field. ● improved query performance ● when a certain after the partition is drop or merge, Oracle automatically performs the same operation on the corresponding index partition, the entire local prefix partition index is still valid, and no rebuild is required, which greatly ensures the availability of the table (2) local non-prefix partition index assume that we need to create a partition index in the area Field of table t. We can: create index idx_t_area on t (area) local; idx_t_area is called a local Non-Prefix partition. Index non-prefix must scan all partitions according to the index, and the performance may be lower. It can guarantee the availability of access by index. Applicable scenarios: if the historical data is frequently organized and cannot bear the long-term index unavailability caused by global partition index reconstruction, and the daily transaction performance is acceptable, we recommend that you design a local Non-Prefix partition index (3 ). global partition index assume that we need to create a partition index in the area Field of table t. We can: create index idx_t_g_area on t (area) global partition by range (area) (partition p1 less ...); global refers to two Global Partition indexes provided by Oracle after the Partition of the Index has nothing to do with the table Partition for 10 GB: ● Global Range Partition Index ● Global HASH Partition Index disadvantages: it is mainly reflected in the high availability of data. When the DROP partition and global partition index are all INVALID, unless the REBULID, but the larger the data volume, the longer the index reconstruction time