Summary of insert and query operations for tens of millions of records in Oracle

Recently, we have implemented a project to insert and query database tables with 10 million records. I thought it was easy to insert and query databases. I don't know if the data reaches the level of millions or even tens of millions, it seems quite difficult.

Recently, we have implemented a project to insert and query database tables with 10 million records. I thought it was easy to insert and query databases. I don't know if the data reaches the level of millions or even tens of millions, it seems quite difficult. After several twists and turns, the task was finally completed.

1. Avoid using the Hibernate framework

Although it is convenient to use hibernate, it does not work well for massive data operations.

Insert:

I tried to use hibernate to insert about 50 thousand pieces of data at a time. If the ID is generated using sequence, Hibernate will obtain 50 thousand sequence from the database 50 thousand times and construct the corresponding object, save the data to the database for another 50 thousand times. It took me 10 minutes. The main time was not spent on inserts, but on retrieving sequence from the database for 50 thousand times, which made me quite depressed. After that, I changed the ID generation method to increase to solve the problem, but I still had to wait for that ten minutes.

Query:

The main idea of hibernate for database query is object-oriented, which will make a lot of data that we do not need to query occupy a large amount of system resources (including database resources and local resources ). Due to my preference for hibernate, I made a lot of attempts including configuring SQL and improving SQL in the spirit of not abandoning or giving up, but all of them ended in failure and I had to endure it.

2. When writing a query statement, you must list the queried fields one by one.

Do not use a statement similar to select * From x_table during query. Use select ID and name from x_table whenever possible to avoid unnecessary data waste. For massive data, a field occupies considerable resources and query time.

3. Reduce Unnecessary query Conditions

When performing a query, we usually submit a query form to the background at the front end, parse the form in the background, and then perform the query operation. When parsing forms, for convenience, we often prefer to replace some conditions that do not need to be queried with permanent conditions (such as: Select count (ID) from x_table where name like '%'), in fact, such an SQL statement is a terrible waste of resources. I tried to use select count (ID) from x_table for the same query of nearly 10 million records. It takes 11 seconds to query the table using select count (ID) from x_table.
Where name like '%' takes 33 seconds.

4. Avoid table join during Query

When querying massive data, try to avoid table connections (especially left and right connections). When you have to connect tables, the data volume of the connected table must not be too large, if tens of thousands of tables are connected, you can think about re-designing the database table, because the waiting time is by no means tolerable to normal users. Network Management Network bitscn_com

5. When nesting a query, try to minimize the query range in the first select statement.

When there are multiple select nested queries, you should try to minimize the scope of the query in the innermost layer, and paging is enabled first. Most of the time, the page is simply put into the inner query, which can form a qualitative change in the query efficiency.

That's all. I hope it will be helpful to my friends who have encountered similar problems!