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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Execute the query by typing a forward slash (/) and pressing Enter.
Great! The rows have been returned in order according to the number returned by the GETRANK function (SALES_PRICE/STORE_COST).
