Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Find the next record in a sequence

Posted on 2010-01-04
15
Medium Priority
?
801 Views
Last Modified: 2013-12-18
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=generic_m_ai') > nlssort('aa','nls_sort=generic_m_ai')
      Or nlssort(Field1,'nls_sort=generic_m_ai') = nlssort('aa','nls_sort=generic_m_ai')
        And (nlssort(Field2,'nls_sort=generic_m_ai') > nlssort('bb','nls_sort=generic_m_ai')
          Or nlssort(Field2,'nls_sort=generic_m_ai') = nlssort('bb','nls_sort=generic_m_ai') And ID>1000)
    ORDER BY nlssort(Field1,'nls_sort=generic_m_ai'), nlssort(Field2,'nls_sort=generic_m_ai'), ID
    )
  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...
0
Comment
Question by:harfang
  • 7
  • 7
15 Comments
 
LVL 15

Accepted Solution

by:
Franck Pachot earned 1000 total points
ID: 26172430
Hi,
I think that your approach is the good one:
 - using NLSSORT function in you don't rely on the session NLS_SORT (and you have seen that changing NLS_SORT within the same query is a bad idea)
 - using a rownum=1 over an order by let oracle optimize its sorting.
Regards,
Franck.
0
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 26172531
I just tested to show you that it uses the index.
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.
0
 
LVL 58

Author Comment

by:harfang
ID: 26172641
Thanks.

I'll try to study the plan tomorrow (end of the day here and no Oracle at home).

(°v°)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Assisted Solution

by:Shaju Kumbalath
Shaju Kumbalath earned 1000 total points
ID: 26178307
HAVE U HEARD ABOUT LAG AND LEAD FUNCTIONS IN ORACLE?
0
 
LVL 58

Author Comment

by:harfang
ID: 26180637
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°)
0
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 26188018
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.
0
 
LVL 58

Author Comment

by:harfang
ID: 26188163
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°)
0
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 26189520
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.
0
 
LVL 58

Author Comment

by:harfang
ID: 26192344
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°)
/*
  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;

Open in new window

0
 
LVL 58

Author Closing Comment

by:harfang
ID: 31672450
Thanks for the advice and support.
0
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 26192692
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.
0
 
LVL 58

Author Comment

by:harfang
ID: 26192912
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°)
With db("NOMEN").OpenRecordset
    .Index = "ndxTaxoAlpha"
    .Seek ">", parent, epi, rang, id
    If .NoMatch Then Return = Null Else Return = !id_name
End With

Open in new window

0
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 26195405
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.
0
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 26195448
as nlssort returns a raw, you can concatenate them with utl_raw.concat
0
 
LVL 58

Author Comment

by:harfang
ID: 26196327
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°)
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question