Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Mar 2006
    Posts
    53

    Unanswered: What is the default record order?

    Stupid question-First, I should know the answer & second any app that depends on records being shown in a specific order should fetch them using an ORDER BY clause-but this one doesn't.

    It appears that the users decided to 'expand' a field by entering additional values for that field on the next record shown in the application-even if the rest of the record was blank. It's worked for them for years because this app always shows the records in the same sequence.

    Now, I need to know what that sequence is. There's nothing explicitly controlling it, but the fact that it always shows the records in the same order indicates that there really *is* a sequence-I just don't know what it is.

    Thanks.

  2. #2
    Join Date
    Mar 2006
    Posts
    53
    I found a FAQ that said there is no row order in an RDBMS. If this is true then why do the records come out in the same order over numerous repetitions of the same query?

    If I add a sequencing column then I need to add values to that column that will reproduce the current sequence. Can I simply select the entire table & set the value of the sequence column to rownum? But is the order of the rows when the entire table is selected the same as the order when a subset is selected?

    I've checked & it *appears* to be but I'm not sure. The problem is that the rows for the subset are scattered when the entire table is selected. Those I've checked (about a dozen) are correct when the non-selected rows are ignored so I'm thinking that maybe this will work.

    Suggestions?

  3. #3
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    The order you get without an ORDER BY is the order that the database happens to get the rows back from physical storage, and that will depend on the execution plan.

    If the optimizer chooses a full table scan, it may start at the first block and work through to the last (although if parallel query is involved that too could change if for example PQ slave #2 happened to return before PQ slave #1). This may appear to be the order in which the records were created but it may not be, if sufficient rows were deleted in one old block for new rows to have been stored there. If the optimizer chooses an index, rows may be accessed in the order their keys appear in the index.

  4. #4
    Join Date
    Mar 2006
    Posts
    53
    So it sounds like my first step should be to check the execution plan of the query. If it uses an index then I'm home free. I suspect that it does since the full table scan doesn't sound consistent enough to match what the users are seeing.

    Thanks.

  5. #5
    Join Date
    Mar 2006
    Posts
    53

    How to reproduce 'default order'

    Thanks to WilliamR I found what was producing the default row order in the users' app but now I need to reproduce that order in another app & I can't! The difference seems to be that the users' app selects one set of records at a time while mine needs to select the entire table.

    Using Explain Plan, the query from the users' app reports TABLE ACCESS BY INDEX ROW, INDEX RANGE SCAN. The query from my app reports TABLE ACCESS BY INDEX ROW, INDEX FULL SCAN. The two outputs are close, but not exact.

    Any suggestion on how I can force my query to do an INDEX RANGE SCAN? This is turning out to be hard enough that once I get it I'm thinking of using it to add a sequence column to the table so I can get the same seqence (when I want to) no matter how I query it. Of course that might muck up the users-but I don't think so. From the way they've mucked up the data trying to make the program work the way they want it to I think they might like the change.

    Thanks.

  6. #6
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    When repeating the same query the same execution plan is quite likely to be used and the rows will quite likely come back in the same order each time. It doesn't tend to change around unless the data is changed significantly by inserts, updates or deletes and the table is re-analyzed (or you rebuild an index, move a table, change database settings, start/stop using parallel query, upgrade the database etc...)

  7. #7
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    You can use workarounds such as WilliamR proposed but IMHO it's very luck-dependent. One day the execution plan will change and as WilliamR pointed out, the order in which data are returned will have changed.

    If you really want a secure way to have results ordered, then you should definitely use an ORDER BY clause. Just find the right one .

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by calvin-c
    Any suggestion on how I can force my query to do an INDEX RANGE SCAN? This is turning out to be hard enough that once I get it I'm thinking of using it to add a sequence column to the table so I can get the same seqence (when I want to) no matter how I query it.
    If the INDEX RANGE SCAN returns records in the correct order all you need to do is add "ORDER BY <the columns of that index>" to your query; that'll guarantee that the results will always come out in the right order, even if the execution plan changes.

  9. #9
    Join Date
    Mar 2006
    Posts
    53
    The problem is that it doesn't. Not 100%. I'd like to add an Order By clause to the query in the user's app-and I could do that if I could find a query that totally reproduces the order. I agree that sometime the order will change-and I've now told the users that. I don't mind being the bearer of bad news because we really don't shoot the messenger around here-but if I'm the one who caused the order to change, well, that's a different mess-and one that I don't want to get into.

    Anyway, right now the problem is finding the query. The basic difference (at the query level) is that the user's app is selecting a few records while I need to select the entire table. Further detail might help-this is the collateral tracking section of a loan servicing application. The user sees the collateral for a specific loan, i.e. "Select collateral ... where coll.loan_id=Parameter:" No Order By clause.

    This query access the table by index using an Index Range Scan. If I add an Order By clause it uses the same access, but adds Sort Order By before the table access and the return order is different.

    So maybe my question was too specific-maybe I don't really need to know how to force the Index Range Scan, but I do need to know how to return the rows, from the entire table, in the same order as they are being returned to the user for the individual loans. And I need (or would like) to do so before a user does something that causes that order to change. Fortunately this isn't a very dynamic table-mostly lookups-so I probably have a couple of months to figure it out, but who knows? It could change at any time.

    I'm not likely to have trouble when it does change, as long as I don't cause it to change, but the users won't be happy & part of my job is to keep them happy. They're now aware of the possibility but they're counting on me to find a way to fix the order before it changes. Whether or not they will hold it against me if I can't depends on how long it goes before it changes. Given enough time I should be able to find a way to reproduce that order, but I just don't know how much time I have so I'm looking for help.

    Thanks.

  10. #10
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    This query access the table by index using an Index Range Scan
    You could try to force the select by that index:
    Code:
    SELECT * FROM Coll_Table WHERE Index_Col > CHR(0);

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  11. #11
    Join Date
    Mar 2006
    Posts
    53
    Doesn't seem to do it. I'm using SQL*Plus to test the queries so chr(0) is invalid-I substituted Is Not Null. The actual table also uses a compound index, plus I need to limit the return for testing, so the actual test was

    select * from coll_table
    where col1='x'
    and col2='y'
    and col3 is not null

    The result is the same whether I include col3 or not-and differs from the result when col3 limits the result to a specific value, e.g. col3='z'.

    If anyone else has any ideas I'd like to hear them. Right now I'm trying to work out whether or not I can set up a query (nested set of queries) that returns the entire table, one loan at a time. Something like:

    Select * from coll_table
    where col3 is in (select unique col3 from coll_table)

    I dunno if that'll work or not. Maybe I'll need to write a program to issue the select recursively, adding the returned rows to a text file, then dropping & reloading the table from the text file with a sequence column. I'm pretty sure that that'd work but it seems a lot of work for something which seems like it should be fairly simple-but since it isn't maybe that's the best way to do it.

    Thanks.

  12. #12
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Is it possible to define the desired order in business terms? Perhaps the requirement is actually for loan_id then record entry date, and (wild guess) the application does not capture record entry date? If so, could you add a column and set it to default to SYSTIMESTAMP or similar?
    Last edited by WilliamR; 03-10-06 at 19:05.

  13. #13
    Join Date
    Mar 2006
    Posts
    53
    Yes, and no. The index already includes the record entry date-but, as noted, the records are not being displayed in index sequence. If I force them into index sequence then I give the users the task of re-establishing the relationships between records.

    It would have been great if the users had let me know about the problem way back when they first encountered it, but they didn't. They developed their own workaround that 'relates' records by the sequence in which they're listed in the app. Now I don't dare change that sequence-all I can do is come up with a way to lock it. If I can.

    Thanks.

  14. #14
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    If you are doing a range scan on a specific index, then the data is being returned using that index. What columns in what order are being referenced by the specific index. Use that as your order.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  15. #15
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Quote Originally Posted by calvin-c
    Yes, and no. The index already includes the record entry date - but, as noted, the records are not being displayed in index sequence.
    Never mind the index for now. Are you saying that no ORDER BY clause can order the rows in the way that the users require? Do the rows just come in some opaque yet consistent order known only to the database? In which case, could you add a column and programmatically set it to that order?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •