Database Reference
In-Depth Information
■
starting with 12
c
, you can instruct Oracle to write undo to the temporary tablespace and thereby eliminate
almost all of the redo generation. this is done by setting the
TEMP_UNDO_ENABLED
parameter to
TRUE
(see Chapter 9 for
details).
Note
Statistics used by the CBO can be generated on a temporary table with care; however, a better guess set of
statistics may be set on a temporary table using the
DBMS_STATS
package or dynamically collected by the optimizer at
hard parse time using dynamic sampling. Starting with Oracle 12
c
you can generate statistics specific to a session. This
provides the optimizer with better information to generate execution plans that are more optimal for the data loaded
in a given session.
Object Tables
We have already seen a partial example of an object table with nested tables. An
object table
is a table that is created
based on a
TYPE
, not as a collection of columns. Normally, a
CREATE TABLE
statement would look like this:
create table t ( x int, y date, z varchar2(25) );
An object table creation statement looks more like this:
create table t of Some_Type;
The attributes (columns) of
T
are derived from the definition of
SOME_TYPE
. Let's quickly look at an example
involving a couple of types, and then review the resulting data structures:
EODA@ORA12CR1> create or replace type address_type
2 as object
3 ( city varchar2(30),
4 street varchar2(30),
5 state varchar2(2),
6 zip number
7 )
8 /
Type created.
EODA@ORA12CR1> create or replace type person_type
2 as object
3 ( name varchar2(30),
4 dob date,
5 home_address address_type,
6 work_address address_type
7 )
8 /
Type created.
EODA@ORA12CR1> create table people of person_type
2 /
Table created.
