i read a statement saying " if a PARTITION BY is defined on a column other than a Primary Index column, then that table cannot have Unique Primary index defined on it. we can only have NON_UNIQUE Primary Index. and it may have secondary indexes."
Suppose you had an employee table with Primary index as Empno, and you wanted to partition it by deptno, you will have to make empno a NUPI though empno was unique. I believe this restriction was placed to avoid TD from probing every partition to ensure the uniqueness of the PI column(s).
That being said, you still have to make sure that your empno was unique. How could you do that ?
The answer is to create a USI on empno table (which is one of those scenarios where you can have two indices on the same column(s)).
Let's say that empno was actually a UPI, now given an empno, we know which AMP it will go to (hashing), but we don't know which partition it belongs, without the deptno information.
So let's say you have 40 depts ( = 40 partitions ) now let's say you wanted to update an employee number to a different number (ok for some weird reasons, though it's a bad idea to update a PI column ... but heck ! this is just an example )
Now let's say the empno was 109 and dept no was 32. the task is to update the empno to 209. But how do you know that 209 is already not there in the table ? you know which AMP it (209) goes to via hashing, so you can send it to that AMP to verify. But how does that AMP know which partition has 209 ? it has no information on that. So it starts looking in all 40 partitions to see if 209 already exist. Which results in bad performance especially when the number of partitions are large.
As I said it's just my theory as to why the restriction was placed. I haven't seen it documented anywhere though.
but i still have some points to say. 1) as you already said.. that was a weird example. when its defined as UPI, we can just forget updating the primary column.
i can put my question more detailed.
1) we all know to which AMP a row goes to.. from hashing algorithem. and that is done by PI,though its UPI or NUPI. 2) and when we use PARTITION BY clause, it will bring all the like values of that column together(in a single partioion) in each AMP's rows depending on which column we decalred the Partition. and we are not bothered here whether its PI column or some other column.
i hope you will accept to both the above points.
if that is the case, why should we have a UPI, when we are putting "PARTITION BY" on some other column. becuse the first fact is not disturbing the second fact and the second fact is not disturbing the first.
i hope you are able to understand the query... if you didnt understand any single point just reply back, so that i can explain you more detailed. thanks once again.
thats a good start actually. ok.. i can explain like this here the PI is empno and the value that we are trying to insert is 409. so the PE will finalyse the AMP depending on the row-id thats obtained using hashing algorithem. so the AMP is ready. so it will place this row into the AMP. as its the UPI, it should have a duplicate row of empno as 409.if its there, the query will abort for sure. this thing will happen even without "PARTITION BY" clause also.
in that case why are they so specific about PARTITION alone?
You got it right to the point that it's easy to decide which AMP the record should go for 409.
Ok now let's talk about the difference between PPI tables and NON PPI tables.
In non PPI Tables, the table is logically sorted in the order of row hash (row ids to exact, but it won't make much of a diff because row hash is at the MSB...)
So if you want to a dupe check for 409, the AMP has to just take the hash of 409 and do a search (now I needn't tell you that search algorithms could be implemented efficiently on a sorted key )
for PPI tables, the records are FIRST ordered by their partition numbers and with in a partition they are ordered by hash. which means that Teradata has to check for the hash code in each partition separately. That's certainly in efficient.
To give a comparable exercise draw a (balanced one for ease of understanding) binary tree of 100 nodes with numbers as data. Pick a leaf node number and count the number of comparisons you will have to make to reach that node. ( do the same exercise looking for a non-existing number )
Now make 10 different binary trees each having 10 nodes and put the same numbers there. Pick the leaf node of 6th binary tree and try counting the number of comparisons you need to make starting from the first tree to reach that node. Now try searching for a number in the 10th tree and try searching for a number that doesn't exist.
Welcome to the damage of partitioning.
Don't cheat make sure you put 100 nodes in that tree !
Thanks for your responses. I understand the concept of PPI and NON-PPI in a table. But my question was different. What I am concerned about is… we have PARTITION used in our table, but not on the PI column. Its on another column. In that case we should not have UPI defined on that table, instead only NUPI is allowed. Now my question here is why is that UPI not allowed?
PARTITION concept is used only for grouping the rows which already distributed per AMP.(what I mean is.. By that time its already decided to which amp the row should go. Once the AMP is decided, all the rows of that table in that AMP are going to get grouped depending on the partition column value). because of this concept the searching time will get reduced as you said. thats absolutely correct.
If it is the case, why is that UPI and NUPI making difference, when we are not PARTITIONING by the PI column.
( You didn't do that exercise I told you ? did ya ? )
Ok so we have an agreement that it takes the same amount of effort to decide to which AMP a row should go whether the table is PPI or non PPI.
Ok given that I was inserting record (409, 33).
So let's say AMP X is the target for hashing on 409.
So the AMP is decided. Everything is cool so far.
for the non PPI table AMP will use that hash, search on that hash and check if 409 is already there ( remember that the table is sorted on row hash, so search is faster )
Now for the PPI table let' say we had 40 depts, then AMP has to probe in all the partitions (within a partition it's sorted on rowhash, but you still don't know the parition to look for a duplicate value) to check for the row hash of 409 that's because there could be a (409, 12) already there or may be a (409, 39) is already there ... and so on .... which means that AMP has to go to each of the 40 partitions and search for this empno to ensure that it's not already there. But the catch is it doesn't know which partitions can contain 409, so it has to search all the partitions.
So what's the big deal ?
Big deal is that searching over multiple partitions are highly time consuming that searching one giant partition (I mean non - PPI table).
It's like the above Tree example I gave you. it's faster to search in a single tree of 100 nodes than in 10 trees of 10 nodes each. ( Seriously if you do that exercise you would understand the difference ). It's a common error to think that both operations take same time, that's why I asked you to do that exercise, in reality the amount of search time required is quite a few many times more.
And if you remember in TD tables can have thousands of partitions, which makes the amount of time quite HUGE.
Due to this time consuming effort in searching for duplicate PI values in all partitions, it was decided not allow a UPI.
BTW I mean to say "it's inefficient to search in all the partitions" in my previous post. I am sorry if I miscommunicated that.
Now let me put another question. Let's say you need to find a book in a library. you know only the name of the book, not whether it's a Chemistry book or a Physics book or so.
All the books irrespective of the Dept are arranged in the order of the alphabets.
All the books are divided into different departments. and within department the are arranged in the alphabetical order.
Which would help you find the book faster given you don't know the dept to which the book belong ? P.S. Imagine you are in library of congress and you have tones of depts, for this question !