select count() works, select max(id) doesn't

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.

aggregate-function max-value long-running-query

asked 03 Jun '16, 08:33

Arthoor
1.3k●35●52●66
accept rate: 11%

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

[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

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

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

... 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

community wiki:

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

learn more about Markdown

Question tags:

long-running-query ×16
max-value ×7
aggregate-function ×5

question asked: 03 Jun '16, 08:33

question was seen: 1,569 times

last updated: 13 Jun '16, 01:34

SAP SQL Anywhere

select count() works, select max(id) doesn't

Follow this question

Related questions