"Bret Halford" <bret@sybase.com> wrote in message
news:34c88f2b.0311291853.9f41dfd@posting.google.co m...

> While there have been a number of changes in the optimizer since
> 11.5.x, the basic information given by these commands is still the
> same (I believe there have been formatting changes, though). If that
> doesn't help you make sense of the output, post it here and you may
> get some good commentary on it.

The table is C_daily_stats. The main fields are:

Column_name Type Length
----------- ---- -----------
C_series_no C_series_no_t 4 (integer)
stats_C_date C_date_t 8 (date)
stats_C_newdate C_newdate_t 4 integer eg
20031201)
....

index_name index_description index_keys
index_max_rows_per_page
---------- ----------------- ----------
----------
C_daily_stats_index clustered, unique located on default C_series_no,
stats_C_date 146
C_daily_stats_index2 nonclustered located on default stats_C_newdate,
C_series_no 0

The SQL to run is:

declare @today C_date_t
select C_logical_date_value
from C_system_dates
where C_logical_date_name = 'today'

select C_series_no,
@today,
convert(int,convert(varchar,@today,112) )
from V_valid_series_daily_stats valsds
where valsds.C_series_no not in (select dais.C_series_no
from C_daily_stats dais
where dais.stats_C_newdate =
convert(int,convert(varchar,@today,112)))

and the showplan is:
QUERY PLAN FOR STATEMENT 1 (at line 1).

STEP 1
The type of query is DECLARE.

QUERY PLAN FOR STATEMENT 2 (at line 2).

STEP 1
The type of query is SELECT.

FROM TABLE
C_system_dates
Nested iteration.
Using Clustered Index.
Index : C_system_dates_index
Ascending scan.
Positioning by key.
Keys are:
C_logical_date_name
Using I/O Size 2 Kbytes.
With LRU Buffer Replacement Strategy.

QUERY PLAN FOR STATEMENT 3 (at line 6).

STEP 1
The type of query is SELECT.


FROM TABLE
C_system_dates
Nested iteration.
Using Clustered Index.
Index : C_system_dates_index
Ascending scan.
Positioning by key.
Keys are:
C_logical_date_name
Using I/O Size 2 Kbytes.
With LRU Buffer Replacement Strategy.

FROM TABLE
C_contract
Nested iteration.
Table Scan.
Ascending scan.
Positioning at start of table.
Using I/O Size 2 Kbytes.
With LRU Buffer Replacement Strategy.

FROM TABLE
C_series
Nested iteration.
Table Scan.
Ascending scan.
Positioning at start of table.


Run subquery 1 (at nesting level 1).
Using I/O Size 16 Kbytes.
With LRU Buffer Replacement Strategy.


FROM TABLE
C_delivery_month
Nested iteration.
Using Clustered Index.
Index : C_delivery_month_index
Ascending scan.
Positioning by key.
Keys are:
C_delivery_month_no
Using I/O Size 2 Kbytes.
With LRU Buffer Replacement Strategy.


FROM TABLE
C_contract_type
Nested iteration.
Table Scan.
Ascending scan.
Positioning at start of table.
Using I/O Size 2 Kbytes.
With LRU Buffer Replacement Strategy.


FROM TABLE
C_contract
Nested iteration.
Using Clustered Index.
Index : C_contract_index
Ascending scan.
Positioning by key.
Keys are:
C_contract_no
Using I/O Size 16 Kbytes.
With LRU Buffer Replacement Strategy.


FROM TABLE
C_contract_month
Nested iteration.
Using Clustered Index.
Index : C_contract_month_index
Ascending scan.
Positioning by key.
Keys are:
C_contract_no
C_delivery_month_no
C_delivery_day_no
Using I/O Size 2 Kbytes.
With LRU Buffer Replacement Strategy.


NESTING LEVEL 1 SUBQUERIES FOR STATEMENT 3.


QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 10).


Correlated Subquery.
Subquery under an IN predicate.

STEP 1
The type of query is SELECT.
Evaluate Ungrouped ANY AGGREGATE.


FROM TABLE
C_daily_stats
dais
EXISTS TABLE : nested iteration.
Using Clustered Index.
Index : C_daily_stats_index
Ascending scan.
Positioning by key.
Keys are:
C_series_no
Using I/O Size 16 Kbytes.
With LRU Buffer Replacement Strategy.


END OF QUERY PLAN FOR SUBQUERY 1.

===

The last bit has a different (faster) showplan on the test servers
STEP 1
The type of query is SELECT.
Evaluate Ungrouped ANY AGGREGATE.


FROM TABLE
C_daily_stats
dais
EXISTS TABLE : nested iteration.
Index : C_daily_stats_index2
Ascending scan.
Positioning by key.
Index contains all needed columns. Base table will not be read.
Keys are:
stats_C_newdate
C_series_no
Using I/O Size 2 Kbytes.
With LRU Buffer Replacement Strategy.