Index dan View IF 6323903 Sistem Basis Data

Index dan View IF 6323903 - Sistem Basis Data andika. amalia@ittelkom-pwt. ac. id

Pokok Bahasan 1. 2. 3. 4. 5. 6. 7. 8. 9. Index Tipe index Creating Index Creating XML Index Creating Partition Index Managing Index Execution Plan Creating and Managing View Full Text Search

What is Index ? What for ?

Database Index?

Ilustrasi • Bayangkan, data pada sebuah database disimpan dalam bentuk halaman data. Masing-masing halaman maksimal berukuran 8 KB, sehingga memungkinkan seluruh data dari satu tabel disimpan dalam halaman yang berbeda-beda • Ketika pengguna melakukan query untuk mengambil nilai dari tabel tersebut, maka prosessor akan mencari dan melakukan scanning ke seluruh halaman yang menyimpan data tabel • Semakin besar jumlah data otomatis jumlah halaman akan makin besar. Jika data yang diambil hanya 1 baris saja, maka sangat membuang waktu jika harus melakukan scanning ke seluruh halaman

Index (1) • Indeks dalam database dapat dibayangkan sebagai indeks buku, sehingga melalui indeks dapat dicari letak item tertentu dalam buku dengan mudah • Index adalah sebuah objek dalam sistem database yang dapat mempercepat proses pencarian (query) data • Saat database dibuat tanpa menggunakan index, maka kinerja server database dapat menurun secara drastis. Hal ini dikarenakan resource CPU banyak digunakan untuk pencarian data atau pengaksesan query SQL dengan metode table-scan. Index membuat pencarian data akan lebih cepat dan tidak banyak menghabiskan resource CPU

Index (2) • Mempercepat query yang menggabungkan tabel, serta melakukan sorting dan grouping • Dapat menerapkan keunikan baris/uniqueness of row (jika dikonfigurasi seperti itu) • Berisi kumpulan kunci dan petunjuk • Index merupakan objek struktur data tersendiri yang tidak bergantung kepada struktur tabel • Setiap index terdiri dari nilai kolom dan penunjuk (atau ROWID) ke baris yang berisi nilai tersebut.

Index (3) • Penunjuk tersebut secara langsung menunjuk ke baris yang tepat pada tabel, sehingga menghindari terjadinya full table-scan (scan ke seluruh halaman) • Akan tetapi lebih banyak index pada tabel tidak berarti akan mempercepat query. Semakin banyak index pada suatu tabel menyebabkan kelambatan pemrosesan perintah-perintah DML (Data Manipulation Language), karena setiap terjadi perubahan data maka index juga harus disesuaikan

Index (4)

Penerapan Index • Diperlukan saat : 1. Kolom sering digunakan dalam klausa WHERE atau dalam kondisi join 2. Kolom berisi nilai dengan jangkauan yang luas 3. Kolom berisi banyak nilai null 4. Tabel berukuran besar dan sebagian besar query menampilkan data kurang dari 2 -4% • Tidak perlu index jika : 1. Tabel sering dilakukan DML 2. Tabel berukuran besar dan sebagian besar query menampilkan data hampir 50% lebih 10

Jenis Index (1) • Key pada index di simpan dalam memory dalam struktur B-Tree • Node pada tree tersebut merupakan halaman • Pada SQL Server terdapat 2 jenis index : 1. Clustered Index 2. Non Clustered Index

Jenis Index (2) A clustered index sorts and stores the data rows in the table based on their key values, as shown in the following figure.

Jenis Index (3) A nonclustered index does not have the same physical order of the rows as the index order, as shown in the following figure.

