(1) Select the most efficient table name order (only valid in rule-based seo/' target='_blank'> optimizer):
ORACLE's parser processes FROM clauses in order from right to left The table name in the FROM clause (the base table driving table) written last will be processed first. When the FROM clause contains multiple tables, you must choose the table with the smallest number of records as the basis. surface. If there are more than 3 tables to join the query, you need to select the intersection table as the base table. The cross table refers to the table that is referenced by other tables.
(2) The connection sequence in the WHERE clause. :
ORACLE uses a bottom-up order to parse WHERE clauses. According to this principle, connections between tables must be written before other WHERE conditions, and those conditions that can filter out the maximum number of records must be written at the end of the WHERE clause.
(3) Avoid using '*' in the SELECT clause:
ORACLE will convert '*' into all column names in sequence during the parsing process. This work is completed by querying the data dictionary, which means it will consume More time
(4) Reduce the number of accesses to get='_blank'> database:
ORACLE performs a lot of work internally: parsing SQL statements, estimating index utilization, binding variables, reading data blocks, etc.;
(5) Reset the ARRAYSIZE parameter in SQL*Plus, SQL*Forms and Pro*C to increase the amount of retrieved data for each get='_blank'> database access. The recommended value is 200
(6) Use the DECODE function To reduce processing time:
Use the DECODE function to avoid repeatedly scanning the same records or repeatedly connecting the same tables.
(7) Integrate simple, non-associative get='_blank'> database access:
If you have several simple get='_blank'>Database query statements, you can integrate them into one query (even if there is no relationship between them)
(8) Delete duplicate records:
The most efficient method to delete duplicate records (because of the use of ROWID) Example:
DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROM EMP When recording, under normal circumstances, rollback segments are used to store information that can be recovered. If you do not have a COMMIT transaction, ORACLE will restore the data to the state before deletion (to be precise, restore to the execution of the delete command (Previous situation) When TRUNCATE is used, the rollback segment no longer stores any information that can be recovered. After the command is run, the data cannot be recovered. Therefore, few resources are called and the execution time will be very short. (Translation Editor's note: TRUNCATE is only applicable when deleting the entire table. TRUNCATE is DDL not DML)
(10) Use COMMIT as much as possible:
Whenever possible, use COMMIT as much as possible in the program. In this way, the performance of the program will be improved and the demand will be improved. Reduced by the resources released by COMMIT:
Resources released by COMMIT:
a. Information used to recover data on the rollback segment.
b. Locks obtained by program statements
c. Space in redo log buffer
d. ORACLE manages the internal costs of the above three resources
(11) Replace the HAVING clause with the Where clause:
Avoid using the HAVING clause, HAVING will only filter the result set after retrieving all records. This processing requires Sorting, totaling and other operations. If you can limit the number of records through the WHERE clause, you can reduce the overhead in this area. (In non-Oracle) on, where, and having are three clauses that can add conditions, on is It is executed first, followed by where, and having last. Because on first filters the records that do not meet the conditions and then counts them, it can reduce the data to be processed in the intermediate operation. Logically speaking, it should be the fastest, and so should where. It is faster than having, because it filters the data before summing, and only uses on when two tables are joined, so when a table is used, only where and having are compared. In the case of single-table query statistics, if the conditions to be filtered do not involve fields to be calculated, then their results are the same, except where can use rushmore technology, but having cannot, and the latter is slower in speed. To involve a calculated field, it means that the value of this field is uncertain before calculation. According to the workflow written in the previous article, the action time of where is completed before calculation, while having is done after calculation. works, so in this case, the results of the two will be different. In multi-table join queries, on takes effect earlier than where. The system first combines multiple tables into a temporary table based on the connection conditions between each table, then filters by where, and then calculates. After calculation, it is filtered by having. It can be seen that if you want the filter condition to play a correct role, you must first understand when the condition should take effect, and then decide where to put it
(12) Reduce table queries:
In SQL statements containing subqueries , pay special attention to reducing queries to the table. Example:
SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME, DB_VER) = ( SELECT
TAB_NAME, DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)
(13) Improve SQL efficiency through internal functions.:
Complex SQL often sacrifices execution efficiency. Being able to master the above methods of using functions to solve problems is very meaningful in actual work
(14) Use table aliases (Alias):
When connecting multiple tables in a SQL statement, please use the alias of the table and prefix the alias to each Column. In this way, you can reduce the parsing time and reduce the syntax errors caused by Column ambiguity.
(15 ) Replace IN with EXISTS and NOT IN with NOT EXISTS:
In many queries based on basic tables, in order to satisfy a condition, it is often necessary to join another table. In this case, use EXISTS (or NOT EXISTS) This will generally improve the efficiency of the query. In a subquery, the NOT IN clause will perform an internal sort and merge. In either case, NOT IN is the least efficient (because it performs an internal sort on the table in the subquery) A full table traversal). In order to avoid using NOT IN, we can rewrite it into outer joins (Outer Joins) or NOT EXISTS.
Example:
(efficient) SELECT * FROM EMP (base table) WHERE EMPNO > 0 AND EXISTS (SELECT 'X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = 'MELB')
(Inefficient) SELECT * FROM EMP (base table) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = 'MELB')
(16) Identify 'inefficient execution' SQL statements:
Although there are currently various graphical tools for SQLseo/' target='_blank'> optimization emerging in an endless stream, but write your own SQL tool Solving the problem is always the best way:
SELECT EXECUTIONS, DISK_READS, BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
SQL_TEXT
FROM V$ SQLAREA
WHERE EXECUTIONS>0
AND BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS ORDER BY 4 DESC;
(17) Use indexes to improve efficiency:
Indexes are a conceptual part of the table and are used to To improve the efficiency of retrieving data, ORACLE uses a complex self-balancing B-tree structure. Generally, querying data through indexes is faster than a full table scan. When ORACLE finds out the best path to execute queries and Update statements, ORACLEseo/' target='_blank'>The optimizer will use indexes. Using indexes can also improve efficiency when joining multiple tables. Another benefit of using indexes is that it provides uniqueness verification of the primary key. With those LONG or LONG RAW data types, you can index almost any column. Generally, using indexes is particularly effective on large tables. Of course, you will also find that using indexes can also improve efficiency when scanning small tables. Although using indexes Query efficiency can be improved, but we must also pay attention to its cost. Indexes require space for storage and regular maintenance. Whenever records are added or deleted in the table or index columns are modified, the index itself will also be modified. . This means that each record's INSERT, DELETE, and UPDATE will cost 4 or 5 more disk I/Os. Because indexes require additional storage space and processing, those unnecessary indexes will actually shorten the query response time. slow.. Periodic rebuilding of indexes is necessary.:
ALTER INDEX REBUILD
18) Replace DISTINCT with EXISTS:
When submitting a query that contains one-to-many table information (such as department tables and employee tables), avoid placing in the SELECT clause Use DISTINCT. Generally, you can consider replacing EXIST. EXISTS makes the query faster, because the RDBMS core module will return the results immediately once the conditions of the subquery are met. Example:
(inefficient):
SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E
WHERE D.DEPT_NO = E.DEPT_NO
(efficient):
SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT 'X'
FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);
(19) The server/' target='_blank'>sql statement is in uppercase; because Oracle always parses the server/' target='_blank'>sql statement first, converts the lowercase letters into uppercase letters and then executes them
( 20) Use the connector "+" as little as possible to connect strings in java code!
(21) Avoid using NOT on index columns. Generally,
We should avoid using NOT on index columns. NOT will have the same impact as using functions on index columns. When ORACLE "encounters" NOT, it will Stop using indexes and perform full table scans instead.
(22) Avoid using calculations on index columns.
In the WHERE clause, if the index column is part of the function. seo/' target='_blank'>The optimizer will not use indexes but use full table scans.
Example:
Inefficient:
SELECT … FROM DEPT WHERE SAL * 12 > 25000;
Efficient:
SELECT … FROM DEPT WHERE SAL > 25000/12;
(23) Use >= instead of>
Efficient:
SELECT * FROM EMP WHERE DEPTNO >=4
Inefficiency:
SELECT * FROM EMP WHERE DEPTNO >3
The difference between the two is that the former DBMS will jump directly to the first record with DEPT equal to 4 while the latter will first Locate the record with DEPTNO=3 and scan forward to the first record with DEPT greater than 3.
(24) Replace OR with UNION (applicable to index columns)
Normally, replacing OR in the WHERE clause with UNION will will have better results. Using OR on index columns will cause a full table scan. Note that the above rules are only valid for multiple index columns. If there are columns that are not indexed, the query efficiency may be reduced because you did not choose OR. In the example below, there are indexes built on both LOC_ID and REGION.
Efficient:
SELECT LOC_ID, LOC_DESC, REGION
FROM LOCATION
WHERE LOC_ID = 10
UNION
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE REGION = "MELBOURNE"
Inefficient:
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ ID = 10 OR REGION = “MELBOURNE ”
If you insist on using OR, then you need to write the index column with the fewest returned records at the front.
(25) Use IN to replace OR
This is a simple and easy-to-remember rule, but the actual execution effect needs to be tested , under ORACLE8i, the execution paths of the two seem to be the same.
Inefficient:
SELECT…. FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30
Efficient
SELECT… FROM LOCATION WHERE LOC_IN IN (10,20,30);
(26) Avoid using on index columns IS NULL and IS NOT NULL
Avoid using any nullable columns in the index, ORACLE will not be able to use the index. For a single-column index, if a column contains a null value, the record will not exist in the index. For a composite index, if each column is null, the record will also not exist in the index. If at least one column is not null, the record exists. in the index. Example: If a unique index is built on columns A and B of the table, and there is a record in the table with A and B values of (123, null), ORACLE will not accept the next record with the same A and B values (123, null) records (insertion). However, if all index columns are null, ORACLE will consider the entire key value to be null and null is not equal to null. Therefore, you can insert 1000 records with the same key value, and of course they are all null. ! Because the null value does not exist in the index column, a null value comparison of the index column in the WHERE clause will cause ORACLE to deactivate the index.
Inefficiency: (index invalid)
SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;
Efficient: (Index valid)
SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0;
(27) Always use the first column of the index:
If the index is built on multiple columns, only the first one in it When a column (leading column) is referenced by a where clause, the seo/' target='_blank'> optimizer will choose to use the index. This is also a simple but important rule, when only the second column of the index is referenced When, seo/' target='_blank'> the optimizer uses a full table scan and ignores the index
28) Replace UNION with UNION-ALL (if possible):
When the SQL statement requires UNION two query result sets When , the two result sets will be merged in a UNION-ALL manner, and then sorted before outputting the final result. If UNION ALL is used instead of UNION, sorting is not necessary. The efficiency will be improved. Things to note Yes, UNION ALL will repeatedly output the same records in the two result sets. Therefore, you still need to analyze the feasibility of using UNION ALL based on business requirements. UNION will sort the result sets, and this operation will use the SORT_AREA_SIZE memory. For this Memory seo/' target='_blank'> optimization is also very important. The following SQL can be used to query the consumption of sorting
Inefficiency:
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC- 95'
UNION
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
Efficient:
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31 -DEC-95'
UNION ALL
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
(29) Replace ORDER BY with WHERE:
The ORDER BY clause only uses the index under two strict conditions.
All columns in ORDER BY Must be included in the same index and maintain the sort order in the index.
All columns in ORDER BY must be specified

