Explain Plan

From: Rob Woods <rswoods_at_nwlink.com>
Date: Thu, 3 Jun 1999 12:29:52 -0700
Message-ID: <3756d815@news.nwlink.com>

Hi,

We have a client with a problem..

They're running 7.1.4, and have two instances, DEMO & PROD.

Last weekend, their admin deleted old data from several tables in PROD, then exported & re-imported the tables, and re-applied all the indexes. The problem is that reports that used to take < 5 minutes on PROD now take over 3 hours to run. I have run explain plan in both instances for the identical sql statement(from one of the reports), and get different results(below). Obviously the full table scan in PROD is the killer here, but I don't know what I am missing. I have examined the indexes in both DEMO & PROD, and they look to be identical.

Any suggestions?

TIA, Rob Woods

DEMO>@ex1

QUERY_PLAN

NESTED LOOPS
NESTED LOOPS

      TABLE ACCESS BY ROWID QUOTE_MODEL_DETAIL
        INDEX RANGE SCAN QUOTE_MODEL_DETAIL_IDX2
      TABLE ACCESS BY ROWID OEDETL
        INDEX RANGE SCAN OEDETL_PRIME
    TABLE ACCESS BY ROWID OEHEAD
      INDEX UNIQUE SCAN OEHEAD_PRIME

8 rows selected.

PROD>@ex1

QUERY_PLAN

NESTED LOOPS
MERGE JOIN

      SORT JOIN
        TABLE ACCESS FULL OEDETL
      SORT JOIN
        TABLE ACCESS BY ROWID QUOTE_MODEL_DETAIL
          INDEX RANGE SCAN QUOTE_MODEL_DETAIL_IDX2
    TABLE ACCESS BY ROWID OEHEAD
      INDEX UNIQUE SCAN OEHEAD_PRIME

9 rows selected.

PROD> --

rwoods.not_at_canright.com
remove .not for email reply

Received on Thu Jun 03 1999 - 14:29:52 CDT