Home arrow Oracle arrow Page 2 - Developing and Implementing Applications, concluded

Indexing Abstract Datatype Attributes - Oracle

This article, the third of three parts, focuses on the design and creation of applications that use the database. It 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).

  1. Developing and Implementing Applications, concluded
  2. Indexing Abstract Datatype Attributes
  3. Quiescing and Suspending the Database
  4. Supporting Iterative Development
  5. Iterative Column Definitions
  6. Security Requirements
  7. The Testing Environment
By: McGraw-Hill/Osborne
Rating: starstarstarstarstar / 6
February 23, 2006

print this article



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:

(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

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,
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:

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.

>>> More Oracle Articles          >>> More By McGraw-Hill/Osborne

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: