The longevity of the
temporary data is controlled by the on commit clause; on commit delete rows removes all rows
from the temporary table when a commit or rollback is issued, and on commit preserve rows
keeps the rows in the table beyond the transaction boundary. However, when the user??™s session
is terminated, all of the user??™s rows in the temporary table are removed.
There are a few other things to keep in mind when using temporary tables. Although you can
create an index on a temporary table, the entries in the index are dropped along with the data
rows, as with a regular table. Also, due to the temporary nature of the data in a temporary table,
no redo information is generated for DML on temporary tables; however, undo information is
created in the undo tablespace.
Index Organized Tables
As you will find out later in the subsection on indexes, creating an index makes finding a particular
row in a table more efficient. However, this adds a bit of overhead, because the database must
maintain the data rows and the index entries for the table. What if your table does not have many
columns, and access to the table occurs primarily on a single column? In this case, an indexorganized
table (IOT) might be the right solution. An IOT stores rows of a table in a B-tree index,
where each node of the B-tree index contains the keyed (indexed) column along with one or
more non-indexed columns.
The most obvious advantage of an IOT is that only one storage structure needs to be maintained
instead of two; similarly, the values for the primary key of the table are stored only once in an
IOT, versus twice in a regular table.
Pages:
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68