troubleshooting Question

Oracle performance question - why does a query filter of "where DAYS_BETWEEN between -10 and 10" take so long?

Avatar of Wm Peck 1958
Wm Peck 1958Flag for United States of America asked on
Oracle Database
7 Comments2 Solutions153 ViewsLast Modified:
When I run a medium complex query with one inner join (and 2 unions), data returns in under 5s, 483 rows. One of the key pieces of info is "Days Between" (the end of one trip and the beginning of another). (The entire data set is small, as are the base tables)

But when I query the whole thing and say "where days_between_trips between -10 and 10", the query never returns !

The problem is a person taking four trips with a short break between trips, trying to get days_between each trip.

So this works great, 483 rows, neat and tidy

select  Trip #, Trip_Info, Next_Trip_Info, days_between from (
--
select Trip_#, Trip_1_Info, Trip_2_Info,
            date_2_start - date_1_end as days_between
    from table_1_vw a
              inner join table_2_vw b on a.user_id = b.user_id
--
union
--
select Trip_#, Trip_2_Info, Trip_3_Info,
            date_3_start - date_2_end as days_between
    from table_2_vw b
              inner join table_3_vw c on b.user_id = c.user_id
--
union
--
select Trip_#, Trip_3_Info, Trip_4_Info,
           date_4_start - date_3_end as days_between
    from table_3_vw c
              inner join table_4_vw d on d.user_id = c.user_id
)
--
where trip_year = [current_year]
   and  trip_status = 'ACTIVE
   ***
-------------

but when I add

*** --> "and days_between between -10 and 10", the query never returns.

I figured the data would be pulled into memory (483 rows) and then it's a simple matter to take this and filter out those rows not meeting the days_between criteria.

Why am I seeing this scenario ?

It's a enterprise system, everything seems good, etc.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros