Dears
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
I have a table TableT with the following indexes on it
Ind_tab_aladat_fi on trunc(a_date)
Ind_order_by_fi on (accepted asc, a_date desc, label desc)
And I want for my query (see below) to use the first index in order to satisfy the where clause and to use the second index to avoid doing sort order by. But I didn't succeed
SELECT
tab.*
FROM tableT tab
WHERE TRUNC (tab.a_date) = TO_DATE ('22092010', 'ddmmrrrr')
ORDER BY
accepted asc
,a_date desc
,label desc;
11295 rows selected.
Elapsed: 00:00:07.93
Execution Plan
----------------------------------------------------------
Plan hash value: 2259490324
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4318 | 581K| | 278 (7)| 00:00:01 |
| 1 | SORT ORDER BY | | 4318 | 581K| 1736K| 278 (7)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| tableT | 4318 | 581K| | 154 (6)| 00:00:0
|* 3 | INDEX RANGE SCAN | Ind_tab_aladat_fi | 4318 | | | 25 (8)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(TRUNC(INTERNAL_FUNCTION("a_date"))=TO_DATE('22092010','ddmmrrrr'))
SELECT /*+ index (tab Ind_order_by_fi) */
tab.*
FROM tableT tab
WHERE TRUNC (tab.a_date) = TO_DATE ('22092010', 'ddmmrrrr')
ORDER BY
accepted asc
,a_date desc
,label desc;
11295 rows selected.
Elapsed: 00:00:08.79
Execution Plan
----------------------------------------------------------
Plan hash value: 2070438661
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4318 | 581K| 9306 (18)| 00:00:04 |
| 1 | TABLE ACCESS BY INDEX ROWID| tableT | 4318 | 581K| 9306 (18)| 00:00:04 |
|* 2 | INDEX FULL SCAN | Ind_order_by_fi | 4318 | | 6426 (26)| 00:00:03 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TRUNC(SYS_OP_UNDESCEND(SYS_OP_DESCEND("a_date")))=TO_DATE('22092010','ddmmrrrr'))
SELECT /*+ index_combine(tab Ind_tab_aladat_fi Ind_order_by_fi) */
tab.*
FROM tableT tab
WHERE TRUNC (tab.alarm_date) = TO_DATE ('22092010', 'ddmmrrrr')
ORDER BY
accepted asc
,alarm_date desc
,train_label desc;
11295 rows selected.
Elapsed: 00:00:08.15
Execution Plan
----------------------------------------------------------
Plan hash value: 2259490324
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4318 | 581K| | 278 (7)| 00:00:01 |
| 1 | SORT ORDER BY | | 4318 | 581K| 1736K| 278 (7)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| tableT | 4318 | 581K| | 154 (6)| 00:00:0
|* 3 | INDEX RANGE SCAN | Ind_tab_aladat_fi | 4318 | | | 25 (8)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(TRUNC(INTERNAL_FUNCTION("A_DATE"))=TO_DATE('22092010','ddmmrrrr'))
Have you any idea?
Thanks
Edited by: Mohamed Houri on 7 oct. 2010 10:47