Use of a Primary Index

Database

Use of a Primary Index

Hello,

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?

Thanks
Tags (1)
12 REPLIES
WAQ
N/A

Re: Use of a Primary 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

Re: Use of a Primary Index

Hello,

The Primary Index is UNIQUE and i need to know if i should use on my "sql-where" all the columns of the Index in order to use it or i could use more or less

Thanks
N/A

Re: Use of a Primary Index

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.

HTH

Re: Use of a Primary Index

Hi,

Thanks

Re: Use of a Primary Index

Thanks

Re: Use of a Primary Index

Hi,

My table has a combination of 4 columns as UPI.The 4th column is a date column.

I ran a query to get all the data for year 2010:-

Sel Col1,col2,col5,col6 from tbl where
col1='string'
and col2 in (val1,va2)
and col3=value
and col4 between '2010/01/01' and '2010/12/31'

It resulted in an all Amp Retrieve and all rows scan. I thought, may be the range on col4 is causing it to full table scan instead of UPI access.So, I changed the query:-

Sel Col1,col2,col5,col6 from tbl where
col1='string'
and col2 in (val1,va2)
and col3=value
and extract(year from col4)='2010'

It resulted in an all Amp Retrieve and all rows scan.

I decided to granularize my col4 into year(col7) and month(col8).Removed col4 from PI and added col7 and col8.I then ran the below query:

Sel Col1,col2,col5,col6 from tbl where
col1='string'
and col2 in (val1,va2)
and col3=value
and col7=2010
and col8 in(1,2,3,4,5,6,7,8,9,10,11,12);

It resulted in an Single Amp Retrieve and UPI access!!

Does that mean, that for PI access one needs to always have an equivalent match and range queries will not utilize UPI. Also, a function on PI column will limit the use of UPI?

Regards,
Ayush Jain

Re: Use of a Primary Index

And yes, I performed collect stats on the index in all the scenarios(Even though, I dont believe that should make a difference ?)

Re: Use of a Primary Index

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.

Re: Use of a Primary Index

I may sound a bit crazy, the same query which I posted couple of days ago:-

Sel Col1,col2,col5,col6 from tbl where
col1='string'
and col2 in (val1,va2)
and col3=value
and col7=2010
and col8 in(1,2,3,4,5,6,7,8,9,10,11,12);

Now results into an all-rows scan and all-amp retrieve..Any ideas why would have happened?