Creating Index (1) • Diterapkan pada kolom tabel atau view yang paling dilakukan query • Jika digunakan lebih dari satu kolom pada penerapan index, maka disebut composite index • Syntax : CREATE [UNIQUE][CLUSTERED | NONCLUSTERED] INDEX index_name ON [{database_name. [schema_name]. | schema_name. }] {table_or_view_name}(column [ASC | DESC][, . . . n]) [WITH(<relational_index_option>[, . . . n])] [ON {partition_scheme_name(column_name[, . . . n]) | filegroup_name | DEFAULT}] <relation_index_option>: : = {PAD_INDEX = {ON | OFF} | FILLFACTOR = fillfactor | ONLINE = {ON | OFF}C

Creating Index (2) • Ketika constraint PRIMARY KEY atau UNIQUE pada dibuat sebuah tabel yang tidak memiliki index, maka DBMS otomatis akan membuat index dengan nama yang sama dengan nama constraint.

Creating Index (3) • Pedoman dalam membuat index 1. Buat clustered index pada kolom yang memiliki nilai unik atau tidak null. 2. Jangan membuat indeks yang tidak sering digunakan. Akan diperlukan waktu dan resource untuk mempertahankan indeks. 3. Buat clustered index sebelum membuat nonclustered index. Clustered index mengubah urutan baris. Sebuah nonclustered index perlu dibangun kembali jika dibangun sebelum clustered index 4. Buat nonclustered index pada semua kolom yang sering digunakan dalam predikat dan dijadikan kondisi join pada query

Creating Index (4) • Latihan membuat Index (gunakan DB Adventure. Works) CLUSTERED INDEX CREATE CLUSTERED INDEX IX_EMPLOYEEID ON Human. Resources. Employee (Employee. ID) WITH FILLFACTOR = 10 NON CLUSTERED INDEX CREATE NONCLUSTERED INDEX IDX_EMPLOYEEID_MANAGERID ON Human. Resources. Employee (Manager. ID)

Creating Index (5) • Membuat Filtered Index § Filtered index adalah index yang dengan terdapat klausa “where” pada skenarionya § Memungkinkan untuk membuat indeks pada baris tabel tertentu daripada keseluruhan tabel (terdapat where, sehingga index berlaku hanya pada data-data yang sesuai kondisi) § Membantu mengurangi ruang penyimpanan indeks dan biaya pemeliharaan dibandingkan dengan indeks untuk satu tabel penuh CREATE NONCLUSTERED INDEX FX_EMPLOYEEID ON Human. Resources. Employee (Employee. ID) WHERE TITLE = ‘Tool Manager’

Creating XML Index (1) • Hanya dapat dibuat pada kolom XML • Mendukung pengindeksan pada satu kolom XML • Tidak dapat memodifikasi primary key • Jenis XML Index : § Primary XML index § Secondary XML index

Creating XML Index (2) • A primary XML index: – Representasi B-Tree Clustered Index dari node dalam data XML. – Membuat beberapa baris data untuk setiap nilai XML di kolom. • A secondary XML index: – Adalah a nonclustered index dari primary XML index. – Jenis • Path index • Value index • Property index

Creating XML Index (3) • Latihan membuat XML Index PRIMARY XML INDEX CREATE PRIMARY XML INDEX PXML_Product. Model_Catalog. Description ON Production. Product. Model (Catalog. Description) PATH INDEX CREATE XML INDEX PIdx_Product. Model_Catalog. Description_Path ON Production. Product. Model (Catalog. Description) USING XML INDEX PXML_Product. Model_Catalog. Description FOR PATH

Creating XML Index (4) • Latihan membuat XML Index VALUE INDEXES CREATE XML INDEX PIdx_Product. Model_Catalog. Description_Value ON ON Production. Product. Model (Catalog. Description) USING XML INDEX PXML_Product. Model_Catalog. Description FOR VALUE INDEXES CREATE XML INDEX PIdx_Product. Model_Catalog. Description_Property ON ON Production. Product. Model (Catalog. Description) USING XML INDEX PXML_Product. Model_Catalog. Description FOR PROPERTY

Just a minute • Which one of the following indexes implements physical sorting of data? a) b) c) d) Nonclustered index Clustered index Unique index XML index • Solution: – Clustered index

Just a minute • Which of the following indexes are types of secondary XML index? a) b) c) d) Path indexes Value indexes Property indexes Optimizing indexes • Solution: – Path indexes, Value indexes, and Property indexes

Creating Partitioned Indexes (1) – Indexes can also be partitioned based on the value ranges. – Partitioning an index will distribute the table data into multiple filegroups, thereby partitioning the table. – To create a partitioned index, you need to perform the following tasks: 1. 2. 3. Create a partition function. Create a partition scheme. Create a clustered index.

Creating Partitioned Indexes (2) 1. Membuat Fungsi Partisi CREATE PARTITION FUNCTION PFOrder. Date (datetime) AS RANGE RIGHT FOR VALUES (‘ 2002 -01 -01’, ‘ 2003 -01 -01’, ’ 2004 -01 -01’, ’ 2005 -01 -01’) 2. Membuat Skema Partisi CREATE PARTITION SCHEME PSOrder. Date AS PARTITION PFOrder. Date TO (fg 1, fg 2, fg 3, fg 4, fg 5) 3. Membuat Clustered Index CREATE CLUSTERED INDEX ix_salesorder. ID ON Sales. My. Sales. Order. Header (Sales. Order. ID) ON PSOrder. Date (Order. Date)

Managing Indexes (1) • The common index maintenance tasks include: – – – Disabling indexes Enabling indexes Renaming indexes Dropping indexes Optimizing indexes

Managing Indexes (2) • Disabling indexes: – When an index is disabled, the user is not able to access the index. – If a clustered index is disabled, then the table data is not accessible to the user. – For example: ALTER INDEX IX_Employee. ID ON Human. Resources. Employee DISABLE Disables a clustered index, IX_Employee. ID, on the Employee table.

Managing Indexes (3) Enabling indexes: Can be achieved by one of the following methods: Using the ALTER INDEX statement with the REBUILD clause Using the CREATE INDEX statement with the DROP_EXISTING clause For example: ALTER INDEX IX_Employee. ID ON Employee REBUILD Rebuilds the clustered index on the Employee table.

Managing Indexes (4) • Indexes can be renamed using the sp_rename system stored procedure. • Indexes can be dropped using the DROP INDEX statement. DROP INDEX IDX_Employee_Manager. ID ON Human. Resources. Employee

