4 Replies Latest reply: Nov 12, 2012 9:17 AM by user10274093 RSS

    SQL tuning

    user10274093
      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
          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
            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
              great,
              realy thanks.
              • 4. Re: SQL tuning
                user10274093
                Thanks.