Business Benefits
Combine commonly used select queries from multiple tables into a single view and save time.
Plan the view structure by identifying which columns from which tables need to be included, and use the SELECT statement to make sure all necessary columns are included.
Plan out the columns that need to be included in the view and what tables they would be obtained from.
Use a SELECT statement to make sure all columns are included, and add or remove any columns as necessary. For example:
SELECT
table1.column1,
table1.column2,
table2.column3,
table2.column4
FROM
table1,
table2
WHERE
table1.column1 = table2.column1
AND table1.column3 = table2.column5
Use the CREATE OR REPLACE VIEW statement to create, name, and update a view.
For example, where My_sample_view is the name of the view:
CREATE OR REPLACE VIEW My_Sample_View AS
SELECT
table1.column1,
table1.column2,
table2.column3,
table2.column4
FROM
table1,
table2
WHERE
table1.column1 = table2.column1
AND table1.column3 = table2.column5;
Use the SELECT statement to execute the view and verify output and performance.
Use the following syntax to check the view’s output:
SELECT * from My_Sample_View;
Use the CREATE INDEX statement to create indexes on columns that have a numeric or long string output to improve performance when executed.
Executing views should take no longer than 30 seconds. If they do, you may need to create indexes on columns that have numeric or long string outputs.
For example:
CREATE INDEX ind_column1 ON table1(column1)
Use the ALTER VIEW statement to modify or update the already created view without dropping it.
For instance, the following example shows how to execute the view while excluding table2.column4.
ALTER VIEW My_Sample_View AS
SELECT
table1.column1,
table1.column2,
table2.column3
FROM
table1,
table2
WHERE
table1.column1 = table2.column1
AND table1.column3 = table2.column5;
Last edited by @hesh_fekry 2023-11-14T11:00:57Z