Cori,
With all due respect, I have to disagree with your fellow developer.
Your fellow developer says that Oracle database views will speed up performance because the access path has already been determined. Well, the first thing wrong with that is that Oracle does NOT predetermine the access path for a view. But even if it did, it is not always desirable to do so. A predetermined access path may not be the BEST access path for a given query.
Consider, for example, that you have a view like this:
CREATE VIEW V1 AS
SELECT A.KEY1, A.F1, A.F2, B.F3, B.F4
FROM A, B
WHERE A.KEY1 = B.KEY1
Assume the predetermined access path for this query involves a full table scan of A and an indexed read of B. (Assume both A and B are indexed on KEY1.)
Now, consider the query:
SELECT F1, F2 FROM V1 (1)
F1 and F2 are only in table A, BUT THE VIEW IS GOING TO READ BOTH TABLES ANYWAY. Hardly the best access method for the query.
Also consider the query:
SELECT F1, F2 FROM V1 WHERE KEY1 = ‘X’ (2)
IF Oracle used predetermined access paths, you would still do a full table scan of A – even though you’ve specified a specific value for KEY1 which should tell Oracle to use the index. Fortunately, Oracle does NOT predetermine access paths. For the above query, Oracle will do an indexed read of A and then do an indexed read of B.
Now, while Oracle does not technically predetermine the access path, it does sort of from the fact that the SQL gets “reused”. Once a query is run, the text of the query and the access method are stored in memory. If the SAME QUERY is submitted again, Oracle looks to see if it is already stored in memory. If it is, then Oracle can skip the optimizing phase and go straight to execution. The problem is, it has to be THE SAME QUERY. The two query examples I’ve given above are different, so Oracle will have to optimize (parse) both. However, if I submit a third query:
SELECT F1, F2 FROM V1 WHERE KEY1 = ‘Y’ (3)
Oracle will see that that is the same query as the second example above, and it will not reparse it.
Of course, the optimization phase of a query is usually very minor. You’re usually talking about milliseconds. If you have a query that takes 2 minutes to return data, you are not going to speed it up by skipping the optimization phase.
There’s one more thing to keep in mind. In some cases, Oracle resolves the text of the view first, then resolves the text of the query. In those cases, you MAY benefit from the feature mentioned above where Oracle will skip the optimizing phase. But in other cases, the text of the view is integrated into the query itself and THEN optimized.
In the second query above (KEY1=‘X’), I indicated that Oracle will actually do an indexed read of A and then an indexed read of B. This is because the view text gets integrated into the query itself, and then optimized.
If, however, the view text contained a set operation – GROUP BY, SUM, COUNT, DISTINCT, etc. – then Oracle must parse and thus optimize the view first, and then the query. For example, if I modified V1:
CREATE OR REPLACE VIEW V1 AS
SELECT DISTINCT A.KEY1, A.F1, A.F2, B.F3, B.F4 FROM A, B
WHERE A.KEY1 = B.KEY1
Then query (2) will, unfortunately, do a full table scan of A instead of an indexed read.
Now, none of this means that views CAN NOT improve performance, or that they do not serve a useful purpose. They are extremely useful. But a view’s purpose is not to optimize performance. So using a view to optimize performance is somewhat like using a screwdriver to drive a nail.
Erich Hurst
Compaq Computer Corporation
(281) 514-9445
Erich.Hurst@Compaq.com
Listserv Archives (BOB member since 2002-06-25)