SAP Knowledge Base Article - Preview

2491508 - A query with many joining tables returned incorrect result in parallel order by phase in IQ16 SP11.08.

Symptom

A query with many joining tables returned incorrect result. 
  The query should be returned 23 rows, but 15 ~ 17 rows are returned at incorrect execution.
 
  When set option "Dml_options8=268435456 ",it makes correct result.
  Another finding is to remove a drived column 'CUST_LONCNT' from top select-list and it makes correct result too.

 * Analysis :
  
  1) Tried to set some DML options. Only DML_Options8=8 affected well and others still returned incorrect output.
  
      Set temporary option DML_Options8=8;           -- Disable shadow parallelism
      Set temporary option DML_options8=268435456  -- Disable shadow Parallel order by retrievers

              
  2) Comparing plans
  
     - Correct plan with Dml_options8=268435456   ;
       
        #87 Group By (Hash)
       
          Parent Node #90
          Child Node 1 #86
          Generated Result Rows 23
          Estimated Result Rows 103
          Optimizer est. max hash rows for these keys 353004337
          Hash est. number of keys 103
          Hash actual number of keys 23
   
     - Incorrect Plan at default run ;
      
        #87 Group By (Hash)
       
          Parent Node #185
          Child Node 1 #86
          Generated Result Rows 136
          Estimated Result Rows 103
          Optimizer est. max hash rows for these keys 353004337
          SMP fragent cost 89549146003094528.00000000
          Hash est. number of keys 103
          Hash actual number of keys 11


Read more...

Environment

SAP IQ 16

Product

SAP IQ 16.0

Keywords

'DML_Options8=8' , 'shadow parallelism' , 'order by retrievers' , 'SubQuery PreFilter', 'dfo::ValidateOutputOrdering' , sybase, faliure, CR806424, CR#806424, 806424, , KBA , BC-SYB-IQ , Sybase IQ , Problem

About this page

This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP ONE Support launchpad (Login required).

Search for additional results

Visit SAP Support Portal's SAP Notes and KBA Search.