Guidelines for creating search arguments
Follow these guidelines when you write search arguments for your queries:
- Avoid functions, arithmetic operations, and other expressions on the column side of search clauses. When possible, move functions and other operations to the expression side of the clause.
- Avoid incompatible datatypes for columns that will be joined and for variables and parameter used as search arguments.
- Use the leading column of a composite index as a search argument. The optimization of secondary keys provides less performance.
- Use all the search arguments you can to give the optimizer as much as possible to work with.
- If a query has more than 102 predicates for a table, put the most potentially useful clauses near the beginning of the query, since only the first 102 SARGs on each table are used during optimization. (All of the search conditions are used to qualify the rows.)
- Some queries using > (greater than) may perform better if you can rewrite them to use >= (greater than or equal to). For example, this query, with an index on int_col uses the index to find the first value where int_col equals 3, and then scans forward to find the first value that is greater than 3. If there are many rows where int_col equals 3, the server has to scan many pages to find the first row where int_col is greater than 3:
select * from table1 where int_col > 3
It is probably more efficient to write the query like this:
select * from table1 where int_col >= 4
This optimization is more difficult with character strings and floating point data. You need to know your data.
- Check show plan output to see which keys and indexes are used.
- If you expect an index is not being used when you expect it to be, checkd bcc traceon(302) output to see if the optimizer is considering the index.
If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.