Hi,

We have recently upgraded Sybase servers from 12.5.4 ESD#3
to 15.0.3 ESD #1(recommended by application vendor) on AIX
6.1. Some of our sqls running very slow. We have run one fo
those queries with different options. With compatibilty_mode
ON, SQLs running faster as pre-upgrade.
I would like to know how can I re-write the query below to
run faster without having compatibility_mode ON. Any
recommendations please ?


WITH COMPATIBILITY OFF:
--------------------------

set compatibility_mode off

select count(*)
from table1 as w,
table2 v_ln_id,
table3 ln (index table3_ND1)
where
w.X_NB *= v_ln_id.X_NB and
v_ln_id.X_IDENTITY *= ln.X_IDENTITY and
substring(MH_APPL, 1, 3 ) = 'NYC'


query plan including I/O stats:
-------------------------------







QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is SET OPTION OFF.


QUERY PLAN FOR STATEMENT 2 (at line 3).
Optimized using the forced options (internally generated
Abstract Plan).


STEP 1
The type of query is SELECT.

13 operator(s) under root

|ROOT:EMIT Operator (VA = 13)

|
| |SEQUENCER Operator (VA = 12) has 2 children.
| |
| | |STORE Operator (VA = 3)
| | | Worktable2 created, in allpages locking
mode, for REFORMATTING.
| | | Creating clustered index.
| | |
| | | |INSERT Operator (VA = 2)
| | | | The update mode is direct.
| | | |
| | | | |HASH VECTOR AGGREGATE Operator (VA =
1)
| | | | | GROUP BY
| | | | | Evaluate Grouped MINIMUM
AGGREGATE.
| | | | | Using Worktable1 for internal
storage.
| | | | | Key Count: 1
| | | | |
| | | | | |SCAN Operator (VA = 0)
| | | | | | FROM TABLE
| | | | | | table3
| | | | | | Table Scan.
| | | | | | Forward Scan.
| | | | | | Positioning at start of table.
| | | | | | Using I/O Size 4 Kbytes for
data pages.
| | | | | | With LRU Buffer Replacement
Strategy for data pages.
| | | |
| | | | TO TABLE
| | | | Worktable2.
| |
| | |SCALAR AGGREGATE Operator (VA = 11)
| | | Evaluate Ungrouped COUNT AGGREGATE.
| | |
| | | |NESTED LOOP JOIN Operator (VA = 10)
(Join Type: Left Outer Join)
| | | |
| | | | |NESTED LOOP JOIN Operator (VA = 7)
(Join Type: Left Outer Join)
| | | | |
| | | | | |RESTRICT Operator (VA =
5)(0)(0)(0)(9)(0)
| | | | | |
| | | | | | |SCAN Operator (VA = 4)
| | | | | | | FROM TABLE
| | | | | | | table1
| | | | | | | w
| | | | | | | Index : table1_ND1
| | | | | | | Forward Scan.
| | | | | | | Positioning at index
start.
| | | | | | | Using I/O Size 4 Kbytes
for index leaf pages.
| | | | | | | With LRU Buffer
Replacement Strategy for index leaf pages.
| | | | | | | Using I/O Size 4 Kbytes
for data pages.
| | | | | | | With LRU Buffer
Replacement Strategy for data pages.
| | | | |
| | | | | |SCAN Operator (VA = 6)
| | | | | | FROM TABLE
| | | | | | Worktable2.
| | | | | | Using Clustered Index.
| | | | | | Forward Scan.
| | | | | | Positioning by key.
| | | | | | Using I/O Size 4 Kbytes for
data pages.
| | | | | | With LRU Buffer Replacement
Strategy for data pages.
| | | |
| | | | |RESTRICT Operator (VA =
9)(0)(0)(0)(14)(0)
| | | | |
| | | | | |SCAN Operator (VA = 8)
| | | | | | FROM TABLE
| | | | | | table3
| | | | | | ln
| | | | | | Index : table3_ND1
| | | | | | Forward Scan.
| | | | | | Positioning at index start.
| | | | | | Index contains all needed
columns. Base table will not be read.
| | | | | | Using I/O Size 4 Kbytes for
index leaf pages.
| | | | | | With LRU Buffer Replacement
Strategy for index leaf pages.


