Also remember that there can be a disconnect between efficient SQL and comprehensible SQL. Always document your code.
* One: only "tune" SQL after code is confirmed as working correctly.
* Two: ensure repeated SQL statements are written absolutely identically to facilate efficient reuse: re-parsing can often be avoided for each subsequent use.
Writing best practices: all SQL verbs in upper-case i.e. SELECT; separate all words with a single space; all SQL verbs begin on a new line; SQL verbs aligned right or left within the initial verb; set and maintain a table alias standard; use table aliases and when a query involves more than one table prefix all column names with their aliases. Whatever you do, be consistent.
* Three: code the query as simply as possible i.e. no unnecessary columns are selected, no unnecessary GROUP BY or ORDER BY.
* Four: it is the same or faster to SELECT by actual column name(s). The larger the table the more likely the savings.
Use:
SELECT customer_id, last_name, first_name, street, city FROM customer; Rather than:
SELECT * FROM customer;
* Five: do not perform operations on DB objects referenced in the WHERE clause:
Use:
SELECT client, date, amount FROM sales WHERE amount > 0;
Rather than:
SELECT client, date, amount FROM sales WHERE amount!= 0;
* Six: avoid a HAVING clause in SELECT statements - it only filters selected rows after all the rows have been returned. Use HAVING only when summary operations applied to columns will be restricted by the clause. A WHERE clause may be more efficient.
Use:
SELECT city FROM country WHERE city!= 'Vancouver' AND city!= 'Toronto'; GROUP BY city;
Rather than:
SELECT city FROM country GROUP BY city HAVING city!= 'Vancouver' AND city!= 'Toronto';
* Seven: when writing a sub-query (a SELECT statement within the WHERE or HAVING clause of another SQL statement):
-- use a correlated (refers to at least one value from the outer query) sub-query when the return is relatively small and/or other criteria are efficient i.e. if the tables within the sub-query have efficient indexes.
-- use a noncorrelated (does not refer to the outer query) sub-query when dealing with large tables from which you expect a large return (many rows) and/or if the tables within the sub-query do not have efficient indexes.
-- ensure that multiple sub-queries are in the most efficient order.
-- remember that rewriting a sub-query as a join can sometimes increase efficiency.
* Eight: minimise the number of table lookups especially if there are sub-query SELECTs or multicolumn UPDATEs.
* Nine: when doing multiple table joins consider the benefits/costs for each of EXISTS, IN, and table joins. Depending on your data one or another may be faster.
Note: IN is usually the slowest.
Note: when most of the filter criteria are in the sub-query IN may be more efficient; when most of the filter criteria are in the parent-query EXISTS may be more efficient.
* Ten: where possible use EXISTS rather than DISTINCT.
* Eleven: where possible use a non-column expression (putting the column on one side of the operator and all the other values on the other). Non-column expressions are often processed earlier thereby speeding the query.
Use:
WHERE SALES <>
Rather than:
WHERE SALES + (n * SALES) <>
• Twelve: the most efficient method for storing large binary objects, i.e. multimedia objects, is to place them in the file system and place a pointer in the DB.
Special cases
-----------------
Rewrite complex subqueries with temporary tables - Oracle created the global temporary table (GTT) and the SQL WITH operator to help divide-and-conquer complex SQL sub-queries (especially those with with WHERE clause subqueries, SELECT clause scalar subqueries and FROM clause in-line views). Tuning SQL with temporary tables (and materializations in the WITH clause) can result in amazing performance improvements.
Use minus instead of EXISTS subqueries - Some say that using the minus operator instead of NOT IN and NOT Exists will result in a faster execution plan.
Use SQL analytic functions - The Oracle analytic functions can do multiple aggregations (e.g. rollup by cube) with a single pass through the tables, making them very fast for reporting SQL.
Re-write NOT EXISTS and NOT EXISTS subqueries as outer joins - In many cases of NOT queries (but ONLY where a column is defined as NULL), you can re-write the uncorrelated subqueries into outer joins with IS NULL tests. Note that this is a non-correlated sub-query, but it could be re-written as an outer join.
select book_key from book
where
book_key NOT IN (select book_key from sales);
Below we combine the outer join with a NULL test in the WHERE clause without using a sub-query, giving a faster execution plan.
select b.book_key from book b, sales s
where
b.book_key = s.book_key(+)
and
s.book_key IS NULL;
Index your NULL values - If you have SQL that frequently tests for NULL, consider creating an index on NULL values. To get around the optimization of SQL queries that choose NULL column values (i.e. where emp_name IS NULL), we can create a function-based index using the null value built-in SQL function to index only on the NULL columns.
Leave column names alone - Never do a calculation on an indexed column unless you have a matching function-based index (a.k.a. FBI). Better yet, re-design the schema so that common where clause predicates do not need transformation with a BIF:
where salary*5 > :myvalue
where substr(ssn,7,4) = "1234"
where to_char(mydate,mon) = "january"
Avoid the use of NOT IN or HAVING. Instead, a NOT EXISTS subquery may run faster (when appropriate).
Avoid the LIKE predicate = Always replace a "like" with an equality, when appropriate.
Never mix data types - If a WHERE clause column predicate is numeric, do not to use quotes. For char index columns, always use quotes. There are mixed data type predicates:
where cust_nbr = "123"
where substr(ssn,7,4) = 1234
Use decode and case - Performing complex aggregations with the “decode” or "case" functions can minimize the number of times a table has to be selected.
Don't fear full-table scans - Not all OLTP queries are optimal when they uses indexes. If your query will return a large percentage of the table rows, a full-table scan may be faster than an index scan. This depends on many factors, including your configuration (values for db_file_multiblock_read_count, db_block_size), query parallelism and the number of table/index blocks in the buffer cache.
Use those aliases - Always use table aliases when referencing columns.
sorry for delay
SQL Constructs Ability to understand and implement Data Types, Basic
SQLSELECT Statements, Restricting and Sorting Data, Single-Row Functions,
Joins, Aggregate and Group Functions, Sub-Queries, Creating & Managing DB
Objects,Manipulating Data (DML), Constraints, Views SQL Query Tuning
No comments:
Post a Comment