set showplan on/off

Let's showing 2 tml below:

========================================================================================
[55] HGCDB1.dmo_govcorp.1>
[55] HGCDB1.dmo_govcorp.2> set showplan on
[55] HGCDB1.dmo_govcorp.3> go
[56] HGCDB1.dmo_govcorp.1> select a.asset_id, a.cusip, 'G'
[56] HGCDB1.dmo_govcorp.2> from asset a left join data_ejv_common..asset_xref b
[56] HGCDB1.dmo_govcorp.3> on a.asset_id = b.asset_id
[56] HGCDB1.dmo_govcorp.4> where b.asset_id is NULL
[56] HGCDB1.dmo_govcorp.5> go

QUERY PLAN FOR STATEMENT 1 (at line 1).


    STEP 1
        The type of query is SELECT.

        3 operator(s) under root

       |ROOT:EMIT Operator (VA = 3)
       |
       |   |MERGE JOIN Operator (Join Type: Left Outer Join) (VA = 2)
       |   | Using Worktable1 for internal storage.
       |   |  Key Count: 1
       |   |  Key Ordering: ASC
       |   |
       |   |   |SCAN Operator (VA = 0)
       |   |   |  FROM TABLE
       |   |   |  asset
       |   |   |  a
       |   |   |  Using Clustered Index.
       |   |   |  Index : asset_PUC
       |   |   |  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.
       |   |
       |   |   |SCAN Operator (VA = 1)
       |   |   |  FROM TABLE
       |   |   |  data_ejv_common..asset_xref
       |   |   |  b
       |   |   |  Index : asset_xref_PU
       |   |   |  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.


Total estimated I/O cost for statement 1 (at line 1): 296138.
(11493 rows affected)


========================================================================================
[57] HGCDB1.dmo_govcorp.1> select asset_id, cusip, 'G' from asset g
[57] HGCDB1.dmo_govcorp.2> where  not exists ( select * from data_ejv_common..asset_xref x
[57] HGCDB1.dmo_govcorp.3>                     where x.asset_id = g.asset_id )
[57] HGCDB1.dmo_govcorp.4> go

QUERY PLAN FOR STATEMENT 1 (at line 1).


    STEP 1
        The type of query is SELECT.

        4 operator(s) under root

       |ROOT:EMIT Operator (VA = 4)
       |
       |   |SQFILTER Operator (VA = 3) has 2 children.
       |   |
       |   |   |SCAN Operator (VA = 0)
       |   |   |  FROM TABLE
       |   |   |  asset
       |   |   |  g
       |   |   |  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.
       |   |
       |   |  Run subquery 1 (at nesting level 1).
       |   |
       |   |  QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 2).
       |   |
       |   |   Correlated Subquery.
       |   |   Subquery under an EXISTS predicate.
       |   |
       |   |   |SCALAR AGGREGATE Operator (VA = 2)
       |   |   |  Evaluate Ungrouped ANY AGGREGATE.
       |   |   |  Scanning only up to the first qualifying row.
       |   |   |
       |   |   |   |SCAN Operator (VA = 1)
       |   |   |   |  FROM TABLE
       |   |   |   |  data_ejv_common..asset_xref
       |   |   |   |  x
       |   |   |   |  Index : asset_xref_PU
       |   |   |   |  Forward Scan.
       |   |   |   |  Positioning by key.
       |   |   |   |  Index contains all needed columns. Base table will not be read.
       |   |   |   |  Keys are:
       |   |   |   |    asset_id ASC
       |   |   |   |  Using I/O Size 2 Kbytes for index leaf pages.
       |   |   |   |  With LRU Buffer Replacement Strategy for index leaf pages.
       |   |
       |   |  END OF QUERY PLAN FOR SUBQUERY 1.


Total estimated I/O cost for statement 1 (at line 1): 335773054.



10-09 07:01