Oracle Database - Index (indices)

Card Puncher Data Processing

About

Relation - Index (Indices) in Oracle

Management

Creation

Create Statement for columns table:

CREATE [UNIQUE|BITMAP} INDEX [SCHEMA].INDEX_NAME 
ON [SCHEMA].TABLE_NAME (COLUMN1, COLUMN2, ...) 
PCTFREE 10 -- Physical Storage Property
INITRANS 2 -- Physical Storage Property
MAXTRANS 255 -- Physical Storage Property
COMPUTE STATISTICS 
NOLOGGING -- logged in the redo log file ?
STORAGE(INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TABLESPACE_NAME" -- Tablespace

LOGGING

The LOGGING/NOLOGGING setting determines if operations are logged in the redo log file.

The LOGGING/NOLOGGING setting also determines whether subsequent Direct Loader (SQL*Loader) and direct-path INSERT operations against the index are logged or not logged.

If you omit this clause, then the logging attribute is that of the tablespace in which it resides.

Usable / Unusable

Unusable

alter index your_index unusable;

Usable

alter index your_index rebuild;

Conf

The index that are marked UNUSABLE can be skipped with the parameter skip_unusable_indexes;

DDL

SELECT DBMS_METADATA.get_ddl('INDEX',index_name,owner) 
from
dba_indexes
where
owner || '.' || index_name in ('USER.INDEX_NAME');

Tablespace

Normal

If you omit the Tablespace clause, then Oracle Database creates the index in the default tablespace of the owner of the schema containing the index.

  • Create
CREATE INDEX OWNER.NAME ON OWNER.TABLE_NAME (COLUMN_NAME,COLUMN_NAME,...) TABLESPACE "TABLESPACE_NAME";
  • (Modify|Move|Rebuild)
ALTER INDEX OWNER.NAME REBUILD TABLESPACE "TABLESPACE_NAME";
  • Select
select index_name, tablespace_name from dba_indexes;

Partition

  • Modify. The blocksize of a partitioned table should be the same for the whole table. You can't move a single partition to another tablespace with a different block_size. Exp/Import method / drop create must be used.
select 'alter index ' || 
       index_owner || '.' || 
       index_name || ' REBUILD PARTITION ' || 
       partition_name ||  ' TABLESPACE  TARGET_TABLESPACE_NAME;' 
from DBA_IND_PARTITIONS where index_name = 'MYINDEXNAME';

DataWarehouse

Basic indexes are created and provided out of the box. These indexes are created based on the kind of reports. Heap and Bitmap indexes are created out of the box.

  • Bitmap indexes are typically created when the cardinality of rows are low.
  • All indexes are created on the Aggregated and Central FACT tables only
  • No additional indexes are created on OFSA Tables
  • If the tables are partitioned then 'LOCAL' indexes are created in case of BITMAP indexes
  • All Indexes are dropped/disabled before load and enabled/created post loading





Discover More
Card Puncher Data Processing
Oracle - Partitions (Table and Indexes)

partition in Oracle. Partitioning enables you to store one logical object – a table or index – transparently in several independent segments. Partitioning can provide great performance improvements...
Db File Sequential Read
Oracle Database - db file sequential read Wait Event

This event signifies that the user process is reading a buffer into the SGA buffer cache and is waiting for a physical I/O call to return. A sequential read is a single-block read. From SQL Developer,...
Oracle Segment Extent Data Block
Oracle Database - (Logical) Segment (Database Object Storage)

A segment is a logical storage structure that contains data structure (segment type) of database objects. For example: each table's data is stored in its own data segment, while each index's data...
Card Puncher Data Processing
Oracle Database - Buffer IO (Logical IO)

A buffer is a container for data. A logical I/O, also known as a buffer I/O, refers to reads and writes of buffers in the buffer cache. When a requested buffer is not found in memory, the database performs...
Card Puncher Data Processing
Oracle Database - Example of query Tuning (to decrease the logical I/O)

To decrease the , you have several possibilities : first, you must compute the statistic to give all information to the in order to find the best . second, to add an index (Not always good) precompute...
Card Puncher Data Processing
Oracle Database - How can we load a large number of rows into an indexed existing table ?

If you have to perform data load into a or data mart, you must skip the index to minimize the generation of redo log, such as : set the indexes to the state. They are not dropped but just setting...
Card Puncher Data Processing
Oracle Database - Index Scans

The Index Scans is a access path used by the query optimizer to produce the best . In this method, a row is retrieved by traversing the index, using the indexed column values specified by the statement....
Card Puncher Data Processing
Oracle Database - Index Statistics

Statistics on Index stored in the data dictionary : DBA_INDEXES DBA_IND_STATISTICS Default Index Values When Statistics Are Missing Index Statistic Default Value Used by Optimizer Levels ...
Card Puncher Data Processing
Oracle Database - Objects

Database object. Each database object is considered to either be: a schema object (ie owned by a user) or a “non-schema object”. See for plsql object type: Object in different namespace are...
Card Puncher Data Processing
Oracle Database - Schema Object

The schema Objects are logical structures of data stored physically in data file and categorized in a schema. private synonym Database objects that are owned by a user are schema...



Share this page:
Follow us:
Task Runner