collapsing NULL values in Oracle query

Question

I often write queries wherein I pivot data and end up with NULL values that I want to collapse. E.g. data like the following:

id  time_in     time_out
1               2009-11-01
1   2009-10-30
2   2008-12-15
2               2009-02-03

I then do an outer query like so:

SELECT id,
       MIN(time_in) AS time_in,
       MIN(time_out) AS time_out
FROM (...query above...)
GROUP BY id

This would produce data like this:

id  time_in     time_out
1   2009-10-30  2009-11-01
2   2008-12-15  2009-02-03

The problem is that I use a limited interface to access the Oracle database, and queries using MIN and MAX often time out. I was wondering if there is a more efficient way of "collapsing" NULL values than what I have done. Sometimes I GROUP BY another field that is not an index, whereas id shown above is a primary key.

Answer 1

SELECT a.id,
       a.time_in,
       b.time_out
FROM 
(
  SELECT id, time_out
  FROM (...query above...)
  WHERE time_in is not null
) a,
(
  SELECT id, time_out
  FROM (...query above...)
  WHERE time_out is not null
) b
WHERE a.id = b.id;

Answer 2

If your query times out, the subquery probably returns many rows, which Oracle usually have to sort in order to group by. I suggest to inspect your subquery to see if you can avoid nulls there.

Answer 3

You'll need to examine the plan for your statement to see why it's timing out - unless your grouping can use an index to get the rows in a guaranteed order, the query must retrieve all the source rows before it can return any result.

Another option to consider (which may or may not help):

SELECT DISTINCT
       id,
       MAX(time_in) OVER (PARTITION BY id),
       MAX(time_out) OVER (PARTITION BY id)
FROM (...)