Re: Strange performance related question

The difference in the two views is based upon which table is selected from first.
The first view you describe;

CREATE VIEW depwork AS
SELECT
   worker.*,
   department.*
FROM
   worker,
   department
WHERE
   department.id = worker.dept;

should have the following execution plan;

SELECT STATEMENT GOAL: CHOOSE

        NESTED LOOPS
        TABLE ACCESS (FULL) OF 'DEPTARTMENT'
              TABLE ACCESS (BY ROWID) OF 'WORKER'
                  INDEX (RANGE SCAN) OF 'WORKDEPT' (NON-UNIQUE)

whereas the second one;

CREATE VIEW depwork AS
SELECT
   worker.*,
   department.*
FROM
   worker,
   department
WHERE
   department.id = (SELECT worker.dept FROM DUAL);

will have;

SELECT STATEMENT GOAL: CHOOSE

        NESTED LOOPS
         TABLE ACCESS (FULL) OF 'WORKER'
         TABLE ACCESS (BY ROWID) OF 'DEPTARTMENT'
             INDEX (UNIQUE SCAN) OF 'DEPTPK' (UNIQUE)
                 TABLE ACCESS (FULL) OF 'DUAL'

It is curious that the second view is actually the fastest as I would expect it to be slower under normal circumstances. I could hazard a guess that there is a poor distribution of workers amongst departments, but would need to investigate further to be sure. The second view could be re-written as;

CREATE VIEW depwork AS
SELECT
   department.*,
   worker.*
FROM
   worker,
   department
WHERE
   department.id = worker.dept;

to look less contrived.
Notice how I have switched the order of the tables around as they are evaluated from the 'bottom' up and it is therefore common to place the table which returns least rows as the last in the from clause.

Another possible reason for your curious behaviour could be if you are using the cost based optimiser and it is choosing a peculiar execution plan. It may be worth running EXPLAIN PLAN yourself on the select statements to see if they are as expected. If the Cost based Optimiser is to blame you could always try using a hint to force use of the rule based optimiser e.g. select /*+ RULE / .....etc.

Hope all this is clear,

Cheers,

Ian Received on Thu Oct 30 1997 - 00:00:00 CST