CODEWITHSUNDEEP
oracle-databaseplsqlgroup-byaggregate
Sarah Vessels
Sarah Vessels

Reputation: 31640

collapsing NULL values in Oracle query

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.

Upvotes: 1

Views: 1653

Answers (3)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

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 (...)

Upvotes: 1

Egor Rogov
Egor Rogov

Reputation: 5398

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.

Upvotes: 0

anon
anon

Reputation: 

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;

Upvotes: 1

Related Questions