1. SQL
Performance Tuning team recommends using COUNT(1) instead COUNT(*) for SQL
query performance optimization.
Do
not use:
SELECT
COUNT(*) FROM master;
Use:
SELECT
COUNT(1) FROM master;
2. If
you are using more than one table, make sure to use table aliases.
SELECT
COUNT(1) FROM master m, detail d WHERE m.id = d.master_id;
3. It is good practice to use table column
names in an SQL query. This way the SQL statements will be more readable, but
that is not the main reason. Example: If in
INSERTstatement you use SELECT * FROM x and
at some point you add a new column in table x, SQL will return an error. The
third reason why it is better to use table column names is to reduce network
traffic
4. In
WHERE statements make sure to compare string with string
and number with number, for optimal SQL query performance.
Do not use:
SELECT id, apn, charging_class FROM master WHERE
id = '4343';
Use:
SELECT id, apn, charging_class FROM master WHERE
id = 4343;
5. Avoid using complex expressions.
Avoid:
WHERE serial_id = NVL(:a1, serial_id)
WHERE NVL(serial_id,-1) = ( 1, etc...).
6. SQL
Performance Tuning recommends using CASE statements. It is more efficient to
run a single SQL statement, rather than two separate SQL statements.
Do not use:
SELECT COUNT (1) FROM emp WHERE salary <=
1000;
SELECT COUNT (1) FROM emp WHERE salary BETWEEN
1000 AND 2000;
Use:
SELECT COUNT (CASE WHEN salary <= 1000
THEN 1 ELSE null END)
count_1,
COUNT (CASE WHEN salary BETWEEN 1001 AND 2000
THEN 1 ELSE null END)
count_2
FROM
emp;
7. Use
UNION ALL instead of UNION, if possible
Do not use:
SELECT id, name FROM emp_bmw
UNION
SELECT id, name FROM emp_bmw_welt
Use:
SELECT id, name FROM emp_bmw
UNION ALL
SELECT id, name FROM emp_bmw_welt
8. SQL
Performance Tuning OR vs. IN.
Our tests showed that using IN in WHERE condition is a little faster then using OR.
Do not use:
SELECT * FROM CDRS_NR WHERE RECORD_TYPE = 'MTC'
OR RECORD_TYPE = 'MOC' OR RECORD_TYPE = 'SMSO';
Use:
SELECT * FROM CDRS_NR WHERE RECORD_TYPE IN
('MTC', 'MOC', 'SMSO')
9. SQL
Performance Tuning recommends to use minimal number of sub queries, if possible.
Do not use:
SELECT id, manufacturer, model
FROM
cars
WHERE
price = ( SELECT MAX(price)
FROM cars_bmw
)
AND
year = ( SELECT MAX(year)
FROM cars_bmw
)
Use:
SELECT id, manufacturer, model
FROM
cars
WHERE
(price, year) = ( SELECT MAX(price), MAX(year)
FROM cars_bmw
)
10. Using the indexes carefully
Having indexes on columns is
the most common method of enhancing performance, but having too many of them
may degrade the performance as well. So, it's very critical to decide wisely
about which all columns of a table we should create indexes on. Few common
guidelines are:- creating indexes on the columns which are frequently used
either in WHERE clause or to join tables, avoid creating indexes on columns
which are used only by functions or operators, avoid creating indexes on the
columns which are required
to changed quite frequently, etc.
11.
Using WHERE instead of HAVING
usage
of WHERE clause may take advantage of the index defined on the column(s) used
in the WHERE clause.
12. Indexed Scan vs Full
Table Scan
Indexed
scan is faster only if we are selecting only a few rows of a table otherwise
full table scan should be preferred. It's estimated that an indexed scan is
slower than a full table scan if the SQL statement is selecting more than 15%
of the rows of the table. So, in all such cases use the SQL hints to force full
table scan and suppress the use of pre-defined indexes. Okay... any guesses why full table scan is faster when a large
percentage of rows are accessed? Because an indexed scan causes multiple reads
per row accessed whereas a full table scan can read all rows contained in a
block in a single logical read operation.
13.
Using ORDER BY for an indexed scan
the
optimizer uses the indexed scan if the column specified in the ORDER BY clause
has an index defined on it. It'll use indexed scan even if the WHERE doesn't
contain that column (or even if the WHERE clause itself is missing). So,
analyze if you really want an indexed scan or a full table scan and if the
latter is preferred in a particular scenario then use 'FULL' SQL hint to force
the full table scan.
14. Using ROWID and ROWNUM wherever possible
These
special columns can be used to improve the performance of many SQL queries. The ROWID search is the fastest for Oracle database and
this luxury must be enjoyed wherever possible. ROWNUM comes really handy in the
cases where we want to limit the number of rows returned.
15. Optimizing the WHERE
clause
There
are many cases where index access path of a column of the WHERE clause is not
used even if the index on that column has already been created. Avoid such
cases to make best use of the indexes, which will ultimately improve the
performance. Some of these cases are: COLUMN_NAME IS NOT NULL (ROWID for a null
is not stored by an index), COLUMN_NAME NOT IN (value1, value2, value3, ...),
COLUMN_NAME != expression, COLUMN_NAME LIKE'%pattern' (whereas COLUMN_NAME LIKE
'pattern%' uses the index access path), etc. Usage of expressions or functions
on indexed columns will prevent the index access path to be used. So, use them
wisely!
16. Using Bind Variables,
Stored Procs, and Packages
Using
identical SQL statements (of course wherever applicable) will greatly improve
the performance as the parsing step will get eliminated in such cases. So, we
should use bind variables, stored procedures, and packages wherever possible to
re-use the same parsed SQL statements.
Full Table Scans can be good when:
Used on a small table (< 500 rows)
Used in a query returning more than a few percent
of the rows in a table
Used in a Sort-Merge or Hash join, but only
when these join methods are intended.
Full Table Scans are bad when:
Used on a large table where indexed or hash
cluster access is preferred.
Used on a medium-large table (> 500 rows)
as the outer table of a Nested Loop join.
Used on a medium-large table (> 500 rows)
in a Nested Sub-Query.
Used in a Sort-Merge or Hash join when a Nested Loop join is
preferred.
