RE: Do any of you have a policy on rebuilding indexes?

However it may make a difference in the order the indexes were built.

Why?
This was necessary in CTE times, because the optimizer estimated all indexes in LIFO sequence ... and after certain time it got an time out. If there were "hundrets" of access plans timeout came before all access path were estimated ... and so the best before the timeout was taken.
With SQE optimization is a little different. The access paths are pre-sorted depending on the listed fields, the join columns, columns specified in the where clauses, group by and order by. Then first the indexes with which an IOA (Index only access) are checked. After all other indexes again sorted depending on including the most key columns from the WHERE Clauses (with =) ... other where clauses, Group by and Order by. Then each index is estimated one after the other and as soon as an index is "more expensive" then the previous one. The previous one is taken and optimization ends. IIRK there is no timeout anymore for the SQE.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization – Education – Consulting on IBM i

IBM Champion since 2020

"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"
„Train people well enough so they can leave, treat them well enough so they don't want to.“ (Richard Branson)

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Rob Berendt
Sent: Thursday, 30 March 2023 16:23
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Do any of you have a policy on rebuilding indexes?

In general I do not see the benefit. However it may make a difference in the order the indexes were built. For example, which is the proper order to build these two indexes?
order, line, receipts (handles partials) order, line or order, line order, line, receipts


On Thu, Mar 30, 2023 at 8:48 AM Mark Murphy <jmarkmurphy@xxxxxxxxx> wrote:

We have a client who wants to rebuild indexes, just because they
haven't been built for a long time (3-4 years). They think it will
improve performance. I've never rebuilt indexes to improve
performance. Does anyone have an opinion on this?
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.