Differences between SQL Server and Oracle database in query optimization (i)

In general, Oracle databases operate more efficiently than SQL Server databases in large data environments. From the query optimization alone, the two are quite different. Below, the author of the database query optimization of the difference to do a description, if there is not accurate place, but also please criticize the advice.

One, in the database sorting query optimization differences.

Before I explain this, I'll start with a concept for the reader to understand what I'm talking about. A hit rate, which is the ratio of obtaining data from memory without obtaining data from the disk. As we have mentioned in previous articles, when querying data in a database, the database server first looks for data from memory. The contents of the database file are read only if the data in memory does not exist. Also, querying data from memory is much more efficient than querying data in a database file. In this respect, if we can improve the hit rate of this query, it is obvious that the query efficiency of the database system can be improved.

Although SQL Server and Oracle have a role to play in this regard, the author believes that Oracle database in this respect the advantages of more obvious. In particular, the Oracle database adopts the temporary segment management mechanism, which obviously improves the hit rate of the database query.

What do you call the temporary section of the database? Assuming that when we have just finished checking the Employee Information table, the contents of the Employee Information table are in the database server's memory. At this point, we need to sort the table query, such as we would like to query the staff for more than two years of service, and the length of seniority to sort. At this point, the Oracle database server manages to sort all rows in an in-memory sort area. The size of this sort area is determined by the initialization file Init.ora of the database. When the sorting area is not large enough to accommodate the number of employee information records we query, the database opens a temporary segment in the database server during the sort operation. Obviously, in the query operation process, if opening a temporary paragraph, will reduce the database hit rate, reduce the efficiency of the sorting query. We now want this sort to be done in memory without having to open up additional temporary segments, so that you can eliminate the overhead of writing data to a temporary segment and improve the efficiency of sorting queries. So, when the data in our database is large, we can consider increasing the length of this sort area to avoid the need of the temporary segment. It is this temporary segment of the problem, such as we just query the Employee Information table, after the query is completed, and then the table to sort the query, it feels as if it is still a new query. In fact, this sort of query problem, we can use a certain way to optimize this to improve the efficiency of sorting queries.

To modify the method:

The first step: the use of query statements to determine whether there is a temporary segment of the production of demand.

SELECT * from V$sysstat where name= ' dtmfg (disk) ' or name= ' DTMFG (memory) ';

If we can periodically use the above statements to query whether there is a temporary segment of the production of requirements. In this statement, DTMFG is the specific database instance name, we just need to modify the name, we can query what we need. If a temporary segment requirement is found in the query results, it is necessary to consider modifying the related configuration file to optimize the sorting query performance.

Step Two: Modify the parameter configuration file.

We need to modify the Inint.ora file to modify the value of the sort-area-size inside. However, after you modify this configuration file, you must restart the database before it takes effect. Under normal circumstances, the database administrator needs to periodically query this temporary segment requirements, and then according to the situation, constantly adjust, do a good job of sorting query optimization functions.

In Microsoft's SQL Server database, the author found no similar features.