ORACLE OBJECTS: AN OBJECTIVE ANALYSIS in Font

Creating Code 3 of 9 in Font ORACLE OBJECTS: AN OBJECTIVE ANALYSIS

111 112 113 114 115 116 117 118 119 120 121 122 123 124

l_component_id components_or_view.component_id%type; begin for i in 1..g_num_of_child_updates loop l_component_id := mod(i,500); update table ( select parts from components_or_view where component_id = l_component_id ) set part_desc = part_desc || ' updated' where part_id = i; end loop; end do_or_update;

The procedure do_rel_update updates one column of all rows in the table components_rel: 126 127 128 129 130 131 procedure do_rel_update as begin update components_rel set component_name = component_name || ' or update'; end do_rel_update;

The procedure do_rel_child_update updates one column of all rows in the child table parts_rel: 133 134 135 136 137 138 139 140 141 142 procedure do_rel_child_update as begin for i in 1..g_num_of_child_updates loop update parts_rel set part_desc = part_desc || ' updated' where part_id = i; end loop; end do_rel_child_update; The procedure do_nt_update updates one column of all rows in the table components_nt: 144 145 146 147 148 149 procedure do_nt_update as begin update components_nt set component_name = component_name || ' or update'; end do_nt_update;

The procedure do_nt_child_update updates one column of all rows in the child table parts_nt (the hidden table containing the nested table data). Note that owing to a bug in Oracle 10g Release 1 and Oracle9i Release 2, because of which the nested_table_get_refs hint does not work in static SQL, the update fails unless you use dynamic SQL, as follows: 151 152 153 154 155 156 157 158 159 160 161 procedure do_nt_child_update as begin for i in 1..g_num_of_child_updates loop execute immediate 'update /*+ nested_table_get_refs */ parts_nt' || ' set part_desc = part_desc|| :1 ' || ' where part_id = :2 ' using 'updated', i; end loop; end do_nt_child_update; The procedure do_or_delete deletes all rows in components_or_view: 163 164 165 166 167 procedure do_or_delete as begin delete components_or_view; end do_or_delete;

The procedure do_rel_delete deletes all rows in components_rel and implicitly in parts_rel: 169 170 171 172 173 procedure do_rel_delete as begin delete components_rel; end do_rel_delete; The procedure do_nt_delete deletes all rows in components_nt and implicitly in parts_nt: 175 176 177 178 179 procedure do_nt_delete as begin delete components_nt; end do_nt_delete;

The procedure do_rel_bulk_insert inserts a given number of parent records and a given number of children for each parent record into the tables components_rel and parts_rel using PL/SQL bulk binding technique. When using bulk binding, you first store all rows to be inserted in a collection, and then insert it in one shot using the special forall syntax, thus avoiding the overhead of switching contexts between the SQL engine and the PL/SQL engine. Bulk binding is discussed in detail in the section Using Bulk Binding of 17.

181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206

procedure do_rel_bulk_insert( p_num_of_parents in int := 50, p_num_of_children in int := 500 ) as l_tmp_comp number; type array is table of parts_rel%rowtype index by binary_integer; l_childdata array; l_part_rel_id number := 1; begin for i in 1..p_num_of_parents loop insert into components_rel values ( i, 'component'||i ); for j in 1..p_num_of_children loop l_childdata(j).component_id := i; l_childdata(j).part_id := l_part_rel_id; l_childdata(j).part_name := 'part'||i||j; l_childdata(j).part_desc := 'part desc' || i||j; l_part_rel_id := l_part_rel_id + 1; end loop; forall X in 1 .. p_num_of_children insert into parts_rel values l_childdata(X); end loop; commit; end do_rel_bulk_insert; end ; /

Package body created. I must point out that the preceding performance benchmark suffers from the drawback that, of necessity, I had to choose specific scenarios for updates, deletes, and selects, among other things. In particular, for comparing updates, I ran my tests on the case where I update all records of rows in the child table, which may not represent reality in general. Also, the nested tables based approach can be improved by storing the nested tables as an index-organized table as mentioned in an earlier note. Having said that, the conclusions drawn from the benchmark should more or less hold true. I used the runstats utility to measure the differences between the elapsed time for inserting 50 parents, each having 500 child records, for each of the three cases compared. To conserve space, I do not show the actual program runs, but that should not be an issue since I give the details of the package I used to run the various comparisons. Table 8-1 summarizes the elapsed times and latches for various scenarios that I observed in my runs. For each case I show in bold and within parentheses, the values normalized to the lowest value of the elapsed time or the latches for a given DML. For example, inserts in the nested tables based approach took 2.86 times the elapsed time and consumed 16.20 times the latches as compared to the relational approach.