Manage views

This topic describes how you can create, modify, and delete views.

A view displays the result of a query. You can use views in most cases where you can use tables. When the data you frequently access is distributed across multiple tables, a view is the best choice.

Create a view

You can use the CREATE VIEW statement to create a view.

Example: Creating the stock_item view This view references values from the stock table and the item table.

obclient> CREATE  VIEW stock_item
     AS
     SELECT /*+ leading(s) use_merge(i) */
     i_price, i_name, i_data, s_i_id, s_w_id, s_order_cnt, s_ytd, s_remote_cnt, s_quantity, s_data, s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05, s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10
     FROM stok s, item i
     WHERE s.s_i_id = i.i_id;
Query OK, 0 rows affected (0.01 sec)

Modify a view

You can use the CREATE OR REPLACE VIEW statement to modify a view.

Example: Modifying the stock_item view

obclient> CREATE OR REPLACE VIEW stock_item
     AS
     SELECT /*+ leading(s) use_merge(i) */
     i_price, i_name, i_data, s_i_id, s_w_id, s_order_cnt, s_ytd, s_remote_cnt, s_quantity, s_data, s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05, s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10
     FROM stok s, item i
     WHERE s.s_i_id = i.i_id;
Query OK, 0 rows affected (0.02 sec)

Delete a view

You can use the DROP VIEW statement to delete one or more views. Deleting a view does not delete the tables that the view references.

If you delete a view that is referenced by another view, queries on the view that references the deleted view fail.

Before you can delete a view, make sure you have the DROP privilege on the view.

For example, you can execute the following statement to delete the V1 view:

obclient> DROP VIEW V1;