I have a query on a big table purposefully hinted with /*+ FULL() */
and uses partition pruning, and for a while the scans for each partition can be seen in V$SESSION_LONGOPS
. After a while no more long ops are listed, the corresponding V$SESSION
entry goes INACTIVE, and eventually disappears, but no rows have been returned.
SELECT /*+ full(t) */ Count(*) n
FROM t WHERE start_time BETWEEN '2015-07-01 00:00:00' AND '2015-07-31 23:59:59'
Plan hash value: 3256883686
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 14M (1)| 57:04:42 | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | |
| 2 | PARTITION RANGE ITERATOR| | 643 | 5144 | 14M (1)| 57:04:42 | 49 | 79 |
| 3 | PARTITION LIST ALL | | 643 | 5144 | 14M (1)| 57:04:42 | 1 | 3 |
|* 4 | TABLE ACCESS FULL | T | 643 | 5144 | 14M (1)| 57:04:42 | 145 | 237 |
-----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
4 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("START_TIME"<=TO_DATE(' 2015-07-31 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
What's going on?