Developing and Implementing Applications, concluded - Indexing Abstract Datatype Attributes (Page 2 of 7 )
In the preceding example, the GEORGE_CUSTOMERS table was created based on a PERSON_TY datatype and an ADDRESS_TY datatype. As shown in the following listing, the GEORGE_ CUSTOMERS table contains a normal column—Customer_ID—and a Person column that is defined by the PERSON_TY abstract datatype:
create table GEORGE_CUSTOMERS
(Customer_ID NUMBER,
Person PERSON_TY);
From the datatype definitions shown in the previous section of this chapter, you can see that PERSON_TY has one column—Name—followed by an Address column defined by the ADDRESS_ TY datatype.
When referencing columns within the abstract datatypes during queries, updates, and deletes, specify the full path to the datatype attributes. For example, the following query returns the Customer_ ID column along with the Name column. The Name column is an attribute of the datatype that defines the Person column, so you refer to the attribute as Person.Name, as shown here:
select C.Customer_ID, C.Person.Name
from GEORGE_CUSTOMERS C;
You can refer to attributes within the ADDRESS_TY datatype by specifying the full path through the related columns. For example, the Street column is referred to as Person.Address.Street, which fully describes its location within the structure of the table. In the following example, the City column is referenced twice—once in the list of columns to select and once within the where clause:
select C.Person.Name,
C.Person.Address.City
from GEORGE_CUSTOMERS C
where C.Person.Address.City like 'C%';
Because the City column is used with a range search in the where clause, the optimizer may be able to use an index when resolving the query. If an index is available on the City column, Oracle can quickly find all the rows that have City values starting with the letter C, as requested by the query.
To create an index on a column that is part of an abstract datatype, you need to specify the full path to the column as part of the create index command. To create an index on the City column (which is part of the Address column), you can execute the following command:
create index I_GEORGE_CUSTOMERS$CITY
on GEORGE_CUSTOMERS(Person.Address.City);
This command will create an index named I_GEORGE_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 create an index for it.
Next: Quiescing and Suspending the Database >>
More Oracle Articles
More By McGraw-Hill/Osborne
|
 This article is excerpted from chapter five of the book Oracle Database 10g DBA Handbook, written by Kevin Loney and Bob Bryla (McGraw-Hill/Osborne, 2005; ISBN: 0072231459). Buy this book now.
|
|
/ 6 
