Nested Tables-Up-Database Tables-2

Here is where the “virtually a table for every row” comes into play. In the SELECT predicate shown earlier, it may not have been obvious that there was a table per row, especially since the joins and such aren’t there; it looks a little like magic. The UPDATE statement, however, shows that there is a table per row. We selected a discrete table to UPDATE—this table has no name, only a query to identify it. If we use a query that does not SELECT exactly one table, we will receive the following:

If we return fewer than one row (one nested table instance), the update fails. Normally, an update of zero rows is OK, but not in this case—it returns an error the same as if we left the table name off a regular table update. If we return more than one row (more than one nested table instance), the update fails. Normally, an update of many rows is perfectly OK. This shows that Oracle considers each row in the DEPT_AND_EMP table to point to another table, not just another set of rows as the relational model does.

This is the semantic difference between a nested table and a parent/child relational table. In the nested table model, there is one table per parent row. In the relational model, there is one set of rows per parent row. This difference can make nested tables somewhat cumbersome to use at times. Consider this model we are using, which provides a very nice view of the data from the perspective of a single department. It is a terrible model if we want to ask questions like “What department does KING work for?”, “How many accountants do we have working for us?”, and so on. These questions are best asked of the EMP relational table, but in this nested table model, we can only access the EMP data via the DEPT data. We must always join; we cannot query the EMP data alone. Well, we can’t do it in a supported, documented method, but we can use a trick (more on this trick later). If we needed to update every row in the EMPS_NT, we would have

to do four updates: one each for the rows in DEPT_AND_EMP to update the virtual table associated with each row.

Another thing to consider is that when we updated the employee data for department 10, we were semantically updating the EMPS column in the DEPT_AND_EMP table. We understand that physically there are two tables involved, but semantically there is only one. Even though we updated no data in the DEPT table, the row that contains the nested table we did modify is locked from update by other sessions. In a traditional parent/child table relationship, this would not be the case.

These are the reasons why I tend to stay away from nested tables as a persistent storage mechanism. It is the rare child table that is not queried stand-alone. In the preceding example, the EMP table should be a strong entity. It stands alone, so it needs to be queried alone. I find this to be the case almost all of the time. I tend to use nested tables via views on relational tables.

So, now that we have seen how to update a nested table instance, inserting and deleting are pretty straightforward. Let’s add a row to the nested table instance department 10 and remove a row from department 20:

SQL> select d.dname, e.empno, ename, deptno from dept_and_emp d, table(d.emps) e where d.deptno in ( 10, 20 );

That is the basic syntax of how to query and modify nested tables. You will find that you often need to unnest these tables as we just did, especially in queries, to make use of them. Once you conceptually visualize the “virtual table per row” concept, working with nested tables becomes much easier.

Previously I stated, “We must always join; we cannot query the EMP data alone,” but then I followed that up with a caveat: “You can if you really need to.” It is not documented heavily; use this approach only as a last ditch method. Where it will come in most handy is if you ever need to mass update the nested table (remember, you would have to do that through the DEPT table with a join). There is an underdocumented hint (it is mentioned briefly and not fully documented), NESTED_TABLE_GET_REFS, which is used by various tools (including the deprecated EXP and IMP utilities) to deal with nested tables. It is also a way to see a little more about the physical structure of the nested tables. If you use this hint, you can query to get some “magical” results. The following query is what EXP (a data unload utility) uses to extract the data from this nested table:

These two columns don’t even show up. They are part of the hidden implementation f nested tables. The NESTED_TABLE_ID is really a foreign key to the parent table EPT_AND_EMP. DEPT_AND_EMP actually has a hidden column in it that is used to join to MPS_NT.
The SYS_NC_ROWINFO$ column is a magic column; it is more of a function than a column. The nested table here is really an object table (it is made of an object type), and SYS_NC_ROWINFO$ is the internal way Oracle references the row as an object, instead of ­referencing each of the scalar columns. Under the covers, all Oracle has done for us is implement a parent/child table with system-generated primary and foreign keys.
If wedig a little deeper, we can query the real data dictionary to see all of the columns in the DEPT_AND_EMP table:

SQL> select namefrom sys.col$where obj# = ( select object_idfrom dba_objectswhere object_name = ‘DEPT_AND_EMP’and owner = ‘EODA’ );

The weird-looking column name, SYS_NC0000400005$, is the system-generated key placed into the DEPT_AND_EMP table. If we dig even deeper, we will find that Oracle has placed a unique index on this column. Unfortunately, however, it neglected to index the NESTED_TABLE_ID in EMPS_NT. This column really needs to be indexed, as we are always joining from DEPT_AND_EMP to EMPS_NT. This is an important thing to remember about nested tables if you use them with all of the defaults as just done: always index the NESTED_TABLE_ID in the nested tables!

I’ve gotten off track, though, at this point—I was talking about how to treat the nested table as if it were a real table. The NESTED_TABLE_GET_REFS hint does that for us. We can use the hint like this:
SQL> update /*+ nested_table_get_refs */ emps_nt set ename = initcap(ename); 14 rows updated.

Again, this is not a thoroughly documented and supported feature. It has a specific functionality for EXP and IMP to work. This is the only environment it is assured to work in. Use it at your own risk, and resist putting it into production code. In fact, if you find you need to use it, then by definition you didn’t mean to use a nested table at all! It is the wrong construct for you. Use it for one-off fixes of data or to see what is in the nested table out of curiosity. The supported way to report on the data is to unnest it like this:

SQL> select d.deptno, d.dname, emp.* from dept_and_emp D, table(d.emps) emp;

This is what you should use in queries and production code.