Hi,

One of our 'delete' queries using index on UAT server, But
not on Production server. Have run delete stats, update
index stats, reorg rebuild etc., still not using index
unless forcing the index.
What could be the reason ? ASE version is 15.7 ESD#2 on both
servers, Configuration also same.
Am I missing anything ? What should I do to let optimiser
use the index(by not forcing).

Query:
---------

DELETE table1 FROM table1 st1, table2 st2 WHERE st1.col1 =
st2.col2

showplan(UAT):
---------------

QUERY PLAN FOR STATEMENT 1 (at line 1).
Optimized using Serial Mode


STEP 1
The type of query is DELETE.

|ROOT:EMIT Operator (VA = 5)

|
| |DELETE Operator (VA = 4)
| | The update mode is direct.
| |
| | |MERGE JOIN Operator (Join Type: Inner Join)
(VA = 3)
| | | Using Worktable1 for internal storage.
| | | Key Count: 1
| | | Key Ordering: ASC
| | |
| | | |SCAN Operator (VA = 0)
| | | | FROM TABLE
| | | | table1
| | | | st1
| | | | Index : table1I1
| | | | Forward Scan.
| | | | Positioning at index start.
| | | | Using I/O Size 16 Kbytes for index
leaf pages.
| | | | With LRU Buffer Replacement Strategy
for index leaf pages.
| | | | Using I/O Size 16 Kbytes for data
pages.
| | | | With LRU Buffer Replacement Strategy
for data pages.
| | |
| | | |GROUP SORTED Operator (VA = 2)
| | | |Distinct
| | | |
| | | | |SCAN Operator (VA = 1)
| | | | | FROM TABLE
| | | | | table2
| | | | | st2
| | | | | Using Clustered Index.
| | | | | Index : table2I1
| | | | | Forward Scan.
| | | | | Positioning at index start.
| | | | | Index contains all needed columns.
Base table will not be read.
| | | | | Using I/O Size 16 Kbytes for index
leaf pages.
| | | | | With LRU Buffer Replacement
Strategy for index leaf pages.
| |
| | TO TABLE
| | table1
| | Using I/O Size 2 Kbytes for data pages.




Showplan (PROD):
------------------

QUERY PLAN FOR STATEMENT 1 (at line 1).
Optimized using Serial Mode


STEP 1
The type of query is DELETE.

5 operator(s) under root

|ROOT:EMIT Operator (VA = 5)
|
| |DELETE Operator (VA = 4)
| | The update mode is direct.
| |
| | |MERGE JOIN Operator (Join Type: Inner Join)
(VA = 3)
| | | Using Worktable1 for internal storage.
| | | Key Count: 1
| | | Key Ordering: ASC
| | |
| | | |SCAN Operator (VA = 0)
| | | | FROM TABLE
| | | | table1
| | | | st1
| | | | Index : table1I1
| | | | Forward Scan.
| | | | Positioning at index start.
| | | | Using I/O Size 16 Kbytes for index
leaf pages.
| | | | With LRU Buffer Replacement Strategy
for index leaf pages.
| | | | Using I/O Size 16 Kbytes for data
pages.
| | | | With LRU Buffer Replacement Strategy
for data pages.
| | |
| | | |GROUP SORTED Operator (VA = 2)
| | | |Distinct
| | | |
| | | | |SCAN Operator (VA = 1)
| | | | | FROM TABLE
| | | | | table2
| | | | | st2
| | | | | Table Scan.
| | | | | Forward Scan.
| | | | | Positioning at start of table.
| | | | | Using I/O Size 16 Kbytes for data
pages.
| | | | | With LRU Buffer Replacement
Strategy for data pages.
| |
| | TO TABLE
| | table1
| | Using I/O Size 2 Kbytes for data pages.



Also,

1> sp_helpindex table2
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
-------------- -------------------------------
----------------- ----------------------- ----------------
-------------------- ------------------- ------------
table2I1 col1 clustered, unique
0 0 0 Nov
29 2012 9:34PM Global Index
table2I2 col2 nonclustered
0 0 0 Nov
29 2012 9:34PM Global Index
table2I3 col3,col4 nonclustered
0 0 0 Nov
29 2012 9:34PM Global Index
table2I4 col5 nonclustered
0 0 0 Nov 29
2012 9:34PM Global Index
table2I5 col6 nonclustered
0 0 0 Nov 29
2012 9:34PM Global Index

(5 rows affected)
index_ptn_name index_ptn_seg
------------------------ -------------
table2I1_421576540 default
table2I2_421576540 default
table2I3_421576540 default
table2I4_421576540 default
table2I5_421576540 default

(5 rows affected)



Thanks,
Raj