We have recently migrated ASE from 12.5.3 to 15.0.3. ASE 15.0.3 generates bad query plan.
this is the part of the query plan generated by 15.0.3 :
SCAN Operator (VA = 4)
| | | FROM TABLE
| | | oe_order_d
| | | Index : oe_order_d_index1
| | | Forward Scan.
| | | Positioning by key.
| | | Keys are:
| | | hos_cd ASC
| | | 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.
but 12.5.3 picksup good index of the same table. here is the query plan generated by 12.5.3.
SCAN Operator (VA = 3)
| | | FROM TABLE
| | | oe_order_d
| | | Using Clustered Index.
| | | Index : pk_oe_order_d
| | | Forward Scan.
| | | Positioning by key.
| | | Keys are:
| | | hos_cd ASC
| | | order_num ASC
| | | 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.
I > sp_spaceused oe_order_d
2> go
name rowtotal reserved data index_size unused
---------- -------- ---------- ---------- ---------- ---------
oe_order_d 10085831 4886684 KB 3344876 KB 1088512 KB 453296 KB
1> sp_helpindex oe_order_d
2> go
Object has the following indexes
index_name index_keys index_description index_max_rows_per_page index_fillfactor index_reservepagegap index_created index_local
------------------ --------------------------
----------
------- ----------------- ----------------------- ---------------- -------------------- ------------------- ------------
oe_order_d_index1 hos_cd, chg_tp, create_ts nonclustered 0 0 0 Sep 9 2004 1:17AM Global Index
pk_oe_order_d hos_cd, order_num, seq_num clustered, unique 0 0 0 NULL Global Index
id_oe_order_d3 hos_cd, issue_dept, request_num, queue_num nonclustered 0 0 0 Sep 9 2004 1:21AM Global Index
NC_IND4_oe_order_d hos_cd, post_ts nonclustered 0 0 0 Jan 26 2007 11:32AM Global Index
I run update index statistics with scale 50 on this table but it still picks up wrong index. Is there any fix for this problem other than setting compatibility_mode to 1?
thanks
