473,221 Members | 1,797 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,221 developers and data experts.

SQL Tuning tricks

Hi,
I have been in several ways benefiited from this site. I would like to share some sql tuning techniques(simple, but effective) with you all.


SQL Tuning Tips
Oracle Tips Session #6
3/31/98
Kathy Gleeson
________________________________________

SQL is the heart of the Oracle system. You can use many different SQL statements to achieve the same result. It is often the case that only one statement will be the most efficient choice in a given situation. The tips below include information about whether one form of the statement is always more efficient or whether each statement is an alternative and the efficency will vary depending on your application.

Oracle processes SQL in two steps: parsing and execution. Tuning may speed up your SQL by reducing either parsing, execution or both. Note, tuning SQL should only be done after your code is working correctly. Be aware that there is an inevitable tug-of-war between writing efficient SQL and understandable SQL.
TIP 1 (Best Tip): SQL cannot be shared within Oracle unless it is absolutely identical. Statements must have match exactly in case, white space and underlying schema objects to be shared within Oracle's memory. Oracle avoids the parsing step for each subsequent use of an identical statement.

sql> SELECT NAME FROM S_CUSTOMER WHERE ID = 212; statement to match
sql> SELECT NAME FROM s_customer WHERE ID = 212; lower case
sql> SELECT NAME FROM S_CUSTOMER WHERE ID=212; white space
sql> SELECT NAME
FROM S_CUSTOMER
WHERE ID=212; white space


o Use SQL standards within an application. Rules like the following are easy to implement and will allow more sharing within Oracle's memory.

- Using a single case for all SQL verbs
- Beginning all SQL verbs on a new line
- Right or left aligning verbs within the initial SQL verb
- Separating all words with a single space


o Use bind variables. The values of bind variables do not need to be the same for two statements to be considered identical. Bind variables are not substituted until a statement has been successfully parsed.

Sharable SQL
SELECT * FROM emp WHERE emp_no = :B1; Bind value: 123
SELECT * FROM emp WHERE emp_no = :B1; Bind value: 987

Non-sharable SQL
SELECT * FROM emp WHERE emp_no = 123;
SELECT * FROM emp WHERE emp_no = 987;


o Use a standard approach to table aliases. If two identical SQL statements vary because an identical table has two different aliases, then the SQL is different and will not be shared.

o Use table aliases and prefix all column names by their aliases when more than one table is involved in a query. This reduces parse time AND prevents future syntax errors if someone adds a column to one of the tables with the same name as a column in another table. (ORA-00918: COLUMN AMBIGUOUSLY DEFINED)

TIP 2: Beware of WHERE clauses which do not use indexes at all. Even if there is an index over a column that is referenced by a WHERE clause included in this section, Oracle will ignore the index. All of these WHERE clauses can be re-written to use an index while returning the same values. In other words, don't perform operations on database objects referenced in the WHERE clause.

Do Not Use Use
Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT account_name, trans_date, amount 
  3. FROM transaction 
  4. WHERE SUBSTR(account_name,1,7) = 'CAPITAL';    
  5.  
  6. SELECT account_name, trans_date, amount 
  7. FROM transaction 
  8. WHERE account_name LIKE 'CAPITAL%';
  9.  
  10. SELECT account_name, trans_date, amount 
  11. FROM transaction 
  12. WHERE account_name = NVL ( :acc_name, account_name);    
  13.  
  14. SELECT account_name, trans_date, amount 
  15. FROM transaction 
  16. WHERE account_name LIKE NVL ( :acc_name, '%');
  17.  
  18. SELECT account_name, trans_date, amount 
  19. FROM transaction 
  20. WHERE TRUNC (trans_date) = TRUNC (SYSDATE);    
  21.  
  22. SELECT account_name, trans_date, amount 
  23. FROM transaction 
  24. WHERE trans_date BETWEEN TRUNC (SYSDATE) AND TRUNC (SYSDATE) + .99999;
  25.  
  26. SELECT account_name, trans_date, amount 
  27. FROM transaction 
  28. WHERE account_name || account_type = 'AMEXA';    
  29.  
  30. SELECT account_name, trans_date, amount 
  31. FROM transaction 
  32. WHERE account_name = 'AMEX' 
  33. AND account_type = 'A';
  34.  
  35. SELECT account_name, trans_date, amount 
  36. FROM transaction 
  37. WHERE amount + 3000 < 5000;    
  38.  
  39. SELECT account_name, trans_date, amount 
  40. FROM transaction 
  41. WHERE amount < 2000;
  42.  
  43. SELECT account_name, trans_date, amount 
  44. FROM transaction 
  45. WHERE amount != 0;    
  46.  
  47. SELECT account_name, trans_date, amount 
  48. FROM transaction 
  49. WHERE amount > 0;
  50.  
  51. SELECT account_name, trans_date, amount 
  52. FROM transaction 
  53. WHERE amount NOT = 0;    
  54.  
  55. SELECT account_name, trans_date, amount 
  56. FROM transaction 
  57. WHERE amount > 0;
  58.  
TIP 3: Don't forget to tune views. Views are SELECT statements and can be tuned in just the same way as any other type of SELECT statement can be. All tuning applicable to any SQL statement are equally applicable to views.

TIP 4: Avoid including a HAVING clause in SELECT statements. The HAVING clause filters selected rows only after all rows have been fetched. Using a WHERE clause helps reduce overheads in sorting, summing, etc. HAVING clauses should only be used when columns with summary operations applied to them are restricted by the clause.

Do Not Use Use
Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT region, AVG (loc_size) 
  3. FROM location 
  4. GROUP BY region 
  5. HAVING region != 'SYDNEY' 
  6. AND region != 'PERTH';    
  7.  
  8. SELECT region, AVG (loc_size) 
  9. FROM location 
  10. WHERE region != 'SYDNEY' 
  11. AND region != 'PERTH'; 
  12. GROUP BY region;
  13.  
TIP 5: Minimize the number of table lookups (subquery blocks) in queries, particularly if your statements include subquery SELECTs or multicolumn UPDATEs.

Separate Subqueries
Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT emp_name 
  3. FROM emp 
  4. WHERE emp_cat = (SELECT MAX (category) 
  5. FROM emp_categories) 
  6. AND emp_range = (SELECT MAX (sal_range) 
  7. FROM emp_categories) 
  8. AND emp_dept = 0020;
  9.  
Combined Subqueries
Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT emp_name 
  3. FROM emp 
  4. WHERE (emp_cat, sal_range) 
  5. = (SELECT MAX (category), MAX (sal_range) 
  6. FROM emp_categories) 
  7. AND emp_dept = 0020;
  8.  
TIP 6: Consider the alternatives EXISTS, IN and table joins when doing multiple table joins. None of these are consistently faster; it depends on your data. If there is a poor performer here, it's likely the IN clause.

(Note, this query returns the employee names from each department in department category 'A'.)
Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT emp_name 
  3. FROM emp E 
  4. WHERE EXISTS ( SELECT 'X' 
  5. FROM dept 
  6. WHERE dept_no = E.dept_no 
  7. AND dept_cat = 'A');
  8. SELECT emp_name 
  9. FROM emp E 
  10. WHERE dept_no IN ( SELECT dept_no 
  11. FROM dept 
  12. WHERE dept_no = E.dept_no 
  13. AND dept_cat = 'A');
  14. SELECT emp_name 
  15. FROM dept D, emp E 
  16. WHERE E.dept_no = D.dept_no 
  17. AND D.dept_cat = 'A';
  18.  
TIP 7: Avoid joins that require the DISTINCT qualifier on the SELECT list in queries which are used to determine information at the owner end of a one-to-many relationship. The DISTINCT operator causes Oracle to fetch all rows satisfying the table join and then sort and filter out duplicate values. EXISTS is a faster alternative, because the Oracle optimizer realizes when the subquery has been satisfied once, there is no need to proceed further and the next matching row can be fetched.

(Note: This query returns all department numbers and names which have at least one employee.)
Expand|Select|Wrap|Line Numbers
  1.  
  2. Do Not Use    Use
  3. SELECT DISTINCT dept_no, dept_name 
  4. FROM dept D, 
  5. emp E 
  6. WHERE D.dept_no = E.dept_no;    SELECT dept_no, dept_name 
  7. FROM dept D 
  8. WHERE EXISTS ( 
  9. SELECT 'X' 
  10. FROM emp E 
  11. WHERE E.dept_no = D.dept_no);
  12.  
