1. Identify tables on which max no of columns in select statement, this table should be in main query where clause.
2. Identify
tables which do have fewer columns ie..1 or 2 columns in select
statement, move this in nested inline subquery in select statement as
coumns rather than put in main where clause, this will reduce joins in
main where clause & significantly improve query performance because
of subquery caching of results by database.
3. Try
to reduce your dataset ie..eliminate most of the rows in early steps of
query execution. Identify such tables which have least cardinality or
rows and put them as last table in FROM clause. In RBO driving table is
last table in from clause, in CBO it is derived by stats and data
dictionary but it can be overridden by index hints.
4. Again
try best to eliminate max rows in initial steps of execution, provide
best filters as predicates in ordered sequence in WHERE clause, again
you can use hints to force which predicate is applied first.
5. Always
try to use indexed column in joins, if column has very fewer type of
data which is repeating many times like gender-M/F , use bitmap index,
if from same table multiple columns are used in join , create composite
index rather than individual index.
6. Always
ensure stats are updated after heavy DML operations, this will
significantly improve your query performance, old stats makes index
unusable and causes full table scan even if index are present.
7. Always
try to use EXIST clause rather than IN clause, EXIST will only check
for indexes while IN will cause inline query to fetch real column data
to compare.
8. Avoid writing NVL, UPPER or any
SQL function on indexed column, in such cases indexes are ignored and
FTS occurs, rewrite in such way that columns are able to use its indexes
or if there is no way to avoid then use function based indexes.
9. Avoid
DISTINCT to group results, rather first try other means in where clause
to construct unique rows, distinct causes aggegration which can hit
query performance.
These are some points i am
able to recall although apart from this, there are many things to be
taken care while designing table for reports or transactional system
which significantly effects query performance, hope it helps somehow.
0 Comments