Re: optimizer behaviour changed

I'll take a guess at the answer.

Your select statement selects only a single field. That field has an index on it. That will make simply reading that index a very attractive proposal as the server will not have to look at any data pages at all.

Given you have also done an order by which is not in the select list - the server must build an index to reorder the data which he is reading directly from the index.

How big is the table ?

If an update statistics will not solve the problem - then I would try out the query again adding the order by field to the select list

select kateginhalt, katkey from sort_tab order by kateginhalt

- just to see whether the optimizer then plumps for your original index because you have made the other index less attractive.

If so - then you are probably caught in the space between the optimizer rules about reading directly from indexes when only keys are being selected and the statistics you have for your table.

Personally - I would add the hint as there is probably not much you can do about it if the above holds true.

I make that about 2 cents worth.

Dualta.


Jürgen Bajdala wrote:
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.)

.

Relevant Pages

  • optimizer behaviour changed
    ... QUERY PLAN FOR STATEMENT 1. ... Using I/O Size 2 Kbytes for index leaf pages. ... With LRU Buffer Replacement Strategy for index leaf pages. ...
    (comp.databases.sybase)
  • Re: Another big delay...continue
    ... this the query plan for the second query: ... Using I/O Size 16 Kbytes for index leaf pages. ... With LRU Buffer Replacement Strategy for index leaf pages. ...
    (comp.databases.sybase)
  • Re: Programming in standard c
    ... may grow larger between the query and the reading. ... That wider programming environment can provide guarantees ... and learned my craft in the days when memory was ...
    (comp.lang.c)
  • Re: Continuous Form and Query
    ... That makes more sense than the way I was reading it. ... I set my query up to look like this in sql: ... Barry Gilbert wrote: ... "Wes H." ...
    (microsoft.public.access.forms)
  • Re: What happens when data in control is updated
    ... and been reading about 'recordsets'.... ... predecessor query which used yet another query (the one i coined ... could assign/pass it in to the query when i wrote it as expressed in my SQL ... >> control whilst the user is contuing to use/view the information on ...
    (microsoft.public.access.formscoding)