Sybase 12.4.2 manual Working with indexes, Introduction to indexes, 132

Working with indexes

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.

132