English

FAQ: Should the Oracle LOB segment's index name be specified when creating an ST_Geometry attribute?

Question

Should the Oracle LOB segment's index name be specified when creating an ST_Geometry attribute?

Answer

Oracle is deprecating the lob_index_clause name clause used when creating an Oracle LOB attribute.

Oracle's MetaLink Note: 159995.1 contains the following announcement:

"lob_index_clause

This clause is deprecated as of Oracle8i. Oracle generates an index for each LOB column. Oracle names and manages the LOB indexes internally. Although it is still possible for you to specify this clause, Oracle Corporation strongly recommends that you no longer do so. In any event, do not put the LOB index in a different tablespace from the LOB data."

The example below creates a streets table with a ST_Geometry attribute and defines the storage clause option for the LOB segment and specifies the LOB segment's index storage clause.

Code:
SQL> CREATE TABLE streets
2 (objectid NUMBER(38) NOT NULL,
3 name VARCHAR2(64) NOT NULL,
4 laddr NUMBER(9),
5 raddr NUMBER(9),
6 shape st_geometry)
7 LOB (shape.points)
8 STORE AS streets_shape_seg
9 (TABLESPACE lob_seg ENABLE STORAGE IN ROW CHUNK 8K RETENTION CACHE
10 STORAGE (INITIAL 500M PCTINCREASE 10)
11 INDEX
12 (TABLESPACE lob_seg STORAGE (INITIAL 50M PCTINCREASE 10)))
13 PCTFREE 10 INITRANS 4
14 TABLESPACE user_data STORAGE (INITIAL 1M PCTINCREASE 0);

Table created.

Because Oracle no longer recommends setting the index clause, the above CREATE TABLE statement should be executed without the index storage clause.

Code:
SQL> CREATE TABLE streets
2 (objectid NUMBER(38) NOT NULL,
3 name VARCHAR2(64) NOT NULL,
4 laddr NUMBER(9),
5 raddr NUMBER(9),
6 shape st_geometry)
7 LOB (shape.points)
8 STORE AS streets_shape_seg
9 (TABLESPACE lob_seg ENABLE STORAGE IN ROW CHUNK 8K RETENTION CACHE
10 STORAGE (INITIAL 500M PCTINCREASE 10))
11 PCTFREE 10 INITRANS 4
12 TABLESPACE user_data STORAGE (INITIAL 1M PCTINCREASE 0);

Table created.

Note:
See Oracle's documentation in the Related Information below for additional information.

Related Information