iBatis和MyBatis:区别和优势解析导语:在Java开发中,持久化是一个常见的需求,而iBatis和MyBatis是两个广泛使用的持久化框架。虽然它们有很多相似之处,但也有一些关键的区别和优势。本文将通过详细分析这两个框架的特性、用法和示例代码,为读者提供更全面的了解。一、iBatis特性:iBatis是目前较为老旧的持久化框架,它使用SQL映射文件

MyBatis注解动态SQL的使用方法详解IntroductiontotheusageofMyBatisannotationdynamicSQLMyBatis是一个持久层框架,为我们提供了便捷的持久化操作。在实际开发中,通常需要根据业务需求来动态生成SQL语句,以实现灵活的数据操作。MyBatis注解动态SQL正是为了满足这一需求而设计的,本

MySQL是一种常用的关系型数据库管理系统,它支持变量的定义和使用。在MySQL中,我们可以使用SET语句来定义变量,并使用SELECT语句来使用已定义的变量。下面将通过具体的代码示例来介绍如何在MySQL中进行变量的定义和使用。首先,我们需要连接到MySQL数据库。可以使用以下命令连接到MySQL数据库:mysql-u用户名-p密码接下来,我们可以

Linux操作系统是一个开源产品,它也是一个开源软件的实践和应用平台。在这个平台下,有无数的开源软件支撑,如apache、tomcat、mysql、php等。开源软件的最大理念是自由和开放。因此,作为一个开源平台,linux的目标是通过这些开源软件的支持,以最低廉的成本,达到应用最优的性能。谈到性能问题,主要实现的是linux操作系统和应用程序的最佳结合。一、性能问题综述系统的性能是指操作系统完成任务的有效性、稳定性和响应速度。Linux系统管理员可能经常会遇到系统不稳定、响应速度慢等问题,例如

