CA-IDMS Explain
Jim Moore
Where can I find more detailed explanations regarding the access plans that CA-IDMS EXPLAIN shows? The SQL Reference seems to be missing quite a bit. Here's my situation:
EXPLAIN ACCESS MODULE XYZ;
SELECT * FROM SCHEMA.ACCESS_PLAN;
In the ACMODE, ACNAME and LFS columns of the ACCESS_PLAN table, I get the following:
ACMODE ACNAME LFS
S DBKEY-IX N
The table has a clustered DBKEY-IX which resides in a unique area. The "S" in ACMODE is documented as "Sequential" (there is also an "I" value for ACMODE if indexed). The actual named "DBKEY-IX" appears in the ACNAME column. Now, the real mystery: LFS is N. LFS stands
for "leaf scan", or as we call it in "legacy" IDMS, Level 0 of the index. Since LFS is set to N and ACMODE is "S", is this ACCESS MODULE using the index or not?
JB Moore
Jim Moore
Alan Fields
Not absolutely certain about this one, but here's an educated
guess................
The answer, I think, is it is not using the index, at least not directly
(read on before throwing stones, please ;-)). Being that the index is in
DBkey sequence (right?), a scan of the data area is actually faster than
chasing the index (it saves the hassle of working through the SR8's and
gets exactly the same results).
A leaf scan happens when a WHERE clause specifies more columns than the
index contains. It is not a scan of the level 0 index entries, but of the
data records. The optimizer will pick the index with the closest match to
the columns of the selection criteria and then examine each selected row
for the remaining values (those not included in the index).
For more on EXPLAIN and a more people friendly presentation of the results,
check out APAR GI36956 on TCC. Hope it helps.
Alan Fields
VF Jeanswear
Greensboro, NC
336-332-5631
Alan_...@vfc.com
Jim Moore <conl...@IX.NETCOM.COM>@LISTSERV.IUASSN.COM> on 03/27/2001
11:43:25 AM
Please respond to IDMS Public Discussion Forum <IDM...@LISTSERV.IUASSN.COM>
Sent by: IDMS Public Discussion Forum <IDM...@LISTSERV.IUASSN.COM>
To: IDM...@LISTSERV.IUASSN.COM
cc:
Subject: [IDMS-L] CA-IDMS Explain
Jim Moore
Thank you for your always excellent input. The SQL table has no real key. So, we are using the DBKEY, system-owned index to cluster the rows (or so we thought). This table is merely "staging" area where data coming from an Oracle database sits until a timer-initiated "legacy" program can grab it.
When we asked the developers how much room they needed, they couldn't give us an accurate space estimate. So, we gave the area that contains the data rows about 3,000 pages. The index area is much smaller.
SQL or not, the indexes are system-owned SR7/SR8 b-trees where the SR7 has a CALC location mode. So, getting to the FIRST key and then navigating in a NEXT direction across the bottom of the index is very quick and presents no problem. Question: Are you saying that the rows aren't retrieved by this sequential scan through the sequence set of the index?
The data rows are physically positioned way out in the middle of the 3,000 pages (clustered around the index?). They take up less than 100 pages and then the remaining pages are all empty.
Area sweep (or tablespace scan, if you like) scans many, many empty pages.
Looks like we need to downsize this area, add bogus index sets to force the optimizer to take different paths or some combination thereof.
Jim Moore
BTW, I'm having a lot of problems with my ISP today. I apologize if I send a post multiple times.
IDMS Public Discussion Forum <IDM...@LISTSERV.IUASSN.COM> wrote:
> Hi Jim --
Not absolutely certain about this one, but here's an educated
guess................
The answer, I think, is it is not using the index, at least not directly
(read on before throwing stones, please ;-)). Being that the index is in
DBkey sequence (right?), a scan of the data area is actually faster than
chasing the index (it saves the hassle of working through the SR8's and
gets exactly the same results).
A leaf scan happens when a WHERE clause specifies more columns than the
index contains. It is not a scan of the level 0 index entries, but of the
data records. The optimizer will pick the index with the closest match to
the columns of the selection criteria and then examine each selected row
for the remaining values (those not included in the index).
For more on EXPLAIN and a more people friendly presentation of the results,
check out APAR GI36956 on TCC. Hope it helps.
Alan Fields
VF Jeanswear
Greensboro, NC
336-332-5631
Alan_...@vfc.com
Jim Moore @LISTSERV.IUASSN.COM> on 03/27/2001
11:43:25 AM
Please respond to IDMS Public Discussion Forum
Sent by: IDMS Public Discussion Forum
Alan Fields
Your existing design should work fine. The rows are positioning just like
you told them to, clustered around the index.
Like I said, I'm not absolutely sure whether the optimizer has selected the
index or an area scan, but I suspect, *in this case*, it is *not* using the
index. Which way he goes is dependent on more than just the existence of
an index. It also depends on the number of rows in the table (if you don't
UPDATE STATISTICS, it will use the estimated row count) and the number of
pages in the area. Remembering that all numbers are relative, if there are
a small number of rows vs. a large area, then the optimizer ought to pick
the index, and, conversely, a small area with lots of rows will generate an
area scan. I don't know if the fact that this is a DBKEY index (sequencing
physically rather than logically) enters into the calculations.
In any event, given enough clues, the optimizer is usually quite good at
selecting the best path through the table. However, without any hints, it
generally has no choice but to fall back on a good, old fashioned area
sweep.
Alan
Jim Moore <conl...@IX.NETCOM.COM>@LISTSERV.IUASSN.COM> on 03/27/2001
03:06:48 PM
Please respond to IDMS Public Discussion Forum <IDM...@LISTSERV.IUASSN.COM>
Sent by: IDMS Public Discussion Forum <IDM...@LISTSERV.IUASSN.COM>
To: IDM...@LISTSERV.IUASSN.COM
cc:
Subject: Re: [IDMS-L] CA-IDMS Explain
DIRK COETSEE
S DBKEY-IX N
It will scan the index entries sequentially and read each corresponding
database record. LFS = Y means that the index contains all the data it needs
i.e. it will only use the index and will not read the corresponding database
record.
regards
Dirk Coetsee
Alan
Alan,
Jim Moore
JB Moore
____________________________________________________________________
Get free email and a permanent address at http://www.netaddress.com/?N=1