Parallel Server   «Prev  Next»

Lesson 4 Using parallel queries on object tables
ObjectiveCreate a parallel query for an object table.

Using Parallel Queries (On object ables)

A new feature of Oracle8i allows you to execute a parallel query on object tables and object-relational tables that contain object type columns. The types of queries supported are the same as those for relational tables. In addition, parallel queries support special features of object types such as methods, constructors, object views, and access to attributes of object types. A prerequisite of parallel execution of a query on an object table is a MAP MEMBER function[1]. A MAP MEMBER function is defined on the object type and used to sort object records quickly. We will look at how to create this kind of function for an object type in the simulation.
In the following simulation, you will create a MAP MEMBER function in an object type. Next, you will create a parallel query for an object table that is based on the object type and MAP MEMBER function you created.

Create Replace Object 1
1) Create Replace Object 1
Create Replace Object 2
2) Create Replace Object 2
Create Replace Object 3
3) Create Replace Object 3
Create Replace Object 4
4) Create Replace Object 4
Create Replace Object 5
5) Create Replace Object 5
Create Replace Object 6
6) Create Replace Object 6
Create Replace Object 7
7) Create Replace Object 7
Create Replace Object 8
8) Create Replace Object 8
Create Replace Object 9
9) Create Replace Object 9
Create Replace Object 10
10) Create Replace Object 10
Create Replace Object 11
11) Create Replace Object 11
Create Replace Object 12
12) Create Replace Object 12
Create Replace Object 13
13) Create Replace Object 13
  1. Let's dive right in! This is the beginning of the object type definition. The object type is called SINGLE_PRODUCT_TYPE and has four attributes. You are now going to begin defining the MAP MEMBER function required by Oracle for parallel execution of queries. Our example uses the function named GETRANK. Type MAP MEMBER FUNCTION GETRANK and press Enter.
  2. Invalid entry
  3. MAP MEMBER FUNCTION GETRANK
  4. Type MAP MEMBER FUNCTION GETRANK at the 8 prompt and press Enter.
  5. A MAP MEMBER function must have no parameters and return a simple data type, such as DATE, NUMBER, or VARCHAR2. In this example, the function returns a number. Type RETURN NUMBER, and press Enter.
  6. RETURN NUMBER
  7. Type RETURN NUMBER, at the 9 prompt and press Enter.

  1. We have added the PRAGMA clause to the definition for you. This clause defines certain restrictions on the execution of the function and is a standard part of defining the MAP MEMBER function. Now, execute the command by typing a forward slash (/) and then press Enter.
  2. Invalid entry
  3. Type / at the 13 prompt and press Enter.
  1. Now that the object is created you must define the GETRANK function. Do this by defining the object type body. Start the definition by typing CREATE TYPE BODY SINGLE_PRODUCT_TYPE AS and pressing Enter.
  2. Invalid entry
  3. CREATE TYPE BODY SINGLE_PRODUCT_TYPE AS
  4. Type CREATE TYPE BODY SINGLE_PRODUCT_TYPE AS at the SQL> prompt and press Enter.

Parallel Query of an Object Table in Oracle Parallel Server: Legacy

  1. Let's dive right in! This is the beginning of the object type definition. The object type is called SINGLE_PRODUCT_TYPE and has four attributes. You are now going to begin defining the MAP MEMBER function required by Oracle8i for parallel execution of queries. Our example uses the function named GETRANK. Type MAP MEMBER FUNCTION GETRANK and press Enter.
  2. A MAP MEMBER function must have no parameters and return a simple data type, such as DATE, NUMBER, or VARCHAR2. In this example, the function returns a number. Type RETURN NUMBER, and press Enter.
  3. We have added the PRAGMA clause to the definition for you. This clause defines certain restrictions on the execution of the function and is a standard part of defining the MAP MEMBER function. Now, execute the command by typing a forward slash (/) and then press Enter.
  4. Now that the object is created you must define the GETRANK function. Do this by defining the object type body. Start the definition by typing CREATE TYPE BODY SINGLE_PRODUCT_TYPE AS and pressing Enter.
  5. The next line begins describing the functions included as methods in this object type. For this example, only one function will be defined: GETRANK. Type MAP MEMBER FUNCTION GETRANK and press Enter.
  6. The next line repeats the specification for the GETRANK function that we saw in the object type definition: the function returns a number and has no parameters. To complete this part of the definition, type RETURN NUMBER IS and press Enter.
  7. We added the standard BEGIN line to the function definition. Now, add the calculation in the function. Keep in mind that the function need not do any calculation at all! You could just return the PRODUCT_ID, for example. Just to illustrate how to use a calculation here, we chose to create a ratio of selling price to cost as the value to calculate. To complete this part of the definition, type RETURN SALE_PRICE/STORE_COST; and press Enter.
  8. We have completed the usual ending for the function and the object type body. Now, execute the command by typing a forward slash (/) and pressing Enter.
  9. Okay, the object type with its MAP MEMBER function has been created. Assume that there is a new object table created using the SINGLE_PRODUCT_TYPE object type we have just defined. The object table's create statement appears here. Press Enter to continue.
  10. Let's also assume that there is data in the table and you are now ready to write a parallel query on the table. Begin the query with a hint telling Oracle8i to use parallel execution. Type SELECT /* +PARALLEL (P, 4) */ and press Enter.
  11. We have filled in all but the last line of this query. The final line tells Oracle8i to use the MAP MEMBER function, GETRANK, to sort the records before returning them to us. The function is called implicitly when we use the kind of ORDER BY clause shown here. Type ORDER BY VALUE(P) and press Enter.
  12. Execute the query by typing a forward slash (/) and pressing Enter.
  13. Great! The rows have been returned in order according to the number returned by the GETRANK function (SALES_PRICE/STORE_COST).

The following list shows restrictions involving parallel queries of object types:
  1. Parallel queries on nested tables are not supported.
  2. Parallel DML and parallel DDL are not supported with object types.
If the Optimizer determines that the query requires non-parallel execution, no error messages are issued informing you of this choice.
The next lesson covers using the Oracle Performance Manager to monitor global V$views.

Using Parallel Queries on Object Tables - Exercise

Click the Exercise link below to practice creating a query and DML commands for the object table.
Using Parallel Queries on Object Tables - Exercise

[1]Map MEMBER function: A function defined as part of an object type to sort or compare object rows with one another.