 |
|
| Computers Forum Index » Computer - Databases - Oracle (Tools) » Dynamic Query... |
|
Page 1 of 1 |
|
| Author |
Message |
| The Magnet... |
 Posted: Thu Sep 10, 2009 5:25 pm |
|
|
|
Guest
|
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...... |
|
|
| Back to top |
|
|
|
| The Magnet... |
| Posted: Thu Sep 10, 2009 6:40 pm |
|
|
|
Guest
|
On Sep 10, 1:24 pm, "Gerard H. Pille" <g... at (no spam) skynet.be> wrote:
Quote: 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? |
|
|
| Back to top |
|
|
|
| ddf... |
| Posted: Thu Sep 10, 2009 8:03 pm |
|
|
|
Guest
|
On Sep 10, 1:40 pm, The Magnet <a... at (no spam) unsu.com> wrote:
Quote: On Sep 10, 1:24 pm, "Gerard H. Pille" <g... at (no spam) 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 |
|
|
| Back to top |
|
|
|
| Gerard H. Pille... |
| Posted: Thu Sep 10, 2009 10:24 pm |
|
|
|
Guest
|
The Magnet wrote:
Quote:
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? |
|
|
| Back to top |
|
|
|
| Gerard H. Pille... |
| Posted: Thu Sep 10, 2009 11:42 pm |
|
|
|
Guest
|
The Magnet wrote:
Quote: On Sep 10, 1:24 pm, "Gerard H. Pille"<g... at (no spam) 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 would save executing that function only once. I will need very precise measuring techniques
to notice the differenc. |
|
|
| Back to top |
|
|
|
|
|
All times are GMT
The time now is Thu Dec 10, 2009 12:44 pm
|
|