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.
Summary of Contents for Adaptive Server IQ 12.4.2
Page 1: ...Administration and Performance Guide Adaptive Server IQ 12 4 2 ...
Page 16: ...xvi ...
Page 20: ...Related documents xx ...
Page 40: ...Compatibility with earlier versions 20 ...
Page 118: ...Troubleshooting startup shutdown and connections 98 ...
Page 248: ...Importing data by replication 228 ...
Page 306: ...Integrity rules in the system tables 286 ...
Page 334: ...Cursors in transactions 314 ...
Page 396: ...Users and permissions in the system tables 376 ...
Page 438: ...Determining your data backup and recovery strategy 418 ...
Page 484: ...Network performance 464 ...
Page 500: ...System utilities to monitor CPU use 480 ...
Page 514: ...Characteristics of Open Client and jConnect connections 494 ...
Page 536: ...Index 516 ...
