What is heap Table

You can find an N number of articles of the Heap Table over the internet dictionary.

But still, I would like to write theory on Heap Table which is also interesting for me.

In simple term, Table without a cluster index is called as Heap Table. Data is stored in the heap without specifying an order.

Initially, data stored in the order of its inserted, but later database engine can move data around in the heap so you cannot predict the order of data and because of this you have to apply ORDER BY clause for each and every use.

When you can use Heap Structure?

Generally, this is not advisable to use Heap but still sometime database architecture can use the Heap table.

You can use Heap structure when the table is tiny. If a table has a very small amount of data then you can use a Heap structure because in this condition Table Scan performs faster than Index Scan.

Heap Structure use RID (Row Indentifier) to fetch data from file and data page. RID contains file number, data page number and slot details of the page.

When you cannot use heap table ?

Simple, when the amount of data is higher and require a predefined ORDER BY to the SELECT bulk of data.

You cannot use a heap table when frequently queried is required on the table.
You cannot use a heap table when data are grouped together because group of the data is required proper ordered of data.

Heap table has also different terminology when you look into a MySQL Heap Table.
Please visit this page to learn Heap table in MySQL.

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from dbrnd.com

Leave a Reply

Be the First to Comment!

Notify of