0

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?

1 Answer 1

0

It seems you didn't reference the /*+ FULL(t) */ hint alias while calling target table. You created the table with the same name as the alias. I'm not sure if that might be the problem... But the docs example follow this structure:

SELECT /*+ FULL(e) */ employee_id, last_name FROM employees e WHERE last_name LIKE :b1;

So, what happens if you instead try:

SELECT /*+ full(t) */ Count(1) n FROM t t WHERE start_time BETWEEN '2015-07-01 00:00:00' AND '2015-07-31 23:59:59'

?

1
  • The hint works well; the query does an awful index skip scan without it, due to bad statistics that I can't recompute yet. It's the seemingly-disconnected session without returning any rows that's puzzling me.
    – arielCo
    Aug 4, 2015 at 20:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.