SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases


Wednesday, May 03, 2006

optimizer behaviour changed

SQL Apprentice Question
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.)



Celko Answers
>> SELECT katkey FROM Sort_Tab ORDER BY kateginhalt; <<


This should not work at all. In Standard SQL, the columns in the ORDER
BY clause of the cursor must appear in the SELECT list. The optimizer
is probably expecting good SQL programming and gets confused by this.
Thus a temp table for the sort instead of seeing the index.

Also, including a description of the storage method ("_tab", whicnis
really silly in a language with only one data structure) or data type
or use in a particjlar location ("-key") is a violation of ISO-11179
rules. Name a data elemetn for what it is in the LOGICAL model.

No comments: