Re: An SQL Challenge...

<mike.jones_at_xenicom.com> wrote in message news:1119549664.535290.161560_at_g47g2000cwa.googlegroups.com...
> Nice. Very Nice.
>
> I must admit that I thought it was harder to order than that, but on
> closer inspection I am clearly wrong. I had tried to get my SQL that
> calculated the correct sequence to update the table via an updatable
> join view but Oracle wouldn't play ball (data manipulation not allowed
> on this view), no idea why I didn't consider a merge, I must have been
> having a senior moment.
>
> Well mucho thanks for a correct solution, not sure if it can be beat to
> any noticable improvement in terms of resource usage and elapsed time.
>

I would guess that you couldn't update from the view because the definition did not allow the primary key of the underlying table to be sufficiently visible, so the view did not have a 'primary key' which means the test_data table you were trying to update from it would not be key-preserved.

My solution updates every single row, even the ones with the correct sequence number. Depending on the data quality, you could improved performance by updating only the rows that needed it.

In 10g you could do this relatively cheaply by adding a condition to the ON clause:
 and test_data.sequence_no != ranked.new_seq and leaving the 'when not matched' with nothing to do.

In 9i you could include the test_data table in the current RANKED query to identify the rows where the sequence numbers did not
match. But this would probably not be worth doing unless a large fraction of the rows had the right sequence number already.

-- 
Regards

Jonathan Lewis

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

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated June 22nd 2005