The only way I know of is to create a hash key from all the fields (or at least all non-key fields). You can use the three fields that combine to a unique key to keep track of new or removed items. You can check for each key value if the calculated hash has changed. If the hash value is different at least one field has a new value. barryharmsen wrote a bit about it here: www.qlikfix.com/2014/03/11/hash-functions-collisions/
I think you're right. Calculating a hash for primary key fields doesn't tell you what has changed and including all fields that may have been modified comes down to reading all records anyway.
Isn't there a technique in RDBMS to define triggers that add your virtual primary keys to a new (delta) table whenever a record changes in the original table? Could serve as a delta index.
Jeroen, I mean the following implementation:
LOAD RowNo() as ID
first 1 LOAD ID as RowsNum
Order By ID desc;
DROP Table tempRowsNo;
LET vRowsNum = peek('RowsNum',0,'tempRowsNum');
DROP Table tempRowsNum;
SQL SELECT * FROM MyTable
SELECT TOP $(vRowsNum ) * FROM MyTable;
Thank you all for your time to help me with this topic.
To resolve this issue I did the following.
After gathering information about the database it seems that there is a field which is unique and sequential but only per category.
So I created a loop per category which fetching the highest number for every category.
Again thank you for all your help.