Managing Indexes (5) OPTIMIZING INDEXES • SQL Server secara otomatis memelihara indeks setiap ada operasi insert, update atau delete dilakukan • Fragmentasi dapat terjadi dengan adanya hal tersebut apalagi jika jumlah operasi yang dilakukan dalam skala besar (sangat banyak/sering) • Fragmentasi terjadi saat indexes memiliki halaman dimana logical ordering tidak cocok dengan physical ordering pada data file dan ini akan berefek kepada performansi query • Oleh karena itu, index defragmentation dapat dilakukan dengan mengatur kembali index yang ada

Managing Indexes (6) OPTIMIZING INDEXES • Untuk mengetahui level fragmentasi index dapat dilakuka dengan melihat daftar index beserta level fragmentasinya dengan sintaks berikut SELECT a. index_id AS Index. ID, name AS Index. Name, avg_fragmentation_in_percent AS Fragmentation FROM sys. dm_db_index_physical_stats (DB_ID (N’Adventure. Works’), OBJECT_ID (‘Human. Resources. Employee’), NULL, NULL) AS a JOIN sys. indexes AS b ON a. object_id = b. object_id AND a. index_id = b. index_id ORDER BY Fragmentation DESC • Sintaks di atas mengambil data dari db master dan resource dr sql server yang telah diinstal

Displaying Execution Plan • The execution plan: – Enables you to view the details of execution of a SQL query. – Provides the troubleshooting method to analyze a slow executing query. – Is available in the following formats : • Graphical plan • Text plan • XML plan

Just a minute Which of the following types of formats are provided by SQL Server to view the execution plan? a) b) c) d) Graphical plan Text plan XML plan Path index Solution: – Graphical plan, Text plan, and XML plan

Controlling Execution Plan • Sometimes, the query optimizer does not provide the best execution plan. • In such situations, you need to control the query execution by providing hints to the query optimizer. • SQL Server allows you to provide the following types of hints: – Query hints – Join hints – Table hints

Latihan Problem Statement: The production manager of the Adventure. Works, Inc. needs to frequently view data from the Product table in the Production schema. He needs to frequently search for data based on the product number. The Product table contains a large volume of data, and therefore a query takes time to execute. To reduce the time taken in the execution of a query and to improve the performance, you need to suggest a solution. For this, you need to check the performance of the query before and after applying the suggested solution.

Creating View (1) • Seorang administrator database dapat membatasi akses data ke pengguna yang berbeda dengan menggunakan View • View adalah 1. Tabel virtual 2. Data yang ada pada view berasal dari satu tabel atau lebih

Creating View (2) • A view ensures security of data by restricting access to: – – – Specific rows of a table. Specific columns of a table. Specific rows and columns of a table. Rows fetched by using joins. Statistical summary of data in a given table. Subsets of another view or a subset of views and tables. • A view is created by using the CREATE VIEW statement. • Syntax: CREATE VIEW view_name [(column_name [, column_name]. . . )] [WITH ENCRYPTION [, SCHEMABINDING]] AS select_statement [WITH CHECK OPTION]

Creating View (3) • Contoh CREATE VIEW vw. Sal AS SELECT i. Employee. ID, i. Manager. ID, j. Rate FROM Human. Resources. Employee AS I JOIN Human. Resources. Employee. Pay. History AS j ON i. Employee. ID = j. Employee. ID Select * from vw. Sal

Managing Views Management of views include: Altering views Renaming views Dropping views

Managing Views (Contd. ) Altering views: To modify a view, you need to use the ALTER VIEW statement. Syntax: ALTER VIEW view_name [(column_name)] [WITH ENCRYPTION]AS select_statement [WITH CHECK OPTION] Renaming views: A view can be renamed by using the sp_rename procedure. Syntax: sp_rename old_viewname, new_viewname Dropping views: A view can be dropped by using the DROP VIEW statement. Syntax: DROP VIEW view_name

Demo: Creating Views Problem Statement: You are a database developer at Adventure. Works, Inc. You need to frequently generate a report containing the following details of the employees: Employee ID Employee first name Employee last name Title Manager first name Manager last name

Demo: Creating Views (Contd. ) To retrieve this data, you always need to execute the following query on the database: SELECT e 1. Employee. ID, c 1. First. Name, c 1. Last. Name, e 1. Title, c 2. First. Name AS [Manager First Name], c 2. Last. Name AS [Manager Last Name] FROM Human. Resources. Employee e 1 INNER JOIN Person. Contact c 1 ON e 1. Contact. ID = c 1. Contact. ID INNER JOIN Human. Resources. Employee AS e 2 ON e 1. Manager. ID = e 2. Employee. ID INNER JOIN Person. Contact AS c 2 ON e 2. Contact. ID = c 2. Contact. ID Simplify the execution of this query so that you need not send such a large query to the database engine every time the report is required.

Demo: Creating Views (Contd. ) Solution: To solve the preceding problem, you need to perform the following tasks: 1. Create a view. 2. Verify the simplification of the query execution.