Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

where condition with add_months

732311Mar 30 2010 — edited Mar 31 2010
Hi,
I have one table with name "table" with one normale index on timestamp column

I have this statment:
select * from table where 1=1
and timestamp >= (select add_months(trunc(sysdate,'MM'),-1) from dual)
--and timestamp >= add_months(trunc(sysdate,'MM'),-1)
in this case the execution is FAST
SQL> SELECT * FROM TABLE( dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1882259959

-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   | 27117 |  5825K|  2980   (1)| 00:00:36 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE             | 27117 |  5825K|  2978   (1)| 00:00:36 |
|*  2 |   INDEX RANGE SCAN          | TABLE_IDX         |  4881 |       |    37   (0)| 00:00:01 |
|   3 |    FAST DUAL                |                   |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("TIMESTAMP">= (SELECT /*+ */ ADD_MONTHS(TRUNC(SYSDATE@!,'fmmm'),-1) FROM
              "SYS"."DUAL" "DUAL"))

16 rows selected.
in this case I have an FULL exectuion
select * from table where 1=1
--and timestamp >= (select add_months(trunc(sysdate,'MM'),-1) from dual)
and timestamp >= add_months(trunc(sysdate,'MM'),-1)
which kind of INDEX can I do?

Many thanks to all
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 28 2010
Added on Mar 30 2010
4 comments
1,491 views