Unconfigured Ad Widget

Collapse

Announcement

Collapse
No announcement yet.

Query Optimizer - us an explicit logical file

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Query Optimizer - us an explicit logical file

    Not to my knowledge. The ONLY effect that specifing a logical on an SQL statement makes, is that logical will definately be evaluated as a possible solution. However, if a better way is determined by the oprimizer, it will use that way instead. If the logical is not specified, the optimizer will start looking for a solution, but it may run out of time before it gets to the logical to evaluate it.

  • #2
    Query Optimizer - us an explicit logical file

    Consider this - you write your SQL to use a logical. What happens when the logical is deleted and you attempt to run your SQL statement? You should not assume that the Optimizer will make the correct decisions on what you want or write/rewrite your SQL for you. (SQE optimizer is occasionally rewriting some SQL statements) Code and Analyze your SQL and put the Logical desired intended for use in a comment after you verify it's use. When specify a logical in an SQL SELECT statement, the optimizer goes back to the physical to look for a satisfactory access path. The WHERE clause chooses the Index based on the order of the fields specified matching an index. Loose rule is that the first 4 conditions should match the intended logical. Also the first 4 conditions (index statistics DSPFD Logical file) should be unique enough to narrow your search to less than 20% of the records. If the Index is chosen on first key then the Data is then filtered by the rest of the WHERE clause conditions. Also add a ORDER BY clause, because it gives an additional hint and forces the use of the access path even if the file is small. ORDER BY clause is ABSOLUTELY required, otherwise your SQL may not run correctly on a multi-CPU system or with DB2 SMP installed. ORDER BY insures that data selected is sorted even if fetched via many tasks. The SQL manuals and redbooks give you charts and information regarding this subject.

    Comment


    • #3
      Query Optimizer - us an explicit logical file

      Exceptions to Specifying a Logical in SQL Statement. SQL Views DDS Joins

      Comment


      • #4
        Query Optimizer - us an explicit logical file

        As I hinted at above, one other time it may be necessary to specify the logical is if the optimizer runs out of time before evaluating all the possible indexes (like thousands of logicals on the same physical, or an extremely complex query), specifying the logical you THINK it should use will cause the optimizer to at least evaluate that logical. Doesn't mean it will use it. -dan

        Comment


        • #5
          Query Optimizer - us an explicit logical file

          Is there a way to explicitly tell the Query Optimizer which logical file to use for an SQL statement?

          Comment


          • #6
            Query Optimizer - us an explicit logical file

            We typically have between 1 and 15 logical files and the debug messages in testing so far have evaluated all of them. One instance where the optimizer seems to choose the logical we use in a Select statement is when it is a right-table in a join. So, when the join is by the keys in that logical and the Where clause includes the logical's select/omit it seems to work. Besides that we'll probably create indexes for slow SQL statements as suggested by the optimizer. Thank you for your help.

            Comment

            Working...
            X