How to Drop Indexes/ Unique Indexes in Oracle?

There can be multiple situations where we don’t require indexes and have to drop them.

• Sometimes it’s better to drop the indexes when there is not much performance gain for your table with indexes.
• Once the indexes becomes invalid, you must first drop the indexes before rebuilding it.
• If your indexes are too fragmented, it’s better to drop the indexes and create a new index since rebuilding an index requires twice the space of the index.

All the extents of the index segment are restored to the containing table

space once you drop the index so that it becomes available to other objects in the table space.

Below is the command to drop indexes:

SYNTAX : DROP INDEX [OWNER.]INDEXNAME [FROM [OWNER.]TABLENAME]

EXAMPLE:

SQL> DROP INDEX EMP_NAME_IDX;

INDEX DROPPED

 SQL>

Conversely, you can't drop any implicitly created index, such as those created by defining a UNIQUE key constraint on a table, with the drop index command. If you try to do so it will throw an error.

SQL> DROP INDEX EMP_NAME_IDX ;

 DROP INDEX EMP_NAME_IDX *

ERROR AT LINE 1: ORA-02429: CANNOT DROP INDEX USED FOR ENFORCEMENT OF UNIQUE/PRIMARY KEY

If you want to drop such an index you have to first drop the constraint defined on the table. In order to drop a constraint, issue the drop constraint command, as shown here:

SQL> ALTER TABLE EMP DROP CONSTRAINT emp_name_PK1;

TABLE ALTERED.

SQL>

You can query the ALL_CONSTRAINTS performance view to understand which constraint the index is used by,

SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE,

 TABLE_NAME, INDEX_OWNER, INDEX_NAME

FROM ALL_CONSTRAINTS

WHERE INDEX_NAME = 'EMP_NAME_IDX';

If you like this post, please share it on google by clicking on the Google +1 button.

Please go through similar Oracle Posts @DWHLAUREATE:

     WHAT IS A PARTITION IN ORACLE

   WHAT IS AN INLINE VIEW?

  WHAT ARE EXTERNAL TABLES IN ORACLE

 WHAT ARE INDEXES IN ORACLE 

Oracle Indexes Performance and Creation Guidelines

These guidelines will help you create and manage indexes and help improving the performance by correct usage of indexes.

DON’T ADD INDEXES WORTHLESSLY:

Addition of indexes increases performance but also ingest disk space.Based on the performance improvement add as many indexes as required sensibly.

MARK INDEXES AS UNUSABLE OR INVISIBLE RATHER THAN DROPPING

Before dropping an index think over marking the indexes as unusable and invisible. This give us an extra option to check for any performance issues before dropping the index. If there are any performance issues we can revert back by rebuilding or re-enable the index without requiring the data definition language (DDL) creation statement.

You can read more about Invisible Indexes here:

Invisible Indexes - Oracle 11g New Feature

It’s better to drop the indexes that are not used by any database objects as it would free up the physical space and improve the performance.

INDEXING METHODOLOGY:

Indexing the columns that are used in queries executed against a table will help improve the performance.

CREATE PRIMARY /UNIQUE CONSTARINTS:

Build primary constraints on all tables and unique constraints wherever applicable. This will automatically create a B-tree index if the columns are not already indexed.

USING SEPARATE TABLESPACE FOR INDEXES

Using distinct table space helps in managing indexes separately from tables. Table and index data may have different storage and/or backup and recovery requirements.

USE BITMAP INDEXES IN DATAWAREHOUSE ENVIRONMENT

Bitmap indexes are used for complex queries in a data warehouse environment to prevent spending long time to access and retrieve answers for the queries. B-Tree index technique is used for high cardinality column and Bitmap Indexes have predominantly been used for low cardinality columns.

Bitmap indexes achieve important functions in answering data warehouse’s queries because they have capability to perform operations at the index level before fetching data

To learn more about Bitmap & B-tree indexes check our previous post

http://dwhlaureate.blogspot.in/2012/08/type-of-indexing-used-in.html

USE APPROPRIATE NAMING STANDARDS

Correct naming standards would help in the maintenance and troubleshooting easier.

If you like this post, please share it on google by clicking on the Google +1 button.

Please go through similar Oracle Posts @DWHLAUREATE:

     WHAT IS A PARTITION IN ORACLE

   WHAT IS AN INLINE VIEW?

  WHAT ARE EXTERNAL TABLES IN ORACLE

 WHAT ARE INDEXES IN ORACLE 

What is a Pipeline in Informatica

Pipeline is nothing but the flow of different components in Informatica.A mapping in Informatica may contain Source, Transformations , Targets connected together, all these together will be considered as a pipeline. There will be many such pipelines in a single mapping .

One pipeline can be connected to another pipeline also; in that case it will be considered as a single pipeline

As seen in the below figure, Source, Transformations, Target are there in a pipeline.

To order the pipeline, there is a option in Designer i.e. Mappings>Target> Load Order

Bitmap Indexes in Datawarehousing

Bitmap indexes are widely used in data warehousing environments. These environments typically have large amounts of data and ad hoc queries, but a low level of concurrent DML transaction

What is a bitmap index:

A bitmap index is a specialized variation of a B-tree index. If the degree of cardinality is high for the attribute, means that there are more unique number of values for a particular attribute. Low cardinality attribute is not suitable for bitmap index because more number of records are locked which result in the locking of a whole table, leading to the lock on a whole database. For eg. A gender column, which has only two distinct values (male and female), is optimal for a bitmap index. However, data warehouse administrators also build bitmap indexes on columns with higher cardinalities.

You can use a bitmap index when both of the following conditions are true:

• The key values in the index contain many duplicates.
• More than one column in the table has an index that the optimizer can use to improve performance on a table scan.

Each bit in the bitmap corresponds to a possible rowid, and if the bit is set, it means that the row with the corresponding rowid contains the key value. A mapping function converts the bit position to an actual rowid, so that the bitmap index provides the same functionality as a regular index. Bitmap indexes store the bitmaps in a compressed way. If the number of distinct key values is small, bitmap indexes compress better and the space saving benefit compared to a B-tree index becomes even better

Note:When creating bitmap indexes, you should use NOLOGGING and COMPUTE STATISTICS. In addition, you should keep in mind that bitmap indexes are usually easier to destroy and re-create than to maintain.

ADVANTAGES

• The Advantages of using bitmap indexes are greatest for columns in which the ratio of the number of distinct values to the number of rows in the table is small

• Space requirements for indexes in a warehouse are often significantly larger than the space needed to store the data, especially for the fact table and particularly if the indexes are B*trees.Hence, you may want to keep indexing on the fact table to a minimum. Typically, you may have one or two concatenated B*tree indexes on the fact table; however, most of your indexes should be bitmap indexes. Bitmap indexes also take up much less space than B*tree indexes and so should be preferred

What is B-tree index

B-tree indexes are most commonly used in a data warehouse to enforce unique keys. In many cases, it may not even be necessary to index these columns in a data warehouse, because the uniqueness was enforced as part of the preceding ETL processing, and because typical data warehouse queries may not work better with such indexes. B-tree indexes are more common in environments using third normal form schemas. In general, bitmap indexes should be more common than B-tree indexes in most data warehouse environments.