Different access path when going through alias? 
Author Message
 Different access path when going through alias?

Platform is DB2 AIX version 7.

We have 10 tables created under schema aaa.  We have an alias for each
table created under schema bbb.  We have a 10 table join using
unqualified table names.  If we run this query as user aaa it returns
fairly quickly.  If we run it as user bbb it runs a long time.
Explain shows that different access paths are being used for each of
these.  The explains show the same tables are being accessed, which it
should since it's exactly the same query.

I can't think of a valid reason that this would happen.  Does anyone
have any ideas?

Thanks.
Steve Toeniskoetter
American Electric Power



Tue, 28 Dec 2004 05:01:07 GMT
 Different access path when going through alias?

Hi Steve,

Do some of the tables occur multiple times in the join?
Not sure if that means anything but some query rewrite rule might fire (or
not) because
it finds a shared derived table (an alias looks like a "SELECT * FROM
source").

Cheers
Serge

--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Canada



Tue, 28 Dec 2004 05:44:33 GMT
 Different access path when going through alias?
Hi Serge,

Thanks for the reply.  All 10 tables in the join are distinct tables,
and there are no sub-selects or other things like that in the query.

I went ahead and opened a pmr with support.

Steve Toeniskoetter
American Electric Power

Quote:

> Hi Steve,

> Do some of the tables occur multiple times in the join?
> Not sure if that means anything but some query rewrite rule might fire (or
> not) because
> it finds a shared derived table (an alias looks like a "SELECT * FROM
> source").

> Cheers
> Serge



Wed, 29 Dec 2004 02:03:55 GMT
 Different access path when going through alias?
The path is based on the objects known in the catalog.

You have tables like  "AAA.Tablex" with probably some indexes defined on it.
Otherside you have aliases (just another name but also another object) like
"BBB.Tablex" with no indexeson BBB tables.

CREATE index <idxName> on table AAA.xxxxxx.......means that the indexe is
just known for AAA table

 Eplain probably gives a IndexScan with AAA and tableScan with BBB tables


Quote:
> Platform is DB2 AIX version 7.

> We have 10 tables created under schema aaa.  We have an alias for each
> table created under schema bbb.  We have a 10 table join using
> unqualified table names.  If we run this query as user aaa it returns
> fairly quickly.  If we run it as user bbb it runs a long time.
> Explain shows that different access paths are being used for each of
> these.  The explains show the same tables are being accessed, which it
> should since it's exactly the same query.

> I can't think of a valid reason that this would happen.  Does anyone
> have any ideas?

> Thanks.
> Steve Toeniskoetter
> American Electric Power

---
Outgoing mail is certified Virus Free by AVG Control Center.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.373 / Virus Database: 208 - Release Date: 2/07/2002


Wed, 29 Dec 2004 06:02:55 GMT
 Different access path when going through alias?

Quote:

> The path is based on the objects known in the catalog.

True, but the alias's name resolution maps to the real table (in your schema
below, that is AAA). Referencing the alias name in schema BBB should pick up
that index. There is something else going on that is not apparent. The PMR
(which original poster opened), is probably the best route for this - unless,
more light gets shed on this in the newsgroup.
Quote:

> You have tables like  "AAA.Tablex" with probably some indexes defined on it.
> Otherside you have aliases (just another name but also another object) like
> "BBB.Tablex" with no indexeson BBB tables.

> CREATE index <idxName> on table AAA.xxxxxx.......means that the indexe is
> just known for AAA table

>  Eplain probably gives a IndexScan with AAA and tableScan with BBB tables



> > Platform is DB2 AIX version 7.

> > We have 10 tables created under schema aaa.  We have an alias for each
> > table created under schema bbb.  We have a 10 table join using
> > unqualified table names.  If we run this query as user aaa it returns
> > fairly quickly.  If we run it as user bbb it runs a long time.
> > Explain shows that different access paths are being used for each of
> > these.  The explains show the same tables are being accessed, which it
> > should since it's exactly the same query.

> > I can't think of a valid reason that this would happen.  Does anyone
> > have any ideas?

> > Thanks.
> > Steve Toeniskoetter
> > American Electric Power

> ---
> Outgoing mail is certified Virus Free by AVG Control Center.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.373 / Virus Database: 208 - Release Date: 2/07/2002



Fri, 31 Dec 2004 08:48:44 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. ER Model - different answers for different join paths

2. ER Model - different answers for different join paths

3. Insert query uses different access path than select query

4. ER Model - different answers for different join paths

5. How to get the path from an alias

6. Reading Path to an Alias

7. Extracting path from BDE alias or database?

8. Alias Path?

9. Getting HDD-Path by Alias

10. Finding the path for an alias

11. aliases path at run time

12. Change path of BDE-alias at run-time


 
Powered by phpBB® Forum Software