Dennis;
It took as long to create the index as it did to create the LF because they do not share the access path. An LF has a 4K(?) space and an index has a 64K space. If you created the LF after the index, the LF will share the access path with the index and would be created "instantly".
I have run across this before, and haven't gotten a definitive answer as to why the optimizer asks for the index and then ignores it. Brigitta or Chuck may be able to give a better answer but I think it comes down to the optimizer got what it thought it wanted but what it got wasn't what it really needed. :)
Duane Christen
--
Duane Christen
Senior Software Engineer
(319) 790-7162
Duane.Christen@xxxxxxxxxx
Visit PAETEC.COM
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dennis Lovelady
Sent: Monday, February 01, 2010 9:08 AM
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL query results in CPI432F
What happens if you create a SQL index? Same message? Also, I seem to
remember that sometimes the optimizer "times out" and does not have
the time to examine every index, so it can advise an already created
index (at least, this seems to apply to the Index Advisor in Visual
Explain)
Same thing happens after:
CREATE INDEX lib/TAPTALTC9
ON lib/TAPTC
(TCER, TCSTGE, TCEN, TCDTH)
(Oddly, it took as long to create this index as it did to create the LF even though the LF still existed at the time.)
If this were due to timeout, would the newly-created index appear in the list of "considered access paths?" (CPI432C) It does in fact. Rejected for reason 5.
It was a matter of curiosity - there are plenty of other reasons this particular SQL will operate inefficiently. Now my interest is in whether we should pursue some PTF at V5R3 for this, or it it's expected under certain
(what?) circumstances.
Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"If living conditions don't stop improving in this country, we're going to run out of humble beginnings for our great men."
-- Russell P. Askue
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
