Primary Indexing

Jigar Vora
A free video tutorial from Jigar Vora
Planning Manager at Panasonic
3.9 instructor rating • 8 courses • 30,397 students

Learn more from the full course

TERADATA: Data Warehousing technology

Teradata tutorial with complete real life examples for people who would like to get certification as DBA.

06:07:02 of on-demand video • Updated June 2015

  • Understanding Datawarehousing concepts
  • Get Certified as : Teradata Certified Technical Specialist
  • Get Certified as : Teradata Database Administrator
English [Auto] Hello folks welcome to the screencast on to promote primary index. Now we have talked about Teradata architecture. How does it behave and Legarda different fallback mechanism example of an McKenna's and you've talked about all those different orders actually. What is it be buying that and about the different features available in the data. Right now let's proceed further and talk about how does data gets stored inside it then the data data is right. So just as in our relational table or relational devil means a table consisting of rows and columns. You have a primary key which will uniquely identify a particular rule. Similarly in terror data you have something called a primary index. But it is not the same as a primary key which you have in relational database. Primally index is something which helps us to effectively distribute rows in a table across different employees. If I remember I talked about employees right like you have a parsing engine then a by net you have impis and then virtual disk. Right. So data should be arranged in impis in such a manner that it can be easily accessed. Right. So who decides the data distribution across impis lake. Suppose you have this Impey one. OK. Let me make an example. Me take an example. Suppose you have a Deeble which comes comes that it contains customer related information. And it compares hundred records. OK. And this hundred records are supposed to be divided among Xoom three different employees. Which in turn will store data on the virtual disk. Right. So if you have hundred records to be stored across these three MP MP 1 MP MP 3 but what we do is that we store data in this fashion. Suppose we store it to record 0 5 records here and the remaining 15 records you know what does it generally add. Lord budo to a particular impurely this particular impy one has too many records to be processed. So they should be effectively VI by which we can distribute the guards near about evenly across different agencies right. And this particular way or this particular method by which we can control or we can regulate the distribution of cards across impis is known as primary index primary index is someone who will take care of that data gets it across employees in an even manner. So suppose you have 40 quotes here you have to do and if I would like to go to you this is what even. Rather than having it go to it and the rest of the world among these two it's sort of human to have 40 to get heard. So who takes care of these distribution across impis at a detail level that when you have data in a table it's a primary index who takes care of all that stuff. OK so if you see it's the physical mechanism used to assign a reward when in B which means if you do find primary index in a particular way based upon that way your rules will be assigned to an MP key and a table should have a parameter index at any cost. You do require a primary index to go and then distribute do because different employees get then a primary index cannot be changed once you define a particular parameter index. You can go to the primary index if at all you want to Gordonstoun your primary index for a particular table. You will have to recreate that again. OK. So I'll come to the syntax of how we define a in there. But before that let's talk about a few features of your primary index and what are the different types of primary index available. So we know that it's a physical mechanism which will be used to send rules to employees and a table should have or should have a primary index. It's a requirement to have a pram in the center table and BERMAN This cannot be changed if the index choice of columns is unique. We call this unique primary index. OK. So a particular table can have two types of indices OK it can have either a unique parameter index which means all the records all the columns are unique all the records are unique and you can even have a non-unique. So an example of a unique term newness would be something like this. Suppose you have an employee table you have employee Adey employee name and salary added up the employees in your employee table. The third employee name is again Sam. And then you pass on some salary to these folks. And suppose this is your employee OK not if you select disparate selection of family indices depend upon you it depends upon you which column you are selecting as a primary index. So generally the tumbril is that you select a column which is unique. So your employer I.D. is unique. Right. So and if you see the values here are changing. Right. It's not similar. So this one can be termed as a unique premi index. And what'll happen when you select it so that unique tremendous what'll happen one drew with this particular Impey the other two could this particular be the third one. It's hurting the. So this is harder to distibution will happen when you declare unique there are many index not assume that there's a table where you are bullied by business that no I don't want to employ ideas to primary unless you make employee name as a primary index that is certainly possible but didn't discuss it with the non-unique family because sortable can have a pram unique or an non-unique primary index. In this case what'll happen. So in the case of a non-unique you might have record one year. Second one can come in here the third one. Here's the difference shading factor. The third one will again go to the first. Why. Because we are distributing based on him and your salary. We are distributing based upon primary index and here your primary index is the sample rate. So that's how we distribute data across impis using unique primary index and non-unique primary index. So if you see this particular laid a unique family and this is something where we choose Look if the index choice of columns is unique We call that unique print the primary index and the unique primary index choice will result in even distribution of the rows of tables across all impis we have seen right that it'll distribute those evenly. This particular thing. And if you select a non-unique family and then in that case if the next choice of column is unique because this is non-unique primary index and really the Zentral even distribution of the rows in the table proportional to the degree of uniqueness of the rate. So your degree of uniqueness of the key here was to write like you if you have three records of one url one was unique and the other two were same. So your data will be distributed accordingly. OK so this was a bold unique index. Non-unique Prem re-index and effective distribution of data across different impis helps us in improved joint performance. And it helps us to provide a more optimized spot. OK you remember like our buzzing engine is supposed to pass on the most optimized plan to the AIM piece. Right. So if you use the index judiciously you use in a more optimized V then it'll certainly add on to your performance to give you an increased performance. OK. So this is the significance of having primally in this table and primally index out of two types. One is unique indexed and the other one is non-unique RMV index. And based upon unique fremd index and non-unique from the index your distribution of data across imbues the No let's look at the syntax of hard to be equal or did create parameter index. OK. This index is something like this if you want to create a unique plane the next. You just write on the create table statement just as we do in normal relational tables. Create table give the table name within brackets give the column names along with the Data Types of the bracket and you specify the unique primary and of kin specified the keyword unique primary index and then specify which column you want to make it as the primary index so your table will be created in didn't do today is which will have unique Trembley indexes on them. OK. Now if you want to create a non unique primary next the syntax to create a non-unique BMD indexes this create table table. Give the column names along with the data types. Here we don't specify any unique word. The difference is that if you just write a create table statement and then write and index and then the column in brackets to non-unique in. So to create a unique member in this we need to specify a keyword called unique index. And then within brackets week of the column you want to act as PEMRA next. And in case of non-unique primary index we're right just like you were and in this not unique. And then within brackets would give the column two to 1 to act as nonuniform the index. OK. Now as I do that if you want to change suppose as if not we have kept column B as the primary index as a unique index at a later point of time. The requirement comes in that no one column as a unique and then what we need to do is that we need to blubbed the table and recreate it again. OK. Again here in this example we have kept only one column as primary index. We can have a combination of 64 columns which can act as your primary index. You can have something like this create table table name suppose let me take this example on. If you have Employee ID employee name and salary you can create indexes something like this create table employee employee ideas in Dejah. Employee name is Frederick out of 20. And then salaries again in teacher if you want to create a unique index. Right. The unique primary industry we're. And if you want employee ID an employee name board to act as a primary index we can certainly do that. Employee ID call my employee name and this way we can opt in 64 columns which can act as a primary index be it for unique and in this be it for non-unique permanent. So this is how we go out and create a unique Trembley next. And if you want to create a non-unique primary index just remove this unique way it will create your own unique premiere. So by default it gives you a unique brand index if you want to explicitly create a unique premi next. You need to specify the keyword unique. OK. And I remember I do that every table need to have a primary index into the data. Otherwise the rules will not be distributed across in pairs and you will not be able to access the records. So assume that by mistake a developer forgets to create a primary index He just writes create table table name and then this particular statement he doesn't write this OK. Just for this. Then in recent versions of data data we have a functionality there by default in the first column will be taken as a primary index. So even if you don't specify a primary index your first column will be taken as a primary index for your table. OK. This is something if you do by mistake but the good programming practice is to mention the primary index in your table when you are creating the table so be it unique or non-unit you should go ahead and mention it all right. So be unique or not unique you should go ahead and mention that in that table because this is a very good programming practice. And suppose if you required other columns to be made as the primary index rather then the first column then it does require it to specify that you were unique from the next or just a from index. OK so this was a screencast where we talked about the importance of having the index in a table because this certainly helps us improve joint performance. We are better prepared and we know in detail that there are rules countryside and it helps us in easy retrieval of it. If your data is distributed uniformly across imbues and all the employees are sharing the workload evenly then you'll get faster results. And that's what the aim of Brambilla index is. And how do we create premice index. This is the syntax. All right. So what this will get back again in the subsequent screencast where we will go in further detail about other indexes get taxed for it and in the screencast have a good think.