Monday, June 8, 2009

INDEX’ler nedir nasıl kullanılır, neden yaratılmalı, index hakkında kısa kısa notlar.

  • 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: