- If you drop an index, all applications continue to work. However, access to previously indexed data might be slower
- If you create one or more indexes before loading data, the database then must update every index as each row is inserted.
- To improve performance on joins of multiple tables, index columns used for joins.
- Primary and unique keys automatically have indexes, but you might want to create an index on a foreign key
- Small tables do not require indexes. If a query is taking too long, then the table might have grown from small to large.
- LONG and LONG RAW columns cannot be indexed
- An index can be created in the same or different tablespace as the table it indexes. (farklı yerlerde olduğunda ikisininde kullanılabilir online olması gerekir.)
- 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. (I/O olayından kaynaklanıyor. Farklı disk erişimleri daha iyi. Burada dikkat edilmesi gereken nokta indexlerin olduğu tablespace offline olmamalı.)
- Büyük indexler yaratılırken NOLOGGING kullanmak performansı arttırabilir.
■ Space is saved in the redo log files.
■ The time it takes to create the index is decreased.
■ Performance improves for parallel creation of large indexes.
Consider dropping an index if:
■ It does not speed up queries. The table could be very small, or there could be many rows in the table but very few index entries.
■ The queries in your applications do not use the index.
■ The index must be dropped before being rebuilt.
ALTER INDEX statement are:
- Specify parallel execution (or not) and alter the degree of parallelism
- Alter storage parameters or physical attributes
- Specify LOGGING or NOLOGGING
- Enable or disable key compression
- Mark the index unusable
- Start or stop the monitoring of index usage
- Deallocate unused space or allocate a new extent
- Rebuild or coalesce an existing index
No comments:
Post a Comment