Find the next record in a sequence
I am trying to implement navigation features in a botanical database.
The main table has a hierarchical structure, so a record has parent(s), children, and siblings. I need to create "next" and "previous" buttons in the interface (the platform is irrelevant at this point), for half a dozen logical and independent sort orders (taxonomical, photogenic, last modification, ID number, project key number, etc.) . The way I have done this in the past was to use a function returning the next record, and I'm trying to write these function in Oracle. But let's just look at the SQL to obtain the "next ID" in a sequence.
The next record *by ID* after record 1000 is for example:
SELECT Min(ID) FROM Table1 WHERE ID>1000;
SELECT ID FROM (SELECT ID FROM Table1 WHERE ID>1000 ORDER BY ID) WHERE ROWNUM=1;
That is the simple part: there are no duplicates and no ambiguity on what '>' means.
If text fields are involved, '>' means "later in alphabetical order", depending on the session's NLS_COMP setting, and ordering is determined by NLS_SORT. So, the "next" in an order over Field1, Field2, ID (current record having values 'aa', 'bb', 1000) becomes
SELECT ID FROM (
SELECT ID FROM Table1
WHERE Field1>'aa' Or Field1='aa' And (Field2>'bb' Or Field2='bb' And ID>1000)
ORDER BY Field1, Field2, ID
)
WHERE ROWNUM = 1
This only works when NLS_SORT and NLS_COMP are compatible, and useful only if they have a meaningful setting. In a previous question, I tried to use "alter session" to solve the issue, but that doesn't work in low-level functions. My current "best idea" looks like this:
SELECT ID FROM (
SELECT ID FROM Table1
WHERE nlssort(Field1,'nls_sort=g
Or nlssort(Field1,'nls_sort=g
And (nlssort(Field2,'nls_sort=
Or nlssort(Field2,'nls_sort=g
ORDER BY nlssort(Field1,'nls_sort=g
)
WHERE ROWNUM = 1
Naturally, there would be a combined calculated index on the ORDER BY expression.
My question:
* Did I miss something?
In theory, I should be able to ask the engine "what is the next entry in that index?". Ideally, I could point to record 1000 (the key field), and then navigate back and forth on any index defined on the table. Is that possible, and if not is there a better way to obtain "next" and "previous" information based on a given ordering of records?
Thanks
(°v°)
PS: in a last comment of the previous question, it appears that one can change the NLS_COMP setting from within a function, but in that case, the function has side-effects...
The main table has a hierarchical structure, so a record has parent(s), children, and siblings. I need to create "next" and "previous" buttons in the interface (the platform is irrelevant at this point), for half a dozen logical and independent sort orders (taxonomical, photogenic, last modification, ID number, project key number, etc.) . The way I have done this in the past was to use a function returning the next record, and I'm trying to write these function in Oracle. But let's just look at the SQL to obtain the "next ID" in a sequence.
The next record *by ID* after record 1000 is for example:
SELECT Min(ID) FROM Table1 WHERE ID>1000;
SELECT ID FROM (SELECT ID FROM Table1 WHERE ID>1000 ORDER BY ID) WHERE ROWNUM=1;
That is the simple part: there are no duplicates and no ambiguity on what '>' means.
If text fields are involved, '>' means "later in alphabetical order", depending on the session's NLS_COMP setting, and ordering is determined by NLS_SORT. So, the "next" in an order over Field1, Field2, ID (current record having values 'aa', 'bb', 1000) becomes
SELECT ID FROM (
SELECT ID FROM Table1
WHERE Field1>'aa' Or Field1='aa' And (Field2>'bb' Or Field2='bb' And ID>1000)
ORDER BY Field1, Field2, ID
)
WHERE ROWNUM = 1
This only works when NLS_SORT and NLS_COMP are compatible, and useful only if they have a meaningful setting. In a previous question, I tried to use "alter session" to solve the issue, but that doesn't work in low-level functions. My current "best idea" looks like this:
SELECT ID FROM (
SELECT ID FROM Table1
WHERE nlssort(Field1,'nls_sort=g
Or nlssort(Field1,'nls_sort=g
And (nlssort(Field2,'nls_sort=
Or nlssort(Field2,'nls_sort=g
ORDER BY nlssort(Field1,'nls_sort=g
)
WHERE ROWNUM = 1
Naturally, there would be a combined calculated index on the ORDER BY expression.
My question:
* Did I miss something?
In theory, I should be able to ask the engine "what is the next entry in that index?". Ideally, I could point to record 1000 (the key field), and then navigate back and forth on any index defined on the table. Is that possible, and if not is there a better way to obtain "next" and "previous" information based on a given ordering of records?
Thanks
(°v°)
PS: in a last comment of the previous question, it appears that one can change the NLS_COMP setting from within a function, but in that case, the function has side-effects...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks.
I'll try to study the plan tomorrow (end of the day here and no Oracle at home).
(°v°)
I'll try to study the plan tomorrow (end of the day here and no Oracle at home).
(°v°)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks shajukg,
No, I hadn't heard of them. At first, it seemed they could be what I was looking for, so I just spent an hour testing them. Sadly, they are worthless in my case:
In order for lag() and lead() to work, the desired records must be part of the current selection. In other words, I need to create a sub-query over 250'000 records, and then extract just one. When extracting "the first" from the sub-query, it takes several seconds (with two different calls to lag() and lead() over different orders). When extracting one random record (what I need), it takes over 5 minutes, with almost a million recursive calls, as many memory sorts and 3.5 million gets.
The functions are clearly meant to show simply "previous" and "next" records in the current view, not to extract information from outside of the view. Almost gadgets. But it was worth a try.
Cheers!
(°v°)
No, I hadn't heard of them. At first, it seemed they could be what I was looking for, so I just spent an hour testing them. Sadly, they are worthless in my case:
In order for lag() and lead() to work, the desired records must be part of the current selection. In other words, I need to create a sub-query over 250'000 records, and then extract just one. When extracting "the first" from the sub-query, it takes several seconds (with two different calls to lag() and lead() over different orders). When extracting one random record (what I need), it takes over 5 minutes, with almost a million recursive calls, as many memory sorts and 3.5 million gets.
The functions are clearly meant to show simply "previous" and "next" records in the current view, not to extract information from outside of the view. Almost gadgets. But it was worth a try.
Cheers!
(°v°)
Hi,
Maybe I didn't get what you need but here is how to use lag:
select * from (select lead(order_id) over (order by order_id) from orders where order_id>=100000) where rownum=1
However, I tested it with autotrace (on 10gR2) and execution plan is not optimal as
select * from (select order_id from orders where order_id>100000 order by order_id) where rownum=1;
Regards,
Franck.
Maybe I didn't get what you need but here is how to use lag:
select * from (select lead(order_id) over (order by order_id) from orders where order_id>=100000) where rownum=1
However, I tested it with autotrace (on 10gR2) and execution plan is not optimal as
select * from (select order_id from orders where order_id>100000 order by order_id) where rownum=1;
Regards,
Franck.
ASKER
Thanks Franck.
Your query is similar to what I tried.
select * from (select id_name, lead(id_name) over (<various tests here>) from NOMEN) where id_name = 123
With "where rownum = 1", it's not that bad (less than a minute) but still not usable. Apparently, the entire table NOMEN is parsed to calculate the lead() for each record. I need this instead:
select id_name, lead(id_name) over (<various tests here>) from NOMEN where id_name = 123
But lead() and lag() do not look outside of the current WHERE clause, which is why I called them gadgets -- cute but almost entirely useless. "So close and yet so far..."
(°v°)
Your query is similar to what I tried.
select * from (select id_name, lead(id_name) over (<various tests here>) from NOMEN) where id_name = 123
With "where rownum = 1", it's not that bad (less than a minute) but still not usable. Apparently, the entire table NOMEN is parsed to calculate the lead() for each record. I need this instead:
select id_name, lead(id_name) over (<various tests here>) from NOMEN where id_name = 123
But lead() and lag() do not look outside of the current WHERE clause, which is why I called them gadgets -- cute but almost entirely useless. "So close and yet so far..."
(°v°)
Hi suppose you will get best performance with:
select * from (select id_name from NOMEN where id_name>=123) where rownum=1
Index on id_name should be used, and only one index entry should be read, so that is less one second.
If you don't have the expected performance, then check the execution plan.
Regards,
Franck.
select * from (select id_name from NOMEN where id_name>=123) where rownum=1
Index on id_name should be used, and only one index entry should be read, so that is less one second.
If you don't have the expected performance, then check the execution plan.
Regards,
Franck.
ASKER
Thanks for the comments. Just for fun, here is my current version (for one of the sort orders), providing for "nulls first", case and accent insensitive, unambiguous, full-scope, ordering of botanical names (based on parent and épithète). Seems awfully complicated to get just the next in a sequence (it's three lines of code in Access).
Cheers!
(°v°)
Cheers!
(°v°)
/*
function NextSiblingAlpha( no ) -- local, variant of NextSibling()
returns the next sibling in taxonomic alphabetical order
*/
function NextSiblingAlpha( no in NOMEN.id_name%TYPE ) return NOMEN.id_name%TYPE
is
parent NOMEN.id_name%TYPE;
epi NOMEN.EPITHETE%TYPE;
rng NOMEN.NO_RANG%TYPE;
sibling NOMEN.id_name%TYPE;
begin
SELECT id_PARENT, EPITHETE, NO_RANG INTO parent, epi, rng
FROM NOMEN WHERE id_NAME = no;
if parent is Null then
SELECT id_NAME INTO sibling FROM (
SELECT id_NAME
FROM NOMEN
WHERE id_PARENT is Null
AND (nvl(nlssort(EPITHETE,'nls_sort=generic_m_ai'), '00') > nvl(nlssort(epi, 'nls_sort=generic_m_ai'), '00')
OR nvl(nlssort(EPITHETE,'nls_sort=generic_m_ai'), '00') = nvl(nlssort(epi, 'nls_sort=generic_m_ai'), '00')
AND (NO_RANG > rng
OR NO_RANG = rng
AND id_NAME > no))
ORDER BY nlssort(EPITHETE,'nls_sort=generic_m_ai') NULLS FIRST, NO_RANG, id_NAME
) C
WHERE ROWNUM = 1;
else
SELECT id_NAME INTO sibling FROM (
SELECT id_NAME
FROM NOMEN
WHERE id_PARENT = parent
AND (nvl(nlssort(EPITHETE,'nls_sort=generic_m_ai'), '00') > nvl(nlssort(epi, 'nls_sort=generic_m_ai'), '00')
OR nvl(nlssort(EPITHETE,'nls_sort=generic_m_ai'), '00') = nvl(nlssort(epi, 'nls_sort=generic_m_ai'), '00')
AND (NO_RANG > rng
OR NO_RANG = rng
AND id_NAME > no))
ORDER BY nlssort(EPITHETE,'nls_sort=generic_m_ai') NULLS FIRST, NO_RANG, id_NAME
) C
WHERE ROWNUM = 1;
end if;
return sibling;
exception when others then return null;
end NextSiblingAlpha;ASKER
Thanks for the advice and support.
Hi,
Your code can be more simple:
- you can avoid the if...then.else with:
(id_PARENT = parent or (id_PARENT is null and parent is null ) )
- you can avouid the = OR > predicates with >=
nlssort may seem complex, but it is a wonderful feature: you can sort text according to different languages, case or accent insenitive,...
Can you show those 3 lines of codes in Access ? I'll tell you if you can do the same with Oracle.
Your code can be more simple:
- you can avoid the if...then.else with:
(id_PARENT = parent or (id_PARENT is null and parent is null ) )
- you can avouid the = OR > predicates with >=
nlssort may seem complex, but it is a wonderful feature: you can sort text according to different languages, case or accent insenitive,...
Can you show those 3 lines of codes in Access ? I'll tell you if you can do the same with Oracle.
ASKER
The full story is at http:/A_1921.html and it's just a little more than three lines. The idea is:
* open the table
* select the desired index
* seek the next record based on a set of values
* use the found record
>> (id_PARENT = parent or (id_PARENT is null and parent is null ) )
I had tried that first. Horrible execution time. Again, this works perfectly in Access (the optimiser selects one or the other condition before running the query because 'parent' is constant), but Oracle performs a full table sweep... and I won't create yet another index for that case.
>> you can avoid the = OR > predicates with >=
No, that would break the logic. I want to express [a, b, c] > [d, e, f]. This means a > d (rest irrelevant), or a = d and b > e (c, f are irrelevant), or a = d and b = e and c > f. You cannot rewrite that using >=. Try it with time, e.g. 12:55:10 > 12:50:40 expressed on hours, minutes and seconds.
(°v°)
* open the table
* select the desired index
* seek the next record based on a set of values
* use the found record
>> (id_PARENT = parent or (id_PARENT is null and parent is null ) )
I had tried that first. Horrible execution time. Again, this works perfectly in Access (the optimiser selects one or the other condition before running the query because 'parent' is constant), but Oracle performs a full table sweep... and I won't create yet another index for that case.
>> you can avoid the = OR > predicates with >=
No, that would break the logic. I want to express [a, b, c] > [d, e, f]. This means a > d (rest irrelevant), or a = d and b > e (c, f are irrelevant), or a = d and b = e and c > f. You cannot rewrite that using >=. Try it with time, e.g. 12:55:10 > 12:50:40 expressed on hours, minutes and seconds.
(°v°)
With db("NOMEN").OpenRecordset
.Index = "ndxTaxoAlpha"
.Seek ">", parent, epi, rang, id
If .NoMatch Then Return = Null Else Return = !id_name
End With
Hi,
Ok I didnt't get the full logic.
Access code is maller because it relies on the index thah defines the order.
In a relational database, index is just there to improve performance, so you have to put the logic in the query. And you're right, as you have 3 columns, there no easy way the get the next rows, you need all those predicates.
There are no alternatives that comes in my mind and that can have good performance (just getting through the index to the first entry only). Except if you can concatenate the columns in order to compare only one value - and have a function base index on that.
Regards,
Franck.
Ok I didnt't get the full logic.
Access code is maller because it relies on the index thah defines the order.
In a relational database, index is just there to improve performance, so you have to put the logic in the query. And you're right, as you have 3 columns, there no easy way the get the next rows, you need all those predicates.
There are no alternatives that comes in my mind and that can have good performance (just getting through the index to the first entry only). Except if you can concatenate the columns in order to compare only one value - and have a function base index on that.
Regards,
Franck.
as nlssort returns a raw, you can concatenate them with utl_raw.concat
ASKER
Good to know. BTW, if I want to store the result of nlssort, what data type should I use. Binary(100) or something like that?
Tomorrow's questions should be about building a recordset through code and returning that to Access... I still have a lot to learn.
(°v°)
Tomorrow's questions should be about building a recordset through code and returning that to Access... I still have a lot to learn.
(°v°)
I've a table ORDERS with an index on ORDER_ID and 50 millions of rows:
SQL> select * from (select order_id from orders where order_id>100000) where rownum=1;
ORDER_ID
----------
100001
Execution Plan
--------------------------
Plan hash value: 1029567356
--------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | INDEX RANGE SCAN| ORDER_PK | 3 | 18 | 3 (0)| 00:00:01 |
--------------------------
Predicate Information (identified by operation id):
--------------------------
1 - filter(ROWNUM=1)
2 - access("ORDER_ID">100000)
Statistics
--------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
517 bytes sent via SQL*Net to client
491 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
you see how the index was used: range scan goes through the index depth for the value > '100000', gets the first entriy and then stops.
Execution read only 3 blocks (consistent gets): Index b-tree level is 2 so it reads 2 branch blocks and one leaf block only.
Just do the same with your query (set autotrace on in sqlplus) and check the execution plan and number of blocks read.
Regards,
Franck.