Embedded Files
Check
Check
SELECT tablespace_name, index_name, status
FROM dba_indexes
WHERE index_name = '&index_name';
A status of 'UNUSABLE' is resolved by rebuilding the index (see later)CREATE
CREATE
B*Tree
B*Tree
CREATE INDEX myIndex ON myTable(myCol1,myCol2);
CREATE INDEX myuser.user_id_idx ON myuser.myaudit(user_id)
PCTFREE 10
INITRANS 2
MAXTRANS 255
COMPUTE STATISTICS
STORAGE(INITIAL 65536
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT)
TABLESPACE myts ;
PCTFREE - The default (10) is a reasonable general-purpose starting point. If the index is built once and the table is append-only / no updates (common in DW loads), consider PCTFREE 0 to pack blocks more tightly.
INITRANS - 2 is the default for indexes
MAXTRANS - deprecated/obsolete in modern Oracle
COMPUTE STATISTICS - it is considered best practice to run DBMS_STATS after index creation or to rely on the daily auto optimizer stats collection if stats are not required immediately.
INITIAL, NEXT, MINEXTENTS, MAXEXTENTS, PCTINCREASE - Overridden by Locally Managed Tablespace AUTOALLOCATE or UNIFORM settings
FREELISTS, FREELIST GROUPS - ASSM tablespaces ignore these values
BUFFER_POOL. FLASH_CACHE, CELL_FLASH_CACHE - only explicitly set if you can justify a non-default value
TABLESPACE - only explicitly set if you are not using the default tablespace of the user
Bitmap
Bitmap
TODO
Function-Based
Function-Based
CREATE_INDEX_COST
CREATE_INDEX_COST
"This procedure determines the cost of creating an index on an existing table. The input is the DDL statement that will be used to create the index. The procedure will output the storage required to create the index."
https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_space.htm#i1002659
REBUILD
REBUILD
Does an Index need to be rebuilt?
Does an Index need to be rebuilt?
Probably not... almost all indexes, even where inserts/deletes affect random blocks, will find an equilibrium point at about 65%-70% utilisation. But, for the edge cases...
ANALYZE INDEX index_name VALIDATE STRUCTURE;
Rebuild vs Coalesce
Rebuild vs Coalesce
Rebuild
Rebuild
You can use rebuild to move an index to another tablespace.
Requires disk space to build a copy of the index (i.e. it does not rebuild in place).
Creates new index tree and shrinks heights
Coalesce
Coalesce
Coalesces leaf blocks that are in the same branch
Quickly frees index leaf blocks for use
Online vs Offline
Online vs Offline
You cannot specify REBUILD ONLINE for bitmap indexes or for indexes that enforce referential integrity constraints.
Examples
Examples
ALTER INDEX index_name REBUILD ONLINE;
DROP
DROP
DROP INDEX myIndex;
Generate DDL
Generate DDL
Bibliography
Bibliography
https://logicalread.com/2013/04/19/oracle-11g-rebuilding-indexes-mc02http://facedba.blogspot.com/2015/12/when-index-should-be-rebuilt.html https://www.oracletutorial.com/oracle-index/ https://www.databasestar.com/oracle-sql-indexes/ https://stackoverflow.com/questions/4127206/do-i-need-to-create-indexes-on-foreign-keys-on-oracle
CREATEhttps://docs.oracle.com/database/121/SQLRF/statements_5013.htm#SQLRF01209
Foreign Keyshttps://stackoverflow.com/questions/4127206/do-i-need-to-create-indexes-on-foreign-keys-on-oracle
NLS related issueshttps://forums.oracle.com/ords/apexds/post/n-before-string-literals-in-query-causing-performance-issue-1230
Bitmaphttps://www.quora.com/What-is-the-difference-between-a-B-tree-index-and-a-Bitmap-index-in-Oracle
Function-Based
CREATEhttps://docs.oracle.com/database/121/SQLRF/statements_5013.htm#SQLRF01209
Foreign Keyshttps://stackoverflow.com/questions/4127206/do-i-need-to-create-indexes-on-foreign-keys-on-oracle
NLS related issueshttps://forums.oracle.com/ords/apexds/post/n-before-string-literals-in-query-causing-performance-issue-1230
Bitmaphttps://www.quora.com/What-is-the-difference-between-a-B-tree-index-and-a-Bitmap-index-in-Oracle
Function-Based
Page updated
Google Sites
Report abuse