Sybase 12.4.2 User Manual

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. 

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. 

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.