This discussion is archived
4 Replies Latest reply: Nov 12, 2012 7:17 AM by user10274093 RSS

SQL tuning

user10274093 Explorer
Currently Being Moderated
Hi,

on 10g R2,

in the following documentation :

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sql_1016.htm#i28528 , it is said :
When you need to use SQL functions on filters or join predicates, do not use them on the columns on which you want to have an index; rather, use them on the opposite side of the predicate, as in the following statement:



TO_CHAR(numcol) = varcol



rather than



varcol = TO_CHAR(numcol)
But when I apply :
SQL> select empno,ename from emp where ENAME = TO_CHAR('9797');



Execution Plan

----------------------------------------------------------

Plan hash value: 938911968



-----------------------------------------------------------------------------------------

| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |           |     1 |    10 |     1   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP       |     1 |    10 |     1   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | EMP_ENAME |     1 |       |     0   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------



Predicate Information (identified by operation id):

---------------------------------------------------



   2 - access("ENAME"='9797')



SQL> select empno,ename from emp where TO_CHAR(ENAME) = '9797';



Execution Plan

----------------------------------------------------------

Plan hash value: 3956160932



--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |    10 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    10 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------



Predicate Information (identified by operation id):

---------------------------------------------------



   1 - filter(INTERNAL_FUNCTION("ENAME")='9797')
Then I see that CPU charge is greater when I apply the recommendation (TO_CHAR(numcol) = varcol). Then what is the advantage of TO_CHAR(numcol) = varcol ?



May be for the execution time : 2 seconds in the second situation instead of 3 seconds in the first case ?



Thanks for your explanations and opinions.
  • 1. Re: SQL tuning
    737608 Newbie
    Currently Being Moderated
    Are you asking If performing a full scan of 1 row is more optimal than performing an index scan of that row?

    Think that the CBO will always try to find the better access plan for that query.

    Make the test on a big table (with statistics up-to-date) where a full scan is more heavy and you'll see the optimizer will choose an access plan with an index instead a full scan of the table.

    Cheers,

    egfh
  • 2. Re: SQL tuning
    613746 Newbie
    Currently Being Moderated
    The general issue is that applying a function to a column renders a "normal" index useless. Explained here: http://use-the-index-luke.com/sql/where-clause/functions/case-insensitive-search

    Your particular example, "numeric strings" is covered here: http://use-the-index-luke.com/sql/where-clause/obfuscation/numeric-strings

    There are, however, some cases when it is not possible to change the clause so that the function is done on "the other side", in that case you might need a function based index.
  • 3. Re: SQL tuning
    user10274093 Explorer
    Currently Being Moderated
    great,
    realy thanks.
  • 4. Re: SQL tuning
    user10274093 Explorer
    Currently Being Moderated
    Thanks.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points