Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the two traps of oracle zone". In daily operation, I believe many people have doubts about what the two traps of oracle zone are. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "what are the two traps of oracle zone?" Next, please follow the editor to study!

1. Individual scenarios can not fundamentally improve the query speed.

Automatic partition generation is not supported in Oracle10g. Technicians manually create partitions for one year or half a year or load the data to the maximum partition when the limit is exceeded. However, after one year and half a year, either the data cannot be inserted or the data of a partition increases sharply. At this time, the automatic partition feature of Oracle11g appears, but the automatic partition name cannot be set manually. If the amount of data is too large or there are cross-partition queries, there will be performance problems.

For example, Chestnut: there is a log storage system online, which stores about 1000W of data every day, supports paging sorting and query by date (if not sorted, this amount of data is small ks for Oracle), so we use the function of partition + overlay index (if you want to know more) query function, the performance is slightly improved. But after a period of time, it turns out that the system is still dead. Because this is the CAP problem, if you want to solve the problem fundamentally, please suggest that the company implement it with nosql (habase, ELK).

If there is such a scenario, where the salary is less than or equal to 5000, greater than 5000 and less than or equal to 12000, greater than 12000 and less than 25000, and greater than or equal to 25000, it is very efficient to create partitions according to these salary levels, because you can specify select * from TBL_OPR_CNT partition (5000_part); `), because the efficiency of the specified partition query is directly improved.

From this, we know that the efficiency of relational database lies in how we give full play to its strengths.

two。 Manually move the table or delete the table partition will cause the index to fail

In the actual online environment, the partitioning solution is often adopted when performance problems are found, but the partitioned table is generally a global index, and then the partitioning function is adopted directly in the original table. it took a period of time to generate historical partition data, and then deleted some of the historical partitions and found that the data could not be inserted. The following error:

Jdbc.exception.UncategorizedSQLException: uncategorized SQLException for SQL [insert into AUDITS]; SQL state [72000]; error code [1502]; ORA-01502: index 'AUDITS_PK' or partition of such index is in unusable state Caused by: ORA-01502: index 'AUDITS_PK' or partition of such index is in unusable state

Take the following method to rebuild the index to solve the processing.

Select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='AUDITS_PK'

Alter session set skip_unusable_indexes=false

Alter index AUDITS_PK rebuild;commit

3. What is the root cause and solution of this problem?

After the tablespace or partition is moved or deleted, the index based on the table will automatically become invalid. When UNUSABLE; accesses or manipulates the table, it will report an ORA-01502 exception; both unique and ordinary indexes have to be rebuilt.

Solution: try to create a local index when using table partitioning. (for example:

Create index AUDITS_PK on AUDITS (id) local

Because id is a partitioning key, this creates a local index with a prefix)

In this way, the index will not fail after the partition is deleted.

At this point, the study on "what are the two traps in oracle partition" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.