crystal reports code 128 DATABASE TABLES in Objective-C

Creation Data Matrix in Objective-C DATABASE TABLES

USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 TABLESPACE "USERS" ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "USERS" NESTED TABLE "EMPS" STORE AS "EMPS_NT" (( CONSTRAINT "EMPS_EMPNO_UNIQUE" UNIQUE ("EMPNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS TABLESPACE "USERS" ENABLE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING TABLESPACE "USERS" ) RETURN AS VALUE The only new thing here so far is the RETURN AS VALUE clause. It is used to describe how the nested table is returned to a client application. By default, Oracle will return the nested table by value to the client; the actual data will be transmitted with each row. This can also be set to RETURN AS LOCATOR, meaning the client will get a pointer to the data, not the data itself. If and only if the client dereferences this pointer will the data be transmitted to it. So, if you believe the client will typically not look at the rows of a nested table for each parent row, you can return a locator instead of the values, saving on the network round-trips. For example, if you have a client application that displays the lists of departments and when the user double-clicks a department it shows the employee information, you may consider using the locator. This is because the details are usually not looked at that is the exception, not the rule. So, what else can we do with the nested table First, the NESTED_TABLE_ID column must be indexed. Since we always access the nested table from the parent to the child, we really need that index. We can index that column using CREATE INDEX, but a better solution is to use an IOT to store the nested table. The nested table is another perfect example of what an IOT is excellent for. It will physically store the child rows co-located by NESTED_TABLE_ID (so retrieving the table is done with less physical I/O). It will remove the need for the redundant index on the RAW(16) column. Going one step further, since the NESTED_TABLE_ID will be the leading column in the IOT s primary key, we should also incorporate index key compression to suppress the redundant NESTED_TABLE_IDs that would be there otherwise. In addition, we can incorporate our UNIQUE and NOT NULL constraint on the EMPNO column into the CREATE TABLE command. Therefore, if we take the preceding CREATE TABLE statement and modify it slightly ops$tkyte%ORA11GR2> CREATE TABLE "OPS$TKYTE"."DEPT_AND_EMP" 2 ("DEPTNO" NUMBER(2, 0), 3 "DNAME" VARCHAR2(14), 4 "LOC" VARCHAR2(13), 5 "EMPS" "EMP_TAB_TYPE") 6 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING 7 STORAGE(INITIAL 131072 NEXT 131072 8 MINEXTENTS 1 MAXEXTENTS 4096 9 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 10 BUFFER_POOL DEFAULT) 11 TABLESPACE "USERS" 12 NESTED TABLE "EMPS" 13 STORE AS "EMPS_NT" 14 ( (empno NOT NULL, unique (empno), primary key(nested_table_id,empno)) 15 organization index compress 1 ) 16 RETURN AS VALUE 17 / Table created.

we now get the following set of objects. Instead of having a conventional table EMPS_NT, we have an IOT EMPS_NT as signified by the index structure overlaid on the table in Figure 10-12.

Figure 10-12. Nested table implemented as an IOT Where the EMPS_NT is an IOT using compression, it should take less storage than the original default nested table and it has the index we badly need.

I do not use nested tables as a permanent storage mechanism myself, for the following reasons: The unnecessary storage overhead of the RAW(16) columns that are added. Both the parent and child table will have this extra column. The parent table will have an extra 16-byte RAW for each nested table column it has. Since the parent table typically already has a primary key (DEPTNO in my examples), it makes sense to use this key in the child tables, not a system-generated key. The unnecessary overhead of the additional unique constraint on the parent table, when it typically already has a unique constraint. The nested table is not easily used by itself, without using unsupported constructs (NESTED_TABLE_GET_REFS). It can be un-nested for queries, but not mass updates. I have yet to find a table in real life that isn t queried by itself.

I do use nested tables heavily as a programming construct and in views. This is where I believe they are in their element. As a storage mechanism, I much prefer creating the parent/child tables myself. After creating the parent/child tables, we can, in fact, create a view that makes it appear as if we had a real nested table. That is, we can achieve all of the advantages of the nested table construct without incurring the overhead. If you do use a nested table as a storage mechanism, be sure to make it an IOT to avoid the overhead of an index on the NESTED_TABLE_ID and the nested table itself. See the previous section on IOTs for