SQL/MP Installation and Management Guide

Adding, Altering, Removing, and Renaming
Database Objects
HP NonStop SQL/MP Installation and Management Guide—523353-004
7-4
Adding Views
For additional information and guidelines related to adding a table, see Creating Base
Tables on page 5-10.
Adding Views
Adding views on existing tables does not affect existing database dependencies. To
add a view, use the CREATE VIEW statement, following these steps:
1. Start an SQLCI session. Enter a LOG command to initiate a log file for the
statements and commands entered in this session. Keep the log for your records.
2. Determine the names of any tables whose columns you want to include in the view.
Shorthand views can also refer to other views.
3. Determine the column names of the view and the column names of the underlying
table or tables of the view. To display the columns of the underlying table or tables,
you can either use the INVOKE command or query the COLUMNS table of the
catalog.
4. Enter the CREATE VIEW statement, or put the statement text into an EDIT file and
enter an OBEY command to run the statement from the file.
5. Alter the security and ownership of the new view, if necessary.
6. For an audited view, make a new TMF online dump.
For additional information and examples of creating views, see Creating Views of Base
Tables on page 5-38.
Adding Indexes
Use the CREATE INDEX statement to add an index to an existing table. The statement
both creates and loads the new index.
Evaluating the Benefit of a New Index
Knowing when to add an index to improve performance requires a detailed
understanding and analysis of your application. Following are the ways to collect data:
•
Analyze the programs and ad hoc queries for the columns used in the DISTINCT,
GROUP BY, ORDER BY, and WHERE clauses, and in join operations.
•
Run the SQL compiler with the EXPLAIN option to obtain a report on the access
paths the compiler chooses for the programs.
•
Analyze Measure statistics on SQL statements.
If you need to determine whether an index can benefit performance, you could test the
performance before implementing the index in the production system.