Skip to Content
avatar image
Former Member

Wrong Results in simple max() Statement

Hello,

We use MaxDB 7.8.02.28

On our Datbase I used the following Query :

select max(ROID) FROM READOUT WHERE AMID = 101721 AND STATE IN ( 'K','M','C' )

and got the Result:

EXPRESSION1

2153087

This is wrong what I checked via :

select * FROM READOUT WHERE AMID = 101721 AND STATE IN ( 'K','M','C' )

ORDER BY ROID DESC

my quick solution was to use:

select max(ROID),max(ROWANN) FROM READOUT WHERE AMID = 101721 AND STATE IN ( 'K','M','C' )

with the result:

EXPRESSION1     EXPRESSION2

7235411               2013-11-22 09:02:38.0

And the Value 7235411 was the one I expected and is obviosly greater than 2153087

but what happens there, and how could we make shure that we dont have same effects in other ends of our Application

I guess it might be tha case if alle Values could be catched out if the index.

The Query Plan is:

SCHEMANAME     TABLENAME     COLUMN_OR_INDEX     STRATEGY                                   PAGECOUNT

MDR                    READOUT          RO_STATE                    RANGE CONDITION FOR INDEX;     14229

                                                                                          ONLY INDEX ACCESSED;

                                                                                          MIN/MAX OPTIMIZATION;

                                                                                          AMID;     (USED INDEX COLUMN);

                                                                                          STATE;     (USED INDEX COLUMN);

                             JDBC_CURSOR_218                              RESULT IS COPIED   , COSTVALUE IS;         3

With index :

create index "RO_STATE" on "MDR"."READOUT" ("AMID" ASC,"STATE" ASC)

//

Any Idea ?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • avatar image
    Former Member
    Dec 05, 2013 at 10:41 AM

    Hi,

    sounds like a problem with the strategy which is used

    Please switch off the following parameter EnableMinMaxAggregationOptimizer = NO

    Please send us the table and index definition as well.

    Regards, Christiane

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Thanks Christiane,

      That solves the Problem.

      nevertheless I provide the table and Index definition here.

      regards

      jph

      ---------

      create table "XYZ"."READOUT"(

        "ROID" INTEGER not null,

        "AMID" INTEGER,

        "ROWANN" TIMESTAMP,

        "WANN" TIMESTAMP,

        "WER" INTEGER,

        "STATE" CHAR (1) ASCII,

        "EVA_RONUM" INTEGER,

        "EVA_ROTERM" VARCHAR (20) ASCII,

        "EVA_PROTERM" VARCHAR (20) ASCII,

        "EVA_USR1" VARCHAR (12) ASCII,

        "EMPTY" INTEGER,

        "EVA_SYSTIME" VARCHAR (6) ASCII,

        "EVA_SYSDATE" VARCHAR (8) ASCII,

        "EVA_PROTIME" VARCHAR (6) ASCII,

        "EVA_PRODATE" VARCHAR (8) ASCII,

        "EVA_ROTIME" VARCHAR (6) ASCII,

        "EVA_RODATE" VARCHAR (8) ASCII,

        "EXPORTED" CHAR (1) ASCII,

        "EXPORT_TS" TIMESTAMP,

        "TSERNR" VARCHAR (16) ASCII,

        "MSERNR" VARCHAR (20) ASCII,

      constraint SYSPRIMARYKEY primary key ("ROID"),

        foreign key "FK_READ_REFE_AUTO" ("AMID") references "XYZ"."AUTOMAT" ("AMID") on delete restrict)

      sample 5 percent

      //

      create index "RO_AMID" on "XYZ"."READOUT" ("AMID" ASC)

      //

      create index "RO_AM_ROWANN_EMPTY" on "XYZ"."READOUT" ("AMID" ASC,"ROWANN" DESC,"EMPTY" ASC)

      //

      create index "RO_NURSTATE" on "XYZ"."READOUT" ("STATE" ASC)

      //

      create index "RO_ROWANN" on "XYZ"."READOUT" ("ROWANN" DESC)

      //

      create index "RO_STATE" on "XYZ"."READOUT" ("AMID" ASC,"STATE" ASC)

      //

      create index "RO_WANN" on "XYZ"."READOUT" ("WANN" ASC)

      //

  • avatar image
    Former Member
    Dec 09, 2013 at 01:06 PM

    OOPS,

    setting EnableMinMaxAggregationOptimizer = NO

    generates performance problems at other ends of our Application.

    I had to change back to YES !

    regards

    jph

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 18, 2013 at 03:44 PM

    Hello Jens-Peter,

    I got another idea - with explain we got the information which index is used for the index only strategie -> RO_STATE                  

    Would you please drop and recreate the index and check if you have still wrong results after the index has been dropped and newly created.

    Regards, Christiane

    Add comment
    10|10000 characters needed characters exceeded