RE: In SQL, can you create an index with JOIN's?

Hi Duane


I believe you would use a view to have key fields from different files. You might want to be sure to have indexes on some or any of the dials (the individual tables in the join), in order to support performance.

Visual Explain can help with whether you need more indexes.


Cheers
Vern


On Mon, 15 May, 2023 at 3:17 PM, Danner, Duane <ddanner@xxxxxxxxxxxx> wrote:


To: midrange systems technical discussion

Is there anyway of getting key fields from two different files?

Thanks

Duane Danner

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx>> On Behalf Of Alan Cassidy
Sent: Tuesday, April 4, 2023 4:59 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: In SQL, can you create an index with JOIN's?

Thank you, Birgitta


On 4/2/2023 5:05 AM, Birgitta Hauser wrote:

Indexes can only be created over 1 Table/PF.
Joined logical files can be created over multiple files ... but the
access path can only be built over one of the joined files, i.e. all
key fields must be from the same file/table!

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<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx>> On Behalf Of
Alan Cassidy
Sent: Saturday, 1 April 2023 22:44
To: midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: In SQL, can you create an index with JOIN's?

I seem to remember that you can only create indexes over one table,
but I have a vague memory of JOIN tables being introduced.

Or did I see something in a different implementation with something
like that?

I can't find the answer to that in the docs.

--Alan Cassidy

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx> To
subscribe, unsubscribe, or change list options,
visit:
https://list/
s.midrange.com<http://midrange.com>%2Fmailman%2Flistinfo%2Fmidrange-l&data=05%7C01%7Cddanne
r%40awtx-itw.com%7C715c830e0df24f190f6f08db3557d05f%7C7fa3ed9ffff94884
a6e28dd98886b775%7C0%7C0%7C638163001741533888%7CUnknown%7CTWFpbGZsb3d8
eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3
000%7C%7C%7C&sdata=ppT%2BdFL5mZAFjRDrNCb%2FO8Hp6OW5WM1ewnvaCKf4eSc%3D&
reserved=0 or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto: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<mailto: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<mailto: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<mailto: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<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.