I´m new on Teradata i´m have a doubt on how Teradata works whith the index.
If i have a Primary Index whith 4 columns, if i make a sql with a where with 3 of this columns, Teradata use the index or not? and if i make a sql whith a where with the 4 columns and one more, Teradara use the index?
First to clear about your concept regarding primary index. Teradata primary index: > is use for data distribution among the AMPs > there is always one index in a table and it is must for every table (even if you don't define it explicitly) > you can not recreate/alter it unless you re-create the table (or empty the table) > is use for data access > ensures one AMP operation > is of two types: UNIQUE and NON-UNIQUE
The primary index is not really an index - it is the columns which go into the hashing algorithm to position (or locate) the record physically within the database. If the PI has 4 columns, but you only give values for 3, it does not have all the references to find the record. It cannot use the PI to locate the record. If you give the 4 values for the PI, plus a fifth value, it can locate the string of records (presumably it is non-unique PI or your Where would not need the fifth value to identify the record). In this case it can just search that string of records. By definition, all these records will be chained from the same start point. One proviso though - if your SQL references more than c. 10% of records, it will still use full table scan.
Secondary index is dofferent. If you give 3 of the PI values and only reference a few records, it will scan the SI and use this to locate the records.
Yes, the PI is an effective access path only when exact values of all the component columns are provided in the WHERE clause. That is why the primary key is not always the best choice for primary index. You can specify a primary key or unique constraint separately, and let the primary index be composed of only those columns that are commonly used for join conditions or specified by exact value in queries. You can, additionally, use a partitioned primary index to optimize for range searches on the date column.