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

This article is about how to insert and query thousands of records in Oracle, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

1. Avoid using Hibernate framework

Although Hibernate is convenient to use, it is inadequate for the operation of massive data.

About inserting:

We have tried to insert about 50, 000 pieces of data at one time with Hibernate. If ID is generated by sequence, Hibernate will get 50, 000 sequence from the database in 50, 000 times. After constructing the corresponding objects, the data will be saved to the database in 50, 000 times. It took me ten minutes. Most of the time was spent not on inserting, but on fetching sequence from the database 50, 000 times, which frustrated me. Although the problem was solved by changing the ID generation method to increase, I was still frightened by the ten-minute wait.

About the query:

Hibernate's main idea of database query is object-oriented, which will make a lot of data that we do not need to query take up a lot of system resources (including database resources and local resources). Due to the preference for Hibernate, in line with the style of not abandoning and not giving up, we have made quite a lot of attempts, including matching SQL, improving SQL and so on, but all of them ended in failure and had to give up.

2. When writing a query, list the fields of the query one by one

Don't use statements like select * from x_table when querying, try to use select id,name from.

X_table to avoid wasting resources when querying unwanted data. For large amounts of data, the resources and query time occupied by a field are considerable.

3. Reduce unnecessary query conditions

When we make a query, the foreground often submits a query form to the background, the background parses the form, and then carries on the query operation. When we parse the form, for convenience, we often like to replace some conditions that do not need to be queried with conditions that are always true (such as: select

Count (id) from x_table where name like

'%'), in fact, such a SQL is a terrible waste of resources. I have tried to use select count (id) from x_table for the same query of nearly 10 million records

It takes 11 seconds to make a table query, but 33 seconds to use select count (id) from x_table where name like'%'.

4. Avoid using table joins when querying

When doing massive data queries, you should try to avoid table joins (especially left and right joins). When you have to join tables, the amount of data of another table to be joined must not be too large. If there are tens of thousands of connected tables, it is estimated that you can consider redesigning the database table, because the waiting time is by no means beyond the tolerance of normal users.

5. When nesting a query, try to minimize the scope of the query at the first select.

When you have multiple select nested queries, you should try to minimize the scope of the query at the innermost level and paging first if you can page. In many cases, paging is simply put into the inner query, which can make a qualitative change to the query efficiency.

The above is how to insert and query thousands of records in Oracle. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.