SEO

Hi,

we have the following table:

create table sort_tab (
katkey INTEGER NULL ,
kateginhalt VARCHAR (245) NULL ,
setnr SMALLINT NULL ,
normkatkey INTEGER NULL
)

(with a hidden SYB_IDENTITY_COL),
with the indexes:

create UNIQUE INDEX i_sort_katkey on sort_tab(katkey)
create INDEX i_sort_inhalt on sort_tab(kateginhalt)
create INDEX i_sort_norm on sort_tab(setnr,normkatkey)
create INDEX sort_tab_syb_id_col on sort_tab( SYB_IDENTITY_COL )

And we have the select statement:

select katkey from sort_tab order by kateginhalt

On a 11.9 server the index i_sort_inhalt is used (as we expected):

QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is SELECT.
FROM TABLE
sort_tab
Nested iteration.
Index : i_sort_inhalt
Forward scan.
Positioning at index start.
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

On a 12.5.2 server an temp. table is build:

STEP 1
The type of query is INSERT.
The update mode is direct.
Worktable1 created, in allpages locking mode, for ORDER BY.

FROM TABLE
sort_tab
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
TO TABLE
Worktable1.
STEP 2
The type of query is SELECT.
This step involves sorting.

FROM TABLE
Worktable1.
Using GETSORTED
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 2 Kbytes for data pages.
With MRU Buffer Replacement Strategy for data pages.

Does anybody know why the optimizer works in this way now?
Can I switch back to the original behaviour?
(Of course I can use an index hint for this special select,
but maybe I have trouble with other selects that I'm unaware
at the moment.)


Thanks
Jürgen