CrazyEngineers
CrazyEngineers

  • Various Index Types in Database

    king99

    king99

    @king99-bh2V1z
    Updated: Oct 25, 2024
    Views: 1.3K
    😕😕😕😕😕😕😕😕😕😕😕😕😕

    Hi ,

    Please can someone explain basic concept of Using Indexand can someone explain how Primary index ,Secondary Index,Cluster Index are different from each other .These question is put up because normally language used for explaining index doesnot clarify common persons concept , Please explain in easiest and most correct way so all can understand !!. If it is possible to use bank example and explain one of schema it will more clarify how to use it in actual implementation.

    Thanks to all database experts !!
    0
    Replies
Howdy guest!
Dear guest, you must be logged-in to participate on CrazyEngineers. We would love to have you as a member of our community. Consider creating an account or login.
Replies

  • Ankita Katdare

    Administrator • Apr 18, 2011

    Hi King99,

    I will try to explain it to you, in as simple words as possible.

    An Index is a collection of data entries which is used to find a record in a file. These indices are stored in a table. An Index table consists of 2 parts:
    1. Value of attributes of the file (called as Index field)
    2. Pointer to the actual physical record in the database

    During searching of a file record, index table is searched for getting the memory address. The use of index enables fast searching of data from the secondary memory.

    Q. What are clustered indexes?
    In clustering, index file records are stored physically in order on a key attribute that does not have a unique value for each record. It is the same as dense index i.e. there is a record in index table for each unique value of the element-attribute being searched and a pointer to the first data record with that value.
    The other records with same value of search-attribute are stored sequentially after the first record.
    Are you sure? This action cannot be undone.
    Cancel

  • Ankita Katdare

    Administrator • Apr 18, 2011

    Now let's see, what are primary and secondary indexes.

    1. Primary Index
    It will consist of all prime-key attributes of a table and a pointer to physical memory address of the record in the data file.

    For eg:

    A primary index for the following table looks like this:

    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

    access-database-table

    2. Secondary Index
    It provides a secondary means of accessing a data. It may be on a 'candidate key' of a table.

    For eg:
    35 25 30 37 32 22 42 38 40 26 28 41
    It means that, if we land on index 32, it will point to record number: 5, 11 and 14 and perform a linear search to find the correct from these 3.
    Are you sure? This action cannot be undone.
    Cancel

  • slashfear

    Member • Apr 18, 2011

    @abrakabarba: good explanation buddy 😀 I have few corrections (if it is wrong correct me back please) In clustered index the values will be unique and thats why when you make a column in a table as primary key it by default creates an clustered index on that column.... and more over the records will be physically reordered for example: if you have a table with records as follows,

    3
    4
    2
    5
    1

    So after you apply clustered index on that column it will be reordered as shown below:

    1
    2
    3
    4
    5

    which makes it easier to search the records by using binary search since the records are in ordered way......

    In non clustered index the records are not reordered but logical mapping is done so that the data's are retrieved in a faster way....


    -Arvind
    Are you sure? This action cannot be undone.
    Cancel

  • Ankita Katdare

    Administrator • Apr 19, 2011

    @slashfear: Thanks for correcting! 😀 Took a note of it.
    Are you sure? This action cannot be undone.
    Cancel

  • king99

    Member • Apr 19, 2011

    per
    Please tell me which indexing scheme we can use for above Bank Schema , please justify attributes that you say we should indexed along the explanation and type of index to be used . These type of question will enable all the people who wish to learn more of index and can know how we can apply proper suitable indexing scheme and index on above schema , your explanation will help in more in depth understanding so please explain in simple language .

    Thanks again , database experts !!
    Are you sure? This action cannot be undone.
    Cancel

  • slashfear

    Member • Apr 19, 2011

    @abrakadabra: Not a prob buddy 😀 anytime....
    Are you sure? This action cannot be undone.
    Cancel

  • slashfear

    Member • Apr 19, 2011

    Hey king,

    Thanks for dropping in the schema here ok here is the answers,

    Before we go for explanation first you have to understand that these tables are related to each other!! ok no lets start,

    In the customer table the custno column will be the primary key(since it will have unique entry because each customer will be assigned a unique number to identify them). So when you have primary key placed it by default will have clustered index placed on that column. so accessing the customer details will be easy and fast when you search the table with the custno column.


    In the branch table the branchno column will be primary key so clustured index will be placed on the branchno column.


    The account table is a child table of the customer and branch table since a person who is a customer only will hold an account in that bank and from a specific branch. So the custno and the branchno column from the account table will refer to the customer table and branch table. which means these two columns will have a non-clustured index on these columns.

    The employee table will have clustured index placed on the empno column and it will also reffer to the branch table, since the employee can work in any branch right, So the branchno column in the employee table will have non clustured index which is referring to the branchno column of the branch table.


    Hope that helped you and guess its not your home work!! 😉

    -Arvind
    Are you sure? This action cannot be undone.
    Cancel

  • king99

    Member • Apr 20, 2011

    Hi @slashfear, thanks for the simpler way you have explained the index for above scenario, My aim is to study index but many books confuse you making things complicated so it was a clear example and will help all who want to study index from start , further i can read book to go into depth and study , thanks for answer!!
    @ abrakadabra , thanks for initial response and starting explanation !!
    Are you sure? This action cannot be undone.
    Cancel

  • slashfear

    Member • Apr 20, 2011

    @king: No prob buddy your welcome 😀
    Are you sure? This action cannot be undone.
    Cancel

  • king99

    Member • Apr 20, 2011

    hi pls i have one doubt , in above answer why secondary index or primary index is not feasible to apply ??
    Are you sure? This action cannot be undone.
    Cancel
Home Channels Search Login Register