In the following example, you will create an IOT with a two-part (composite) primary key:
create table oe.sales_summ_by_date
( sales_date date,
dept_id number,
total_sales number(18,2),
constraint ssbd_pk primary key
(sales_date, dept_id))
organization index tablespace xport_dw;
Each entry in the IOT contains a date, a department number, and a total sales amount for the
day. All three of these columns are stored in each IOT row, but the IOT is built based on only the
date and department number. Only one segment is used to store an IOT; if you build a secondary
index on this IOT, a new segment is created.
Because the entire row in an IOT is stored as the index itself, there is no ROWID for each
row; the primary key identifies the rows in an IOT. Instead, Oracle creates logical ROWIDs
derived from the value of the primary key; the logical ROWID is used to support secondary
indexes on the IOT.
No special syntax is required to use an IOT; although it is built and maintained much like an
index, it appears as a table to any SQL select statement or other DML statements. Also, IOTs can
be partitioned; information about partitioning IOTs is presented later in this chapter, in the section
???Partitioned Index-Organized Tables.???
Global Temporary Tables
Temporary tables have been available since Oracle8i. They are temporary in the sense of the data
that is stored in the table, not in the definition of the table itself.
Pages:
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828