SQL-Indexing-Page11

-

                                                                                        


👉Indexing

Indexing a table is a way to sort and search records in the table.

Indexes are essential to improve the speed with which records can be located and retrieved from a table.


Indexing involves forming a two-dimensional matrix completely independent of the table on which the index is being created.

It consists of a column that will hold sorted data, extracted from the table column(s) on which the index is created.

Another column called the address field identifies the location of the record in the database.


When a Select is fired to search for a particular record, the database engine must first locate the table on the hard disk.

The database engine reads system information and finds the start location of a table.

Then it performs a sequential search to locate records that match user-defined criteria as specified in the "Select".


Since the data in the index is sorted, the sequential search ends as soon as the database engine reads an index data value that meets the search criteria.

Thus database engine need not search the entire indexed column.

This sharply reduces data retrieval time.


Once the data value in an index is located, the address field in an index specifies a ROWID which points to a data file, block, and the record number directly.

Thus the time taken by the database engine to locate table data on the hard disk is reduced and data retrieval time is vastly improved.


✅RowID: The address field of an index is called RowID.

RowID is an internally generated and maintained value, which uniquely identifies a record. The information in the RowID column provides the database engine the location of the table and a specific record in the database.


When the user defines a primary key or a unique key constraint, the database engine automatically creates a unique index on the primary key or unique key columns.


Note: I shall upload a detailed explanation and inner mechanism of indexing in my upcoming posts on Advanced SQL.

Comments

Post a Comment

Popular posts from this blog

SQL-ForeignKeyConstraint-Page6

-
Image
                                                                              👉 Foreign Key Constraint Foreign Key represent relationships between tables. A foreign key is a column or group of columns whose values are derived from the primary key or unique key of some other table. ✅ The table in which the foreign key is defined is called Foreign table or Detail table. ✅ The table that defines the primary or unique key and is referenced by the foreign key is called the Primary table or Master table. 👉 Features 1)Master table cannot be updated if child records exists. 2)Records cannot be inserted or deleted into a detail table if corresponding records in the master table do not exist. 3)Records of the master table cannot be deleted if corresponding records in the detail table already exist. 👉 O...
Read more

Javascript-OOPs(Part3)-Inheritance - (9)

-
Image
   👉 Javascript with OOPs (Inheritance) In Javascript, Inheritance is done using prototype object. A prototype object is an object pointing to another object. We don't have a direct inheritance in Javascript, we can mimic inheritance using prototype. Let's say, we have one more class that inherits from Customer, and it submits to Server2. function CustomerChild(){         this.submit = function() {         alert ("this submits the data to Server2" + " "      +  this.CustomerName + " "                                       + this.CustomerCode");   }}             CustomerChild.prototype = new Customer(); var cust = new Customer(); cust.CustomerName ("John"); cust.Submit();  --this is a call to the submit method of the Customer class.  cust = new CustomerChild(); cust...
Read more

Javascript-Unobtrusive - (6)

-
Image
  👉 Unobtrusive Javascript In web pages, we have presentation and behavior. Presentation is controlled by HTML and behavior is controlled by javascript. <input type = "button" onclick = "Fun1();" id = btn1/> <script language = javascript>              Function Fun1()                       {alert ("Hi");} </script> Here, button is tightly linked with the behavior. Unobtrusive javascript seperates presentation from behavior, making HTML more sleek. <input type = "button"  id = btn1/> <script src = "jscript1.js" type = "text/ javascript"> </script> code of jscript1.js file: <script language = "javascript">            Function Fun1() {                      alert("Hi");             } var e1 = document.getElementByID("btn1"); e1.onclick = ...
Read more