Dynamic Query
The Magnet
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......
Gerard H. Pille
How would not executing this function, modify the plan?
The Magnet
Would it not speed it up, as it would not have to do the function call
convert whatever to upper case?
Gerard H. Pille
It would save executing that function only once. I will need very precise measuring techniques
to notice the differenc.
ddf
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