Ten Technical Tips for using Oracle SQL

Oracle Structured Query Language (SQL) has been at the forefront of technological advancement since its creation in the late 1970’s, and a staple in the handling and exchange of data and information across the globe. For this reason, we thought we would give our Ten Technical Tips for using Oracle SQL, either for the newcomer, or the seasoned veteran.

So, without further ado, here is a collection of different pointers which could be utilised as a guideline to write and review custom SQL queries. However, it should be taken into account that this list of Ten Technical Tips for using Oracle SQL is not necessarily a perfect how-to guide to achieve the best tuning results, but it can provide ample reference and guidance to sidestep any possible mistakes and setbacks while working with Oracle SQL:

1. If and when it is necessary to set up a composite index, you can attempt to use the “Leading” column in the “WHERE” clause. Now while Index skip scan is a possible option, it will undoubtedly incur extra costs in producing virtual indexes, and it may not be always possible depending on the cardinality of the leading columns.

2. It is highly recommended to not use the set operator UNION if the desired results can be produced through a UNION ALL. The UNION function requires an extra sort operation which is unnecessary.

3. You should only ever select those columns in a query which are mandatory. If any extra columns are included, that hold no data and are not actually used, end up incurring more I/O on the database and increase network traffic.

4. There must always be a level of consistency when formatting SQL statements (e.g. any and all keywords should be inputted in CAPS only) to aid readability. Bear in mind, this is not necessarily a tip to improve performance. However, it is important and worth the time in good practice.

5. Writing independent SQL statements for different tasks is a great technique to maintain efficiency, but if you are restricted to using only one SQL statement, then you can drastically simplify a statement by using the UNION ALL operator

6. It is never a good idea to manipulate any indexed columns with functions like TRUNC, RTRIM, UPPER, TO_CHAR as this will restrict the optimizer from properly identifying the index. If viable, you could implement the alteration on the constant side of the condition. If the indexed column is frequently accessed through a function (e.g NVL), it could be plausible to create a function based index.

7. It should be noted that when querying from a particular view all the tables are required to be properly accessed in order for the data to be returned. If it is the case that is not required, then do not use the view. Alternatively, you could use the base table(s), or if unavoidable, define a new view.

8. It cannot be understated that understanding the data functionally, and properly comprehend its diversity and volume in order so as to tune the query. Both Cardinality (skew) and Selectivity (predicate) factors have a large impact on the query plan. Therefore, use of Statistics and Histograms can drive the query towards a better plan.

9. Never forget that queries lean towards performing worse as they age, primarily because of volume increase, certain structural changes in the database and application, upgrades and so on. It is advisable to use Automatic Database Diagnostic Monitor (ADDM) and Automatic Workload Repository (AWR) to better recognise change in execution plan and throughput of top queries over a period of time.

10. Try your best to avoid using an ORDER BY on a large data, particularly if the response time is important.