Hi,
We are in the process of converting all our Actuate reports using SQL89connection to SQL92connection. To do this we encounter many challenges. THe first challenge we face was after we completed running the index statistics for all Progress tables. From progress SQL explore, we execute several SQL query joining several tables. It took several hours to run just a simple join. WHen the query finally finish, we check the SQL Query plan. The reason was the query always picking the first set of index from the tables. Please advice what we should do:
Here is an example of the query we ran:
SELECT mortgage.applloan, applrela.nameaddrcd, nameaddr.socsecnbr1
FROM mortgage
join applrela on applrela.cid = mortgage.cid and applrela.div = mortgage.div and applrela.applloan = mortgage.applloan and applrela.reltype = 'BWR'
join nameaddr on nameaddr.cid = applrela.cid and nameaddr.div = applrela.div and nameaddr.nameaddrcd = applrela.nameaddrcd
WHERE mortgage.cid = 'e' and mortgage.div = 'e' and mortgage.loannumber = '0005064060'
Here is how the 3 table indexes setup:
mortgage index name: Applrela Index name: Nameaddr Index name:
agent applrela by_stmtdt
applloan nameaddrcd ivcde
loannumber nameaddrcd
firstduedt school
Here is the query plan for the above query ran:
SELECT COMMAND.
PROJECT [96] (
| JOIN [270][AUG_NESTED_LOOP-JOIN]
| | [RHS-SORTED(-ASC-DUPS) ](
| | JOIN [269][NESTED_LOOP-JOIN](
| | | PROJECT [81] (
| | | | PUB.MORTGAGE. [3](
| | | | | INDEX SCAN OF (
| | | | | | LOANNUMBER,
| | | | | | | (PUB.MORTGAGE.CID) = (e)
| | | | | | | (PUB.MORTGAGE.DIV) = (e)
| | | | | | | (PUB.MORTGAGE.LOANNUMBER) = (000126)
| | | | )
| | | , PUB.MORTGAGE.LOANTYP
| | | , PUB.MORTGAGE.CID
| | | , PUB.MORTGAGE.DIV
| | | , PUB.MORTGAGE.LOANNUMBER
| | | , PUB.MORTGAGE.APPLLOAN
| | | , PUB.MORTGAGE.rowid
| | | )
| | ,
| | | <no join operator exists>
| | ,
| | | (PEXPR3) = (PEXPR9) and [join expr]
| | | (PEXPR2) = (PEXPR8)
| | ,
| | | PROJECT [94] (
| | | | PUB.NAMEADDR. [22](
| | | | | INDEX SCAN OF (
| | | | | | by_StmtDt,
| | | | | | | (PUB.NAMEADDR.CID) = (e)
| | | | | | | (PUB.NAMEADDR.DIV) = (e))
| | | | )
| | | , PUB.NAMEADDR.SOCSECNBR1
| | | , PUB.NAMEADDR.CID
| | | , PUB.NAMEADDR.DIV
| | | , PUB.NAMEADDR.NAMEADDRCD
| | | , PUB.NAMEADDR.rowid
| | | )
| | )
| ,
| | (PEXPR2, PEXPR3, PEXPR5, PEXPR10) = (PEXPR13, PEXPR1
| | -- above defines ANL left side keys <relop> right s
| ,
| | (PEXPR9) = (PEXPR14) and [join expr]
| | (PEXPR8) = (PEXPR13)
| ,
| | RESTRICT [136] (
| | | PROJECT [88] (
| | | | PUB.APPLRELA. [4](
| | | | | INDEX SCAN OF (
| | | | | | APPLRELA,
| | | | | | | (PUB.APPLRELA.CID, PUB.APPLRELA
| | | | )
| | | , PUB.APPLRELA.APPLLOAN
| | | , PUB.APPLRELA.CID
| | | , PUB.APPLRELA.DIV
| | | , PUB.APPLRELA.RELTYPE
| | | , PUB.APPLRELA.NAMEADDRCD
| | | , PUB.APPLRELA.rowid
| | | )
| |
| | | ((PEXPR4) = (bwr))
| | | and (((PEXPR2) = (e))
| | | and ((PEXPR3) = (e)))
| | | Evaluation callback list(
| | | | col id# 4 @ 00441208
| | | | col id# 5 @ 004417d8
| | | | col id# 8 @ 00442298
| | | | [can terminate scan]
| | | )
| | )
| )
, PEXPR1
, PEXPR12
, PEXPR7
Fetch next 101 records (y/n)? y
substring(_description,1,60)
------------------------------------------------------------
)
SELECT COMMAND.
PROJECT [12] (
| PROJECT [10] (
| | PUB._sql_qplan. [4](
| | | TABLE SCAN
| | )
| , PUB._sql_qplan._Description
| )
, substring (PEXPR1,1,60)
)
THe version of progress with are currently using in 9.1E. According to the query plan, it seems progress is always picking the first set of index from my query join. Is there a way to pick the index from the query or how is progress know which set of index to pick? Please advise.
Norman
phone: 916-631-5095.
We are in the process of converting all our Actuate reports using SQL89connection to SQL92connection. To do this we encounter many challenges. THe first challenge we face was after we completed running the index statistics for all Progress tables. From progress SQL explore, we execute several SQL query joining several tables. It took several hours to run just a simple join. WHen the query finally finish, we check the SQL Query plan. The reason was the query always picking the first set of index from the tables. Please advice what we should do:
Here is an example of the query we ran:
SELECT mortgage.applloan, applrela.nameaddrcd, nameaddr.socsecnbr1
FROM mortgage
join applrela on applrela.cid = mortgage.cid and applrela.div = mortgage.div and applrela.applloan = mortgage.applloan and applrela.reltype = 'BWR'
join nameaddr on nameaddr.cid = applrela.cid and nameaddr.div = applrela.div and nameaddr.nameaddrcd = applrela.nameaddrcd
WHERE mortgage.cid = 'e' and mortgage.div = 'e' and mortgage.loannumber = '0005064060'
Here is how the 3 table indexes setup:
mortgage index name: Applrela Index name: Nameaddr Index name:
agent applrela by_stmtdt
applloan nameaddrcd ivcde
loannumber nameaddrcd
firstduedt school
Here is the query plan for the above query ran:
SELECT COMMAND.
PROJECT [96] (
| JOIN [270][AUG_NESTED_LOOP-JOIN]
| | [RHS-SORTED(-ASC-DUPS) ](
| | JOIN [269][NESTED_LOOP-JOIN](
| | | PROJECT [81] (
| | | | PUB.MORTGAGE. [3](
| | | | | INDEX SCAN OF (
| | | | | | LOANNUMBER,
| | | | | | | (PUB.MORTGAGE.CID) = (e)
| | | | | | | (PUB.MORTGAGE.DIV) = (e)
| | | | | | | (PUB.MORTGAGE.LOANNUMBER) = (000126)
| | | | )
| | | , PUB.MORTGAGE.LOANTYP
| | | , PUB.MORTGAGE.CID
| | | , PUB.MORTGAGE.DIV
| | | , PUB.MORTGAGE.LOANNUMBER
| | | , PUB.MORTGAGE.APPLLOAN
| | | , PUB.MORTGAGE.rowid
| | | )
| | ,
| | | <no join operator exists>
| | ,
| | | (PEXPR3) = (PEXPR9) and [join expr]
| | | (PEXPR2) = (PEXPR8)
| | ,
| | | PROJECT [94] (
| | | | PUB.NAMEADDR. [22](
| | | | | INDEX SCAN OF (
| | | | | | by_StmtDt,
| | | | | | | (PUB.NAMEADDR.CID) = (e)
| | | | | | | (PUB.NAMEADDR.DIV) = (e))
| | | | )
| | | , PUB.NAMEADDR.SOCSECNBR1
| | | , PUB.NAMEADDR.CID
| | | , PUB.NAMEADDR.DIV
| | | , PUB.NAMEADDR.NAMEADDRCD
| | | , PUB.NAMEADDR.rowid
| | | )
| | )
| ,
| | (PEXPR2, PEXPR3, PEXPR5, PEXPR10) = (PEXPR13, PEXPR1
| | -- above defines ANL left side keys <relop> right s
| ,
| | (PEXPR9) = (PEXPR14) and [join expr]
| | (PEXPR8) = (PEXPR13)
| ,
| | RESTRICT [136] (
| | | PROJECT [88] (
| | | | PUB.APPLRELA. [4](
| | | | | INDEX SCAN OF (
| | | | | | APPLRELA,
| | | | | | | (PUB.APPLRELA.CID, PUB.APPLRELA
| | | | )
| | | , PUB.APPLRELA.APPLLOAN
| | | , PUB.APPLRELA.CID
| | | , PUB.APPLRELA.DIV
| | | , PUB.APPLRELA.RELTYPE
| | | , PUB.APPLRELA.NAMEADDRCD
| | | , PUB.APPLRELA.rowid
| | | )
| |
| | | ((PEXPR4) = (bwr))
| | | and (((PEXPR2) = (e))
| | | and ((PEXPR3) = (e)))
| | | Evaluation callback list(
| | | | col id# 4 @ 00441208
| | | | col id# 5 @ 004417d8
| | | | col id# 8 @ 00442298
| | | | [can terminate scan]
| | | )
| | )
| )
, PEXPR1
, PEXPR12
, PEXPR7
Fetch next 101 records (y/n)? y
substring(_description,1,60)
------------------------------------------------------------
)
SELECT COMMAND.
PROJECT [12] (
| PROJECT [10] (
| | PUB._sql_qplan. [4](
| | | TABLE SCAN
| | )
| , PUB._sql_qplan._Description
| )
, substring (PEXPR1,1,60)
)
THe version of progress with are currently using in 9.1E. According to the query plan, it seems progress is always picking the first set of index from my query join. Is there a way to pick the index from the query or how is progress know which set of index to pick? Please advise.
Norman
phone: 916-631-5095.