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