Re: Dynamic Query
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 - 22:03:21 CEST