ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

No Logical Files Allowed in Views - Workaround ?

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

  • No Logical Files Allowed in Views - Workaround ?

    Hi, I have a view with several joins, that takes 10 seconds to run, which is a bit long for users to wait.
    I expect that joining to a Logical File would improve performance, but LF Joins are not allowed for some reason.

    Are there any "Tricks" to getting a view to use a LF or equivalent somehow ?

  • #2
    When including logical files in views or SQL Statements ... the query optimizer has to rewrite the queries based on the DDS description, i.e. it takes the Field selection, Join information and select/omit claused out of the DDS files and rewrites the SQL-Statement based on this information ... before it goes into optimization.
    What is not selected are key information!
    You have to analyze your SQL-Statement / View and then create the appropriate neccessary indexes!

    Comment


    • #3
      Originally posted by B.Hauser View Post
      When including logical files in views or SQL Statements ... the query optimizer has to rewrite the queries based on the DDS description, i.e. it takes the Field selection, Join information and select/omit claused out of the DDS files and rewrites the SQL-Statement based on this information ... before it goes into optimization.
      What is not selected are key information!
      You have to analyze your SQL-Statement / View and then create the appropriate neccessary indexes!
      So I should create an index similar to my logical file, then the optimizer will make use of the index, in the view when I join on the physical file ? :

      CREATE INDEX MYLIB.MYINDEX on MYLIB.MYPHYSICAL (KEY)

      SELECT OTH.FLD1 , MP.FLD2
      FROM OTHERFILE OTH
      JOIN MYPHYSICAL MP on MP.KEY = OTH.KEY

      Is there a way to specify which index gets used ? I might have an index with a WHERE STATUS = 'A' and want my view to use that, similar to Select/Omit in logical file.

      Or better NOT to put WHERE in the index and put WHERE in the VIEW instead. Use the Index just for "key" fields ?
      Last edited by MFisher; March 14, 2023, 01:20 PM.

      Comment


      • #4
        An index can only be created over a single physical file or table.
        Make sure there are indexes (or may be logcial files) over the key fields in both tables.
        If you select STATUS What ever it makes sense to include STATUS as key field.
        It is hard to suggest anything else without knowing your tables and connection between the tables and your data.
        So it is hard to say whether the WHERE Condition would be needed or not.

        In either way you may run the view (with your expected WHERE conditions and orders) through visual explain and then check if and what access path are needed.

        Comment


        • #5
          Thanks. I don't have a specific use at the moment. Just trying to understand different options.
          My original post is obsolete because we have made changes to the process.
          If I have several indexes on a file, I think having WHERE in one Index could be dangerous if I don't know which index the optimizer will choose.

          Comment


          • #6
            My understanding was always that the SQL engine will consider all indexes, including those defined in logical files, when performing a query
            So e.g. if you do "select * from x join y on x.a=y.a", and there are logicals that define keys for column a in both tables, then it will use that logical like it would an index.

            Is that correct?

            Comment


            • #7
              The query optimizer considers all kinds of access path (SQL Indexes, Keys in logical files, Primary/Unique Key Constraints) incl. Derived and Sparse Index.
              The query optimizer may prefer SQL indexes over DDS described logical files because of the larger page size (Per Default: DDS LF 8K / SQL Index 64k)

              ... but the query optimizer will only use indexes (or LF or Key Constraints) if maximum 15-20% of the data in a table is returned.
              ... and not every tables scan is bad and not every index access is good

              ... without knowing more it is like a look into a crystal ball
              Last edited by B.Hauser; April 6, 2023, 03:59 AM.

              Comment

              Working...
              X