Oracle SQL Optimization

Absrtact: Database performance problem has always been the focus of decision-makers and technical staff, an important factor affecting database performance is the inefficiency of SQL query statement, and the use of multi-table join query in SQL query statement is very high, the most can reflect the query complexity, is often the focus and difficulty of SQL optimization. In order to improve the query speed and improve the efficiency of the database application system, the paper discusses the query optimization technique from the analysis of the multi-table query processing process of relational database, and points out the principle of multi-table query optimization. Through the research of several optimization strategies, the performance of the system is improved in time and space, and the query efficiency is improved to some extent.
Keywords: SQL optimization join
        Introduction
        Multi-table Join operation often consumes a lot of CPU time and memory, so the multi-table join query plays a very important role in query optimization, it is the most basic, most common and most complex operation in the database. In the database management information system, the join query operation is the largest proportion of all database operations. When the database system accumulates to a certain extent, if the query using a single sequential scan, then scan all the records may have to spend a few 10 minutes, or even a few hours, such a system lost its practical value. What kind of query strategy to take to reduce the time of join query is the query optimization problem that this paper needs to study.
        1 Optimization principle
        The premise of the join query operation is Cartesian product, that is, to take all the tuples in multiple tables to calculate, and then find the matching conditions, which itself increases the burden of the operation, so we do the optimization as far as possible to avoid the join query. If you must use a join query to try to use fewer join tables, query optimization tries to find a given equivalent expression, but the execution is more efficient, a query often has many implementation methods, the key is how to find an equivalent and less time to operate the expression. The core problem of optimization is to minimize the amount of data involved in the processing of the tables in the query, thus achieving the goal of optimizing time and space.
        2 Principles of Join query optimization
        2.1 Use single-table operation as much as possible
        Given three relationship patterns: (example below)
        S (sno,sname,sec,birthday,email)
        C (Cno,cname,creadit,tname)
        SC (Sno,cno,score)
        Example: Query 1001 students to take the course results.
        Analysis: In this query involves Sno and score two fields, Sno in the S table, score in the SC table, to use the S and SC joins,
The SQL statement is: SELECT score from S,SC WHERE S.sno=sc.sno and sno= ' 1001 '
Then this query is a multi-table join query, so it is bound to do Cartesian product operation, so it will increase the time of retrieval.
        Analysis: The SNO and score two fields involved in this query can all be found in the SC table, as far as possible using the single table operation principle, you can use only one SC table.
        The SQL statement is changed to: SELECT score from SC WHERE sno= ' 1001 ' This avoids the Cartesian product operation when joining, greatly improves the retrieval speed.
        2.2 The following principles should be achieved in connection operations that cannot be avoided:
        2.2.1 Joins the table when the number of joins in a long table join query, the number of joined tables as few as possible, so that you can reduce the amount of data involved in the query processing, so as to optimize the time and space.
        Example: Query the student's number and score of the "database Application" course.

Journal article classification query, in the periodical library

        Analysis: This query involves the SNO, CNAME, and score three fields, respectively, in S, C, and SC, to use S, C, and SC joins,
The SQL statement is: SELECT sno,score from S,C,SC WHERE S.sno=sc.sno and C.cno=sc.cno and cname= ' database application '
        Then this query is a multi-table join query, and in the three tables joined, 22 will do the Cartesian product operation, so the retrieval time is increased.
        Analysis: This query involves Sno, CNAME and score three fields, respectively, in the C table and SC table can be found in all, according to join the table when the principle of less than many, you can use only C and SC table.
        SQL statement instead: SELECT sno,score from C,SC WHERE c.cno=sc.cno and cname= ' database application '
        2.2.2 Joins the table when the joint is less than the number of links on the basis to be able to implement joins in a long table join query, so that the number of joined tables as few as possible, but not blindly pursue the number of tables, if the use of the table can not reach the data we want to query, or can not achieve the connection, that is not successful.
        Example 3: Query the names of students who have enrolled in the database application course.
        Analysis: This query involves sname and CNAME two fields, respectively, in the S table and the C table, to use the joins of S and C, the SQL statement is: SELECT sname,cname from s,c WHERE cname= ' Database application ' Then this query is a multi-table join query, but the two tables do not have public properties, so the implementation of the join, so that the operation becomes a simple Cartesian product operation, in this connection will query out the record of non-conformance, lost the meaning of the query.
        Analysis: This query involves sname and CNAME two fields, respectively in S table and C table, but S table and C table have no public properties, cannot implement join, so we must use SC table, so this query is S table, c table and SC table three tables join.
        SQL statement instead: SELECT sname,cname from S,C,SC WHERE S.sno=sc.sno and C.cno=sc.cno and cname= ' database application '
        2.3 First filter after join
        When querying multiple data tables, filter the back joins first.
        Example 5: Query for information on all students with a score greater than 70.
        Analysis: To do the S-table and SC table Two table joins, and filter out the matching records.
        The SQL statement is: Select*from s,sc WHERE S.sno=sc.sno and score>70
        This query statement first joins two data tables by number, and then filters the records that match the criteria. Since some records are filtered out after two data tables are joined, and multiple data table joins are Cartesian operations, the time consumed will increase rapidly as the number of records increases.
        SQL statement instead: Select*from s,sc WHERE score>70 and S.sno=sc.sno
This join query statement overcomes the above shortcomings, first to filter out the matching records, reduce the number of records to join, and then execute the join query, greatly improving the query efficiency.
        3 concluding remarks
        Query optimization to grasp the key issues, for the database application, the focus is how to improve the efficiency of SQL execution. In the process of database development and maintenance, the optimization design of multi-table join query can improve the system performance, especially for the database system with large data volume. Several optimization strategies described above make the multi-table join query improve the performance of the system in time and space, and improve the query efficiency to some extent.

Oracle SQL Optimization