Filter
Top Products
CHAPTER 12 Managing System Resources
453
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.