Re: Dynamic Query

From: ddf <oratune_at_msn.com>
Date: Thu, 10 Sep 2009 13:03:21 -0700 (PDT)
Message-ID: <ce6720b6-ce51-425f-97a3-cd81c610e6ea_at_z24g2000yqb.googlegroups.com>



On Sep 10, 1:40 pm, The Magnet <a..._at_unsu.com> wrote:
> On Sep 10, 1:24 pm, "Gerard H. Pille" <g..._at_skynet.be> wrote:
>
> > The Magnet wrote:
>
> > > Hi,
>
> > > We have some applications that put together dynamic queries.  One
> > > thing I noticed is that all of the queries put an UPPER function at
> > > the end of it, assuming that the value passed will be alphanumeric.
> > > Although something like UPPER('123') makes no sense, it does it
> > > anyways.
>
> > > Is Oracle smart enough to know this and not execute the function
> > > call?  I ran a simple query and the plan was exactly the same......
>
> > How would not executing this function, modify the plan?
>
> Would it not speed it up, as it would not have to do the function call
> convert whatever to upper case?

It doesn't appear to change much of anything:

SQL>
SQL> select narmo, smeem, flaub
  2 from functest
  3 where narmo = upper('77');

     NARMO SMEEM FLAUB

---------- ------- --------------------
        77 Plompu0 Schneezo77ump

Elapsed: 00:00:00.06

Execution Plan



Plan hash value: 3861929469
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)|
Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |    55 |  1650 |  1376   (1)|
00:00:17 |
|* 1 | TABLE ACCESS FULL| FUNCTEST | 55 | 1650 | 1376 (1)| 00:00:17 |

Predicate Information (identified by operation id):


   1 - filter("NARMO"=77)

Note


  • dynamic sampling used for this statement

Statistics


        112  recursive calls
         16  db block gets
       7550  consistent gets
          0  physical reads
     173728  redo size
        540  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> select narmo, smeem, flaub
  2 from functest
  3 where narmo = 77399;

     NARMO SMEEM FLAUB

---------- ------- --------------------
     77399 Plompu0 Schneezo77399ump

Elapsed: 00:00:00.04

Execution Plan



Plan hash value: 3861929469
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)|
Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |    55 |  1650 |  1376   (1)|
00:00:17 |
|* 1 | TABLE ACCESS FULL| FUNCTEST | 55 | 1650 | 1376 (1)| 00:00:17 |

Predicate Information (identified by operation id):


   1 - filter("NARMO"=77399)

Note


  • dynamic sampling used for this statement

Statistics


         27  recursive calls
         15  db block gets
       5068  consistent gets
          0  physical reads
        896  redo size
        545  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> create index functest_idx
  2 on functest(narmo);

Index created.

Elapsed: 00:00:01.46
SQL>
SQL> select narmo, smeem, flaub
  2 from functest
  3 where narmo = upper('77');

     NARMO SMEEM FLAUB

---------- ------- --------------------
        77 Plompu0 Schneezo77ump

Elapsed: 00:00:00.03

Execution Plan



Plan hash value: 1636333159
| Id  | Operation                   | Name         | Rows  | Bytes |
Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    30
|     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| FUNCTEST     |     1 |    30
|     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | FUNCTEST_IDX |     1 |
|     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):


   2 - access("NARMO"=77)

Note


  • dynamic sampling used for this statement

Statistics


         32  recursive calls
         15  db block gets
         94  consistent gets
          2  physical reads
        896  redo size
        540  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> select narmo, smeem, flaub
  2 from functest
  3 where narmo = 77399;

     NARMO SMEEM FLAUB

---------- ------- --------------------
     77399 Plompu0 Schneezo77399ump

Elapsed: 00:00:00.03

Execution Plan



Plan hash value: 1636333159
| Id  | Operation                   | Name         | Rows  | Bytes |
Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    30
|     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| FUNCTEST     |     1 |    30
|     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | FUNCTEST_IDX |     1 |
|     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):


   2 - access("NARMO"=77399)

Note


  • dynamic sampling used for this statement

Statistics


         30  recursive calls
         15  db block gets
         94  consistent gets
          1  physical reads
        848  redo size
        545  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> It's a function call on a string literal; won't affect index access and it's called once in the query, not once for each row.

David Fitzjarrell Received on Thu Sep 10 2009 - 15:03:21 CDT

Original text of this message