Slide 30
Slide 30 text
Database Views
Database views have been used for decades to help simplify both ad-hoc queries and reporting
But the problem with defining views is they are either too specific or not specific enough
In this example, we need a view to help quickly find details about orders that are waiting to ship
Copyright © 2023, Oracle and/or its affiliates
CREATE OR REPLACE VIEW orders_waiting
AS
SELECT i.*
FROM orders o, order_items i
WHERE o.order_status > 6
AND o.order_total >= 100
AND o.order_id = i.order_id’;
Extremely specific, efficient view
includes no unnecessary data but not reusable
CREATE OR REPLACE VIEW order_details
AS
SELECT o.order_status, o.order_total,
o.cust_id, o.order_date, o.rep,
o.order_mode, o.promotion_id, i.*
FROM orders o, order_items i
WHERE o.order_id = i.order_id;
Nonspecific view that includes
lots of unnecessary data , so it can be reusable