1 / 73

Chapter 10

Chapter 10. Partitions, Objects, and Collections. Table Partitioning. Partitioning Concepts (1/3).

iman
Download Presentation

Chapter 10

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 10 Partitions, Objects, and Collections

  2. Table Partitioning

  3. Partitioning Concepts (1/3) • Database designers and administrators have been partitioning tables since long before Oracle8 hit the scene. In general, table partitioning within a single database is done to improve performance and simplify administration tasks, while table partitioning between databases is meant to facilitate data distribution. For example, sales data might be partitioned by region and each partition hosted in a database housed at its respective regional sales office. Whereas a central data warehouse might gather sales data from each office for reporting and decision-support queries, it might be perfectly reasonable for the operational sales data to be distributed across multiple sites.

  4. Partitioning Concepts (2/3) • Partitioning by sets of rows such as in the sales data example, in which the value of the sales office column determines where the data resides, is known as horizontal partitioning. Partitioning may also be accomplished by splitting up sets of columns, in which case it is called vertical partitioning . For example, sensitive data such as salary information and social security numbers may be split off from the employee table into a separate table with restricted access. When partitioning vertically, primary key columns must be included in the set of columns for every partition. Therefore, unlike horizontal partitioning, where each partition contains non-overlapping subsets of data, vertical partitioning mandates that some data be duplicated in each partition.

  5. Partitioning Concepts (3/3) • While both vertical and horizontal partitioning may be accomplished manually within and between Oracle databases, the Partitioning Option introduced in Oracle8 specifically deals with horizontal partitioning within a single database.

  6. Partitioning Tables (1/2) • When partitioning is employed, a table changes from a physical object to a virtual concept. There isn't really a table anymore, just a set of partitions. Since all of the partitions must share the same attribute and constraint definitions, however, it is possible to deal with the set of partitions as if they were a single table. The storage parameters, such as extent sizes and tablespace placement, are the only attributes that may differ among the partitions. This situation can facilitate some interesting storage scenarios, such as hosting infrequently accessed partitions on a CD jukebox while the heavily-hit data partitions reside on disk. You can also take advantage of Oracle's segmented buffer cache to keep the most active partitions in the keep buffer so they are always in memory, while the rest of the partitions can be targeted for the recycle or default buffers. Additionally, individual partitions may be taken offline without affecting the availability of the rest of the partitions, giving administrators a great deal of flexibility.

  7. Partitioning Tables (2/2) • Depending on the partitioning scheme employed, you must choose one or more columns of a table to be the partition key . The values of the columns in the partition key determine the partition that hosts a particular row. Oracle also uses the partition key information in concert with your WHERE clause to determine which partitions to search during SELECT, UPDATE, and DELETE operations.

  8. Partitioning Indexes (1/3) • So what, you may wonder, happens to the indexes on partitioned tables? The answer is that you have to choose whether each index will stay intact (referred to as a global index), or be split into pieces corresponding to the table partitions (referred to as a local index). Furthermore, with global indexes, you can choose to partition the index in a different manner than the table was partitioned. When you throw the fact that you can partition both b-tree and bit-map indexes into the mix, things can become overwhelming.

  9. Partitioning Indexes (2/3) • When you issue a SELECT, UPDATE, or DELETE statement against a partitioned table, the optimizer can take several routes to locate the target rows: • Use a global index, if one is available and its columns are referenced in the SQL statement, to find the target rows across one or more partitions. • Search a local index on every partition to identify whether any particular partition contains target rows. • Define a subset of the partitions that might contain target rows, and then access local indexes on those partitions.

  10. Partitioning Indexes (3/3) • While global indexes might seem to be the simplest solution, they can be problematic. Because global indexes span all of the partitions of a table, they are adversely affected by partition maintenance operations. For example, if a partition is split into multiple pieces, or if two partitions are merged into one, all global indexes on the partitioned table are marked as UNUSABLE and must be rebuilt before they can be used again. This is especially troubling when you consider that primary key constraints on partitioned tables utilize global indexes by default. Instead of global indexes, consider using local indexes. You may also want to explore the use of local unique indexes as the mechanism for maintaining integrity for your partitioned tables.

  11. Partitioning Methods • In order to horizontally partition a table (or index), you must specify a set of rules so that Oracle can determine in which partition a given row should reside. The following sections explore the four types of partitioning available in Oracle9i.

  12. Range Partitioning (1/2) • The first partitioning scheme, introduced in Oracle8 and known as range partitioning, allows a table to be partitioned over ranges of values for one or more columns of the table. The simplest and most widely-implemented form of range partitioning is to partition using a single date column.

  13. Range Partitioning (2/2) • Consider the following DDL statement: • CREATE TABLE cust_order ( order_nbr NUMBER(7) NOT NULL, cust_nbr NUMBER(5) NOT NULL, order_dt DATE NOT NULL, sales_emp_id NUMBER(5) NOT NULL, sale_price NUMBER(9,2), expected_ship_dt DATE, cancelled_dt DATE, ship_dt DATE, status VARCHAR2(20)) PARTITION BY RANGE (order_dt) (PARTITION orders_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')) TABLESPACE ord1, PARTITION orders_2000 VALUES LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY')) TABLESPACE ord2, PARTITION orders_2001 VALUES LESS THAN (TO_DATE('01-JAN-2002','DD-MON-YYYY')) TABLESPACE ord3);

  14. Hash Partitioning (1/3) • In some cases, you may wish to partition a large table, but there are no columns for which range partitioning is suitable. Available in Oracle8i, hash partitioning allows you to specify the number of partitions and the partition columns (the partition key), but leaves the allocation of rows to partition up to Oracle. As rows are inserted into the partitioned table, Oracle attempts to evenly spread the data across the partitions by applying a hashing function to the data in the partition key; the value returned by the hashing function determines the partition that hosts the row. If the partition columns are included in the WHERE clause of a SELECT, DELETE, or UPDATE statement, Oracle can apply the hash function to determine which partition to search.

  15. Hash Partitioning (2/3) • The following DDL statement demonstrates how the part table might be partitioned by hashing the part_nbr column: • CREATE TABLE part ( part_nbr VARCHAR2(20) NOT NULL, name VARCHAR2(50) NOT NULL, supplier_id NUMBER(6) NOT NULL, inventory_qty NUMBER(6) NOT NULL, status VARCHAR2(10) NOT NULL, inventory_qty NUMBER(6), unit_cost NUMBER(8,2) resupply_date DATE) PARTITION BY HASH (part_nbr) (PARTITION part1 TABLESPACE p1, PARTITION part2 TABLESPACE p2, PARTITION part3 TABLESPACE p3, PARTITION part4 TABLESPACE p4);

  16. Hash Partitioning (3/3) • In order for the data to be evenly distributed across the partitions, it is important to choose columns with high cardinality as partition keys. A set of columns is said to have high cardinality if the number of distinct values is large compared to the size of the table. Choosing a high cardinality column for your partition key ensures an even distribution across your partitions; otherwise, the partitions can become unbalanced, causing performance to be unpredictable and making administration more difficult.

  17. Composite Partitioning (1/2) • If you are torn between whether to apply range or hash partitioning to your table, you can do some of each. Composite partitioning, also unveiled with Oracle8i, allows you to create multiple range partitions, each of which contains two or more hash subpartitions. Composite partitioning is often useful when range partitioning is appropriate for the type of data stored in the table, but you want a finer granularity of partitioning than is practical using range partitioning alone. For example, it might make sense to partition your order table by year based on the types of queries against the table. If you want more than one partition per year, however, you could subpartition each year by hashing the customer number across four buckets.

  18. Composite Partitioning (2/2) • The following example expands on the range-partitioning example shown earler by generating subpartitions based on a hash of the customer number: • CREATE TABLE cust_order ( order_nbr NUMBER(7) NOT NULL, cust_nbr NUMBER(5) NOT NULL, order_dt DATE NOT NULL, sales_emp_id NUMBER(5) NOT NULL, sale_price NUMBER(9,2), expected_ship_dt DATE, cancelled_dt DATE, ship_dt DATE, status VARCHAR2(20)) PARTITION BY RANGE (order_dt) SUBPARTITION BY HASH (cust_nbr) SUBPARTITIONS 4 STORE IN (order_sub1, order_sub2, order_sub3, order_sub4) (PARTITION orders_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')) (SUBPARTITION orders_1999_s1 TABLESPACE order_sub1, SUBPARTITION orders_1999_s2 TABLESPACE order_sub2, SUBPARTITION orders_1999_s3 TABLESPACE order_sub3, SUBPARTITION orders_1999_s4 TABLESPACE order_sub4), PARTITION orders_2000 VALUES LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY')) (SUBPARTITION orders_2000_s1 TABLESPACE order_sub1, SUBPARTITION orders_2000_s2 TABLESPACE order_sub2, SUBPARTITION orders_2000_s3 TABLESPACE order_sub3, SUBPARTITION orders_2000_s4 TABLESPACE order_sub4), PARTITION orders_2001 VALUES LESS THAN (TO_DATE('01-JAN-2002','DD-MON-YYYY')) (SUBPARTITION orders_2001_s1 TABLESPACE order_sub1, SUBPARTITION orders_2001_s2 TABLESPACE order_sub2, SUBPARTITION orders_2001_s3 TABLESPACE order_sub3, SUBPARTITION orders_2001_s4 TABLESPACE order_sub4));

  19. List Partitioning (1/2) • Introduced in Oracle9i, list partitioning allows a table to be partitioned by one or more distinct values of a particular column. For example, a warehouse table containing sales summary data by product, state, and month/year could be partitioned into geographic regions, as in: • CREATE TABLE sales_fact ( state_cd VARCHAR2(3) NOT NULL, month_cd NUMBER(2) NOT NULL, year_cd NUMBER(4) NOT NULL, product_cd VARCHAR2(10) NOT NULL, tot_sales NUMBER(9,2) NOT NULL) PARTITION BY LIST (state_cd) (PARTITION sales_newengland VALUES ('CT','RI','MA','NH','ME','VT') TABLESPACE s1, PARTITION sales_northwest VALUES ('OR','WA','MT','ID','WY','AK') TABLESPACE s2, PARTITION sales_southwest VALUES ('NV','UT','AZ','NM','CO','HI') TABLESPACE s3, PARTITION sales_southeast VALUES ('FL','GA','AL','SC','NC','TN','WV') TABLESPACE s4, PARTITION sales_east VALUES ('PA','NY','NJ','MD','DE','VA','KY','OH') TABLESPACE s5, PARTITION sales_california VALUES ('CA') TABLESPACE s6, PARTITION sales_south VALUES ('TX','OK','LA','AR','MS') TABLESPACE s7, PARTITION sales_midwest VALUES ('ND','SD','NE','KS','MN','WI','IA', 'IL','IN','MI','MO') TABLESPACE s8);

  20. List Partitioning (2/2) • List partitioning is appropriate for low cardinality data in which the number of distinct values of a column is small relative to the number of rows. Unlike range and hash partitioning, where the partition key may contain several columns, list partitioning is limited to a single column. While it seems reasonable that composite partitioning could employ either range or list partitioning at the first level, only range/hash composite partitioning has been implemented by Oracle at this time.

  21. Specifying Partitions (1/6) • When you are writing SQL against partitioned tables, you have the option to treat the partitions as single, virtual tables, or to specify partition names within your SQL statements. If you write DML against a virtual table, the Oracle optimizer determines the partition or partitions that need to be involved. For an INSERT statement, the optimizer uses the values provided for the partition key to determine where to put each row. For UPDATE, DELETE, and SELECT statements, the optimizer uses the conditions from the WHERE clause along with information on local and global indexes to determine the partition or partitions that need to be searched.

  22. Specifying Partitions (2/6) • If you know that your DML statement will utilize a single partition, and you know the name of the partition, you can use the PARTITION clause to tell the optimizer which partition to use. For example, if you want to summarize all orders for the year 2000, and you know that the cust_order table is range-partitioned by year, you could issue the following query: • SELECT COUNT(*) tot_orders, SUM(sale_price) tot_sales FROM cust_order PARTITION (orders_2000)WHERE cancelled_dt IS NULL;

  23. Specifying Partitions (3/6) • If your table is composite-partitioned, you can use the SUBPARTITION clause to focus on a single subpartition of the table. For example, the following statement deletes all rows from the orders_2000_s1 subpartition of the composite-partitioned version of the cust_order table: • DELETE FROM cust_order SUBPARTITION (orders_2000_s1);

  24. Specifying Partitions (4/6) • You can also use the PARTITION clause to delete the entire set of subpartitions that fall within a given partition: • DELETE FROM cust_order PARTITION (orders_2000); • This statement would delete all rows from the orders_2000_s1, orders_2000_s2, orders_2000_s3, and orders_2000_s4 subpartitions of the cust_order table.

  25. Specifying Partitions (5/6) • Here are a few additional things to consider when working with partitioned tables: • If the optimizer determines that two or more partitions are needed to satisfy the WHERE clause of a SELECT, UPDATE, or DELETE statement, the table and/or index partitions may be scanned in parallel. Therefore, depending on the system resources available to Oracle, scanning every partition of a partitioned table could be much faster than scanning an entire unpartitioned table. • Because hash partitioning spreads data randomly across the partitions, it is unclear why you would want to use the PARTITION clause for hash-partitioned tables or the SUBPARTITON clause for composite-partitioned tables, since you don't know what data you are working on. The only reasonable scenario that comes to mind might be when you want to modify every row in the table, but you don't have enough rollback available to modify every row in a single transaction. In this case, you can perform an UPDATE or DELETE on each partition or subpartition and issue a COMMIT after each statement completes. • Partitions can be merged, split, or dropped at any time by the DBA. Therefore, use caution when explicitly naming partitions in your DML statements. Otherwise, you may find your statements failing, or worse, your statements might work on the wrong set of data because partitions have been merged or split without your knowledge. You may want to check with your DBA to determine her policy concerning naming partitions in your DML statements.

  26. Specifying Partitions (6/6) • If you need to access a single partition or subpartition but don't like having partition names sprinkled throughout your code, consider creating views to hide the partition names, as in the following: • CREATE VIEW cust_order_2000 AS SELECT * FROM cust_order PARTITION (orders_2000); • You can then issue your SQL statements against the view: • SELECT order_nbr, cust_nbr, sale_price, order_dt FROM cust_order_2000 WHERE quantity > 100;

  27. Partition Pruning (1/8) • Even when you don't name a specific partition in your SQL statement, the fact that a table is partitioned might still influence the manner in which you access the table. When an SQL statement accesses one or more partitioned tables, the Oracle optimizer attempts to use the information in the WHERE clause to eliminate some of the partitions from consideration during statement execution. This process, called partition pruning, speeds statement execution by ignoring any partitions that cannot satisfy the statement's WHERE clause.

  28. Partition Pruning (2/8) • To do so, the optimizer uses information from the table definition combined with information from the statement's WHERE clause. For example, given the following table definition: • CREATE TABLE tab1 ( col1 NUMBER(5) NOT NULL, col2 DATE NOT NULL, col3 VARCHAR2(10) NOT NULL) PARTITION BY RANGE (col2) (PARTITION tab1_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')) TABLESPACE t1, PARTITION tab1_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')) TABLESPACE t1, PARTITION tab1_2000 VALUES LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY')) TABLESPACE t3, PARTITION tab1_2001 VALUES LESS THAN (TO_DATE('01-JAN-2002','DD-MON-YYYY')) TABLESPACE t4);

  29. Partition Pruning (3/8) • The following query: • SELECT col1, col2, col3 FROM tab1 WHERE col2 > TO_DATE('01-OCT-2000','DD-MON-YYYY'); • The optimizer would eliminate partitions tab1_1998 and tab1_1999 from consideration, since neither partition could contain rows with a value for col2 greater than October 1, 2000.

  30. Partition Pruning (4/8) • In order for the optimizer to make these types of decisions, the WHERE clause must reference at least one column from the set of columns that comprise the partition key. While this might seem fairly straightforward, not all queries against a partitioned table naturally include the partition key. If a unique index exists on the col1 column of the tab1 table from the previous example, for instance, the following query would generally offer the most efficient access: • SELECT col1, col2, col3 FROM tab1 WHERE col1 = 1578;

  31. Partition Pruning (5/8) • If the index on col1 had been defined as a local index, however, Oracle would need to visit each partition's local index to find the one that holds the value 1578. If you also have information about the partition key (col2 in this case), you might want to consider including it in the query so that the optimizer can eliminate partitions, as in the following: • SELECT col1, col2, col3 FROM tab1 WHERE col1 = 1578 AND col2 > TO_DATE('01-JAN-2001','DD-MON-YYYY');

  32. Partition Pruning (6/8) • With the additional condition, the optimizer can now eliminate the tab1_1998, tab1_1999, and tab1_2000 partitions from consideration. Oracle will now search a single unique index on the tab1_2001 partition instead of searching a unique index on each of the four table partitions. Of course, you would need to know that data pertaining to the value 1578 also had a value for col2 greater then January 1, 2001. If you can reliably provide additional information regarding the partition keys, than you should do so; otherwise, you'll just have to let the optimizer do its best. Running EXPLAIN PLAN on your DML statements against partitioned tables will allow you to see which partitions the optimizer decided to utilize.

  33. Partition Pruning (7/8) • When checking the results of EXPLAIN PLAN, there are a couple of partition-specific columns that you should add to your query against plan_table in order to see which partitions are being considered by the optimizer. To demonstrate, we'll explain the following query against tab1: • EXPLAIN PLAN SET STATEMENT_ID = 'qry1' FOR SELECT col1, col2, col3 FROM tab1 WHERE col2 BETWEEN TO_DATE('01-JUL-1999','DD-MON-YYYY') AND TO_DATE('01-JUL-2000','DD-MON-YYYY');

  34. Partition Pruning (8/8) • When querying the plan_table table, you will include the partition_start and partition_end columns whenever the operation field starts with 'PARTITION': • SELECT lpad(' ',2 * level) || operation || ' ' || options || ' ' || object_name || DECODE(SUBSTR(operation, 1, 9), 'PARTITION', ' FROM ' || partition_start || ' TO ' || partition_stop, ' ') "exec plan" FROM plan_table CONNECT BY PRIOR id = parent_id START WITH id = 0 AND statement_id = 'qry1'; exec plan ------------------------------------------------------ SELECT STATEMENT PARTITION RANGE ITERATOR FROM 2 TO 3 TABLE ACCESS FULL TAB1 • The value of PARTITION RANGE for the operator column along with the value of ITERATOR for the options column indicates that more than one partition will be involved in the execution plan. The values of the partition_start and partition_end columns (2 and 3, respectively) indicate that the optimizer has decided to prune partitions 1 and 4, which correlate to the tab1_1998 and tab1_2001 partitions. Given that our WHERE clause specifies a date range of July 1, 1999 to July 1, 2000, the optimizer has correctly pruned all partitions that cannot contribute to the result set.

  35. Objects and Collections

  36. Object Types (1/5) • An object type is a user-defined datatype that combines data and related methods in order to model complex entities. In this regard, they are similar to class definitions in an object-oriented language such as C++ or Java. Unlike Java and C++, however, Oracle object types have a built-in persistence mechanism, since a table can be defined to store an object type in the database. Thus, Oracle object types can be directly manipulated via SQL.

  37. Object Types (2/5) • The best way to define the syntax and features of an object type is with an example. The following DDL statement creates an object type used to model an equity security such as a common stock: • CREATE TYPE equity AS OBJECT ( issuer_id NUMBER, ticker VARCHAR2(6), outstanding_shares NUMBER, last_close_price NUMBER(9,2), MEMBER PROCEDURE apply_split(split_ratio in VARCHAR2));

  38. Object Types (3/5) • The equity object type has four data members and a single member procedure. The body of the apply_split procedure is defined within a CREATE TYPE BODY statement. The following example illustrates how the apply_split member procedure might be defined: • CREATE TYPE BODY equity AS MEMBER PROCEDURE apply_split(split_ratio in VARCHAR2) IS from_val NUMBER; to_val NUMBER; BEGIN /* parse the split ratio into its components */ to_val := SUBSTR(split_ratio, 1, INSTR(split_ratio, ':') - 1); from_val := SUBSTR(split_ratio, INSTR(split_ratio, ':') + 1); /* apply the split ratio to the outstanding shares */ SELF.outstanding_shares := (SELF.outstanding_shares * to_val) / from_val; /* apply the split ratio to the last closing price */ SELF.last_close_price := (SELF.last_close_price * from_val) / to_val; END apply_split; END;

  39. Object Types (4/5) • Instances of type equity are created using the default constructor, which has the same name as the object type and expects one parameter per attribute of the object type. As of Oracle9i, there is no way to generate your own constructors for your object types. The following PL/SQL block demonstrates how an instance of the equity object type can be created using the default constructor: • DECLARE eq equity := NULL; BEGIN eq := equity(198, 'ACMW', 1000000, 13.97); END;

  40. Object Types (5/5) • Object type constructors may also be called from within DML statements. The next example queries the issuer table to find the issuer with the name 'ACME Wholesalers', and then uses the retrieved issuer_id field to construct an instance of the equity type: • DECLARE eq equity := NULL; BEGIN SELECT equity(i.issuer_id, 'ACMW', 1000000, 13.97) INTO eq FROM issuer i WHERE i.name = 'ACME Wholesalers';END;

  41. Object Attributes (1/3) • An object type may be used along with Oracle's built-in datatypes as an attribute of a table. The following table definition includes the equity object type as an attribute of the common_stock table: • CREATE TABLE common_stock ( security_id NUMBER NOT NULL, security equity NOT NULL, issue_date DATE NOT NULL, currency_cd VARCHAR2(5) NOT NULL);

  42. Object Attributes (2/3) • When adding records to the table, you must utilize the object type constructor, as illustrated by the following INSERT statement: • INSERT INTO common_stock (security_id, security, issue_date, currency_cd) VALUES (1078, equity(198, 'ACMW', 1000000, 13.97), SYSDATE, 'USD');

  43. Object Attributes (3/3) • In order to see the attributes of the equity object, you must provide an alias for the table and reference the alias, the name of the attribute containing the object type, and the object type's attribute. The next query retrieves the security_id, which is an attribute of the common_stock table, and the ticker, which is an attribute of the equity object within the common_stock table: • SELECT c.security_id security_id, c.security.ticker ticker FROM common_stock c; SECURITY_ID TICKER ----------- ------ 1078 ACMW

  44. Object Tables (1/6) • In addition to embedding object types in tables alongside other attributes, you can also build a table specifically for holding instances of your object type. Known as an object table, these tables are created by referencing the object type in the CREATE TABLE statement using the OF keyword: • CREATE TABLE equities OF equity;

  45. Object Tables (2/6) • The equities table can be populated using the constructor for the equity object type, or it may be populated from existing instances of the equity object type. For example, the next statement populates the equities table using the security column of the common_stock table: • INSERT INTO equities SELECT c.security FROM common_stock c;

  46. Object Tables (3/6) • When querying the equities table, you can reference the object type's attributes directly, just as you would an ordinary table: • SELECT issuer_id, ticker FROM equities; ISSUER_ID TICKER --------- ------ 198 ACMW

  47. Object Tables (4/6) • If you want to retrieve the data in the equities table as an instance of an equity object rather than as a set of attributes, you can use the VALUE function to return an object. The following PL/SQL block retrieves the object having a ticker equal to 'ACMW' from the equities table: • DECLARE eq equity := NULL; BEGIN SELECT VALUE(e) INTO eq FROM equities e WHERE ticker = 'ACMW'; END;

  48. Object Tables (5/6) • Now that you have an object stored in the database, you can explore how to call the apply_split member procedure defined earlier. Before you call the procedure, you need to find the target object in the table and then tell the object to run its apply_split procedure. The following PL/SQL block expands on the previous example, which finds the object in the equities table with a ticker of 'ACMW', by finding an equity object, invoking its apply_split method, and saving it back to the table again: • DECLARE eq equity := NULL; BEGIN SELECT VALUE(e) INTO eq FROM equities e WHERE ticker = 'ACMW'; /* apply a 2:1 stock split */ eq.apply_split('2:1'); /* store modified object */ UPDATE equities e SET e = eq WHERE ticker ='ACMW'; END;

  49. Object Tables (6/6) • It is important to realize that the apply_split procedure is not operating directly on the data in the equities table; rather, it is operating on a copy of the object held in memory. After the apply_split procedure has executed against the copy, the UPDATE statement overwrites the object in the equities table with the object referenced by the local variable eq, thus saving the modified version of the object.

  50. Object Parameters (1/7) • Regardless of whether you decide to store object types persistently in the database, you can use them as vehicles for passing data within or between applications. Object types may be used as input parameters and return types for PL/SQL stored procedures and functions. Additionally, SELECT statements can instantiate and return object types even if none of the tables in the FROM clause contain object types. Therefore, object types may be used to graft an object-oriented veneer on top of a purely relational database design.

More Related