Re: sql

"Vladimir M. Zakharychev" wrote:

> In my tests on 8.1.7.4,
>
> SELECT MIN(id) second_max FROM
> (SELECT id FROM
> (SELECT id FROM my_table ORDER BY id DESC)
> WHERE rownum <= 2
> )
>
> hit just two blocks of unique index on id (root and tail leaf, I guess)
> with INDEX FULL SCAN DESCENDING, then COUNT STOPKEY and
> SORT AGGREGATE on top of this.
>
> SELECT MAX(id) second_max
> FROM my_table
> WHERE id < (SELECT MAX(id) FROM my_table)
>
> visited four index blocks with INDEX RANGE SCAN (MIN/MAX),
> FIRST ROW and SORT AGGREGATE on top.
>
> SELECT id second_max FROM
> (SELECT id,
> rank() OVER (ORDER BY id DESC) rank
> FROM my_table
> )
> WHERE rank = 2
>
> was the worst because it hit all index blocks with INDEX FULL SCAN (in
> ascending sort order, while with the first query CBO was smart enough
> to choose reverse order to get to needed rows as fast as it could) and
> then used WINDOW SORT PUSHED RANK to get to the data. I think we
> could get results similar to those for the first query if CBO could choose
> descending index scan for the window, but for some reason it couldn't.
>
> --
> Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
> Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
> All opinions are mine and do not necessarily go in line with those of my employer.
>
> "Brian E Dick" <bdick_at_cox.net> wrote in message news:d63k9.23183$IL6.1312087_at_news2.east.cox.net...
> > What? You want performance, too? <g>
> >
> > I was a bit quick with the send button. How about
> >
> > select max(c) from t where c < (select max(c) from t)
> >
> > What effect would a descending index on c make? Can the database resolve the
> > outer max without totally enumerating all the values of c less than the
> > inner max? Does the explain plan prove or disprove this? Or do you have to
> > infer it from TKPROF IO counts?
> >
> > "Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message
> > news:3D908DB2.A32D71FA_at_exesolutions.com...
> > > Brian E Dick wrote:
> > >
> > > > select max(c) from (select c from t where c < (select max(c) from t))
> > > >
> > > > "abhishek" <singhhome_at_yahoo.com> wrote in message
> > > > news:e52380ab.0209232324.24bd2cbd_at_posting.google.com...
> > > > > how to write sql querry to
> > > > > 1. show the second maximum value of a column.
> > >
> > > Fascinating. I like it.
> > >
> > > Now to explain plan them all.
> > >
> > > Thanks everyone for the material. My students, in a few weeks, will thank
> > > you too. (I think)
> > >
> > > Daniel Morgan
> > >
> >
> >

Thanks. Another great example of why and how it is impossible to predict the outcome of a complex SQL statement without explain planning it.

Dan Morgan Received on Wed Sep 25 2002 - 11:10:04 CDT