Inline view hangs...?

From: <jason.judge_at_virgin.net>
Date: Tue, 12 Dec 2000 15:27:48 GMT
Message-ID: <915g5a$ggl$1_at_nnrp1.deja.com>

Hi,

I have an inline view that hangs when I add a WHERE clause to my query, but returns immediately when there is no WHERE clause. To illustrate, the following query will return 114 rows:

SELECT EIVIEW.*
FROM (

      SELECT P.SEGMENT1,
             T.CARRYING_OUT_ORGANIZATION_ID,
             EI.EXPENDITURE_TYPE,
             P.PROJECT_TYPE,
             P.NAME,
             P.PROJECT_STATUS_CODE,
             P.DESCRIPTION,
             EI.QUANTITY,
             EI.EXPENDITURE_ITEM_DATE,
             EI.BURDEN_COST,
             EI.ACCRUED_REVENUE,
             EI.NON_LABOR_RESOURCE
      FROM   PA_EXPENDITURES E,
             PA_EXPENDITURE_ITEMS EI,
             PA_TASKS T,
             PA_PROJECTS P
      WHERE  P.SEGMENT1 = '9596959'
      AND    P.PROJECT_ID = T.PROJECT_ID
      AND    T.TASK_ID = EI.TASK_ID
      AND    EI.EXPENDITURE_ID = E.EXPENDITURE_ID
      ) EIVIEW

--WHERE EIVIEW.EXPENDITURE_TYPE = 'Ind Norm'

BUT remove the comment on the WHERE clause and it does not return for AGES. The execution plan is IDENTICAL in both cases and there are no full-table scans.

As I understand it, the inline view (EIVEIW) should execute first and the WHERE-clause should then be applied to the results. This should not take a long time to do. Am I looking at this too simplistically?

We are using Oracle 8.0.5.1.0 and this has been driving me potty for the last three days! Any clues what may be happening?

Regards,

Jason Judge

P.S. Query structure is:

project_number            [expenditure_type]
   |                             |
   v                             v

projects --<E tasks --<E expenditure_items --<E expenditures

Execution plan is:

SELECT STATEMENT Optimizer=RULE
  NESTED LOOPS
    NESTED LOOPS

      NESTED LOOPS
        TABLE ACCESS (BY INDEX ROWID) OF PA_PROJECTS_ALL
          INDEX (UNIQUE SCAN) OF PA_PROJECTS_U2 (UNIQUE)
        TABLE ACCESS (BY INDEX ROWID) OF PA_TASKS
          INDEX (RANGE SCAN) OF PA_TASKS_U2 (UNIQUE)
      TABLE ACCESS (BY INDEX ROWID) OF PA_EXPENDITURE_ITEMS_ALL
        INDEX (RANGE SCAN) OF PA_EXPENDITURE_ITEMS_N18 (NON-UNIQUE)
    TABLE ACCESS (BY INDEX ROWID) OF PA_EXPENDITURES_ALL
      INDEX (UNIQUE SCAN) OF PA_EXPENDITURES_U1 (UNIQUE)

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Dec 12 2000 - 16:27:48 CET