CREATE TABLE objects_tab AS SELECT * FROM all_objects;
ALTER TABLE objects_tab ADD (
CONSTRAINT objects_tab_pk PRIMARY KEY (object_id)
EXEC DBMS_STATS.gather_table_stats(USER, 'objects_tab', cascade=>TRUE);
SET AUTOTRACE TRACEONLY EXP;
SELECT * FROM objects_tab WHERE object_id = 10;
Plan
SELECT STATEMENT ALL_ROWSCost: 2 Bytes: 113 Cardinality: 1
2 TABLE ACCESS BY INDEX ROWID TABLE BINHTV.OBJECTS_TAB Cost: 2 Bytes: 113 Cardinality: 1
1 INDEX UNIQUE SCAN INDEX (UNIQUE) BINHTV.OBJECTS_TAB_PK Cost: 1 Cardinality: 1
SET AUTOTRACE TRACEONLY EXP
SELECT * FROM objects_tab WHERE object_name = 'USER_TABLES';
Plan
SELECT STATEMENT ALL_ROWSCost: 1,391 Bytes: 565 Cardinality: 5
1 TABLE ACCESS STORAGE FULL TABLE BINHTV.OBJECTS_TAB Cost: 1,391 Bytes: 565 Cardinality: 5
CREATE INDEX objects_tab_object_name_vi ON objects_tab(object_name) NOSEGMENT;
SET AUTOTRACE TRACEONLY EXP
SELECT * FROM objects_tab WHERE object_name = 'USER_TABLES';
ALTER SESSION SET "_use_nosegment_indexes" = TRUE;
SET AUTOTRACE TRACEONLY EXP
SELECT * FROM objects_tab WHERE object_name = 'USER_TABLES';
SET AUTOTRACE OFF
SELECT index_name FROM user_indexes;
OBJECTS_TAB_PK
TO_NUMBER_IX
FROM_NUMBER_IX
SELECT object_name FROM user_objects WHERE object_type = 'INDEX';
FROM_NUMBER_IX
TO_NUMBER_IX
OBJECTS_TAB_PK
OBJECTS_TAB_OBJECT_NAME_VI
SQL> EXEC DBMS_STATS.gather_index_stats(USER, 'objects_tab_object_name_vi');
PL/SQL procedure successfully completed.
SQL>
SQL> CREATE INDEX objects_tab_object_name_vi2 ON objects_tab(object_name) NOSEGMENT;
CREATE INDEX objects_tab_object_name_vi2 ON objects_tab(object_name) NOSEGMENT
*
ERROR at line 1:
ORA-01408: such column list already indexed
SQL> CREATE INDEX objects_tab_object_name_i ON objects_tab(object_name);
Index created.
SQL>
