Re: SQL frustration - cannot make "obvious" query work well

giuliano.procida_at_googlemail.com wrote:
> Charles Hooper wrote:
>
> > I understand a bit what you are trying to accomplish. Maybe an index
> > on:
> > SLICE, X DESC, Y DESC would help?
> The primary key is slice, x, y and indexing slice asc or slice desc
> should not make that much difference. I'd hoped that Oracle could do a
> single skipping index scan to find the values wanted, but, alas, no
> direct solution to a).

I was making an educated guess that an index on SLICE, X DESC, Y DESC would help reduce the need for intermediate sorts of the columns. Jonathan Lewis's "Cost-Based Oracle Fundamentals" essentially states that Oracle will automatically read an index backwards if necessary, but I don't know if that applies to multi-column indexes where two of the three columns need to be read in descending order for the sort. An explain plan would likely tell you if my educated guess is completely wrong.

> I clearly had got my analytics RANK/ROW_NUMBER query wrong, because
> yours works beautifully with the magic "window sort pushed rank" (new
> to me - looks like its analogous to stopkey on normal queries) in the
> resulting query plan.

The stopkey is likely caused by the specification of ROWNUM = 1. The "window sort pushed rank" is likely caused by the RANK() OVER (PARTITION BY SLICE ORDER BY) clause.

> > If we can then eliminate all result rows, except those with
> > RESULT_ROW=1, we will get close to the answer that you are looking for:
> > SELECT
> > SLICE,
> > VALUE
> > FROM
> > (SELECT
> > SLICE,
> > X,
> > Y,
> > VALUE,
> > RANK() OVER (PARTITION BY SLICE ORDER BY X DESC, Y DESC) RESULT_ROW
> > FROM
> > POINTS
> > WHERE
> > SLICE<=100
> > AND X<=10000
> > AND Y<=1000)
> > WHERE
> > RESULT_ROW=1;
>
> This is just right, and the "slice <= 100" is going to be the bit we
> vary for each particular subset of values we need.
>
> Performance seems to be better than the other options. I will compare a
> bit more thoroughly on a quiet DB.
>
> Thanks again,
> Giuliano.

I clearly need to pull the BS in mathematics out of the toilet to understand your explanation (this is good). It looks like a very interesting project.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Nov 30 2006 - 12:42:22 CST