Re: Working with huge tables of chronological data

"charely" <nospam_at_skynet.be> wrote in message news:461a24bd$0$13860$ba620e4c_at_news.skynet.be...
>
> This is basically the same query and execution as I suggested in an other
> part of this thread.
> One issue however , you will be missing all rows combinations in ta that
> are later than any
> event in tb , that is where "select min(tb.timed) from tb where tb.timed
> > ta1.timed"
> returns null. This can be remedied by using nvl and some arbitrary large
> date:
> nvl((select min(tb.timed) from tb where tb.timed > ta1.timed") , sysdate
> + 1)
> - assuming there is are no future events in ta.
>

Charley,

You are correct - I lost sight of that entire branch of the thread. It is your solution, minus the boundary condition that you had captured in your version of the code. (I leave the need for < or <= operators to the original poster to decide - the choice depends on his exact requirements).

I think the 'select max(timed) from ta' option in your original solution is actually more elegant than picking an arbitrary future date - it doesn't add materially to the resource usage as it will only execute once through a min/max range scan.

The answer to Charles Hooper's question in the follow-up to your original post: where did the bind variable come from ? It's how Oracle handles the correlated column from the outer table as it generates the plan for the correlated subquery.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html