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......

The Magnet wrote:
How would not executing this function, modify the plan?

On Sep 10, 1:24*pm, "Gerard H. Pille" <g... (AT) skynet (DOT) be> wrote:
Would it not speed it up, as it would not have to do the function call
convert whatever to upper case?

The Magnet wrote:
It would save executing that function only once. I will need very precise measuring techniques
to notice the differenc.

On Sep 10, 1:40*pm, The Magnet <a... (AT) unsu (DOT) com> wrote:
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

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

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

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

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

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

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

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

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