3. Using the Tables Utility : Tables, Synonyms, Views, and Indexes
 
Share this page                  
Tables, Synonyms, Views, and Indexes
Before using the Tables Utility to access tables, synonyms, views, or indexes, you must understand how each is used and what operations you can perform on them in the Tables Utility:
Tables
Tables are used to store all the data in your database. Each row in a table holds an individual record that contains one or more related data items. Each column contains one particular type of data. You can use the Tables Utility to create and delete tables, to look at the structure of a table, and to retrieve or modify the data contained in a table.
Except for rows containing large objects (long varchar, byte, byte varying, and long byte data types), all rows in a given table are the same width, measured in bytes, with no row exceeding the lesser of the maximum configured row size and 32,000. The number of rows is limited only by disk space.
Each column is assigned a data type (see page Column Specification—Data Types) to indicate the type of data to be stored and the length of the data (or width of the column). For example, a column with a data type of varchar (25) can hold 25 text (ASCII) characters. A maximum of 1024 columns is allowed in a table. Each column has a name that uniquely identifies the column within the table.
You can access any table for which you have been granted the proper permissions. For more information on permissions, see the Database Administrator Guide.
Synonyms
A synonym is a redefinable label for a table, which you can use as an alternative to the actual table name. You can access a table with an existing synonym, but you cannot create synonyms in the Tables Utility. The list of tables from which you can choose contains only synonyms you or the DBA own, freely intermixed with table names. For more information on synonyms, see Access to Database Tables in the chapter "Fundamentals of Using Querying and Reporting Tools."
Views
A view is actually a special definition, or virtual table, constructed from one or more tables. A view is a way of looking at or updating data stored in tables and does not contain any data or exist in physical storage.
Views enable you to:
Limit a user's access to specific rows and columns of a table
Manipulate data from multiple tables as if all the data were contained in a single table
Gain access to aggregates (sets of data) as if they were individual columns of data
Views simplify retrieval and modify the user's view of data to only certain rows and columns in a table. For example, the table on which you base a view can contain the columns name, title, and hourly_rate. However, the view based on that table might only show columns for name and title and could be restricted to certain rows. Views also allow you to run queries and reports on specific subsets of data and to specify certain rows that fit particular criteria.
You can use the Tables Utility to look at the structure of a view, and to retrieve, modify, or perform computations on the data in the underlying tables. For example, while in the Tables Utility, you could access a particular view based on the emp and tasks tables to compute the average hourly rate of programmers (emp table) working on a particular project (tasks table).
Views are created with a query language such as SQL. You cannot create or destroy views from within the Ingres Menu or the Tables Utility. For detailed information on views, see the description of the create view command in your query language reference guide.
You can access any view for which you have been granted the proper permissions. For more information on permissions, see the Database Administrator Guide.
Indexes
An index is a table that indicates where data is stored in another table. It contains the locations of specified columns in the base table that are queried frequently. The index can speed up the retrieval of information.
You can examine an index's structure but cannot perform any other operations on it in the Tables Utility. You create an index on one or more columns of a table, using a query language such as SQL. Whenever a user enters a query based on the indexed column in the base table, the index helps locate the information quickly. Use of indexes is recommended to improve performance of the queries in your applications.
Indexes are created with the create index query language statement. You cannot create, destroy, or directly query or run reports on indexes from within the Ingres Menu or the Tables Utility. For more information on indexes, see the create index statement in your query language reference guide.