Index are created on one or more column of a table.After it is created,an index is automatically maintained and used by oracle
Oracle server uses two method to locate the desired information.
1.Table scan: In table scan oracle scans the entire table to locate desired information.
2.Index Scan: An index scan oracle uses index to locate place that hold desired information and then jump to that place to get required
information.This is much Faster.
Can you create an index on frequently updated column?
No We can not Create index on Frequently update Column.
It reduce Performance.
Difference between btree and bitmap index
A Internally, a bitmap and a btree indexes are very different, but functionally
they are identical in that they serve to assist Oracle in retrieving rows faster than a full-table scan.
The basic differences between b-tree and bitmap indexes include:
1.The bitmap index includes the "bitmap" keyword. The btree index does not say "bitmap".
2.The bitmap index is generally for columns with lots of duplicate values (low cardinality), while b-tree indexes are best for high cardinality columns.
3.A b-tree index has index nodes (based on data block size).A bitmap index looks like this, a two-dimensional array with zero and one (bit) values
4.B-tree Index is userful for OLTP, where as Bitmap Index is useful for Dataware Housing.
5.B-tree index updates on key values has relatively inexpensive, where as Bitmap index has more expensive.
Difference between Clustered Index and Non Clustered Index?
The concept behind indexes is to change the order of the data (clustered index) or to add metadata (non-clustered index) for improving the performance of queries.
Clustered indexes
1. Physically stored in order (ascending or descending)
2. Only one per table
3. When a primary key is created a clustered index is automatically created as well.
4. If the table is under heavy data modifications or the primary key is used for searches, a clustered index on the primary key is recommended.
5. Columns with values that will not change at all or very seldom, are the best choices.
6. For use on columns that are frequently searched for ranges of data
7. For use on columns with low selectivity
Non-clustered indexes
1. Up to 249 nonclustered indexes are possible for each table or indexed view.
2. The clustered index keys are used for searching therefore clustered index keys should be chosen with a minimal length.
3. Covered queries (all the columns used for joining, sorting or filtering are indexed) should be non-clustered.
4. Foreign keys should be non-clustered.
5. If the table is under heavy data retrieval from fields other than the primary key, one clustered index and/or one or more non-clustered indexes should be created for the column(s) used to retrieve the data.
6. For use on columns that are searched for single values
7. For use on columns with high selectivity
Have you ever worked on indexes, what is the default index? Why we can create a Function based index?
There are times when even though an index exits,oracle doesn't use it and instead follows table scan.
This is usually happen when index created on a column,but the SQL query references that column with Function
or arithmetic expression.
For example, an index is created on the city column of the customer table and following query is executed.
select * from customer where upper(city)='MUMBAI';
above query is references the city column along with upper function and hence oracle doesn't use index So we create function based index.
Create index I4 on customer(upper(City));
What are the disadvantages of indexes in Oracle?
1.They increase the disk space requirements of your database
2.They slow down dml (i.e. inserts, updates and deletes)
3.They increase the maintenance requirements of your Oracle database
4.They may make your queries slower instead of faster
How many types of indexes?
1.B-tree Index:
a.Simple Index
b.Composite Index
c.Unique Index
d.Function Based Index
2.Bitmap Index:
How does index work internally?
1.When an Index is created on table, Oracle internally forms a two dimensional matrix that contain Data extracted from the column on which index is created and physical address of the record(rowid).
2.When an SQL query that has a where clause based on the column on which index is fired,oracle the find value in index and locate the record in the table using ROWID.
When index is created on table??
1.Column frequently access in where clause or in a join condition.
2.Column contain Wide range of values.
3.Contain Large number of values.
4.Two or more columns are frequently used together in where clause or join condition
How to know oracle use my index or not?
We can EXPLAIN PLAN to check oracle used Index or not
How to rebuild index, syntax of rebuild index?
ALTER INDEX index_name REBUILD
How to see invalid indexes?
select * from user_objects
where object_type = 'INDEX'
and status ='INVALID
Some columns having indexes, How to know which columns having indexes in a table?
SELECT index_name, column_name, column_position
FROM user_ind_columns
WHERE table_name='MYTABLENAME'
ORDER BY index_name, column_position
What is global and local index?
Global and Local Index partitioning with Oracle
The first partitioned index method is called a LOCAL partition. A local partitioned index creates a one-for-one match between the indexes and the partitions in the table. Of course, the key value for the table partition and the value for the local index must be identical. The second method is called GLOBAL and allows the index to have any number of partitions.
The partitioning of the indexes is transparent to all SQL queries. The great benefit is that the Oracle query engine will scan only the index partition that is required to service the query, thus speeding up the query significantly. In addition, the Oracle parallel query engine will sense that the index is partitioned and will fire simultaneous queries to scan the indexes.
Local partitioned indexes
Local partitioned indexes allow the DBA to take individual partitions of a table and indexes offline for maintenance (or reorganization) without affecting the other partitions and indexes in the table.
In a local partitioned index, the key values and number of index partitions will match the number of partitions in the base table.
CREATE INDEX year_idx
on all_fact (order_date)
LOCAL
(PARTITION name_idx1),
(PARTITION name_idx2),
(PARTITION name_idx3);
Oracle will automatically use equal partitioning of the index based upon the number of partitions in the indexed table. For example, in the above definition, if we created four indexes on all_fact, the CREATE INDEX would fail since the partitions do not match. This equal partition also makes index maintenance easier, since a single partition can be taken offline and the index rebuilt without affecting the other partitions in the table.
Global partitioned indexes
A global partitioned index is used for all other indexes except for the one that is used as the table partition key. Global indexes partition OLTP (online transaction processing) applications where fewer index probes are required than with local partitioned indexes. In the global index partition scheme, the index is harder to maintain since the index may span partitions in the base table.
For example, when a table partition is dropped as part of a reorganization, the entire global index will be affected. When defining a global partitioned index, the DBA has complete freedom to specify as many partitions for the index as desired.
Now that we understand the concept, let's examine the Oracle CREATE INDEX syntax for a globally partitioned index:
CREATE INDEX item_idx
on all_fact (item_nbr)
GLOBAL
(PARTITION city_idx1 VALUES LESS THAN (100)),
(PARTITION city_idx1 VALUES LESS THAN (200)),
(PARTITION city_idx1 VALUES LESS THAN (300)),
(PARTITION city_idx1 VALUES LESS THAN (400)),
(PARTITION city_idx1 VALUES LESS THAN (500));
Here, we see that the item index has been defined with five partitions, each containing a subset of the index range values. Note that it is irrelevant that the base table is in three partitions. In fact, it is acceptable to create a global partitioned index on a table that does not have any partitioning
A global Index in a single partition may correspond to multiple table partitions. They are created with the GLOBAL keyword and this the global clause allows you to create a non-partitioned index.
Global indexes may perform uniqueness checks faster than local (partitioned) indexes.Also you cannot create global indexes for hash partitions or subpartitions.
Ex:
SQL> CREATE INDEX invoices_idx
ON COST_TABLE(invoice_date)
GLOBAL PARTITION BY RANGE (invoice_date)
(PARTITION invoices01 VALUES LESS THAN
(TO_DATE('01/04/2001', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION invoices02 VALUES LESS THAN
(TO_DATE('01/07/2001', 'DD/MM/YYYY')) TABLESPACE users);
Similar to table partitions, it is possible to move them from one device to another. But unlike table partitions, movement of index partitions requires individual reconstruction of the index or each partition (only in the case of global index).
Ex:
SQL> alter index stud_ind rebuild partition p2
Index partitions cannot be dropped manually.They are dropped implicitly when the data they refer to is dropped from the partitioned table.
What is local index
Local partitioned indexes are easier to manage than other types of partitioned indexes. They also offer greater availability and are common in DSS environments.
In the local prefixed index the partition key is specified on the left prefix. Local keyword tells oracle to create a separte index for each partition.Local prefixed indexes can be unique or non unique and is easier to manage
Ex:CREATE INDEX invoices_idx ON invoices (invoice_date)
LOCAL (PARTITION invoices_q1 TABLESPACE users,
PARTITION invoices_q2 TABLESPACE users);
What is reverse key index?
In the Index leaf block,Oracle store the index key value and rowid.
Assume that there is unique index created on custid,suppose 3 individual conurrently hit the database so insert row with customer id 102,103,104 then index entries store in same leaf block which cause buffer busy wait.If index is reverse unique index then the entries will be stored in different block
Create index ind on customer(cust_id) reverse;
What is the syntax of organization table index?
Create table temp1(Slno number(3) Not null,
constraint pk_temp1
primary key(Slno))
organization index;
What is unique index?
Unique index doesn't allow duplicate values into the column on which index is created.
Unique index is created using keyword UNIQUE.
When do we create bitmap ?
Bitmap indexes are meant to be used on low cardinality columns. A low cardinality column just means that the column has relatively few unique values. For example, a column called Sex which has only “Male” and “Female” as the two possible values is considered low cardinality because there are only two unique values in the column.Bitmap index useful when data is not modified by concurrent transaction.
It used is data warehouse.
Oracle server uses two method to locate the desired information.
1.Table scan: In table scan oracle scans the entire table to locate desired information.
2.Index Scan: An index scan oracle uses index to locate place that hold desired information and then jump to that place to get required
information.This is much Faster.
Can you create an index on frequently updated column?
No We can not Create index on Frequently update Column.
It reduce Performance.
Difference between btree and bitmap index
A Internally, a bitmap and a btree indexes are very different, but functionally
they are identical in that they serve to assist Oracle in retrieving rows faster than a full-table scan.
The basic differences between b-tree and bitmap indexes include:
1.The bitmap index includes the "bitmap" keyword. The btree index does not say "bitmap".
2.The bitmap index is generally for columns with lots of duplicate values (low cardinality), while b-tree indexes are best for high cardinality columns.
3.A b-tree index has index nodes (based on data block size).A bitmap index looks like this, a two-dimensional array with zero and one (bit) values
4.B-tree Index is userful for OLTP, where as Bitmap Index is useful for Dataware Housing.
5.B-tree index updates on key values has relatively inexpensive, where as Bitmap index has more expensive.
Difference between Clustered Index and Non Clustered Index?
The concept behind indexes is to change the order of the data (clustered index) or to add metadata (non-clustered index) for improving the performance of queries.
Clustered indexes
1. Physically stored in order (ascending or descending)
2. Only one per table
3. When a primary key is created a clustered index is automatically created as well.
4. If the table is under heavy data modifications or the primary key is used for searches, a clustered index on the primary key is recommended.
5. Columns with values that will not change at all or very seldom, are the best choices.
6. For use on columns that are frequently searched for ranges of data
7. For use on columns with low selectivity
Non-clustered indexes
1. Up to 249 nonclustered indexes are possible for each table or indexed view.
2. The clustered index keys are used for searching therefore clustered index keys should be chosen with a minimal length.
3. Covered queries (all the columns used for joining, sorting or filtering are indexed) should be non-clustered.
4. Foreign keys should be non-clustered.
5. If the table is under heavy data retrieval from fields other than the primary key, one clustered index and/or one or more non-clustered indexes should be created for the column(s) used to retrieve the data.
6. For use on columns that are searched for single values
7. For use on columns with high selectivity
Have you ever worked on indexes, what is the default index? Why we can create a Function based index?
There are times when even though an index exits,oracle doesn't use it and instead follows table scan.
This is usually happen when index created on a column,but the SQL query references that column with Function
or arithmetic expression.
For example, an index is created on the city column of the customer table and following query is executed.
select * from customer where upper(city)='MUMBAI';
above query is references the city column along with upper function and hence oracle doesn't use index So we create function based index.
Create index I4 on customer(upper(City));
What are the disadvantages of indexes in Oracle?
1.They increase the disk space requirements of your database
2.They slow down dml (i.e. inserts, updates and deletes)
3.They increase the maintenance requirements of your Oracle database
4.They may make your queries slower instead of faster
How many types of indexes?
1.B-tree Index:
a.Simple Index
b.Composite Index
c.Unique Index
d.Function Based Index
2.Bitmap Index:
How does index work internally?
1.When an Index is created on table, Oracle internally forms a two dimensional matrix that contain Data extracted from the column on which index is created and physical address of the record(rowid).
2.When an SQL query that has a where clause based on the column on which index is fired,oracle the find value in index and locate the record in the table using ROWID.
When index is created on table??
1.Column frequently access in where clause or in a join condition.
2.Column contain Wide range of values.
3.Contain Large number of values.
4.Two or more columns are frequently used together in where clause or join condition
How to know oracle use my index or not?
We can EXPLAIN PLAN to check oracle used Index or not
How to rebuild index, syntax of rebuild index?
ALTER INDEX index_name REBUILD
How to see invalid indexes?
select * from user_objects
where object_type = 'INDEX'
and status ='INVALID
Some columns having indexes, How to know which columns having indexes in a table?
SELECT index_name, column_name, column_position
FROM user_ind_columns
WHERE table_name='MYTABLENAME'
ORDER BY index_name, column_position
What is global and local index?
Global and Local Index partitioning with Oracle
The first partitioned index method is called a LOCAL partition. A local partitioned index creates a one-for-one match between the indexes and the partitions in the table. Of course, the key value for the table partition and the value for the local index must be identical. The second method is called GLOBAL and allows the index to have any number of partitions.
The partitioning of the indexes is transparent to all SQL queries. The great benefit is that the Oracle query engine will scan only the index partition that is required to service the query, thus speeding up the query significantly. In addition, the Oracle parallel query engine will sense that the index is partitioned and will fire simultaneous queries to scan the indexes.
Local partitioned indexes
Local partitioned indexes allow the DBA to take individual partitions of a table and indexes offline for maintenance (or reorganization) without affecting the other partitions and indexes in the table.
In a local partitioned index, the key values and number of index partitions will match the number of partitions in the base table.
CREATE INDEX year_idx
on all_fact (order_date)
LOCAL
(PARTITION name_idx1),
(PARTITION name_idx2),
(PARTITION name_idx3);
Oracle will automatically use equal partitioning of the index based upon the number of partitions in the indexed table. For example, in the above definition, if we created four indexes on all_fact, the CREATE INDEX would fail since the partitions do not match. This equal partition also makes index maintenance easier, since a single partition can be taken offline and the index rebuilt without affecting the other partitions in the table.
Global partitioned indexes
A global partitioned index is used for all other indexes except for the one that is used as the table partition key. Global indexes partition OLTP (online transaction processing) applications where fewer index probes are required than with local partitioned indexes. In the global index partition scheme, the index is harder to maintain since the index may span partitions in the base table.
For example, when a table partition is dropped as part of a reorganization, the entire global index will be affected. When defining a global partitioned index, the DBA has complete freedom to specify as many partitions for the index as desired.
Now that we understand the concept, let's examine the Oracle CREATE INDEX syntax for a globally partitioned index:
CREATE INDEX item_idx
on all_fact (item_nbr)
GLOBAL
(PARTITION city_idx1 VALUES LESS THAN (100)),
(PARTITION city_idx1 VALUES LESS THAN (200)),
(PARTITION city_idx1 VALUES LESS THAN (300)),
(PARTITION city_idx1 VALUES LESS THAN (400)),
(PARTITION city_idx1 VALUES LESS THAN (500));
Here, we see that the item index has been defined with five partitions, each containing a subset of the index range values. Note that it is irrelevant that the base table is in three partitions. In fact, it is acceptable to create a global partitioned index on a table that does not have any partitioning
A global Index in a single partition may correspond to multiple table partitions. They are created with the GLOBAL keyword and this the global clause allows you to create a non-partitioned index.
Global indexes may perform uniqueness checks faster than local (partitioned) indexes.Also you cannot create global indexes for hash partitions or subpartitions.
Ex:
SQL> CREATE INDEX invoices_idx
ON COST_TABLE(invoice_date)
GLOBAL PARTITION BY RANGE (invoice_date)
(PARTITION invoices01 VALUES LESS THAN
(TO_DATE('01/04/2001', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION invoices02 VALUES LESS THAN
(TO_DATE('01/07/2001', 'DD/MM/YYYY')) TABLESPACE users);
Similar to table partitions, it is possible to move them from one device to another. But unlike table partitions, movement of index partitions requires individual reconstruction of the index or each partition (only in the case of global index).
Ex:
SQL> alter index stud_ind rebuild partition p2
Index partitions cannot be dropped manually.They are dropped implicitly when the data they refer to is dropped from the partitioned table.
What is local index
Local partitioned indexes are easier to manage than other types of partitioned indexes. They also offer greater availability and are common in DSS environments.
In the local prefixed index the partition key is specified on the left prefix. Local keyword tells oracle to create a separte index for each partition.Local prefixed indexes can be unique or non unique and is easier to manage
Ex:CREATE INDEX invoices_idx ON invoices (invoice_date)
LOCAL (PARTITION invoices_q1 TABLESPACE users,
PARTITION invoices_q2 TABLESPACE users);
What is reverse key index?
In the Index leaf block,Oracle store the index key value and rowid.
Assume that there is unique index created on custid,suppose 3 individual conurrently hit the database so insert row with customer id 102,103,104 then index entries store in same leaf block which cause buffer busy wait.If index is reverse unique index then the entries will be stored in different block
Create index ind on customer(cust_id) reverse;
What is the syntax of organization table index?
Create table temp1(Slno number(3) Not null,
constraint pk_temp1
primary key(Slno))
organization index;
What is unique index?
Unique index doesn't allow duplicate values into the column on which index is created.
Unique index is created using keyword UNIQUE.
When do we create bitmap ?
Bitmap indexes are meant to be used on low cardinality columns. A low cardinality column just means that the column has relatively few unique values. For example, a column called Sex which has only “Male” and “Female” as the two possible values is considered low cardinality because there are only two unique values in the column.Bitmap index useful when data is not modified by concurrent transaction.
It used is data warehouse.
No comments:
Post a Comment