City);
Chapter 5: Developing and Implementing Applications 157
This command will create an index named I_CON_K_CUSTOMER_CITY on the Person.Address.
City column. Whenever the City column is accessed, the optimizer will evaluate the SQL used
to access the data and determine if the new index can be useful to improve the performance of
the access.
When creating tables based on abstract datatypes, you should consider how the columns
within the abstract datatypes will be accessed. If, like the City column in the previous example,
certain columns will commonly be used as part of limiting conditions in queries, they should be
indexed. In this regard, the representation of multiple columns in a single abstract datatype may
hinder your application performance, because it may obscure the need to index specific columns
within the datatype.
When you use abstract datatypes, you become accustomed to treating a group of columns as
a single entity, such as the Address columns or the Person columns. It is important to remember
that the optimizer, when evaluating query access paths, will consider the columns individually.
You therefore need to address the indexing requirements for the columns even when you are
using abstract datatypes. In addition, remember that indexing the City column in one table that
uses the ADDRESS_TY datatype does not affect the City column in a second table that uses the
ADDRESS_TY datatype. If there is a second table named BRANCH that uses the ADDRESS_TY
datatype, then its City column will not be indexed unless you explicitly create an index for it.
Pages:
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289