Sybase 12.4.2 manual Using join indexes, Allowing enough disk space for deletions, 453

CHAPTER 12 Managing System Resources

You should create either an LF or HG index in addition to the default index on each column referenced by the WHERE clause in a join query. Adaptive Server IQ cannot guarantee that its query optimizer will produce the best execution plan if some columns referenced in the WHERE clause lack either an LF or HG index. Non-aggregated columns referenced in the HAVING clause must also have the LF or HG index in addition to the default index. For example:

SELECT c.name, SUM(l.price * (1 - l.discount))

FROM customer c, orders o, lineitem l

WHERE c.custkey = o.custkey

AND o.orderkey = l.orderkey

AND o.orderdate >= "1994-01-01"

AND o.orderdate < "1995-01-01"

GROUP by c.name

HAVING c.name NOT LIKE "I%"

AND SUM(l.price * (1 - l.discount)) > 0.50

ORDER BY 2 desc

In addition to the default index, all columns in this example beside l.price and l.discount should have an LF or HG index.

Using join indexes

Users frequently need to see the data from more than one table at once. This data can be joined at query time, or in advance by creating a join index. You can usually improve query performance by creating a join index for columns that must be joined in a consistent way.

Because join indexes require substantial time and space to load, you should create them only for joins needed on a regular basis. Adaptive Server IQ join indexes support one-to-many and one-to-one join relationships.

Allowing enough disk space for deletions

When you delete data rows, Adaptive Server IQ creates a version page for each database page that contains any of the data being deleted. The versions are retained until the delete transaction commits. For this reason, you may need to add disk space when you delete data. See “Overlapping versions and deletions” for details.

453