我在调用以下函数时遇到错误“ORA-00911:无效字符”。如果我使用带有硬编码值的SQL查询(截至目前,它已在下面的代码片段中注释掉),那么我可以在邮递员中以JSON响应获取数据库记录,没有任何问题。所以,看起来我的论点做错了。仅供参考,我正在使用“github.com/sijms/go-ora/v2”包连接到oracledb。另外,“DashboardRecordsRequest”结构位于数据模型包中,但我已将其粘贴到下面的代码片段中以供参考。请注意,当我进行POC时,我们将使用存

MyBatis标签详解:掌握MyBatis中各种常用标签的功能与用法,需要具体代码示例引言:MyBatis是一个强大且灵活的Java持久化框架,广泛应用于Java开发中。了解MyBatis标签的功能和用法对于使用MyBatis进行数据库操作非常重要。本文将详细介绍MyBatis中几个常用的标签,并提供相应的代码示例。一、select标签select标签用于执

JPAvsMyBatis:如何选择最佳的持久化框架?引言:在现代软件开发中,使用持久化框架来处理数据库操作是必不可少的。JPA(Java持久化API)和MyBatis是两个常用的持久化框架。然而,如何选择最适合你的项目的持久化框架是一个具有挑战性的任务。本文将分析JPA和MyBatis的特点,并提供具体的代码示例,帮助你做出更明智的选择。JPA的特点:J

备份数据库的sql语句有mysqldump命令、pg_dump命令、expdp命令、BACKUP DATABASE命令、mongodump命令和redis-cli命令。


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Dreamweaver CS6
Visual web development tools

ZendStudio 13.5.1 Mac
Powerful PHP integrated development environment

Safe Exam Browser
Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool
