In the most of cases in practice the tables have many records, maybe up to hundreds of millions. In these conditions performance criteria are required: you have to create such optimized queries that return the results as quickly as possible. In the best (ideal) case, a query should return the results in the same amount of time whatever the number of records. For this reason, every time you try to write a query, you have to try to optimize it.
Next, I will give you some tips and tricks for this purpose. Avoid using functions on the columns of a table Many queries use a standard syntax, as shown below:
SELECT * FROM TABLE WHERE TO_CHAR(TO_DATE(EFFDT, 'DD/MM/YYYY'), 'DD/MM/YYYY') = '01/01/2007'
In most cases, ORACLE will use the function TO_CHAR(TO_DATE(effd, ‘DD/MM/YYYY’), ‘DD/MM/YYYY’) as a predicate to get the data from the table. The problem of this type of sentence is that ORACLE doesn’t know the result of the function TO_CHAR(TO_DATE(effd, ‘DD/MM/YYYY’), ‘DD/MM/YYYY’) before executing it. So, it’s very possible to generate a FULL TABLE SCAN with this type of syntax and to dramatically decrease the performances. Moreover, we can have this result even if the column EFFDT is an indexed column. Generally, as a consequence, if you apply a function to the indexed column into a table this will invalidate the power of the index. In this case the solution is to modify the query and to rewrite it to take advantage of the index of EFFDT:
SELECT FROM TABLE WHERE EFFDT = TO_DATE('01/01/2007', 'DD/MM/YYYY')
As a general rule, we have to avoid as much as possible applying the functions on the table columns – e.g. to test equality between columns and parameters, in our case ‘01/01/2007′ could be a parameter given by the program. If there are situations when this can’t be done, it is better to create an index on the result of the function therefore allowing ORACLE to use the power of the indexes.
No Comments