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

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 application is querying against a large number of 2-d step functions (piecewise constant functions where a step is a top-right quarter-plane) and each "slice" is a sparse representation a function. We need to know function values (across a large number of these) for given points in the plane.

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).

> Thanks for posting the SQL to generate the table. I used it to create
> a test case with 4,000,000 rows:

[useful SQL snipped]

[query returning ranked rows snipped]

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.

> 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. Received on Thu Nov 30 2006 - 11:06:51 CST