SQL & PL/SQL

Hi, everyone,

My goal is as follows: I have a table with four main columns (Type, Name, Id1, Id2), where Id1 and Id2 are the primary key. My goal is to implement a partial fetch on this table, when the idea is to bring all the items starting from a given record (where the first row may be given by Id1, Id2, I will refer to this record as X). Column Type must be equal to 1 (Type = 1) in all appearing rows, and the rows must be ordered by NLS_UPPER(Name) (where the Name is not unique). Furthermore, any two rows with the same NLS_UPPER(Name) should appear in the same order regardless of X. The table contains ~1M records.

In order to improve the performance I have declared the following index: <Type, NLS_UPPER(Name), Id1, Id2 > . Now, the query I try to run is as follows:

Select Top 100
From T
Where Type = 1
And [NLS_UPPER(Name), Id1, Id2] > [NLS_UPPER(:V_Name), :V_Id1, :V_Id2].
Order by NLS_UPPER(Name), Id1, Id2

My problem with this is the fact that once I use the row before the last I cannot force Oracle to use the index in effective way (well, actually it uses a Index Range scan for Type = 1 which is ~800K records, but it does not use first rows scan). The only way I know how to write it (even assuming the Ids and NLS_UPPER(Name) are never null) is as follows

NLS_UPPER(Name) > NLS_UPPER(:V_Name) OR
NLS_UPPER(Name) = NLS_UPPER(:V_Name) AND
(ID1 > :V_ID1 OR
ID1 = :V_ID1 AND
ID2 > :V_ID2)))

But this query section is too complex to allow the Oracle to use the index no matter which hint I set.

I realize that the problem is syntactic only. In operational aspect it should merely go to the index and get all the items from the given row downwards. The problem is I don't know how to say this to the Oracle.

All analytical functions I saw don't accept parameters that represent values in the searched table.

Thank you in advance for any help.