151. How to monitor the hit rate of the Dictionary buffer in SGA?
Select Parameter, Gets, GetMisses, getMisses / (Gets getMisses) * 100
"Miss Ratio",
(1- (getMisses) / (sum (gets) sum (getMisses)))) * 100 "Hit Ratio"
From v $ rowcache
Where gets getMisses <> 0
GROUP BY Parameter, Gets, getMisses
152. How should I monitor the hit rate of the share of the share of the co-buffer area in SGA, should it be less than 1%?
SELECT SUM (Pins) "Total Pins", SUM (Reloads "Total Reloads",
SUM (RELOADS) / SUM (Pins) * 100 Libcache
From v $ librarycache;
Select SUM (Pinhits-Reloads) / Sum (Pins "HIT
Radio, SUM (RELOADS) / SUM (PINS) "Reload Percent"
From v $ librarycache;
153. How do I display the category and size of all database objects?
Select Count (Name) Num_Instances, Type, Sum (Source_size)
Source_size,
SUM (Parsed_size) Parsed_size, sum (code_size) code_size
, Sum (Error_Size) Error_Size,
SUM (Source_Size) SUM (Parsed_Size) Sum (Code_SIZE)
SUM (Error_Size) Size_Required
From DBA_Object_size
Group by Type Order by 2;
154. Monitoring the hit rate of the SGA medium-red log cache, should be less than 1%
Select Name, Gets, Misses, Immediate_Gets, IMMEDIATE_MISS,
Decode (Gets, 0, 0, Misses / Gets * 100) Ratio1,
Decode (IMMEDIATE_GETS IMMEDIATE_MISS, 0, 0,
IMMEDIATE_MISSES / (IMMEDIAT_GETS IMMEDIATE_MISSES) * 100) Ratio2
From v $ latch where name in ('redo allocation');
155. Monitor the sorting ratio of memory and hard disc, it is best to make it less than .10, increase sort_area_size
SELECT NAME, VALUE from V $ sysstat where name in ('sorts ",
'sorts (disk));
156. How do I monitor what SQL statement is running in the current database?
SELECT OSUSER, Username, SQL_Text from V $ Session A, V $ SQLText B
WHERE A.SQL_Address = B.Address Order By Address, Piece
157. How do I monitor the dictionary buffer?
SELECT (SUM (Pins - Reloads)) / SUM (Pins "LIB Cache" from
V $ librarycache;
SELECT (SUM (gets - getMisses - usage - fixed) / sum (gets) "Rowcache" from v $ rowcache
Select SUM (Pins) "Executions", SUM (Reloads) "Cache Misses While
Executing "from V $ librarycache;
The latter except for the former, this ratio is less than 1%, close to 0% is good.
Select Sum (Gets) "Dictionary Gets", SUM (GetMisses) Dictionary
Cache Get Misses
From v $ rowcache
158. Monitor MTS
Select Busy / (Busy IDle) "Shared Servers Busy" from V $ dispatcher;
When this value is greater than 0.5, the parameters need to be increased.
Select SUM (WAIT) / SUM (Totalq) "Dispatcher Waits" from v $ queue where
TYPE = 'dispatcher';
SELECT Count (*) from V $ dispatcher;
SELECT Servers_Highwater from V $ MTS;
When Servers_Highwater is close to MTS_MAX_SERVERS, the parameters need to increase
159. How to know the ID number of the current user
SQL> Show user;
Oral
SQL> SELECT User from Dual;
160. How to view the high degree of debris
Select segment_name table_name, count (*) extents
From DBA_SEGMENTS WHERE OWNER NOT IN ('sys') group by
segment_name
Having count (*) = (Select max) from DBA_SEGMENTS GROUP
By segment_name;
162. How to know the storage situation in the table space
Select segment_name, sum (bytes), count (*) ext_quan from dba_extents
WHERE
TABLESPACE_NAME = '& TABLESPACE_NAME' AND segment_type = 'table' group
By tableSpace_name, segment_name;
163. How to know the storage situation of the index in the tablespace
Select segment_name, count (*) from DBA_EXTENTS WHERE
Segment_type = 'index' and wer = '& oowner'
GROUP BY segment_name;
164, how to know more users using CPU session
11 is CPU buy by this session
SELECT A.SID, SPID, STATUS, SUBSTR (A.Program, 1,40)
PROG, A.Terminal, Osuser, Value / 60/100 Value
From v $ sessions a, V $ PROCESS B, V $ SESSSTAT C
Where c.statistic # = 11 and c.SID = a.sid and a.paddr = B.Addr Order by
Value desc;
165. How to know the listener log file
Take 8i as an example
$ Oracle_home / network / log / listener.log166. How to know the listener parameter file
Take 8i as an example
$ Oracle_home / network / admin / listener.ora
167. How to know TNS connection files
Take 8i as an example
$ Oracle_home / network / admin / tnsnames.ora
168. How to know SQL * NET environment files
Take 8i as an example
$ Oracle_home / network / admin / sqlnet.ora
169. How do I know a warning log file?
Take 8i as an example
$ Oracle_Home / Admin / Sid / BDump / Sidalrt.log
170. How to know the basic structure
Take 8i as an example
$ Oracle_home / rdbms / admin / standard.sql
171. How to know a dictionary view
Take 8i as an example
$ Oracle_home / rdbms / admin / catalog.sql
172. How to know the Dictionary Dictionary view of establishing an audit
Take 8i as an example
$ Oracle_home / rdbms / admin / cataudit.sql
173. How to know a quick-study Dictionary Dictionary view
Take 8i as an example
$ Oracle_home / rdbms / admin / catsnap.sql
This reason is mainly to optimize the SQL statement! Mainly based on Oracle9i.
174. / * all_ROWS * /
Indicates that the 对 语 选择 选择 优 方法 方法 方法 方法 方法 方法 方法 方法, 并 并 获得, 获得, 获得, 方法, 获得, 方法, 方法, 方法, 方法, 方法. 方法, 方法, 方法,
E.g:
SELECT / * ALL _ROWS * / EMP_NO, EMP_NAM, DAT_IN from BSempms Where
EMP_NO = 'CCBZP';
175. / * first_ROWS * /
Indicates that the 对 语 选择 优 方法 方法 方法 方法 方法 方法 方法 方法, 并 时间 获得 时间 时间 时间 时间 时间 时间 时间 时间 时间 获得 时间 获得 时间 方法 时间 获得 时间 方法 时间 方法 时间 获得 时间 方法. 方法 时间 方法.
E.g:
SELECT / * first_ROWS * / EMP_NO, EMP_NAM, DAT_IN from BSempms Where
EMP_NO = 'CCBZP';
176. / * choose * /
Indicates that there is a visiting statistics in the information dictionary, will be based on the optimization method of overhead, and achieve the best throughput;
Indicates that if there is no statistical information of the table in the data dictionary, it will be based on the optimization method of rule overhead;
E.g:
SELECT / * CHOOSE * / EMP_NO, EMP_NAM, DAT_IN from BSempms Where
EMP_NO = 'CCBZP';
177. / * rule * /
Indicates the selection of rules based on rules.
E.g:
SELECT / * RULE * / EMP_NO, EMP_NAM, DAT_IN from BSempms Where
EMP_NO = 'CCBZP';
178. / * Full (Table) * /
Indicates how globally scanned on the table.
E.g:
SELECT / * FULL (A) * / EMP_NO, EMP_NAM from BSempms A Where
EMP_NO = 'CCBZP';
179. / * RowId (Table) * /
Tip Explicitly indicate that the specified table is accessed according to ROWID.
E.g:
SELECT / * ROWID (BSEMPMS) * / * from bsempms where
Rowid> = 'Aaaaaaaaaaaaa'
AND EMP_NO = 'CCBZZP';
180. / * Cluster (TABLE) * /
Tip Explicitly indicate the access method for selecting a cluster scan for the specified table, which is only valid for the cluster object.
E.g:
SELECT / * Cluster * / bsempms.emp_no, dpt_no from bsempms, BSDPTMS
WHERE DPT_NO = 'TEC304' and bsempms.dpt_no = bsdptms.dpt_no; 181. / * Index (Table Index_name) * /
Indicates the scanning method for selecting an index.
E.g:
SELECT / * Index (bsempms sex_index) USE SEX_INDEX BECAUSE THERE ARE
FewMale bsempms * / from bsempms where sex = 'm';
182. / * Index_asc (Table Index_name) * /
Indicates the scanning method for the index boost.
E.g:
SELECT / * INDEX_ASC (bsempms pk_bsempms) * / from bsempms Where
DPT_NO = 'CCBZP';
183. / * index_combine * /
To the specified table, select a dot mapping to access the road. If there is no index as a parameter in Index_Combine, you will select a dot mapping index.
Bablin combination.
E.g:
SELECT / * INDEX_COMBINE (bsempms Sal_bmi HiRedate_bmi) * / * from
BSempms
WHERE SAL <5000000 and hiredate
184. / * index_join (Table Index_name) * /
Tip Clear command Optimizer Use the index as an access path.
E.g:
SELECT / * INDEX_JOIN (bsempms Sal_hmi Hiredate_BMI) * / Sal, Hiredate
From bsempms where Sal <60000;
185. / * Index_Desc (Table Index_name) * /
Indicates the scanning method for selecting an indexing of the table.
E.g:
SELECT / * INDEX_DESC (bsempms pk_bsempms) * / from bsempms Where
DPT_NO = 'CCBZP';
186. / * index_ffs (Table Index_name) * /
Performing a fast total index scan for the specified table, not a full meter scan.
E.g:
SELECT / * INDEX_FFS (bsempms in_empnam) * / * from bsempms Where
DPT_NO = 'TEC305';
187. / * Add_equal Table Index_nam1, Index_nam2, ... * /
Tip Explicitly, the selection of the planning is clear, and the scanning of several single colony indexes will be made.
E.g:
SELECT / * INDEX_FFS (bsempms in_dptno, in_empno, in_sex) * / * from
BSEMPMS where EMP_NO = 'CCBZP' and dpt_no = 'TDC306';
188. / * USE_CONCAT * /
The OR condition behind the WHER in the query is converted to a combination of Union ALL.
E.g:
SELECT / * USE_CONCAT * / * from bsempms where dpt_no = 'tdc506' and
SEX = 'm';
189. / * NO_EXPAND * /
The no_expand will prevent it from expanding it based on the optimizer based on the OR or In-List of WHERE.
E.g:
SELECT / * NO_EXPAND * / * from bsempms where dpt_no = 'TDC506' AND
SEX = 'm'; 190. / * NOWRITE * /
It is forbidden to rewrite the query of the query block.
191. / * Rewrite * /
The view can be used as a parameter.
192. / * Merge (Table) * /
Ability to consolidate the respective queries of the view.
E.g:
SELECT / * MERGE (V) * / a.emp_no, a.emp_nam, b.dpt_no from bsempms a
(Selet DPT_NO
, AVG (SAL) AS AVG_SAL from bsempms b Group by dpt_no) v where
A.dpt_no = v.dpt_no
And A.SAL> V.AVG_SAL;
193. / * no_merge (Table) * /
For views with merger are no longer merged.
E.g:
SELECT / * NO_MERGE (V) * / a.emp_no, a.emp_nam, b.dpt_no from bsempms
A (Selet DPT_NO
, AVG (SAL) AS AVG_SAL from bsempms b Group by dpt_no) v where
A.dpt_no = v.dpt_no
And A.SAL> V.AVG_SAL;
194. / * ordered * /
The order in which the form appears in the FROM, ordered the Oracle in this order.
E.g:
SELECT / * Ordered * / a.col1, b.col2, c.col3 from table1 a, table2
B, Table3 C
WHERE A.COL1 = B.COL1 and B.COL1 = C.COL1;
195. / * USE_NL (Table) * /
Connect the specified table with the row of nesting connections, and use the specified table as the internal table.
E.g:
SELECT / * Ordered Use_nl (bsempms) * /
BSDPTMS.DPT_NO, BSEMPMS.EMP_NO, BSEMPMS.EMP_NAM from Bsempms, BSDPTMS
Where bsempms.dpt_no = bsdptms.dpt_no;
196. / * USE_MERGE (TABLE) * /
Connect the specified table with other line sources by merged sorting.
E.g:
SELECT / * USE_MERGE (BSEMPMS, BSDPTMS) * / * from bsempms, BSDPTMS
WHERE
BSEMPMS.DPT_NO = BSDPTMS.DPT_NO;
197. / * Use_hash (Table) * /
Connect the specified table with other row sources through a hash connection.
E.g:
SELECT / * USE_HASH (BSEMPMS, BSDPTMS) * / * from bsempms, BSDPTMS WHERE
BSEMPMS.DPT_NO = BSDPTMS.DPT_NO;
198. / * driving_site (TABLE) * /
Enforce the query execution with different locations selected from Oracle.
E.g:
SELECT / * DRIVING_SITE (DEPT) * / * from bsempms, dept @ BSDPTMS WHERE
Bsempms.dpt_no = dept.dpt_no;
199. / * Leading (Table) * /
The specified table is used as the head table in the connection.
200. / * Cache (Table) * /
When full menu scanning is performed, the Cache prompts to place the retrieval block of the table in the buffer cache recently used the most recent list of LRUs.
E.g:
SELECT / * FULL (BSEMPMS) CAHE (BSEMPMS) * / EMP_NAM from bsempms;
201. / * nocache (Table) * /
When full menu scanning is performed, the Cache prompts to place the retrieval block of the table in the buffer cache recently used the most recent list of LRUs.
E.g:
SELECT / * FULL (BSEMPMS) Nocahe (BSEMPMS) * / EMP_NAM from bsempms;
202. / * append * /
Inserted directly into the table, can increase the speed.
INSERT / * APPEND * / INTO TEST1 SELECT * AROM TEST4;
203. / * noApppend * /
Start regular insert by stopping parallel mode within the insertion statement survival.
INSERT / * NoAppnd * / INTO TEST1 SELECT * AROM TEST4;
Oracle optimizer
Use suitable Oracle optimizer
Oracle's optimizer has three types:
a. Rule (based on rules) b. COST (Based on cost) c. choose (selective)
Set the default optimizer, you can pass all kinds of declarations of the Optimizer_Mode parameter in the init.ora file, such as Rule, Cost, Choose, All_Rows, First_Rows
You are of course covered by SQL sentences or session (session).
To use a cost-based optimizer (CBO, COST-BASED OPTIMIZER), you must run Analyze often
Command to increase the accuracy of object statistics in the database.
If the data library's optimizer mode is set to selectability, the actual optimizer mode will be related to whether you have run an Analyze command.
If Table has been overwhelmed, the optimizer mode will automatically become CBO. Conversely, the database will use an optimizer in the form of Rule.
By default, Oracle uses a Choose optimizer, in order to avoid unnecessary full surface scans (Full Table Scan),
You must try to avoid using the Choose optimizer without using rules or cost-based optimers.
2. Way to access Table
Oracle uses two ways to record in the table:
a full table scan
The full table scan is to sequentially access each record in the table. Oracle uses a multi-data block (Database Block) to optimize full mete scan.
b. Access to the RowID
You can use ROWID-based access to improve access to the efficiency,
The ROWID contains physical location information recorded in the table .. Oracle adopts index (INDEX) to implement the contact between data and storage data.
Usually the index provides a way to quickly access the ROWID, so those queries based on the index column can be improved.
3. Shared SQL statement
In order not to repeat the same SQL statement, after the first resolution, Oracle stores the SQL statement in the memory. This block is located in the system global zone SGA (System
The memory in the shared buffer pool of the global area can be shared by all the database users.
So when you perform a SQL statement (sometimes called a cursor), if it
And the previously performed statements are exactly the same, Oracle can quickly get the statements that have been parsed and the best
Perform the path. Oracle's functionality greatly increases SQL execution performance and saves the use of memory.
Unfortunately, Oracle only provides cache buffering for simple tables, which does not apply to multi-table connection queries.
The database administrator must set the appropriate parameters for this area in Init.ora, the larger the memory area, the more statements can be retained, and the possibility of being shared is, the larger.
When you submit an SQL statement to Oracle, Oracle will first find the same statement in this memory.
What needs to be indicated here is that oracle takes a strict match to reach a shared, SQL statement must
Example (including space, wrap, etc.).
The shared statement must meet three conditions:
A. Comparison of characters:
The statement currently executed and the statement in the shared pool must be exactly the same.
E.g:
SELECT *.
And each of the following
SELECT *.
Select * from Emp; SELECT *.
B. The object referred to by the two statements must be exactly the same:
E.g:
How does the user object name access?
Jack Sal_Limit Private Synonym
Work_city public synonym
Plant_Detail public synonym
Jill Sal_Limit Private Synonym
Work_city public synonym
Plant_Detail Table Owner
Consider whether the following SQL statement can be shared between the two users.
SQL
Can you share
the reason
SELECT MAX (SAL_CAP) from Sal_Limit;
Cannot
Each user has a private synonym - sal_limit, which is different objects.
SELECT Count (* 0 from work_city where sdesc like 'new% ";
can
Two users access the same object Public Synonym - Work_CITY
SELECT A.SDESC, B.Location from work_city a, plan_detail b Where
a.city_id = b.city_id
Cannot
User Jack Access Plant_Detail via Private Synonym and Jill is the owner of the table, the object is different.
C. Bind variations of the same name must be used in two SQL statements (Bind Variables)
E.g:
The two SQL statements of the first group are the same (can be shared), and the two statements in the second group are different (even when running, the same value is different)
a.
Select Pin, Name from People Where Pin =: BLK1.PIN;
Select Pin, Name from People Where Pin =: BLK1.PIN;
b.
Select Pin, Name from People Where Pin =: BLK1.OT_IND;
Select Pin, Name from People Where Pin =: BLK1.OV_IND;
4. Select the most efficient table order (which is only valid in rule-based optimers)
Oracle's parser handles the table name in the FROM clause according to the order from right to left, so the FROM clause writes in the last table (basic table Driving
Table) will be processed. In the case of multiple tables in the FROM clause, you must select the table with the least number of records as a basic table. When Oracle handles multiple tables,
Connect them by sorting and merge. First, scan the first table (the last table in the FROM clause) and sequence the record, then scan the second table (the last second table in the FROM clause " ), Finalizing all records detected from the second table with the appropriate record in the first table.
E.g:
Table Tab1 16, 384 records
Table Tab2 1 record
Select Tab2 as a base table (best way)
Select count (*) from tab1, tab2 execution time 0.96 seconds
Select Tab2 as a basic table (poor method)
SELECT Count (*) from Tab2, Tab1 execution time 26.09 seconds
If there are more than 3 table connection queries, you need to select the intersect table as a base table.
The crosstab refers to the table referenced by the other tables.
E.g:
The EMP table describes the intersection of the Location table and the Category table.
SELECT *
From location L,
Category C,
EMP E
WHERE E.EMP_NO BETWEEN 1000 and 2000
And E.cat_no = c.cat_no
And e.locn = l.locn
More efficient than the following SQL
SELECT *
From EMP E,
Location L,
Category C
Where e.cat_no = c.cat_noand e.locn = L.LOCN
And E.EMP_NO BETWEEN 1000 and 2000
5. The connection order in the WHERE clause.
Oracle parses the WHERE clause in order, according to this principle, the connection between the table must be written before other WHERE conditions.
Those conditions that can be filtered out of the maximum number of records must be written at the end of the WHERE clause.
E.g:
(Inefficient, execution time 156.3 seconds)
SELECT ...
From EMP E
Where sal> 5000
And job = 'manager'
And 25 <(Select Count (*) from EMP
WHERE MGR = E.EMPNO);
(Efficient, execution time is 10.6 seconds)
SELECT ...
From EMP E
WHERE 25 <(Select Count (*) from EMP
Where mgr = e.empno)
And Sal> 50000
And Job = 'Manager';
6. Avoid using '*' in the SELECT clause
When you want to list all column in the Select clause, use dynamic SQL columns to reference '*'
It is a convenient way. Unfortunately, this is a very inefficient method. In fact, Oracle will convert '*' in turn to all column names in the process of parsing.
This job is done by querying the data dictionary, which means willing to take more time.
7. Reduce the number of visits to the database
When performing each SQL statement, Oracle has performed a lot of work inside: parsing the SQL statement, estimating the utilization of the index, binds variables, reading data blocks, etc.
It can be seen that the number of access to the database can actually reduce the workload of Oracle.
E.g,
There are three ways to retrieve employee numbers equal to 0342 or 0291.
Method 1 (Minimum Efficiency)
SELECT EMP_NAME, SALARY, GRADE
From EMP
WHERE EMP_NO = 342;
SELECT EMP_NAME, SALARY, GRADE
From EMP
WHERE EMP_NO = 291;
Method 2 (inefficient)
Declare
Cursor C1 (E_NO Number) IS
SELECT EMP_NAME, SALARY, GRADE
From EMP
WHERE EMP_NO = E_NO;
Begin
Open C1 (342);
Fetch C1 Into ..., .., ..;
... ..
Open C1 (291);
Fetch C1 Into ..., .., ..;
CLOSE C1;
END;
Method 3 (Efficiency)
Select a.emp_name, A.salary, A.Grade,
B.emp_name, b.salary, b.grade
From EMP A, EMP B
WHERE a.emp_no = 342
And B.EMP_NO = 291;
note:
Reset the Arraysize parameters in SQL * Plus, SQL * Forms, and Pro * C, you can increase the amount of retrieval data accessed each repository
, The recommended value is 200
8. Use the Decode function to reduce processing time
Use the DECode function to avoid repeated scans the same record or repeat the same table.
E.g:
SELECT Count (*), SUM (SAL)
From EMP
WHERE DEPT_NO = 0020
And ename Like 'Smith%';
SELECT Count (*), SUM (SAL)
From EMP
WHERE DEPT_NO = 0030
And ename Like 'Smith%';
You can use the decode function to get the same result.
Select Count (DEPT_NO, 0020, 'X', NULL) D0020_Count, Count (DEPT_NO, 0030, 'X', NULL) D0030_COUNT,
SUM (DEPT_NO, 0020, SAL, NULL) D0020_sal,
SUM (decode (DEPT_NO, 0030, SAL, NULL) D0030_SAL
From Emp where ename Like 'smith%';
Similarly, the DECode function can also be used in Group By and Order By clauses.
9. Integrate simple, unrelated database access
If you have a few simple database query statements, you can integrate them into a query (even if there is no relationship)
E.g:
Select Name
From EMP
WHERE EMP_NO = 1234;
Select Name
From DPT
WHERE DPT_NO = 10;
Select Name
From cat
WHERE CAT_TYPE = 'RD';
The above three queries can be merged into one:
Select E.NAME, D.NAME, C.NAME
From Cat C, DPT D, EMP E, DUAL X
WHERE NVL ('x', x.dummy) = NVL ('x', E.ROWID ( ))
And NVL ('x', x.dummy) = NVL ('x', D.RowID ( ))
And NVL ('x', x.dummy) = nVL ('x', C.RowID ( ))
And E.EMP_NO ( ) = 1234
And D.DEPT_NO ( ) = 10
And c.cat_type ( ) = 'rd';
(Translator Press: Although this method is taken, efficiency is improved, but the reader is greatly reduced, so the reader is still the advantage of the pros and cons.
10. Delete Record
The most efficient deletion of repeated recording methods (because RowID is used)
Delete from EMP E
WHERE E.ROWID> (Select Min (X.RowID)
From EMP X
Where x.emp_no = E.EMP_NO);
11. Replace Delete with Truncate
When the recording in the table is deleted, in the usual case, the Rollback Segments is used to store information that can be recovered.
If you don't have a Commit transaction, Oracle will restore the data to the state before delete (accurately said
Restore the situation before performing the deletion command)
And when using Truncate,
Rollback segments no longer store any information that can be recovered. When the command is running, the information cannot be recovered. Therefore, few resources are called, the execution time will be short.
(Translator Press: Truncate is only deleted full table, truncate is DDL is not DML)
12. Try to use Commit as much as possible
As long as it is possible, try to use Commit as much as possible in the program, which is improved, and the demand will also decrease due to the resources released by Commit:
Resources released by Commit:
a. Information on the recovery of information on a rollback segment.
b. Locks obtained by the computing statement
c. Space in Redo Log Buffer
d. Oracle is the internal cost of the above three resources.
(Translator Press: You must pay attention to the integrity of transactions when using commits. The efficiency and transaction integrity of realities are often fish and bear's own.
13. Calculate the number of records
In contrast, count (*) is slightly faster than count (1), of course, if you can retrieve it, the count of the index column is still fastest. For example
Count (Empno)
(Translator Press: In the 9CBS Forum, I have had a very enthusiastic discussion on this.
The author's point of view is not very accurate, through the actual test, the above three methods have no significant performance differences)
14. Replace the Having clause with WHERE clause
Avoid using a Having clause, Having will only filter the result set after retrieving all records. This processing requires sorting, totaling, etc. If you can limit the number of records through the WHERE clause, it can reduce this overhead. .
E.g:
Inefficient:
Select Region, AVG (log_size)
From location
GROUP BY Region
Having Region Region! = 'Sydney'
And Region! = 'Perth'
Efficient
Select Region, AVG (log_size)
From location
WHERE region region! = 'Sydney'
And Region! = 'Perth'
GROUP BY Region
(Translator Press: The conditions in Having are generally used to compare some set functions, such as count (), and so on.
In addition, the general conditions should be written in the WHERE clause.)
15. Reduce the query of the table
In the SQL statement containing subquery, pay special attention to reduce the query of the table.
E.g:
Inefficient
SELECT TAB_NAME
From tables
Where tab_name = (Select Tab_name
From tab_columns
Where version = 604)
And db_ver = (SELECT DB_VER
From tab_columns
Where version = 604)
Efficient
SELECT TAB_NAME
From tables
Where (tab_name, db_ver)
= (SELECT TAB_NAME, DB_VER)
From tab_columns
Where version = 604)
Update multiple column examples:
Inefficient:
Update EMP
SET EMP_CAT = (SELECT MAX (CATEGORY) from EMP_CATEGORIES,
SAL_RANGE = (SELECT MAX (SAL_RANGE) from EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;
Efficient:
Update EMP
Set (EMP_CAT, SAL_RANGE)
= (SELECT MAX (Category), Max (SAL_RANGE)
From EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;
16. Improve SQL efficiency by internal functions.
Select H.empno, E.ename, H.hist_type, t.type_desc, count (*)
From history_type t, emp e, EMP_HISTORY H
Where h.empno = E.empno
And h.hist_type = t.hist_type
GROUP BY H.Empno, E.ENAME, H.HIST_TYPE, T.TYPE_DESC;
Efficiency can be improved by calling the following functions.
Function Lookup_hist_type (Typ in Number) Return Varchar2
AS
TDESC VARCHAR2 (30);
Cursor C1 IS
SELECT TYPE_DESC
From history_type
WHERE HIST_TYPE = TYP;
Begin
Open C1;
FETCH C1 INTO TDESC;
CLOSE C1;
Return (NVL (TDESC, ''));
END;
Function lookup_emp (EMP in Number) Return Varchar2
AS
ENAME VARCHAR2 (30);
Cursor C1 IS
SELECT ENAME
From EMP
WHERE EMPNO = EMP;
Begin
Open C1;
FETCH C1 INTO ENAME;
CLOSE C1;
Return (NVL (ENAME, ''));
END;
SELECT H.EMPNO, LOOKUP_EMP (H.Empno),
H.hist_type, lookup_hist_type (h.hist_type), count (*)
From EMP_HISTORY H
Group by h.empno, h.hist_type;
(Translator Press: It is often seen in the forum. If 'can write out ....' The post is often written.
Methods to master the above use function solutions are very meaningful in actual work)
17. Use the alias (Alias)
When connecting multiple tables in the SQL statement,
Please use the aliasing name and put the alias on each column. This way, you can reduce the parsing time and reduce the syntax errors caused by the ambiguous caused by Column.
(Translator Note:
The COLUMN ambiguity is due to different tables in SQL have the same Column name. When this column appears in the SQL statement, the SQL parser cannot judge the property of column.
18. Substitution with exists in
In many queries based on the base table, in order to meet a condition, another table is often needed to join. In this case, use exists (or Not
EXISTS will usually improve the efficiency of the query.
Inefficient:
SELECT *
From EMP (Basic Table)
WHERE Empno> 0
And Deptno in (SELECT Deptno
From dept
WHERE LOC = 'MELB')
Efficient:
SELECT *
From EMP (Basic Table)
WHERE Empno> 0
And exists (SELECT 'X'
From dept
Where dept.deptno = Emp.deptno
And loc = 'melb')
(Translator Press: Relatively, replace Not in IN in NOT INTS will be more apparent, and the next section will be pointed out)
19. Substitution with not exists Not in
In the child query, the Not in clause will execute an internal sort and merge. No matter which case, Not in is the lowest efficiency.
(Because it performs a full table traversal to the table in the subquery). In order to avoid using Not in, we can rewrite it to an external connection (Outer Join) or Not
EXISTS.
E.g:
SELECT ...
From EMP
WHERE DEPT_NO NOT IN (SELECT DEPT_NO
From dept
Where dept_cat = 'a';
In order to improve efficiency. Rewriting is:
(Method 1: Efficient)
SELECT ....
From EMP A, DEPT B
Where a.dept_no = B.DEPT ( )
And B.DEPT_NO is NULL
And B.DEPT_CAT ( ) = 'a'
(Method 2: Most efficient)
SELECT ....
From EMP E
WHERE NOT EXISTS (SELECT 'X'
From DEPT D
WHERE D.DEPT_NO = E.DEPT_NO
And dept_cat = 'a');
20. Replace EXISTS with table
Typically, the way to use table connections is more efficient than EXISTS.
SELECT ENAME
From EMP E
WHERE EXISTS (SELECT 'X'
From dept
Where dept_no = E.DEPT_NO
And dept_cat = 'a');
(More efficient)
SELECT ENAME
From DEPT D, EMP E
WHERE E.DEPT_NO = D.DEPT_NO
And dept_cat = 'a';
