Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Posts
    39

    Unanswered: Indexing - confused...

    I could use some help with indexing on the AS400. I have 5 reports that use the UNION ALL command to link together a similar SQL command about 30 different times. A very common representation of that SQL is:

    PRODDTA.TBL_Chart.Account AS Account,
    PRODDTA.TBL_Chart.Description AS Descp,
    PRODDTA.TBL_BSLevel3.Level3Group AS Level3,
    PRODDTA.TBL_BSLevel3.L3Order AS L3Order,
    PRODDTA.TBL_BSLevel4.Level4Group AS Level4,
    PRODDTA.TBL_BSLevel4.L4Order AS L4Order,
    PRODDTA.TBL_BSLevel6.Level6Group AS Level6,
    PRODDTA.TBL_BSLevel6.L6Order AS L6Order,
    0 AS BAL3,
    0 AS BAL6,
    0 AS BAL9,
    0 AS BALFOR,
    PRODDTA.F0911LA.GLAA AS DISBAL, 0 AS ADDBAL ,0 AS ACQBAL ,0 AS RECBAL,0 AS TRNBAL,0 AS RETBAL,0 AS NRCBAL

    FROM PRODDTA.TBL_Chart,PRODDTA.F0911LA,PRODDTA.F0006, PRODDTA.TBL_BSLevel3, PRODDTA.TBL_BSLevel4, PRODDTA.TBL_BSLevel5, PRODDTA.TBL_BSLevel6

    WHERE PRODDTA.TBL_CHART.Account = PRODDTA.F0911LA.GLOBJ
    AND PRODDTA.TBL_BSLevel3.Level3Group = PRODDTA.TBL_BSLevel4.Level3Group
    AND PRODDTA.TBL_BSLevel4.Level4Group = PRODDTA.TBL_BSLevel5.Level4Group
    AND PRODDTA.TBL_BSLevel5.Level5Group = PRODDTA.TBL_BSLevel6.Level5Group
    AND PRODDTA.TBL_Chart.BSLevel7Group = PRODDTA.TBL_BSLevel6.Level6Group
    AND PRODDTA.F0911LA.GLCTRY = 20
    AND PRODDTA.F0911LA.GLFY = 03
    AND PRODDTA.F0911LA.GLLT = 'AA'
    AND PRODDTA.F0911LA.GLPOST = 'P'
    AND PRODDTA.F0911LA.GLPN <= 9
    AND PRODDTA.F0911LA.GLPN >= 9
    AND PRODDTA.F0911LA.GLDCT = 'JD'
    AND PRODDTA.F0911LA.GLCO = PRODDTA.F0006.MCCO
    AND PRODDTA.F0911LA.GLMCU = PRODDTA.F0006.MCMCU
    AND PRODDTA.F0006.MCRP04 = 'C'
    AND PRODDTA.F0006.MCRP05 <> 'HPI'

    My problem is with the F0911LA table. I have built an index for the following fields in the following order

    CREATE INDEX PRODDTA.F0911_CIB1

    ON PRODDTA.F0911 ( GLCTRY ASC , GLFY ASC , GLLT ASC , GLPOST ASC , GLPN ASC , GLDCT ASC , GLCO ASC , GLMCU ASC , GLOBJ ASC

    This matches up with the order of the WHERE clause beginning with the where clause join and the following params. However, while the index is consistently used, the AS400 is building a temporary index on the GLOBJ column for every instance of the SQL rather than using the GLOBJ in the index. Furthermore, I have built a separate index with only that column and that is not used either. The temp index being built as many as 8 times in each report is really slowing things down.

    What am I doing wrong?

    Thanks in advance.

    Ryan

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: Indexing - confused...

    What's a temp index?

    And why this?

    AND PRODDTA.F0911LA.GLPN <= 9
    AND PRODDTA.F0911LA.GLPN >= 9

    Also it looks like is the F0911LA driver...

    Why not make a derived table out of that with you predicates...THEN join it to the other tables...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Aug 2003
    Posts
    39

    Re: Indexing - confused...

    Originally posted by Brett Kaiser
    What's a temp index?

    And why this?

    AND PRODDTA.F0911LA.GLPN <= 9
    AND PRODDTA.F0911LA.GLPN >= 9

    Also it looks like is the F0911LA driver...

    Why not make a derived table out of that with you predicates...THEN join it to the other tables...
    The AS400 will build a temporary index on the fly (much like a hash table) which takes more processor, and much more disk than using sufficient pre-built indexes. This is what I am trying to avoid. The <=9 and >=9 was a typo. It should have been <=9, >=8 (August 1 - Sept 1).

    F0911LA is a logical file for F0911. The developers seem to have a habit of using logicals to view the parent file rather than going straight to the parent. I am trying to get them to change this since the size of the pages is 4K for a logical and I believe 64K for a regular file.

    Regarding the derived table...I'm not sure since I am the admin rather than the one of the report writers. Maybe it has to do with our reporting software.

    Is my logic correct for creating this index??

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •