|
Indexes are created either in the account of the user who owns the table on which they are built, or in a different account. It is generally the practice to create all indexes in the table owner's account. There are several things to consider when you are deciding whether to create an index. This section discusses many of these considerations. Criteria for Creating an IndexYou will likely want to create an index if you have queries that frequently retrieve less than roughly 15% of the rows in a large table. The percentage naturally varies somewhat according to the relative speed of a full table scan on that table and that column and on exactly how clustered the row data is in relationship to the index key. The faster the table scan runs, the lower the percentage of rows has to be to really be able to justify the index. The more clustered the row data is with respect to the index column, the higher the percentage should be. You would create an index to improve the query performance on joins of multiple tables. The index columns in this case would be used for joins. Primary keys (covered in Chapter 14, "Maintaining Data Integrity") already have indexes on them, but foreign keys might be a good candidate for indexing so that the joins perform optimally. Also, foreign key columns are excellent candidates for indexing when there are a significant number of concurrent inserts, updates, or deletes to the parent and child tables. Without this index, the entire child table would have to be locked whenever the parent tables data is changed. Small tables are not likely to benefit from indexes because full table scans will likely be done regardless of indexing. If queries with relationship to the small table become increasingly long, you may find that the table has grown, and an index might be beneficial. Columns that might be strong candidates for indexing have one or more of the following characteristics:
The following would not be good candidates for indexing:
The size of a single index entry should not exceed roughly one-half to two-thirds (minus some overhead) of the available space in the data block. The order that the columns appear in the CREATE INDEX statement can affect query performance. Although Skip Scanning (covered in relation to the Performance Tuning exam) can play a significant role, in general it is better to specify the most frequently used columns first. If you create a single index that includes columns 1, 2, and 3 in the table and then have many queries that access just column 1, the query is speeded up, but a query that accesses just column 2 or just column 3, or columns 2 and 3 together will not gain any benefit from the index. Any table can have any number of indexes. Oracle does not set a definitive limit. However, it is important to remember that the more indexes you have on a table, the more overhead there is whenever the table or its data is modified, inserted, or deleted. To create an index in your own schema, Oracle requires that a user meet at least one of the following conditions:
To create an index in another user's schema, all the following conditions must be true:
Now that we have discussed the types of indexes, we need to discuss how to create these indexes. Creating a B-Tree IndexIf we were to create a B-tree index on our friends table created in Chapter 12, "Managing Tables," we could use the following syntax: CREATE INDEX friends_last_names_idx ON friends(lname) TABLESPACE useridx; This syntax creates an index on the last names of the entries in the table. The general index creation syntax follows:
The general syntax includes the following keywords:
When determining whether you want to build an index, or what kind of index you want to build, you need to balance your DML needs with your query needs. Indexes tend to speed up queries and slow down DML. You should, therefore, minimize indexes on highly volatile tables. Always place indexes in a separate tablespace from the table on which they are built and not in a tablespace that has temporary segments being created in it. Although this will not provide extreme gains in performance, it will allow you to recover just the data tablespaces in case of a failure, speeding recovery, and rebuilding the indexes after the database is back online. Now, how do we create this index in OEM? Using OEM to Create a B-Tree IndexFrom a launched OEM Console, expand the database with which you will be working. Expand the Schema folder and select Indexes from the resulting navigator tree. Choose Object from the menu; then choose Create. Expand the database that resides in the independently launched window and select Index from the list of icons; then click Create. Enter General (index name, index schema, tablespace, table schema, table, columns, and type of index that you are creating), Partitions (if appropriate), Storage characteristics, and Options (parallel or not, the degree of parallelism, and whether to override LOGGING) information in the property sheet and click Create. You can also choose to let OEM automatically define the storage and block utilization parameters based on your estimate of the initial volume for the table, the anticipated table growth rate, the anticipated insert activity on the table, and the order in which rows are to be inserted (sorted or not). GuidelinesIf you are setting extent sizes for index tablespaces, you should attempt to create those tablespaces with uniform extent sizes. Because you really don't "lose" anything if indexes are lost, only the time to rebuild and the performance hit that your applications might see if the index gets lost, you should probably consider NOLOGGING for large indexes. The performance trade-offs are likely worth the risk. This is also a good reason to segregate indexes into their own tablespaces. Initial transactions (INITRANS) should, in general, be higher on indexes than on the tables on which they are built. This is the reasoning behind default INITRANS being 2 rather than 1 (as it is for tables); index entries are, typically, smaller than table row entries. This means that there are usually more entries per block. Again, because indexes work differently from the way tables work, the PCTFREE parameter also works somewhat differently between the two. PCTFREE in index building is used only during the creation of the index to reserve space for more index entries that may need to be inserted into the same blocks. When a key column in the index is updated, there is a logical delete of the original, and an insert of the updated. You would use a low PCTFREE for indexes created on columns that include system generated numbers, such as sequence numbers. The low PCTFREE is excellent because new indexes are appended to the existing entries, and there is little or no need to insert an entry between two existing entries. A higher PCTFREE would be more appropriate for an index where a column of an inserted row can take on any value or can fall within a range of values that already exist. Creating a Bitmap IndexTo create a bitmap index, you use the CREATE_BITMAP_AREA_SIZE parameter to specify the amount of memory to be allocated for bitmap creation. This initialization parameter determines the amount of space in memory that will be used for storing bitmap segments. The default value is 8MB; however, a larger value may enable faster index creation. Columns with low cardinality can allow this value to be lowered. As a general rule, the higher the cardinality, the more memory needed for optimal index creation performance. The CREATE INDEX command is as follows: CREATE BITMAP INDEX index_name ON table_name(column); The only noticeable difference between the CREATE INDEX command and the CREATE BITMAP INDEX command is the addition of the BITMAP keyword and the fact that bitmap indexes cannot be unique, so will never take the UNIQUE key word. Using OEM to Create Bitmap IndexesThe only difference between creating a B-tree index and a bitmap index is the selection of Bitmap as the type. It is possible to change some (but not all) of the storage parameters for indexes after they are created. |
|