What are good candidates for creating indexes alongside pk and unique columns?
I always ask myself, should I create an index or not on my Oracle tables, what criteria in the table makes the index useful to create? And is this also the criteria in the request? And are there (in relation to Oracle) different types of indexes that you can create based on some factor (s)?
source to share
Oracle automatically creates a unique index on a column (or set of columns) when a UNIQUE constraint is created. The index is used for constraint enforcement.
Oracle also automatically creates a unique index on a column (or set of columns) when a PRIMARY KEY constraint is created. This index is also used to enforce constraints. There is a slight but slight difference between the PRIMARY KEY constraint and the UNIQUE constraint.
Also, when a column (or set of columns) is frequently used to join a pair of tables, a foreign key relationship must be specified. To do this, the referenced column must be the primary key in the referenced table, which in turn means that this referenced table will have a unique (primary key).
Also, when a particular column (or set of columns) is frequently used in WHERE clauses, it makes sense to create an index, unique or otherwise. Columns with particularly low cardinality (few unique values ββrelative to the total number of rows), BITMAP indexes can be beneficial. The pros and cons of BITMAP indices can be a lengthy discussion.
Column ordering in multi-column indexes should be carefully considered. First, a multi-column index does not work at all as separate single-column indexes on the same columns, but Oracle rarely (if at all, especially without any hint) uses more than one index per query (BITMAP indexes are a possible exception) ... If you usually have columns A, B, and C in a WHERE clause, you might want an index in A, B, and C. However, if you also use A and C a lot in a WHERE clause without B, then you probably want to order the columns in your index as A, C, B. Such an index can also be used when only A is in your WHERE clause. Simply put, Oracle can only use a subset of columns in an index using the index prefix, not a random assortment of columns in the index.
It is also important to note that the more indexes you have on a table, the slower it will write to that table. Just consider all the work Oracle has to do to update the table and all associated indexes. BITMAP indices can be even more significant.
As a final note, EXPLAIN PLAN is your friend. If you find frequently executed queries doing full table scans on large tables, the index might be fine.
source to share
Your inquiries.
You should look or better view your client application profile to see which fields are used the most and / or performance issues. If your application hasn't been built yet, consider how the data will be used. For example; If this is a table that will be used primarily for reporting and you have a datetime field, you will most likely need an index (join or not) on that field.
source to share
A rule of thumb is to create indexes on foreign keys (PCs often have auto-generated indexes), fields involved in joins, where clauses or ordering. Indexes have a cost to insert, so don't create them willy-nilly all over the place. Choose the material that is used most frequently and the queries that have the longest running time to see where you might need to index. Check if the query execution plans are running (different databases call this something else, but all have a way to see how the query will use the indexes) to see if the indexes are being used after they are created. An index that doesn't use anything is just adding the insertion and division times without corresponding impotence at the selected time.
source to share
Depends entirely on what kind of queries you are doing against the table.
Likely candidates include:
- Fields involved in the connection
- Fields that are part of the WHERE clause
- ORDER BY fields
Please don't think that this means that you have to create a whole bunch of indexes on a table, each in one field. I see this a lot and it is almost always the wrong answer. A few additional considerations:
- Consider compound indexes or especially covering indexes.
- Consider the selectivity of a field when deciding whether to index it in general or what order to place it inside the index.
source to share
Some good ideas here. Pay close attention to queries with performance issues and try to create highly selective indexes.
As an aside, it's not a bad idea to keep track of your index usage:
ALTER INDEX <index> MONITORING USAGE;
You can periodically check the value of v $ object_usage.used to see if this index has ever been accessed since monitoring was enabled:
SELECT i.table_name, i.index_name, i.tablespace_name, bytes/1000000 mb, u.used
FROM dba_indexes i JOIN dba_segments ON (i.index_name = segment_name)
LEFT OUTER JOIN v$object_usage u ON (i.index_name = u.index_name)
WHERE i.owner = <schema>
AND NVL(USED,'NO') = 'NO'
AND i.table_name = <table of interest>
ORDER BY bytes DESC;
I have managed to truncate multiple indexes with the information it provides.
source to share