In an Oracle database which of the following statement is true about indexes
The owner of the other schema has a quota for the tablespaces to contain the index or index partitions, or CREATE UNIQUE INDEX dept_unique_index ON dept (dname) TABLESPACE indx;9 system privilege. Show
This section contains the following topics: Creating an Index ExplicitlyYou can create indexes explicitly (outside of integrity constraints) using the SQL statement CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY, age INTEGER) ENABLE PRIMARY KEY USING INDEX TABLESPACE users;0. The following statement creates an invisible index named CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY, age INTEGER) ENABLE PRIMARY KEY USING INDEX TABLESPACE users;1 for the CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY, age INTEGER) ENABLE PRIMARY KEY USING INDEX TABLESPACE users;2 column of the CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY, age INTEGER) ENABLE PRIMARY KEY USING INDEX TABLESPACE users;3 table: Indexes are optional structures associated with tables and clusters that allow SQL statements to execute more quickly against a table. Just as the index in this manual helps you locate information faster than if there were no index, an Oracle index provides a faster access path to table data. You can use indexes without rewriting any queries. Your results are the same, but you see them more quickly. Oracle provides several indexing schemes that provide complementary performance functionality. These are:
Indexes are logically and physically independent of the data in the associated table. Being independent structures, they require storage space. You can create or drop an index without affecting the base tables, database applications, or other indexes. Oracle automatically maintains indexes when you insert, update, and delete rows of the associated table. If you drop an index, all applications continue to work. However, access to previously indexed data might be slower. This section discusses guidelines for managing indexes and contains the following topics: Create Indexes After Inserting Table DataData is often inserted or loaded into a table using the either the SQL*Loader or Import utility. It is more efficient to create an index for a table after inserting or loading the data. If you create one or more indexes before loading data, Oracle then must update every index as each row is inserted. Creating an index on a table that already has data requires sort space. Some sort space comes from memory allocated for the index's creator. The amount for each user is determined by the initialization parameter CREATE INDEX emp_ename ON emp(ename) TABLESPACE users STORAGE (INITIAL 20K NEXT 20k PCTINCREASE 75) PCTFREE 0;1. Oracle also swaps sort information to and from temporary segments that are only allocated during the index creation in the users temporary tablespace. Under certain conditions, data can be loaded into a table with SQL*Loader's direct path load and an index can be created as data is loaded. Index the Correct Tables and ColumnsUse the following guidelines for determining when to create an index:
Some columns are strong candidates for indexing. Columns with one or more of the following characteristics are candidates for indexing:
Columns with the following characteristics are less suitable for indexing:
CREATE INDEX emp_ename ON emp(ename) TABLESPACE users STORAGE (INITIAL 20K NEXT 20k PCTINCREASE 75) PCTFREE 0;4 and CREATE INDEX emp_ename ON emp(ename) TABLESPACE users STORAGE (INITIAL 20K NEXT 20k PCTINCREASE 75) PCTFREE 0;4 CREATE INDEX emp_ename ON emp(ename) TABLESPACE users STORAGE (INITIAL 20K NEXT 20k PCTINCREASE 75) PCTFREE 0;6 columns cannot be indexed. The size of a single index entry cannot exceed roughly one-half (minus some overhead) of the available space in the data block. Order Index Columns for PerformanceThe order of columns in the CREATE INDEX emp_ename ON emp(ename) TABLESPACE users STORAGE (INITIAL 20K NEXT 20k PCTINCREASE 75) PCTFREE 0;7 CREATE INDEX emp_ename ON emp(ename) TABLESPACE users STORAGE (INITIAL 20K NEXT 20k PCTINCREASE 75) PCTFREE 0;8 statement can affect query performance. In general, specify the most frequently used columns first. If you create a single index across columns to speed up queries that access, for example, CREATE INDEX emp_ename ON emp(ename) TABLESPACE users STORAGE (INITIAL 20K NEXT 20k PCTINCREASE 75) PCTFREE 0;9, CREATE UNIQUE INDEX dept_unique_index ON dept (dname) TABLESPACE indx;0, and CREATE UNIQUE INDEX dept_unique_index ON dept (dname) TABLESPACE indx;1; then queries that access just CREATE INDEX emp_ename ON emp(ename) TABLESPACE users STORAGE (INITIAL 20K NEXT 20k PCTINCREASE 75) PCTFREE 0;9, or that access just CREATE INDEX emp_ename ON emp(ename) TABLESPACE users STORAGE (INITIAL 20K NEXT 20k PCTINCREASE 75) PCTFREE 0;9 and CREATE UNIQUE INDEX dept_unique_index ON dept (dname) TABLESPACE indx;0, are also speeded up. But a query that accessed just CREATE UNIQUE INDEX dept_unique_index ON dept (dname) TABLESPACE indx;0, just CREATE UNIQUE INDEX dept_unique_index ON dept (dname) TABLESPACE indx;1, or just CREATE UNIQUE INDEX dept_unique_index ON dept (dname) TABLESPACE indx;0 and CREATE UNIQUE INDEX dept_unique_index ON dept (dname) TABLESPACE indx;1 does not use the index. Limit the Number of Indexes for Each TableA table can have any number of indexes. However, the more indexes there are, the more overhead is incurred as the table is modified. Specifically, when rows are inserted or deleted, all indexes on the table must be updated as well. Also, when a column is updated, all indexes that contain the column must be updated. Thus, there is a trade-off between the speed of retrieving data from a table and the speed of updating the table. For example, if a table is primarily read-only, having more indexes can be useful; but if a table is heavily updated, having fewer indexes could be preferable. Drop Indexes That Are No Longer RequiredConsider dropping an index if:
Specify Index Block Space UseWhen an index is created for a table, data blocks of the index are filled with the existing values in the table up to CREATE UNIQUE INDEX dept_unique_index ON dept (dname) TABLESPACE indx;9. The space reserved by CREATE UNIQUE INDEX dept_unique_index ON dept (dname) TABLESPACE indx;9 for an index block is only used when a new row is inserted into the table and the corresponding index entry must be placed in the correct index block (that is, between preceding and following index entries). If no more space is available in the appropriate index block, the indexed value is placed where it belongs (based on the lexical set ordering). Therefore, if you plan on inserting many rows into an indexed table, CREATE UNIQUE INDEX dept_unique_index ON dept (dname) TABLESPACE indx;9 should be high to accommodate the new index values. If the table is relatively static without many inserts, CREATE UNIQUE INDEX dept_unique_index ON dept (dname) TABLESPACE indx;9 for an associated index can be low so that fewer blocks are required to hold the index data. CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY, age INTEGER) ENABLE PRIMARY KEY USING INDEX TABLESPACE users PCTFREE 0;3 cannot be specified for indexes. Estimate Index Size and Set Storage ParametersEstimating the size of an index before creating one can facilitate better disk space planning and management. You can use the combined estimated size of indexes, along with estimates for tables, rollback segments, and redo log files, to determine the amount of disk space that is required to hold an intended database. From these estimates, you can make correct hardware purchases and other decisions. Use the estimated size of an individual index to better manage the disk space that the index uses. When an index is created, you can set appropriate storage parameters and improve I/O performance of applications that use the index. For example, assume that you estimate the maximum size of an index before creating it. If you then set the storage parameters when you create the index, fewer extents are allocated for the table's data segment, and all of the index's data is stored in a relatively contiguous section of disk space. This decreases the time necessary for disk I/O operations involving this index. The maximum size of a single index entry is approximately one-half the data block size. Specify the Tablespace for Each IndexIndexes can be created in any tablespace. An index can be created in the same or different tablespace as the table it indexes. If you use the same tablespace for a table and its index, it can be more convenient to perform database maintenance (such as tablespace or file backup) or to ensure application availability. All the related data is always online together. Using different tablespaces (on different disks) for a table and its index produces better performance than storing the table and index in the same tablespace. Disk contention is reduced. But, if you use different tablespaces for a table and its index and one tablespace is offline (containing either data or index), then the statements referencing that table are not guaranteed to work. Consider Parallelizing Index CreationYou can parallelize index creation, much the same as you can parallelize table creation. Because multiple processes work together to create the index, Oracle can create the index more quickly than if a single server process created the index sequentially. When creating an index in parallel, storage parameters are used separately by each query server process. Therefore, an index created with an CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY, age INTEGER) ENABLE PRIMARY KEY USING INDEX TABLESPACE users PCTFREE 0;4 value of 5M and a parallel degree of 12 consumes at least 60M of storage during index creation. Consider Creating Indexes with NOLOGGINGYou can create an index and generate minimal redo log records by specifying CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY, age INTEGER) ENABLE PRIMARY KEY USING INDEX TABLESPACE users PCTFREE 0;5 in the CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY, age INTEGER) ENABLE PRIMARY KEY USING INDEX TABLESPACE users PCTFREE 0;6 statement. Note: Because indexes created using CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY, age INTEGER) ENABLE PRIMARY KEY USING INDEX TABLESPACE users PCTFREE 0;5 are not archived, perform a backup after you create the index. Creating an index with CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY, age INTEGER) ENABLE PRIMARY KEY USING INDEX TABLESPACE users PCTFREE 0;5 has the following benefits:
In general, the relative performance improvement is greater for larger indexes created without CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY, age INTEGER) ENABLE PRIMARY KEY USING INDEX TABLESPACE users PCTFREE 0;9 than for smaller ones. Creating small indexes without CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY, age INTEGER) ENABLE PRIMARY KEY USING INDEX TABLESPACE users PCTFREE 0;9 has little affect on the time it takes to create an index. However, for larger indexes the performance improvement can be significant, especially when you are also parallelizing the index creation. Consider Costs and Benefits of Coalescing or Rebuilding IndexesImproper sizing or increased growth can produce index fragmentation. To eliminate or reduce fragmentation, you can rebuild or coalesce the index. But before you perform either task weigh the costs and benefits of each option and choose the one that works best for your situation. Table 16-1 is a comparison of the costs and benefits associated with rebuilding and coalescing indexes. Table 16-1 To Rebuild or Coalesce ... That Is the QuestionRebuild IndexCoalesce IndexQuickly moves index to another tablespace Cannot move index to another tablespace Higher costs: requires more disk space Lower costs: does not require more disk space Creates new tree, shrinks height if applicable Coalesces leaf blocks within same branch of tree Enables you to quickly change storage and tablespace parameters without having to drop the original index. Quickly frees up index leaf blocks for use. In situations where you have B-tree index leaf blocks that can be freed up for reuse, you can merge those leaf blocks using the following statement: ALTER INDEX vmoore COALESCE; Figure 16-1 illustrates the effect of an CREATE TABLE a ( a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));1 on the index CREATE TABLE a ( a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));2. Before performing the operation, the first two leaf blocks are 50% full. This means you have an opportunity to reduce fragmentation and completely fill the first block, while freeing up the second. In this example, assume that CREATE TABLE a ( a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));3. Figure 16-1 Coalescing IndexesText description of the illustration admin026.gif Consider Cost Before Disabling or Dropping ConstraintsBecause unique and primary keys have associated indexes, you should factor in the cost of dropping and creating indexes when considering whether to disable or drop a CREATE TABLE a ( a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));4 or CREATE TABLE a ( a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));5 constraint. If the associated index for a CREATE TABLE a ( a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));4 key or CREATE TABLE a ( a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));5 constraint is extremely large, you can save time by leaving the constraint enabled rather than dropping and re-creating the large index. You also have the option of explicitly specifying that you want to keep or drop the index when dropping or disabling a CREATE TABLE a ( a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));4 or CREATE TABLE a ( a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));5 constraint. Creating IndexesThis section describes how to create indexes. To create an index in your own schema, at least one of the following conditions must be true:
To create an index in another schema, all of the following conditions must be true:
This section contains the following topics: Creating an Index ExplicitlyYou can create indexes explicitly (outside of integrity constraints) using the SQL statement CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY, age INTEGER) ENABLE PRIMARY KEY USING INDEX TABLESPACE users PCTFREE 0;6. The following statement creates an index named CREATE TABLE b( b1 INT, b2 INT, CONSTRAINT bu1 UNIQUE (b1, b2) USING INDEX (create unique index bi on b(b1, b2)), CONSTRAINT bu2 UNIQUE (b2, b1) USING INDEX bi);5 for the CREATE TABLE b( b1 INT, b2 INT, CONSTRAINT bu1 UNIQUE (b1, b2) USING INDEX (create unique index bi on b(b1, b2)), CONSTRAINT bu2 UNIQUE (b2, b1) USING INDEX bi);6 column of the CREATE TABLE b( b1 INT, b2 INT, CONSTRAINT bu1 UNIQUE (b1, b2) USING INDEX (create unique index bi on b(b1, b2)), CONSTRAINT bu2 UNIQUE (b2, b1) USING INDEX bi);7 table: CREATE INDEX emp_ename ON emp(ename) TABLESPACE users STORAGE (INITIAL 20K NEXT 20k PCTINCREASE 75) PCTFREE 0; Notice that several storage settings and a tablespace are explicitly specified for the index. If you do not specify storage options (such as CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY, age INTEGER) ENABLE PRIMARY KEY USING INDEX TABLESPACE users PCTFREE 0;4 and CREATE TABLE b( b1 INT, b2 INT, CONSTRAINT bu1 UNIQUE (b1, b2) USING INDEX (create unique index bi on b(b1, b2)), CONSTRAINT bu2 UNIQUE (b2, b1) USING INDEX bi);9) for an index, the default storage options of the default or specified tablespace are automatically used. Creating a Unique Index ExplicitlyIndexes can be unique or nonunique. Unique indexes guarantee that no two rows of a table have duplicate values in the key column (or columns). Nonunique indexes do not impose this restriction on the column values. Use the CREATE TABLE c(c1 INT, c2 INT); CREATE INDEX ci ON c (c1, c2); ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1) USING INDEX ci;0 statement to create a unique index. The following example creates a unique index: CREATE UNIQUE INDEX dept_unique_index ON dept (dname) TABLESPACE indx; Alternatively, you can define CREATE TABLE a ( a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));4 integrity constraints on the desired columns. Oracle enforces CREATE TABLE a ( a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));4 integrity constraints by automatically defining a unique index on the unique key. This is discussed in the following section. However, it is advisable that any index that exists for query performance, including unique indexes, be created explicitly Creating an Index Associated with a ConstraintOracle enforces a CREATE TABLE a ( a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));4 key or CREATE TABLE a ( a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));5 integrity constraint on a table by creating a unique index on the unique key or primary key. This index is automatically created by Oracle when the constraint is enabled. No action is required by you when you issue the CREATE TABLE c(c1 INT, c2 INT); CREATE INDEX ci ON c (c1, c2); ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1) USING INDEX ci;5 or CREATE TABLE c(c1 INT, c2 INT); CREATE INDEX ci ON c (c1, c2); ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1) USING INDEX ci;6 statement to create the index, but you can optionally specify a CREATE TABLE c(c1 INT, c2 INT); CREATE INDEX ci ON c (c1, c2); ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1) USING INDEX ci;7 clause to exercise control over its creation. This includes both when a constraint is defined and enabled, and when a defined but disabled constraint is enabled. To enable a CREATE TABLE a ( a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));4 or CREATE TABLE a ( a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));5 constraint, thus creating an associated index, the owner of the table must have a quota for the tablespace intended to contain the index, or the CREATE TABLE b( b1 INT, b2 INT, CONSTRAINT bu1 UNIQUE (b1, b2) USING INDEX (create unique index bi on b(b1, b2)), CONSTRAINT bu2 UNIQUE (b2, b1) USING INDEX bi);3 system privilege. A constraint's associated index always assumes the name of the constraint, unless you optionally specify otherwise. Specifying Storage Options for an Index Associated with a ConstraintYou can set the storage options for the indexes associated with CREATE TABLE a ( a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));4 and CREATE TABLE a ( a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));5 constraints using the CREATE TABLE c(c1 INT, c2 INT); CREATE INDEX ci ON c (c1, c2); ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1) USING INDEX ci;7 clause. The following CREATE TABLE c(c1 INT, c2 INT); CREATE INDEX ci ON c (c1, c2); ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1) USING INDEX ci;5 statement enables a CREATE TABLE a ( a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));5 constraint and specifies the associated index's storage options: CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY, age INTEGER) ENABLE PRIMARY KEY USING INDEX TABLESPACE users PCTFREE 0; Specifying the Index Associated with a ConstraintIf you require more explicit control over the indexes associated with CREATE TABLE a ( a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));4 and CREATE TABLE a ( a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));5 constraints, Oracle allows you to:
These options are specified using the CREATE TABLE c(c1 INT, c2 INT); CREATE INDEX ci ON c (c1, c2); ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1) USING INDEX ci;7 clause. The following statements present some examples. Example 1: CREATE TABLE a ( a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1))); Example 2: CREATE TABLE b( b1 INT, b2 INT, CONSTRAINT bu1 UNIQUE (b1, b2) USING INDEX (create unique index bi on b(b1, b2)), CONSTRAINT bu2 UNIQUE (b2, b1) USING INDEX bi); Example 3: CREATE TABLE c(c1 INT, c2 INT); CREATE INDEX ci ON c (c1, c2); ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1) USING INDEX ci; If a single statement creates an index with one constraint and also uses that index for another constraint, the system will attempt to rearrange the clauses to create the index before reusing it. Collecting Incidental Statistics when Creating an IndexOracle provides you with the opportunity to collect statistics at very little resource cost during the creation or rebuilding of an index. These statistics are stored in the data dictionary for ongoing use by the optimizer in choosing a plan for the execution of SQL statements. The following statement computes index, table, and column statistics while building index e CREATE INDEX emp_ename ON emp(ename) COMPUTE STATISTICS;9 on column CREATE TABLE b( b1 INT, b2 INT, CONSTRAINT bu1 UNIQUE (b1, b2) USING INDEX (create unique index bi on b(b1, b2)), CONSTRAINT bu2 UNIQUE (b2, b1) USING INDEX bi);6 of table CREATE TABLE b( b1 INT, b2 INT, CONSTRAINT bu1 UNIQUE (b1, b2) USING INDEX (create unique index bi on b(b1, b2)), CONSTRAINT bu2 UNIQUE (b2, b1) USING INDEX bi);7: CREATE INDEX emp_ename ON emp(ename) COMPUTE STATISTICS; Creating a Large IndexWhen creating an extremely large index, consider allocating a larger temporary tablespace for the index creation using the following procedure:
Using this procedure can avoid the problem of expanding your usual, and usually shared, temporary tablespace to an unreasonably large size that might affect future performance. Creating an Index OnlineYou can create and rebuild indexes online. This enables you to update base tables at the same time you are building or rebuilding indexes on that table. You can perform DML operations while the index build is taking place, but DDL operations are not allowed. Parallel execution is not supported when creating or rebuilding an index online. The following statements illustrate online index build operations: WHERE COL_X IS NOT NULL0 Note: While you can perform DML operations during an online index build, Oracle recommends that you do not perform major/large DML operations during this procedure. This is because while the DML on the base table is taking place it holds a lock on that resource. The DDL to build the index cannot proceed until the transaction acting on the base table commits or rolls back, thus releasing the lock. For example, if you want to load rows that total up to 30% of the size of an existing table, you should perform this load before the online index build. Creating a Function-Based IndexFunction-based indexes facilitate queries that qualify a value returned by a function or expression. The value of the function or expression is precomputed and stored in the index. See Also: These books provide additional information about function-based indexes. Features of Function-Based IndexesFunction-based indexes allow you to:
How Function-Based Indexes WorkFor the creation of a function-based index in your own schema, you must be granted the WHERE COL_X IS NOT NULL23 system privileges. To create the index in another schema or on another schema's tables, you must have the CREATE TABLE b( b1 INT, b2 INT, CONSTRAINT bu1 UNIQUE (b1, b2) USING INDEX (create unique index bi on b(b1, b2)), CONSTRAINT bu2 UNIQUE (b2, b1) USING INDEX bi);1 and WHERE COL_X IS NOT NULL25 privileges. You must have the following initialization parameters defined to create a function-based index:
Additionally, to use a function-based index:
To illustrate a function-based index, lets consider the following statement that defines a function-based index ( WHERE COL_X IS NOT NULL35) defined on the function WHERE COL_X IS NOT NULL36: WHERE COL_X IS NOT NULL2 In the following SQL statement, when WHERE COL_X IS NOT NULL36 is referenced in the WHERE COL_X IS NOT NULL13 clause, the optimizer considers using the index WHERE COL_X IS NOT NULL35. WHERE COL_X IS NOT NULL3 Table owners should have WHERE COL_X IS NOT NULL40 privileges on the functions used in function-based indexes. Because a function-based index depends upon any function it is using, it can be invalidated when a function changes. If the function is valid, you can use an WHERE COL_X IS NOT NULL41 statement to enable a function-based index that has been disabled. The WHERE COL_X IS NOT NULL42 statement allows you to disable the use of a function-based index. Consider doing this if you are working on the body of the function. Examples of Function-Based IndexesSome examples of using function-based indexes follow. Example: Function-Based Index for Case-Insensitive SearchesThe following statement creates function-based index idx on table emp based on an uppercase evaluation of the CREATE TABLE b( b1 INT, b2 INT, CONSTRAINT bu1 UNIQUE (b1, b2) USING INDEX (create unique index bi on b(b1, b2)), CONSTRAINT bu2 UNIQUE (b2, b1) USING INDEX bi);6 column: WHERE COL_X IS NOT NULL4 Now the WHERE COL_X IS NOT NULL44 statement uses the function-based index on WHERE COL_X IS NOT NULL45ename WHERE COL_X IS NOT NULL46 to retrieve all employees with names that start with WHERE COL_X IS NOT NULL47: WHERE COL_X IS NOT NULL5 This example also illustrates a case-insensitive search. Example: Precomputing Arithmetic Expressions with a Function-Based IndexThis statement creates a function-based index on an expression: WHERE COL_X IS NOT NULL6 WHERE COL_X IS NOT NULL44 statements can use either an index range scan (in the following WHERE COL_X IS NOT NULL44 statement the expression is a prefix of the index) or index full scan (preferable when the index specifies a high degree of parallelism). WHERE COL_X IS NOT NULL7 Examples: Function-Based Index for Language-Dependent SortingYou can use function-based indexes to support a linguistic sort index. WHERE COL_X IS NOT NULL12 is a function that returns a sort key that has been given a string. Thus, if you want to build an index on name using WHERE COL_X IS NOT NULL12, issue the following statement: WHERE COL_X IS NOT NULL8 This statement creates index nls_index on table t_table with the collation sequence WHERE COL_X IS NOT NULL52. Now, the following statement selects from WHERE COL_X IS NOT NULL53 using the WHERE COL_X IS NOT NULL54 index: WHERE COL_X IS NOT NULL9 Rows are ordered using the collation sequence in WHERE COL_X IS NOT NULL52. The following example combines a case-insensitive sort and a language sort: ALTER INDEX vmoore COALESCE;0 Here, an WHERE COL_X IS NOT NULL54 specification does not appear in the WHERE COL_X IS NOT NULL12 argument because WHERE COL_X IS NOT NULL12 looks at the session setting for the language of the linguistic sort key. The previous example illustrated a case where WHERE COL_X IS NOT NULL54 was specified. Creating a Key-Compressed IndexCreating an index using key compression enables you to eliminate repeated occurrences of key column prefix values. Key compression breaks an index key into a prefix and a suffix entry. Compression is achieved by sharing the prefix entries among all the suffix entries in an index block. This sharing can lead to huge savings in space, allowing you to store more keys for each index block while improving performance. Key compression can be useful in the following situations:
You enable key compression using the WHERE COL_X IS NOT NULL63 clause. The prefix length (as the number of key columns) can also be specified to identify how the key columns are broken into a prefix and suffix entry. For example, the following statement compresses duplicate occurrences of a key in the index leaf block: ALTER INDEX vmoore COALESCE;1 The WHERE COL_X IS NOT NULL63 clause can also be specified during rebuild. For example, during rebuild you can disable compression as follows: ALTER INDEX vmoore COALESCE;2 Altering IndexesTo alter an index, your schema must contain the index or you must have the WHERE COL_X IS NOT NULL65 system privilege. Among the actions allowed by the WHERE COL_X IS NOT NULL66 statement are:
You cannot alter an index's column structure. More detailed discussions of some of these operations are contained in the following sections: Altering Storage Characteristics of an IndexAlter the storage parameters of any index, including those created by Oracle to enforce primary and unique key integrity constraints, using the WHERE COL_X IS NOT NULL66 statement. For example, the following statement alters the CREATE TABLE b( b1 INT, b2 INT, CONSTRAINT bu1 UNIQUE (b1, b2) USING INDEX (create unique index bi on b(b1, b2)), CONSTRAINT bu2 UNIQUE (b2, b1) USING INDEX bi);5 index: ALTER INDEX vmoore COALESCE;3 The storage parameters CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY, age INTEGER) ENABLE PRIMARY KEY USING INDEX TABLESPACE users PCTFREE 0;4 and WHERE COL_X IS NOT NULL72 cannot be altered. All new settings for the other storage parameters affect only extents subsequently allocated for the index. For indexes that implement integrity constraints, you can choose to adjust storage parameters by issuing an CREATE TABLE c(c1 INT, c2 INT); CREATE INDEX ci ON c (c1, c2); ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1) USING INDEX ci;6 statement that includes the CREATE TABLE c(c1 INT, c2 INT); CREATE INDEX ci ON c (c1, c2); ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1) USING INDEX ci;7 subclause of the WHERE COL_X IS NOT NULL75 clause. For example, the following statement changes the storage options of the index created on table emp to enforce the primary key constraint: ALTER INDEX vmoore COALESCE;4 Rebuilding an Existing IndexBefore rebuilding an existing index, compare the costs and benefits associated with rebuilding to those associated with coalescing indexes as described in Table 16-1. When you rebuild an index, you use an existing index as the data source. Creating an index in this manner enables you to change storage characteristics or move to a new tablespace. Rebuilding an index based on an existing data source removes intra-block fragmentation. Compared to dropping the index and using the CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY, age INTEGER) ENABLE PRIMARY KEY USING INDEX TABLESPACE users PCTFREE 0;6 statement, re-creating an existing index offers better performance. The following statement rebuilds the existing index WHERE COL_X IS NOT NULL77: ALTER INDEX vmoore COALESCE;5 The WHERE COL_X IS NOT NULL78 clause must immediately follow the index name, and precede any other options. It cannot be used in conjunction with the WHERE COL_X IS NOT NULL79 clause. If have the option of rebuilding the index online. The following statement rebuilds the WHERE COL_X IS NOT NULL77 index online: ALTER INDEX vmoore COALESCE;6 If you do not have the space required to rebuild an index, you can choose instead to coalesce the index. Coalescing an index can also be done online. Monitoring Index UsageOracle provides a means of monitoring indexes to determine if they are being used or not used. If it is determined that an index is not being used, then it can be dropped, thus eliminating unnecessary statement overhead. To start monitoring an index's usage, issue this statement: ALTER INDEX vmoore COALESCE;7 Later, issue the following statement to stop the monitoring: ALTER INDEX vmoore COALESCE;8 The view WHERE COL_X IS NOT NULL81 can be queried for the index being monitored to see if the index has been used. The view contains a WHERE COL_X IS NOT NULL82 column whose value is WHERE COL_X IS NOT NULL83 or WHERE COL_X IS NOT NULL84, depending upon if the index has been used within the time period being monitored. The view also contains the start and stop times of the monitoring period, and a WHERE COL_X IS NOT NULL85 column ( WHERE COL_X IS NOT NULL83/ WHERE COL_X IS NOT NULL84) to indicate if usage monitoring is currently active. Each time that you specify WHERE COL_X IS NOT NULL88, the WHERE COL_X IS NOT NULL81 view is reset for the specified index. The previous usage information is cleared or reset, and a new start time is recorded. When you specify WHERE COL_X IS NOT NULL90, no further monitoring is performed, and the end time is recorded for the monitoring period. Until the next WHERE COL_X IS NOT NULL91 statement is issued, the view information is left unchanged. Monitoring Space Use of IndexesIf key values in an index are inserted, updated, and deleted frequently, the index can lose its acquired space efficiently over time. Monitor an index's efficiency of space usage at regular intervals by first analyzing the index's structure, using the WHERE COL_X IS NOT NULL34 statement, and then querying the WHERE COL_X IS NOT NULL93 view: ALTER INDEX vmoore COALESCE;9 The percentage of an index's space usage varies according to how often index keys are inserted, updated, or deleted. Develop a history of an index's average efficiency of space usage by performing the following sequence of operations several times:
When you find that an index's space usage drops below its average, you can condense the index's space by dropping the index and rebuilding it, or coalescing it. Dropping IndexesTo drop an index, the index must be contained in your schema, or you must have the WHERE COL_X IS NOT NULL95 system privilege. Some reasons for dropping an index include:
When you drop an index, all extents of the index's segment are returned to the containing tablespace and become available for other objects in the tablespace. How you drop an index depends on whether you created the index explicitly with a CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY, age INTEGER) ENABLE PRIMARY KEY USING INDEX TABLESPACE users PCTFREE 0;6 statement, or implicitly by defining a key constraint on a table. If you created the index explicitly with the CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY, age INTEGER) ENABLE PRIMARY KEY USING INDEX TABLESPACE users PCTFREE 0;6 statement, then you can drop the index with the WHERE COL_X IS NOT NULL98 statement. The following statement drops the CREATE TABLE b( b1 INT, b2 INT, CONSTRAINT bu1 UNIQUE (b1, b2) USING INDEX (create unique index bi on b(b1, b2)), CONSTRAINT bu2 UNIQUE (b2, b1) USING INDEX bi);5 index: CREATE INDEX emp_ename ON emp(ename) TABLESPACE users STORAGE (INITIAL 20K NEXT 20k PCTINCREASE 75) PCTFREE 0;0 You cannot drop only the index associated with an enabled CREATE TABLE a ( a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));4 key or CREATE TABLE a ( a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));5 constraint. To drop a constraint's associated index, you must disable or drop the constraint itself. Note: If a table is dropped, all associated indexes are dropped automatically. Viewing Index InformationThe following views display information about indexes: ViewDescriptionALTER INDEX vmoore COALESCE;02 ALTER INDEX vmoore COALESCE;03 ALTER INDEX vmoore COALESCE;04 ALTER INDEX vmoore COALESCE;05 view describes indexes on all tables in the database. ALTER INDEX vmoore COALESCE;06 view describes indexes on all tables accessible to the user. ALTER INDEX vmoore COALESCE;07 view is restricted to indexes owned by the user. Some columns in these views contain statistics that are generated by the ALTER INDEX vmoore COALESCE;08 package or ALTER INDEX vmoore COALESCE;09 statement. ALTER INDEX vmoore COALESCE;10 ALTER INDEX vmoore COALESCE;11 ALTER INDEX vmoore COALESCE;12 These views describe the columns of indexes on tables. Some columns in these views contain statistics that are generated by the ALTER INDEX vmoore COALESCE;08 package or ALTER INDEX vmoore COALESCE;09 statement. What is the use of indexes in Oracle database?Indexes are used in Oracle to provide quick access to rows in a table. Indexes provide faster access to data for operations that return a small portion of a table's rows. Although Oracle allows an unlimited number of indexes on a table, the indexes only help if they are used to speed up queries.
What is true about indexes?What is true about indexes? Explanation: Indexes tend to improve the performance. Take Database Management System Mock Tests - Chapterwise!
Which are true about the index in best practices?Some indexing best practices. Don't index every column. ... . Index columns that you filter on. ... . Only index data that you need to look up. ... . Consider other index types. ... . Use indexes to pre-sort data. ... . Use multi-column indexes, but sparingly. ... . Look after your indexes. ... . Pick your battles.. What is true Oracle index?What is an Index in Oracle? 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.
|