Re: How do I tune 'star' outer join in ORA73?

From: Bart Loosvelt <bl_at_REMOVE_THIS_TO_GET_REAL_ADDRESS.hemmis.be>
Date: 2000/03/08
Message-ID: <38c70387.2429483@pchemmis>#1/1

On Tue, 07 Mar 2000 20:49:58 GMT, michael_bialik_at_my-deja.com wrote:

> 1. It is NOT "star" type of query.

Oops, I'm sorry if I confused anyone.

> 2. Post EXPLAIN PLAN.

The exact query is like this:

SELECT ...
FROM A, B, C,
  D, E, F, G,
  H RXOPERATOR, H SUPERVISOR, I, J

WHERE B.ID_B (+) = A.ID_B
  AND C.ID_C (+) = B.ID_C
  AND D.ID_D (+) = A.ID_D
  AND E.ID_E (+) = A.ID_E
  AND F.ID_F (+) = A.ID_F
  AND G.ID_G (+) = A.ID_G

  AND RXOPERATOR.ID_H (+) = A.ID_RX_OPERATOR   AND SUPERVISOR.ID_H (+) = A.ID_SUPERVISOR   AND I.ID_I (+) = A.ID_I
  AND J.ID_J (+) = I.ID_J
ORDER BY ID_A DESC

1. 1782 rows (result set should have exactly the same number of rows)
2. 547 rows
3. 401 rows
4. 5 rows
5. 82 rows
6. 284 rows
7. 38 rows
8. 33 rows
9. 110 rows
10. 149 rows

All join and sort expressions in the query are on indexed primary key columns. Statistics on all tables, columns and indexes have been computed. The execution plan looks like this:

SELECT STATEMENT Cost = 1586
  SORT ORDER BY
    HASH JOIN OUTER

      NESTED LOOPS OUTER
        HASH JOIN OUTER
          HASH JOIN OUTER
            HASH JOIN OUTER
              HASH JOIN OUTER
                HASH JOIN OUTER
                  HASH JOIN OUTER
                    HASH JOIN OUTER
                      HASH JOIN OUTER
                        TABLE ACCESS FULL A
                        TABLE ACCESS FULL D
                      TABLE ACCESS FULL H
                    TABLE ACCESS FULL H
                  TABLE ACCESS FULL I
                TABLE ACCESS FULL G
              TABLE ACCESS FULL J
            TABLE ACCESS FULL F
          TABLE ACCESS FULL E
        TABLE ACCESS BY ROWID B
          INDEX UNIQUE SCAN PKC_B
      TABLE ACCESS FULL C

Response time: 17 seconds on a fast development server, several minutes on the slow server at the customer's.

> 3. Try using hints:
> SELECT /*+ ORDERED INDEX( B B_PK ) INDEX( C C_PK ) INDEX( D D_PK )
> INDEX( E E_PK ) */
After I have implemented your suggestion the plan changed as follows:

SELECT STATEMENT Cost = 5575
  SORT ORDER BY
    HASH JOIN OUTER

      NESTED LOOPS OUTER
        HASH JOIN OUTER
          HASH JOIN OUTER
            HASH JOIN OUTER
              HASH JOIN OUTER
                HASH JOIN OUTER
                  HASH JOIN OUTER
                    HASH JOIN OUTER
                      NESTED LOOPS OUTER
                        TABLE ACCESS FULL A
                        TABLE ACCESS BY ROWID B
                          INDEX UNIQUE SCAN PKC_B
                      TABLE ACCESS BY ROWID C
                        INDEX FULL SCAN PKC_C
                    TABLE ACCESS BY ROWID D
                      INDEX FULL SCAN PKC_D
                  TABLE ACCESS BY ROWID E
                    INDEX FULL SCAN PKC_E
                TABLE ACCESS BY ROWID F
                  INDEX FULL SCAN PKC_F
              TABLE ACCESS BY ROWID G
                INDEX FULL SCAN PKC_G
            TABLE ACCESS FULL H
          TABLE ACCESS FULL H
        TABLE ACCESS BY ROWID I
          INDEX UNIQUE SCAN PKC_I
      TABLE ACCESS BY ROWID J
        INDEX FULL SCAN PKC_J

Response time: 22 sec on our server.

Thanks for your help,
Bart Received on Wed Mar 08 2000 - 00:00:00 CST