{"id":1049,"date":"2020-06-11T09:00:00","date_gmt":"2020-06-11T06:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=1049"},"modified":"2026-02-24T11:21:10","modified_gmt":"2026-02-24T08:21:10","slug":"pos-apothikeyontai-oi-vaseis-dedomenon-kai-ti-einai-ta-indexes","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-apothikeyontai-oi-vaseis-dedomenon-kai-ti-einai-ta-indexes\/","title":{"rendered":"How databases are stored and what indexes are"},"content":{"rendered":"

We have seen what a relational database is and that our entities are logical objects called tables.<\/p>\n\n\n\n

But how are they stored \u201cphysically\u201d in an RDBMS (relational database management system)?<\/h4>\n\n\n\n

Tables like objects are also stored on a hard disk.<\/p>\n\n\n\n

You call their information the most fragmented part of it page<\/strong> (page). Several pages together form one extent<\/strong>. <\/p>\n\n\n\n

It should be mentioned that in Oracle<\/strong> the page size is not constant, while in SQL Server<\/strong> it is 8K. As also in Oracle <\/strong>a set of extents is called segment<\/strong>.<\/p>\n\n\n\n

What is query optimizer and execution plan?<\/h4>\n\n\n\n

By storing a huge amount of data on hard drives, the recovery of specific information becomes time-consuming with a high cost of resources.<\/p>\n\n\n\n

The RDBMS <\/strong>they have a tool that works like a GPS. <\/p>\n\n\n\n

It can find the paths to follow to get to the data and choose the fastest way with the least cost.<\/p>\n\n\n\n

This tool is called query optimizer<\/strong> and its corresponding map is called execution plan<\/strong>.<\/p>\n\n\n\n

What are indexes and statistics?<\/h4>\n\n\n\n

The main allies of the query optimizer are the indexes<\/strong> and statistics<\/strong>. As Index we define a storage structure on the hard disk associated with a table or view. <\/p>\n\n\n\n

Statistics are a set of statistical information for a table or view such as number of records, average length of each field, how many fields are empty (null), etc.<\/p>\n\n\n\n

As changes are made to the database, the data on disk changes. So these two structures need maintenance. The indexes defragmentation\/rebuild<\/strong> and statistics update<\/strong>.<\/p>\n\n\n\n

The tables \/ views that have changes over 10% and their statistics have not been updated are called stale<\/strong>.<\/p>\n\n\n\n

\"\"
Diagram of how the database data is stored, on a "physical" medium.<\/figcaption><\/figure>\n\n\n\n

What is the difference between clustered and non-clustered indexes?<\/h2>\n\n\n\n

<\/p>\n\n\n\n

Clustered index<\/h4>\n\n\n\n

Clustered index<\/strong> is the index that has its data in order based on the field defined, usually the primary key<\/strong> (it is the default behavior in SQL Server) and there can only be one per entity. <\/p>\n\n\n\n

Tables \/ views that do not have a clustered index are called heap. <\/strong>By using them the data is physically stored on the disk not randomly but in order. <\/p>\n\n\n\n

For example, in a table that had the identity number defined as a primary key, those numbers starting with A would be in neighboring blocks. <\/p>\n\n\n\n

So it would be very easy for the RDBMS to answer a query like bring me as many citizens whose ID starts with A. <\/p>\n\n\n\n

As a result, when we talk about a clustered index, we are talking about the way the actual data of the records are stored on the hard disk.<\/p>\n\n\n\n

The key fields of indexes, whether clustered or non-clustered, are usually organized with its architecture b+ tree<\/a>. This is so that they are faster and easier to access.<\/p>\n\n\n\n

Non-clustered index<\/h4>\n\n\n\n

Non-clustered index<\/strong> unlike clustered indexes they are separate from the entity record data and function like an index.<\/p>\n\n\n\n

They contain one or several fields (eg Surname, First Name) that we have defined to be created. Through pointers (row locators) they point to the line where the entire record of the table\/view is located or to the primary key of the clustered index if it is not a heap. <\/p>\n\n\n\n

For example if we create a non-clustered index on the Name field of the customer table. We will be able to easily bring customers whose name is Kostas or whose name starts with K.<\/p>\n\n\n\n

\"\"
Example of a table with a Clustered Index that also has two Non-Clustered.<\/figcaption><\/figure>\n\n\n\n

Through the example we see that the real table (with Clustered Index) is in order according to the customer code which is the field where the index is created. <\/p>\n\n\n\n

In the two Non-Clustered the data is ordered by a different field, one by phone and the other by name. <\/p>\n\n\n\n

However, if in any of them we need extra information outside of the Non-Clustered Index of eg Surname. A key should be made lookup<\/strong> to the client table to fetch the value for each of them.<\/p>\n\n\n\n

This is usually a time consuming process towards the base.<\/p>\n\n\n\n

In the event that we want our data to be in order (sorted<\/strong>) with a field, but also contains information from the rest of the data in the table without lookup, then we make this field include<\/strong>. <\/p>\n\n\n\n

For example in the Non-Clustered Index in the Name we add (include<\/strong>) as information the adjective.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

What is the drawback?<\/h4>\n\n\n\n

In closing, it should be mentioned why, since indexes are useful, not to make many non-clustered indexes in most fields or not to include all records.<\/p>\n\n\n\n