TIP 8: Consider whether a UNION ALL will suffice in place of a UNION. The UNION clause forces all rows returned by each portion of the UNION to be sorted and merged and duplicates to be filtered before the first row is returned. A UNION ALL simply returns all rows including duplicates and does not have to perform any sort, merge or filter. If your tables are mutually exclusive (include no duplicate records), or you don't care if duplicates are returned, the UNION ALL is much more efficient.

UNION UNION ALL
Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT acct_num, balance_amt 
  3. FROM debit_transactions 
  4. WHERE tran_date = '31-DEC-95' 
  5. UNION 
  6. SELECT acct_num, balance_amt 
  7. FROM credit_transactions 
  8. WHERE tran_date = '31-DEC-95';    
  9.  
  10. SELECT acct_num, balance_amt 
  11. FROM debit_transactions 
  12. WHERE tran_date = '31-DEC-95' 
  13. UNION ALL 
  14. SELECT acct_num, balance_amt 
  15. FROM credit_transactions 
  16. WHERE tran_date = '31-DEC-95';
  17.  
TIP 9: Consider using DECODE to avoid having to scan the same rows repetitively or join the same table repetitively. Note, DECODE is not necessarily faster as it depends on your data and the complexity of the resulting query. Also, using DECODE requires you to change your code when new values are allowed in the field.

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT COUNT(*) 
  3. FROM emp 
  4. WHERE status = 'Y' 
  5. AND emp_name LIKE 'SMITH%'; 
  6. ---------- 
  7. SELECT COUNT(*) 
  8. FROM emp 
  9. WHERE status = 'N' 
  10. AND emp_name LIKE 'SMITH%';
  11. SELECT COUNT(DECODE(status, 'Y', 'X', NULL)) Y_count, 
  12. COUNT(DECODE(status, 'N', 'X', NULL)) N_count 
  13. FROM emp 
  14. WHERE emp_name LIKE 'SMITH%';
  15.  
TIP 10: Oracle automatically performs simple column type conversions (or casting) when it compares columns of different types. Depending on the type of conversion, indexes may not be used. Make sure you declare your program variables as the same type as your Oracle columns, if the type is supported in the programming language you are using.

Datatype
of
field in
where
clause Your Query After Implicit Conversion Index
Used?
emp_no
indexed
numeric SELECT ...
FROM emp
WHERE emp_no = '123'; SELECT ...
FROM emp
WHERE emp_no = TO_NUMBER('123'); YES
emp_type
indexed
varchar2 SELECT ...
FROM emp
WHERE emp_type = 123; SELECT ...
FROM emp
WHERE TO_NUMBER (emp_type) = 123; NO!


________________________________________
Jun 7 '07 #1
0 20253

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Maryam | last post by:
Hi, I am having problems getting anything useful out of the index tuning wizard. I have created a table and inserted data into it. When i run the index tuning wizard i expect 2 indexes to be...
12
by: serge | last post by:
I have an SP that is big, huge, 700-800 lines. I am not an expert but I need to figure out every possible way that I can improve the performance speed of this SP. In the next couple of weeks I...
1
by: Jean-Marc Blaise | last post by:
Hi, I find much regrettable that Database Engine Tuning Advisor be not part of MS-Express Edition ... A server without such help is not a server. Besides, you've got the tutorials, but not the...
3
by: mleal | last post by:
Does anyone have some more detailed information about how Oracle and MS implement / allow Tuning on Oracle 10g and SQL Server 2005 and the differences between them? Which of them, In a deep...
4
by: bearophileHUGS | last post by:
I have started doing practice creating C extensions for CPython, so here are two ideas I have had, possibly useless. If you keep adding elements to a CPython dict/set, it periodically rebuilds...
13
by: atlaste | last post by:
Hi, I'm currently developing an application that uses a lot of computational power, disk access and memory caching (to be more exact: an information retrieval platform). In these kind of...
0
by: travolta004 | last post by:
Windows XP secrets and tips & tricks http://windowsxpsp2pro.blogspot.com/
4
by: 73k5blazer | last post by:
Hello again all.. We have a giant application from a giant software vendor that has very poor SQL. It's a PLM CAD application, that makes a call to the db for every cad node in the assembly. So...
3
by: dunleav1 | last post by:
In 9.1 and 9.5 (Linux 64 bit) when a buffer pool is set to self- tuning, how are blocks configured in respect to blocked vs non-blocked when self-tuning is set to on? (ie) I have one bufferpool...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.