Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 152 / 536

Working with indexes
132
You can use DBISQL to browse the information in these tables. Type the
following statement in the DBISQL command window to see all the columns
in the SYS.SYSVIEWS view:
SELECT *
FROM SYS.SYSVIEWS
To extract a text file containing the definition of a specific view, use a statement
such as the following:
SELECT viewtext FROM SYS.SYSVIEWS
WHERE viewname = ’DepartmentSize’;
OUTPUT TO viewtext.sql
FORMAT ASCII
Working with indexes
Performance is a vital consideration when designing and creating your
database. Adaptive Server IQ indexes dramatically improve the performance
of database searches over searches in traditional relational databases. Even
within Adaptive Server IQ, however, it is important to choose the right indexes
for your data, to achieve the greatest performance, and to make best use of
memory, disk, and CPU cycles.
Introduction to indexes
All IQ database columns with data need an index. When you create a database
in an IQ store, a default index is created automatically on every column of
every table. You can also choose from several other index types:
• Four column index types optimize specific types of queries on the indexed
column.
• Join indexes optimize queries that relate columns from two or more tables.
You will almost certainly want to supplement the default indexing by selecting
one or more indexes for many of the columns in your database. You will also
want to define join indexes for any table columns that are joined in a consistent
way in user queries. Select indexes based on the size of your database, the disk
space available, and the type of queries users submit.