Re: Index advice requested.

In article <3bf951f1.20904248_at_ausnews.austin.ibm.com>, "Ed Stevens" <Ed_Stevens_at_nospam.noway.nohow> wrote:

I see a lot of hash join action here, obviously, and most of it driven by REMOTE accesses. It's pulling the whole table across the network and hash joining it against a local table, three separate times. That may seem bad, but the alternative (index lookups over the wire) is a very grim thing to behold.

REMOTE accesses are driven by SQL queries, and I think there are some real limitations on what the query planner can ask for, eg I don't know if it can use ORDER BY to get sorted data, and I don't know if it can find out about indexes, stats, etc. on the remote end.

I suspect the output of a hash join may simply be best suited for another hash join, yielding the hash-o-rama we see below. You probably won't see a non-hash join until the remote access is changed.

A few things to try:

1. Get the sql that goes with those REMOTE operators. It's in the explain table somewhere. It might tell you, for instance, how many columns are being pulled along with the keys. Post it for us to enjoy.
2. Reduce the REMOTEs. If you can stage the table locally, you'll eliminate three problem joins (against the same table, no less). Try it on a temporary basis to see if there's an improvement.
3. Clarify what the selectivity is here - is the idea that too much data is being joined (you only need a small fraction), or that the joins are inefficient? Hash joins seem like a good way to go for whole-table joins.

Kendall

> OPERATION OPTIONS OBJECT NAME ORDER OPT
> ------------------------- ---------------
> ----------------------------------------------- ------------ ------
> SELECT STATEMENT COST = 7171
> 0-0-7171 CHOOSE
> SORT UNIQUE
> 1-0-1
> HASH JOIN OUTER
> 2-1-1
> HASH JOIN OUTER
> 3-2-1
> HASH JOIN OUTER
> 4-3-1
> HASH JOIN OUTER
> 5-4-1
> FILTER
> 6-5-1
> HASH JOIN OUTER
> 7-6-1
> HASH JOIN
> 8-7-1
> TABLE ACCESS FULL TABLE_01
> 9-8-1 ANALYZ
> REMOTE
> 10-8-2
> TABLE ACCESS FULL TABLE_02
> 11-7-2 ANALYZ
> TABLE ACCESS FULL TABLE_03
> 12-5-2 ANALYZ
> REMOTE
> 13-4-2
> REMOTE
> 14-3-2
> TABLE ACCESS FULL TABLE_04
> 15-2-2 ANALYZ
>
>
>
> TBL NDX POS COL
>
> -------------------- -------------------- -------- --------------------
>
> TABLE_01 TBL1_IDX1 1 TBL1_REQ_NBR
>
> 2 TBL1_SPLT_SEQNBR
>
>
>
> TBL1_IDX2 1 TBL1_PO_NBR
>
> 2 TBL1_PO_AMDT_NBR
>
> 3 TBL1_REQ_NBR
>
> 4 TBL1_SPLT_SEQNBR
>
> 5 TBL1_RFQ_NBR
>
>
>
>
> TBL NDX POS COL
>
> -------------------- -------------------- -------- --------------------
>
> TABLE_02 TBL2_IDX1 1 TBL2_PO_NBR
>
> 2 TBL2_PO_AMDT_NBR
>
>
>
> TBL2_IDX2 1 TBL2_PRIM_REQ_NBR
>
> 2 TBL2_PRIM_SEQNBR
>
>
>
> TBL2_IDX3 1 TBL2_PO_NBR
>
> 2 TBL2_PO_AMDT_NBR
>
> 3 TBL2_PDF_IND
>
> 4 TBL2_ADJ_TOT_CSTAMT
>
>
>
> TBL2_IDX4 1 TBL2_PO_STA_CDE
>
> 2 TBL2_PO_NBR
>
>
>
> TBL2_IDX5 1 TBL2_PO_AMDT_NBR
>
> 2 TBL2_PRIM_REQ_NBR
>
> 3 TBL2_PRIM_SEQNBR
>
> 4 TBL2_PO_NBR
>
>
>
>
> TBL NDX POS COL
>
> -------------------- -------------------- -------- --------------------
>
> TABLE_03 TBL3_IDX1 1 TBL3_RFQ_NBR
>
>
>
> TBL NDX POS COL
>
> -------------------- -------------------- -------- --------------------
>
> TABLE_04 TBL4_IDX1 1 TBL4_RFQ_NBR
>
> 2 TBL4_SEQ_NBR
>
> 3 TBL4_QTE_SEQ_NBR
>
>
>
>
>
> --
> Ed Stevens
> (Opinions expressed do not necessarily represent those of my employer.)
Received on Tue Nov 20 2001 - 02:15:36 CST