Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Compiste keys, large sets, interactive scrolling

Re: Compiste keys, large sets, interactive scrolling

From: Mike Burden <michael.burden_at_capgemini.co.uk>
Date: Wed, 27 Jan 1999 14:29:14 +0000
Message-ID: <36AF22B9.CE5524DA@capgemini.co.uk>


I have not found an answer to this either. I've needed to do the same thing myself several times. If anyone has an answer then I to would be very interested too. The closest I've managed is:

col1 > 'value' and col1 || col2 || col3 > 'val1' || 'val2' || 'val3'

where col1 is the first column in the composite key.

The optimizer uses the index for col1 if the cardinality is high enough and the second condition makes sure you start from the correct position. You may have to use hints to make sure Oracle uses the index to return the results.

I just don't understand how the DBMS vendors can omit something so useful. It's the sort of functionality that was easy using old index file types like IBM vsam. Why can't modern DBMS provide it?!

The syntax could be similar to the in clause e.g.

(col1,col2,col3) > ('va1',6,'xxx'). This would allow the use of mixed types and allow the optimizer to easily distinguish whether an index would be appropriate.

Bernard Dhooghe wrote:

> Suppose a table with columns:
>
> col1 col2 col3 col4 col5 col6 ...
>
> where these columns by the definition of the table, are not allowed
> to contain NULL.
>
> A composite key key_comp is made of number of these columns:
>
> key_comp = (col3,col4,col1,col2,col7)
>
> Suppose the cols are all character, the logical composed key would
> be the
> concatenation of the columns.
>
> Can Oracle server handle the queries (written in pseudo-sql):
>
> select * from table where key_comp > (?,?,?,?,?)
>
> or
>
> select * from table where key_comp <= (?,?,?,?,?)
>
> or
>
> select * from table where key_comp >= (?,?,?,?,?)
>
> or
>
> select * from table where key_comp < (?,?,?,?,?)
>
> where the result set is "big" f.ex. 3 million rows of a table with 6
> million rows and the user wants to scroll through the set (page by page,
> f.ex. 20 lines) and have an optimal answer time (that fills without
> delay the screen).
>
> How has the query to be written in Oracle to achieve it?
>
> Bernard Dhooghe
Received on Wed Jan 27 1999 - 08:29:14 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US