Total writes for this command: 0


1495


Table: Worktable2 scan count 1, logical reads: (regular=5333
apf=0 total=5333), physical reads: (regular=3983 apf=0
total=3983), apf IOs used=0
Table: Worktable2 scan count 1, logical reads:
(regular=320208 apf=0 total=320208), physical reads:
(regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable1 scan count 1, logical reads: (regular=723
apf=0 total=723), physical reads: (regular=669 apf=0
total=669), apf IOs used=0
Table: table3 scan count 1, logical reads: (regular=161800
apf=2 total=161802), physical reads: (regular=21643 apf=6821
total=28464), apf IOs used=6821
Table: table1 (w) scan count 1, logical reads:
(regular=224527 apf=0 total=224527), physical reads:
(regular=37604 apf=130024 total=167628), apf IOs used=130017
Table: Worktable2 scan count 1495, logical reads:
(regular=330032 apf=0 total=330032), physical reads:
(regular=0 apf=0 total=0), apf IOs used=0
Table: table3 (ln) scan count 1495, logical reads:
(regular=1643005 apf=0 total=1643005), physical reads:
(regular=8 apf=1093 total=1101), apf IOs used=1090
Total writes for this command: 0


WITH COMPATIBILITY ON:
-----------------------------



set compatibility_mode on

select count(*)
from table1 as w,
table2 v_ln_id,
table3 ln (index table3_ND1)
where
w.X_NB *= v_ln_id.X_NB and
v_ln_id.X_IDENTITY *= ln.X_IDENTITY and
substring(MH_APPL, 1, 3 ) = 'NYC'



query plan including I/O stats:
---------------------------------








QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is SET OPTION ON.


QUERY PLAN FOR STATEMENT 2 (at line 3).


STEP 1
The type of query is SELECT (into Worktable1).
GROUP BY
Evaluate Grouped MINIMUM AGGREGATE.

FROM TABLE
table3
Nested iteration.
Table Scan.
Forward Scan.
Positioning at start of table.
Using I/O Size 4 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

TO TABLE
Worktable1.

STEP 2
The type of query is SELECT.
Evaluate Ungrouped COUNT AGGREGATE.

FROM TABLE
table1
w
Nested iteration.
Table Scan.
Forward Scan.
Positioning at start of table.
Using I/O Size 4 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
Worktable1.
Nested iteration.
Using Clustered Index.
Forward Scan.
Positioning by key.
Using I/O Size 4 Kbytes for data pages.
With MRU Buffer Replacement Strategy for data pages.

FROM TABLE
table3
ln
Nested iteration.
Index : table3_ND1
Forward Scan.
Positioning by key.
Index contains all needed columns. Base table will not be
read.
Keys are:
X_IDENTITY ASC
Using I/O Size 4 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.

STEP 3
The type of query is SELECT.

Total writes for this command: 0


1495


Table: table1 scan count 1, logical reads: (regular=168810
apf=0 total=168810), physical reads: (regular=467 apf=5
total=472), apf IOs used=5
Table: table3 scan count 1, logical reads: (regular=161800
apf=2 total=161802), physical reads: (regular=0 apf=0
total=0), apf IOs used=0
Table: table3 scan count 1056, logical reads: (regular=1730
apf=0 total=1730), physical reads: (regular=0 apf=0
total=0), apf IOs used=3
Table: Worktable1 scan count 1495, logical reads:
(regular=900925 apf=0 total=900925), physical reads:
(regular=0 apf=0 total=0), apf IOs used=0
Total writes for this command: 0