index, cursors question

[Saritha Penumudi]

Hello All,

I would really appreciate if you could clarify these doubts on index, cursors by EOD.

1. What are different Types of Indexes:
2. What type of index by default is being for a primary key and unique key?
3. What are different types of cursors and explain them?

Thank you
Saritha

[nilesh Katakkar]

What Database are you using ?

1. What are different Types of Indexes:
Binary tree indexes are the most common across databases. They use B trees to store the index information. In Oracle there are many other indexes. Clustered indexes, Bitmap Indexes and so on.

2. What type of index by default is being for a primary key and unique key?
If you donot mention specific type of index then it's B tree index.

3. What are different types of cursors and explain them?
From DB perspective.. in Oracle - there are implicit cursors explicit cursors and ref cursors.

Do not know why you would put these questions in Java / Jdbc forum. They are DB related.

[Saritha Penumudi]

Thank you Neelesh,

All my questions are more general questions. Not specific to any Database.
I guess I missed DB forum from the list. Hence I thought this is the best place to post my questions. You can move this to DB forum. I am more concerned about getting answers to my questions.

1. What are different Types of Indexes:
Binary tree indexes are the most common across databases. They use B trees to store the index information. In Oracle there are many other indexes. Clustered indexes, Bitmap Indexes and so on.

Can you please explain what each mean and how and when they are used?

2. What type of index by default is being used for a primary key and unique key?
If you donot mention specific type of index then it's B tree index.

I somewhere read that for primarykey, the default index that is being created in Clustered. and for Unique key it is non-clustered. Please correct me if I am wrong.

These are the definitions of the Clustered and Non-Clustered Indexes.
Clustered: Along with the index, whole data is being stored in the index table. whenever data changes, the record is deleted and new record is inserted which is a performance overhead.
Non-Clustered: Along with the index, reference to the data is being stored in the index table. I also read that this is the default index which is by default created for primary key and reply to my question says it is B tree. I am not sure what is the correct answer. I would appreciate if someone could help me in finding more information on this.


Thank you
Saritha

[Jeanne Boyarsky]

Saritha,
This is the correct forum to post database questions.

The reason Neelesh was asking which database you are using is that different databases often have different rules/defaults for this type of thing. That's also why you are reading contradicting information about the type of index used with a primary key.

[nilesh Katakkar]

Ok. Sarita let me try to explain. Since I have mostly worked with Oracle I will talk from that context.

When an index is stored in Binary tree data structure format, its called B tree Index which is the most convenient form used. The way this B tree data structures are orgnaized on the disk further classifies what type it is. B tree index can be clustered as well as non clustered.

When you create primary key the default index created is non clustered. In non clustered index, the b tree formed for the index is basically a balanced tree. All the leaf nodes hold data value of the indexed key column(s) and the corresponding rowid (pointer to the physical localtion of the row on the disk) for it. These B Tree data structures are implemented as double linked lists. Primary keys by definition are unique so unique indexes behave similarly.

There's nothing called as non-unique index in oracle. When you create a non-unique index ,Oracle actually appends rowid to the indexed key column to make it unique and stores it on the disk. Since it's a double linked list, traversing between values is trivial.

A cluster in oracle is a way to store common column(s) in a group of tables on the same database block and to store related data from all these tables together on the same block.Conceptually, you are storing data prejoined. These common columns are called clustered keys. All the data is clustered around cluster key value. And cluster keys are built using B Tree index E.g. if you are joining 3 tables in a cluster, the common column will be the cluster key index stored in B Tree format. And all the data related to these keys from 3 tables will be stored on the same block. Data in Clustered Key tables is not in sorted order however.

It can also be used on single table.

If you use a hash function on the clustered keys it becomes hash clustered index. There's no physical storage for that index. Hash function is applied ( user provided or default) to determine where to find the data.

Now again consider the conventional B tree index. Instead of storing just key and rowid , if we store the entire row itself in the B Tree structure,it would mean even less time to retrieve data. When tables are stored in B Tree index structure they are called Index Organized Tables (IOT) The data in table is naturally ordered. You dont need seperate physical storage for index as index and data are same. Such tables are good for fast retrieval / look up purposes. Frequent updates / insert would mean building and balancing the B Tree appropriately so there are few overheads associated with it.

Terminology differs from DB to DB. If I am not wrong, then cluster index in SQL server is actually similar to IOT.


Bitmap Index - If you store an index as a bitmap instead of B Tree then it's called Bitmap index. THis type of index is primarily used on columns that have very few distinct value.Bitmap is nothing but simple representation of rows in memory. For every row a bit is checked or set to true if that field holds a particular value. Good in data warehousing not so good in OLTP apps as update locks all the rows in the bitmap.

hope this clarifies atleast some of your doubts.