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.
阅读(1127) | 评论(0) | 转发(0) |
