are views that query from other views also refreshed automatically-CodePudding

Let's say I have a view that queries from another view:

create view another_view as (select * from my_table);

create view one_view as (select * from another_view);

select * from one_view;

When I issue the last statement select * from one_view; does that also refreshes and queries another_view ?

CodePudding user response：

Views are not persisted anywhere so they cannot be "refreshed". When you query from a view then the SQL engine will rewrite the query to use the view's query and select directly from the underlying table(s) applying all the joins, filters, etc. from the view.

Given the setup:

CREATE TABLE my_table (value) AS
SELECT 1 FROM DUAL;

create view another_view as (select * from my_table);

create view one_view as (select * from another_view);

Then you can look at the explain plan for selecting from the view:

EXPLAIN PLAN FOR
select * from one_view;

Then:

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

Which outputs:

PLAN_TABLE_OUTPUT
Plan hash value: 3804444429
 
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |     3 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| MY_TABLE |     1 |     3 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Oracle does not select from any view, it rewrites the query to select directly from the underlying table. Therefore there is no concept of the view having to "refresh"; it is always whatever is current in the table.

fiddle