Symptom
On a simple select into the showplan for the failed result set shows:
1> select top 1000 acid into #p1d from pt_trmast where tmcode in ('B','S') and acid > 100000
2> go
QUERY PLAN FOR STATEMENT 1 (at line 1).
Optimized using Parallel Mode
Executed in parallel by coordinating process and 8 worker processes.
STEP 1
The type of query is CREATE TABLE.
STEP 2
The type of query is INSERT.
7 operator(s) under root
|ROOT:EMIT Operator (VA = 7)
|
| |TOP Operator (VA = 6)
| | Top Limit: 1000
| |
| | |EXCHANGE Operator (VA = 5) (Merged)
| | |Executed in parallel by 8 Producer and 1 Consumer processes.
| | |
| | | |EXCHANGE:EMIT Operator (VA = 4)
| | | |
| | | | |INSERT Operator (VA = 3)
| | | | | The update mode is direct.
| | | | |
| | | | | |TOP Operator (VA = 2)
| | | | | | Top Limit: 1000
| | | | | |
| | | | | | |RESTRICT Operator (VA = 1)(0)(0)(0)(7)(0)
| | | | | | |
| | | | | | | |SCAN Operator (VA = 0)
| | | | | | | | FROM TABLE
| | | | | | | | pt_trmast
| | | | | | | | Table Scan.
| | | | | | | | Forward Scan.
| | | | | | | | Positioning at start of table.
| | | | | | | | Executed in parallel with a 8-way partition scan.
| | | | | | | | Using I/O Size 16 Kbytes for data pages.
| | | | | | | | With LRU Buffer Replacement Strategy for data pages.
| | | | |
| | | | | TO TABLE
| | | | | #p1d
| | | | | Using I/O Size 16 Kbytes for data pages.
Total estimated I/O cost for statement 1 (at line 1): 2147483647.
(0 rows affected)
In-house Repro:
1> select top 1000 acid into #t from pt_trmast (index 0) where acid > 100000 and acid !=500000
2> go
QUERY PLAN FOR STATEMENT 1 (at line 1).
Optimized using Parallel Mode
Optimized using the forced options (internally generated Abstract Plan).
Executed in parallel by coordinating process and 8 worker processes.
STEP 1
The type of query is CREATE TABLE.
STEP 2
The type of query is INSERT.
6 operator(s) under root
|ROOT:EMIT Operator (VA = 6)
|
| |TOP Operator (VA = 5)
| | Top Limit: 1000
| |
| | |EXCHANGE Operator (VA = 4) (Merged)
| | |Executed in parallel by 8 Producer and 1 Consumer processes.
| | |
| | | |EXCHANGE:EMIT Operator (VA = 3)
| | | |
| | | | |INSERT Operator (VA = 2)
| | | | | The update mode is direct.
| | | | |
| | | | | |TOP Operator (VA = 1)
| | | | | | Top Limit: 1000
| | | | | |
| | | | | | |SCAN Operator (VA = 0)
| | | | | | | FROM TABLE
| | | | | | | pt_trmast
| | | | | | | Table Scan.
| | | | | | | Forward Scan.
| | | | | | | Positioning at start of table.
| | | | | | | Executed in parallel with a 8-way partition
scan.
| | | | | | | Using I/O Size 32 Kbytes for data pages.
| | | | | | | With LRU Buffer Replacement Strategy for data
pages.
| | | | |
| | | | | TO TABLE
| | | | | #t
| | | | | Using I/O Size 32 Kbytes for data pages.
(0 rows affected) <<< no rows
However, the same query WITHOUT the select into:
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is SET OPTION ON.
1> select top 1000 acid from pt_trmast (index 0) where acid > 100000 and acid !=500000
2> go
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is EXECUTE.
Executing a newly cached statement (SSQL_ID = 2117487096).
QUERY PLAN FOR STATEMENT 1 (at line 0).
STEP 1
The type of query is DECLARE.
QUERY PLAN FOR STATEMENT 2 (at line 1).
Optimized using Parallel Mode
Optimized using the forced options (internally generated Abstract Plan).
Executed in parallel by coordinating process and 8 worker processes.
STEP 1
The type of query is SELECT.
4 operator(s) under root
|ROOT:EMIT Operator (VA = 4)
|
| |TOP Operator (VA = 3)
| | Top Limit: 1000
| |
| | |EXCHANGE Operator (VA = 2) (Merged)
| | |Executed in parallel by 8 Producer and 1 Consumer processes.
| | |
| | | |EXCHANGE:EMIT Operator (VA = 1)
| | | |
| | | | |SCAN Operator (VA = 0)
| | | | | FROM TABLE
| | | | | pt_trmast
| | | | | Table Scan.
| | | | | Forward Scan.
| | | | | Positioning at start of table.
| | | | | Executed in parallel with a 8-way partition scan.
| | | | | Using I/O Size 32 Kbytes for data pages.
| | | | | With LRU Buffer Replacement Strategy for data pages.
acid
-----------
(1000 rows affected) <<<< All expected rows returned.
Read more...
Environment
- SAP Adaptive Server Enterprise 15.7
- SAP Adaptive Server Enterprise 16.0
Product
Keywords
KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , Bug Filed
About this page
This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP for Me (Login required).Search for additional results
Visit SAP Support Portal's SAP Notes and KBA Search.