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 CON_K_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 CON_K_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_CON_K_CUSTOMERS_CITY
on CON_K_CUSTOMERS(Person.Address.
Pages:
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288