The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

Hi,
Our customer has a strange problem with a very tiny table (it has only 100-200 rows):

CREATE TABLE test (
    id BIGINT PRIMARY KEY DEFAULT AUTOINCREMENT,
    uid BIGINT NULL
)

These statements do work immediately:

SELECT count() from test;
SELECT min(id) from test;
SELECT FIRST id FROM test ORDER BY id DESC;

This statement doesn't, i. e. runs "forever":

SELECT max(id) from test;

Execution plan for this statement:

( Plan [B]
  ( SingleRowGroupBy 
    ( RowLimit 
      ( IndexOnlyScan test test**(R) )
    )
  )
)

This happens only in one DB instance, other instances work as expected.
The table and its primary key were validated without errors.
Isolation_level is always 0, so locking problems should not be the reason.

Any thoughts, what else should we check or do to make it work?

Version - SA11.

Thanks.

asked 03 Jun '16, 08:33

Arthoor's gravatar image

Arthoor
1.1k264056
accept rate: 0%

edited 03 Jun '16, 08:38

I think I would run a VALIDATE INDEX to determine if the index is corrupted. See http://dcx.sap.com/index.html#1101/en/dbreference_en11/validate-statement.html*d5e50521

(03 Jun '16, 08:39) Mark Culp
Replies hidden
1

[gentle mockery]

What is it about "The table and its primary key were validated without errors" that doesn't do what VALIDATE INDEX might accomplish? :)

[/gentle mockery]

(03 Jun '16, 09:22) Breck Carter
1

Ah, missed that. My thought was (is) that the max operator will do a reverse lookup on the index (on 'id') to find the largest value and if the index was corrupted it could spin forever trying to find the entry. It could still be that the index is corrupted and the validation process is not detecting it.

As an experiment, it could be insightful to drop the index on 'id' and then recreate it, and see if that resolves the problem.

(03 Jun '16, 09:46) Mark Culp
1

That is, to run

ALTER TABLE test DROP PRIMARY KEY;
ALTER TABLE test ADD PRIMARY KEY (id);

which should re-create the index automatically, right?

(03 Jun '16, 09:50) Volker Barth
Comment Text Removed

Yabbut that might not be foreign-key-friendly...

...sheesh but I can be an a*****e sometimes :)

(03 Jun '16, 10:13) Breck Carter
1

... being a little Drew-ish this a.m. I C

But the suggestion is still a valid one. The might be an undetected corruption in the compressed index here.

There is also the possiblity of a compressed index issue addressed in 11.0.1#2566+

(03 Jun '16, 11:30) Nick Elson S...

The problem was solved by this experiment (i. e. primary key was dropped and recreated again). Thank you for the hint. So it turns out that we can't trust the validation.
P. s.: if this comment was an answer I could accept it.

(13 Jun '16, 01:34) Arthoor
showing 2 of 7 show all flat view
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×13
×6
×5

question asked: 03 Jun '16, 08:33

question was seen: 203 times

last updated: 13 Jun '16, 01:34