24 Kasım 2022 Perşembe

CREATE INDEX

Giriş
Not : Clustered vs Non-clustered indexes yazısına bakabilirsiniz.

Açıklaması şöyle. CREATE INDEX ile yaratılan index non-clustered ve içinde veri taşımıyor. Sadece clustered index'e pointer gibi.

Normal İşleyiş
Örnek
Şöyle yaparız
CREATE INDEX idx_name ON employee_tab (name);
Açıklaması şöyle
When querying using a non-clustered index, the database performs an index seek to first locate the index key and the clustered index.

If the requested data is not in the non-clustered index tree, the database performs a key lookup in the clustered index tree to retrieve the entire row and join the data.

This key lookup incurs additional I/O and can slow down the query.

INCLUDE
Açıklaması şöyle
When querying using a non-clustered index, the database performs an index seek to first locate the index key and the clustered index.

If the requested data is not in the non-clustered index tree, the database performs a key lookup in the clustered index tree to retrieve the entire row and join the data.

This key lookup incurs additional I/O and can slow down the query. 

To mitigate this issue, you can use the INCLUDE command to include non-key columns in the non-clustered index tree.
Örnek
Şöyle yaparız
CREATE INDEX idx_example ON employee_tab (name) INCLUDE (age);

Hiç yorum yok:

Yorum Gönder

CREATE EVENT - Scheduled Task İçindir

Örnek Şöyle yaparız CREATE EVENT myevent     ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR     DO       UPDATE myschema.mytable SET myc...