I tried to determine the types of problems that arise when working with both optimizers and identify the best ways to solve them.
The main causes of poor work performance rule-based optimizer (often recurring problems in the case of rule-based optimizer):
- Inadequate control table
- Inadequate index
- Inadequate control index
- Using the index instead of the index ORDER BY WHERE
Inadequate control table
If the control table in a join is not selected optimally, it may be that significantly increases the time needed to execute the query. Earlier (in the section: 'RBO rules do not tell # 6 ") was already discussed what determines the choice of control table. Presented following example illustrates the potential differences in the time for completion:
SELECT COUNT (*)
Acct FROM a, b WHERE b.cost_center trans = 'MASS'
AND a.acct_name = 'MGA'
AND a.acct_name = b.acct_name;
In this example - if we assume that acct_name represents a unique key index, and the index cost_center represents a single column of values nieunikatowych - make a unique key that the table becomes a table control acct.
If both cost_center and acct_name indexes were nieunikatowych individual columns of values, the rule-based optimizer for a table would take control of trans table because it was listed as the last in the FROM clause. Such a situation with a high degree of probability could mean waiting a long time to execute, because usually there is only one row in the table acct for a certain account name (accouni) and the number of transactions for each cost center (cost center).
In the case of rule-based optimizer - if the status of the two indices are the same - the system does Oracle simply click on the order in which during the parsing tables are defined. Due to the fact that the syntax analyzer converts table names from right to left, the table whose name was given as the last (in the example above - the table trans) is actually processed first (control table).
SELECT COUNT {*)
FROM acct a, Trans b
WHERE b.cost_center = 'MASS'
AND a.acct_name = 'MGA'
AND a.acct_name = b.acct_name;
Answer = 19.722 seconds
The response time when switching the order of the tables in the FROM clause is
as follows:
SELECT COUNT (*)
FROM trans b, and acct
WHERE b.cost_center = 'MASS'
AND a.acct_name = 'MGA'
AND a.acct_name = b.acct_name;
Answer = 1.904 seconds
It is important that the table in the FROM clause that are listed as the last, contained the smallest number of rows returned. You can also customize the indexing so that it forced the use of a specific table as a control table. For example, you can make cost_center index index skonkatenowanym, joined with the column, which is often used in SQL queries along with the current column. Thus avoid giving it such a high rank, when there is a join of the tables.
Inadequate index
WHERE clause often contains multiple indexes, which can use rule-based optimizer. The optimizer does not have, however, no information on the number of rows that will need to review the index or the potential impact of your choice on the bad response time. Inappropriate selection of the index significantly increases the time.
Rule-based optimizer is guided by the simple principles of selection index. These rules (as well as some scenarios) described earlier (in the following sections under the heading "RBO rules do not tell").
It is at this point consider an example situation.
ERP package developed in general form to allow the use of columns for reporting purposes in any, as selected by the user. Suppose you have a column called business_unit, which has a single-column index. In most centers there are hundreds of business units (business unit), but in the other - only one.
Journal table has one index in column (business_ unit) and the second in columns (business_unit, account, journal_data). WHERE expression used in the query is as follows:
WHERE business_unit - 'A203'
AND account = 801919
BETWEEN AND journal_data
'01-DEC-2001 'AND '3 l-DEC-2001'
Single-column index takes precedence over trzykolumnowym index, although the latter returns the result in a fraction of the response time using one-column index. This happens because the request is using all one-column the column index. In this case, the only possibility is to remove the index, or use the cost-based optimizer. If no customized software is used, it can also use clues.
Inadequate control index
The way in which conditions are given in the expression (expressions) WHERE SELECT statement has the greatest impact on the performance of SQL statements, because the order in which conditions are given, the index influences the choice made by the optimizer.
If the rank two indices are equal (when, for example, in the WHERE clause are columns two indices jednokolumnowych) merges Oracle indexes. The order of consolidation can have a huge impact on the command. If the index that controls the query returns more rows than the other indexes, the query performance will not be optimal. The effect is very similar to the one that played in the wrong order of tables in the FROM clause.
It should consider the following example:
SELECT COUNT (*)
FROM trans
WHERE cost_center - 'MASS'
AND bmark_id = 9;
Response time: 4.255 seconds
The index, which has a column in the WHERE clause referred to as the first, it will control index. If the above command cost_center indexed value = 'MASS' pay far more rows than bmark_id = 9, in which case it will return one or at most two rows.
The following query order conditions in the WHERE clause was converted, which results in much shorter execution time.
COUNTf SELECT *)
FROM trans
WHERE bmark_id = 9;
AND cost_center = 'MASS'
Response time: 1.044 seconds
In the case of rule-based optimizer is the first place the WHERE clause conditions that will return the minimum number of lines.
Use the index instead of the index ORDER BY WHERE
Less frequently occurring problems associated with the choice of an index that observed in systems using rule-based optimizer:
SELECT fod_flag, account_no ...
FROM account_master
WHERE (account_code LIKE '1% ')
ORDER BY account_no;
Index_l UNIQUE (ACCOUNT_NO)
Index_2 (ACCOUNT_CODE)
At the specified index of this query execution time was 20 minutes. Request was used to create the report and performed daily by many brokers.
In this example, the optimizer tries to avoid sorting and chooses the index that contains a column with ORDER BY expression, instead of having an index column in the WHERE clause.
Centre, which described the problems occurred, the brokerage firm. SQL statement was executed frequently in order to create financial account summaries.
Managed to solve the problem of creating a concatenated index on both columns:
# Additional Index (ACCOUNT_CODE, ACCOUNT_NO)
It was decided to remove the index Index_2 (ACCOUNT_CODE), which was no longer needed, because the column account_code was leading a column of the new index. Account_no column has been added to the new index in order to take advantage of the growing data storage in an index. This allows avoiding the need to sort and, consequently, execution time decreased to 10 seconds.
No comments:
Post a Comment