with a simple query and key combination ads does only find corresponding key when "TOP 1" is used in the query. No Index but full scan without TOP 1:

SHOW PLAN FOR select * from liefpos WHERE MAT_GRP = 'S' AND STUECK_NR = 'G00470364' => [{"NodeID"=>1, "ParentID"=>0, "RowType"=>"SELECT", "StatementText"=>"select * from liefpos WHERE MAT_GRP = 'S' AND STUECK_NR = 'G00470364'", "StatementID"=>6, "Operator"=>nil, "Arguments"=>nil, "EstimatedExecution"=>1, "Warning"=>nil}, {"NodeID"=>2, "ParentID"=>1, "RowType"=>"PLAN", "StatementText"=>"Table Name : liefpos\nTable Alias : liefpos", "StatementID"=>6, "Operator"=>"TABLE SCAN (LIVE)", "Arguments"=>"\nAOF<mat_grp=\\"s\\" .and.="" stueck_nr="\\"G00470364\\""> \n====================\nAOF Optimization Details: \n Index: <none>, Expr: STUECK_NR=\"G00470364\", Estimate: NA, Actual: NA\n Index: <none>, Expr: MAT_GRP=\"S\", Estimate: NA, Actual: NA\n====================", "EstimatedExecution"=>1, "Warning"=>"Restriction is not optimized.\nUnoptimized Part : STUECK_NR=\"G00470364\".AND.MAT_GRP=\"S\""}]

Same query with TOP 1 finds correct index and index scan - 100 times faster!:

SHOW PLAN FOR select top 1 * from liefpos WHERE MAT_GRP = 'S' AND STUECK_NR = 'G00470364' => [{"NodeID"=>1, "ParentID"=>0, "RowType"=>"SELECT", "StatementText"=>"select top 1 * from liefpos WHERE MAT_GRP = 'S' AND STUECK_NR = 'G00470364'", "StatementID"=>5, "Operator"=>nil, "Arguments"=>nil, "EstimatedExecution"=>1, "Warning"=>nil}, {"NodeID"=>2, "ParentID"=>1, "RowType"=>"PLAN", "StatementText"=>nil, "StatementID"=>5, "Operator"=>"EVALUATE", "Arguments"=>"WHERE clause \n<[liefpos].[MAT_GRP]=\"S\" .AND. [liefpos].[STUECK_NR]=\"G00470364\">", "EstimatedExecution"=>nil, "Warning"=>nil}, {"NodeID"=>3, "ParentID"=>2, "RowType"=>"PLAN", "StatementText"=>"Table Name : liefpos\nTable Alias : liefpos\nTag : matstk_i", "StatementID"=>5, "Operator"=>"INDEX SCAN", "Arguments"=>"\nScope Optimization for restriction: <stueck_nr =="" \\"g00470364\\"="" and="" mat_grp="\\"S\\"">", "EstimatedExecution"=>1, "Warning"=>"Restriction is not optimized."}]

What am i doing wrong?

asked 22 Nov '15, 12:09

freakout's gravatar image

freakout
11446
accept rate: 0%

Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×325
×172
×31

Asked: 22 Nov '15, 12:09

Seen: 1,480 times

Last updated: 22 Nov '15, 12:09

Advantage Developer Zone Contact Us Privacy Policy Copyright Info


Powered by Advantage Database Server and OSQA
Disclaimer: Opinions expressed here are those of the poster and do not necessarily reflect the views of the company.