Sybase merge_join

Posted on 2011-04-26
Last Modified: 2012-05-11
At one point a stored procedure was working correctly and then we decided to insert rows into a table to do further testing. Not sure what got messed up after the insertion but after that sybase started giving an error message-

Fatal Error 644 occurred at Apr 25 2011  2:05PM.  Please note the error and time, and contact a user with System Administrator (SA) authorization.

00:00000:00074:2011/04/26 14:05:39.42 server  Index row entry for data row id (23693, 6) is missing from index page 41953 of index id 2 of table 'employee_table' in database 'sybasedb_user'. Xactid is (51051,11). Drop and re-create the index.

We tried dropping the table and recreating all indexes, but the problem remained.

After doing quite some analysis, we found that the query plan being used for one of the sql in the stored procedure was using a merge_join. There is supposedly a bug in sybase ver15.0 that causes the above mentioned error. We had to include a statement "set merge_join off" in the stored procedure to get rid of this error and the stored procedure started working fine again.

My question is, why did the sp start to fail only after the inserts were done to the table and not before - when it was working just fine - and when we did not even need the statement "set merge_join off" . What during the simple insert statement would have caused Sybase to give the fatal errror.

Question by:4Learning
    LVL 13

    Expert Comment

    Acoording to Sybase manuals:

    "Error 644


    Message text

    Index row entry for data row id (%ld, %d) is missing from index page %ld of index id %d of table '%S_OBJID' in database '%S_DBID'. Xactid  is (% ld,%d). Drop and re-create the index.

    NoteThis error may be caused by a hardware problem.

    This error occurs when the nonclustered index indicated by “index id” is corrupt. This corruption is detected when a process tries to delete a nonexistent row."

    I don't think that the actual error has anything to do with merge_join. It might be just a coincidence.

    What is exact version of 15.0? Maybe it is a bug in an earlier version and you need to apply a recent maintenance release.

    Or it might be a defect on disk. Have you checked operating system logs for hardware problems?

    Author Comment

    Regarding nonclustered index, we recreated the table and indexes, but despite that still get the error

    Again, just by adding the merge_join statement, the sp works, but without, it keeps giving the fatal error about the same index - even though the table and the index is brand new.

    The version is 15.0.3. We asked the DBAs to do a dbcc check on the table, but it seems that if the problem is fixed in whatever way, then further analysis just does not take place :-(

    Also this only happened on the table after we did the insert. The exact same table on another server with the same sp  is giving absolutely no problems. Did the insert by any chance mess any indexes up?

    And the OS logs are not accessible as only the dbas have access to it.

    Any other thoughts/ideas/suggestions/clues?
    LVL 13

    Accepted Solution

    If there is a defect on your disk (it may be hardware defect as well), you might be hitting that page again when you recreate the index.

    By adding the merge_join statement, you avoid using that index. But it doesn't mean that it is not corrupt any more. You can check it by scanning that table through that index (you can force query optimizer to do that).

    I think you should convince your DBA to run dbcc, running dbcc for one index doesn't take long.

    If you insert more data on the other server, most probably you will not experience any problem. It might be a good way to test whether it is due to a defect on that particular server.

    Author Closing Comment

    I tried using another column in the where clause, so that the index would be bypassed and guess what, the query even without the merge_join now works. And no more fatal error!

    And I think you are right about the statement you made...
    "If there is a defect on your disk (it may be hardware defect as well), you might be hitting that page again when you recreate the index......"

    I will investigate it further .

    thankyou alpmoon for suggesting an option that even though I did not try it exactly, but something else close to it, it works.

    And yes, I will certainly now follow up with the dba who are very certain that there is no hardware problem ;-)


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Not sure what the best email signature size is? Are you worried about email signature image size? Follow this best practice guide.
    Digital marketing agencies have encountered both the opportunities and difficulties that emerge from working with a wide-ranging organizations.
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now