Databases Reference
In-Depth Information
To display the number of extents and space used, run the following query:
select a.segment_name, a.segment_type, a.extents, a.bytes
from user_segments a, user_indexes b
where a.segment_name = b.index_name
and b.table_name in ('CUST','ADDRESS');
Notice that for this example the output shows there are no segments, extents, or space allocated for
the index.
no rows selected
Starting with Oracle Database 11g Release 2, when you create a table, the creation of the associated
segment (and extents) is deferred until the first row is inserted into the table. This means that any
associated indexes also don't have segments created until rows are inserted into the related tables. To
illustrate this, let's insert one row into the
CUST
table and one in the
ADDRESS
table, like so:
insert into cust values(1,'STARK','JIM');
insert into address values(100,1,'Vacuum Ave','Portland','OR');
Rerunning this query (that reports on segment usage) yields the following output:
SEGMENT_NAME SEGMENT_TYPE EXTENTS BYTES
-------------------- -------------------- ---------- ----------
CUST_PK INDEX 1 1048576
CUST_UK1 INDEX 1 1048576
ADDR_FK1 INDEX 1 1048576
Displaying Index Code
From time to time you'll need to drop an index. This could be because of an obsolete application or
you've established that an index is no longer used. Prior to dropping an index, we recommend that you
generate the data definition language (DDL) that would be required to re-create the index. This allows
you to re-create the index (as it was before it was dropped) in the event that dropping the index has a
detrimental impact on performance and needs to be re-created.
Use the
DBMS_METADATA.GET_DDL
function to display an object's DDL. Make sure you set the
LONG
variable to an appropriate value so that the returned CLOB value is displayed in its entirety. For example,
SQL> set long 1000000
SQL> select dbms_metadata.get_ddl('INDEX','ADDR_FK1') from dual;
Here is the output:
DBMS_METADATA.GET_DDL('INDEX','ADDR_FK1')
--------------------------------------------------------------------------------
CREATE INDEX "MV_MAINT"."ADDR_FK1" ON
"MV_MAINT"."ADDRESS" ("CUST_ID") PCTFREE 10 INITRANS 2 MAXTRANS 255
COMPUTE STATISTICS STORAGE(INITIAL 1048576
NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
FREELISTS 1 FREELIST GROUPS 1
