1)What is RDBMS?
A relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd. Most popular databases currently in use are based on the relational database model.
2)What is an index?
An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.
4)What are the different types of Indexes?
BTree Index
Bitmap Index
Functional Index
Composite Index
Prefixed Index
Non Prefixed Index
Context Index
Partitioned Index
Clustered Index
Non Clustered Index
Reverse Key Index
Compressed Index
Invisible Index
Bitmap Join Index
5)When should you go in for a Bitmap Index?
With bitmap indexes, the optimizer can efficiently answer queries that include AND, OR, or XOR. (Oracle supports dynamic B-tree-to-bitmap conversion, but it can be inefficient.)
With bitmaps, the optimizer can answer queries when searching or counting for nulls. Null values are also indexed in bitmap indexes (unlike B-tree indexes).
We usually go in for Bitmap Index when the cardinality of the column is very low which is the count of distinct values in the column is low.
6)What is the backend architecture of Bitmap Indexes?
The real power of the bitmap index is seen when a table contains multiple bitmap indexes. With multiple bitmap indexes available, Oracle has the ability to merge the result sets from each of the bitmap indexes to quickly eliminate the unwanted data.
7)What are the different initialization parameters relating to the use of Indexes?
DB_FILE_MULTIBLOCK_READ_COUNT
OPTIMIZER_INDEX_CACHING
OPTIMIZER_INDEX_COST_ADJ
8)What is the Use of a Functional Index?
You can't modify a column that has a function-based index applied to it. You'll have to drop the index, modify the column, and then re-create the index.
This is because the Functional Index is created by making use of a SQL Function and therefore there is a possibility that some of the SQL Functions don't work based on the data stored in the Columns on which the functional index is created.
Any user-created SQL functions must be declared deterministic before they can be used in a function-based index. Deterministic means that for a given set of inputs, the function always returns the same results. You must use the keyword DETERMINISTIC when creating a user-defined function that you want to use in a function-based index
9)What are the different initialization parameters which are considered important?
BACKGROUND_DUMP_DEST
BUFFER_POOL_KEEP
BUFFER_POOL_RECYCLE
COMPATIBLE
CONTROL_FILES
CONTROL_FILE_RECORD_KEEP_TIME
CPU_COUNT
CURSOR_SHARING
DBWR_IO_SLAVES
DB_BLOCK_SIZE
DB_CACHE_ADVICE
DB_CACHE_SIZE
DB_CREATE_FILE_DEST
DB_FILE_MULTIBLOCK_READ_COUNT
DB_KEEP_CACHE_SIZE
DB_RECYCLE_CACHE_SIZE
DB_WRITER_PROCESSES
FAST_START_MTTR_TARGET
HASH_AREA_SIZE
HASH_JOIN_ENABLED
LARGE_POOL_SIZE
JOB_QUEUE_PROCESSES
LOG_ARCHIVE_DEST
LOG_FILE_NAME_CONVERT
OPEN_CURSORS
OPTIMIZER_DYNAMIC_SAMPLING
OPTIMIZER_FEATURES_ENABLE
OPTIMIZER_INDEX_CACHING
OPTIMIZER_INDEX_COST_ADJ
OPTIMIZER_MODE
OS_AUTHENT_PREFIX
PGA_AGGREGATE_TARGET
QUERY_REWRITE_ENABLED
QUERY_REWRITE_INTEGRITY
REMOTE_OS_AUTHENT
RESOURCE_LIMIT
RESOURCE_MANAGER_PLAN
SESSION_CACHED_CURSORS
SGA_MAX_SIZE
SHARED_POOL_RESERVED_SIZE
SHARED_POOL_SIZE
SORT_AREA_RETAINED_SIZE
SORT_AREA_SIZE
STATISTICS_LEVEL
TRACE_ENABLED
UNDO_MANAGEMENT
UNDO_RETENTION
UNDO_TABLESPACE
UTL_FILE_DIR
WORKAREA_SIZE_POLICY
A relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd. Most popular databases currently in use are based on the relational database model.
2)What is an index?
An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.
4)What are the different types of Indexes?
BTree Index
Bitmap Index
Functional Index
Composite Index
Prefixed Index
Non Prefixed Index
Context Index
Partitioned Index
Clustered Index
Non Clustered Index
Reverse Key Index
Compressed Index
Invisible Index
Bitmap Join Index
5)When should you go in for a Bitmap Index?
With bitmap indexes, the optimizer can efficiently answer queries that include AND, OR, or XOR. (Oracle supports dynamic B-tree-to-bitmap conversion, but it can be inefficient.)
With bitmaps, the optimizer can answer queries when searching or counting for nulls. Null values are also indexed in bitmap indexes (unlike B-tree indexes).
We usually go in for Bitmap Index when the cardinality of the column is very low which is the count of distinct values in the column is low.
6)What is the backend architecture of Bitmap Indexes?
The real power of the bitmap index is seen when a table contains multiple bitmap indexes. With multiple bitmap indexes available, Oracle has the ability to merge the result sets from each of the bitmap indexes to quickly eliminate the unwanted data.
7)What are the different initialization parameters relating to the use of Indexes?
DB_FILE_MULTIBLOCK_READ_COUNT
OPTIMIZER_INDEX_CACHING
OPTIMIZER_INDEX_COST_ADJ
8)What is the Use of a Functional Index?
You can't modify a column that has a function-based index applied to it. You'll have to drop the index, modify the column, and then re-create the index.
This is because the Functional Index is created by making use of a SQL Function and therefore there is a possibility that some of the SQL Functions don't work based on the data stored in the Columns on which the functional index is created.
Any user-created SQL functions must be declared deterministic before they can be used in a function-based index. Deterministic means that for a given set of inputs, the function always returns the same results. You must use the keyword DETERMINISTIC when creating a user-defined function that you want to use in a function-based index
9)What are the different initialization parameters which are considered important?
BACKGROUND_DUMP_DEST
BUFFER_POOL_KEEP
BUFFER_POOL_RECYCLE
COMPATIBLE
CONTROL_FILES
CONTROL_FILE_RECORD_KEEP_TIME
CPU_COUNT
CURSOR_SHARING
DBWR_IO_SLAVES
DB_BLOCK_SIZE
DB_CACHE_ADVICE
DB_CACHE_SIZE
DB_CREATE_FILE_DEST
DB_FILE_MULTIBLOCK_READ_COUNT
DB_KEEP_CACHE_SIZE
DB_RECYCLE_CACHE_SIZE
DB_WRITER_PROCESSES
FAST_START_MTTR_TARGET
HASH_AREA_SIZE
HASH_JOIN_ENABLED
LARGE_POOL_SIZE
JOB_QUEUE_PROCESSES
LOG_ARCHIVE_DEST
LOG_FILE_NAME_CONVERT
OPEN_CURSORS
OPTIMIZER_DYNAMIC_SAMPLING
OPTIMIZER_FEATURES_ENABLE
OPTIMIZER_INDEX_CACHING
OPTIMIZER_INDEX_COST_ADJ
OPTIMIZER_MODE
OS_AUTHENT_PREFIX
PGA_AGGREGATE_TARGET
QUERY_REWRITE_ENABLED
QUERY_REWRITE_INTEGRITY
REMOTE_OS_AUTHENT
RESOURCE_LIMIT
RESOURCE_MANAGER_PLAN
SESSION_CACHED_CURSORS
SGA_MAX_SIZE
SHARED_POOL_RESERVED_SIZE
SHARED_POOL_SIZE
SORT_AREA_RETAINED_SIZE
SORT_AREA_SIZE
STATISTICS_LEVEL
TRACE_ENABLED
UNDO_MANAGEMENT
UNDO_RETENTION
UNDO_TABLESPACE
UTL_FILE_DIR
WORKAREA_SIZE_POLICY
No comments:
Post a Comment