Why does TimeXtender truncate the table when creating a new index? Is there a way to avoid that?
(We’re using TimeXtender legacy 20.10.35.64)
Solved
New index truncates table
Best answer by pontus.berglund
Hi
For non-incrementally loaded tables, adding or changing an index will make TimeXtender want to deploy the table structure. This is the case for both the valid and the raw table.
You can confirm this by manually adding an index to a table > right click and click deploy > click review tasks and note which boxes are checked.
Deploying the (valid/raw) table structure will truncate the (valid/raw) table.
For incrementally loaded tables however, there are different rules. They are inherently more protected against truncation, and the only thing that will truncate them is if “Full load valid table” is deployed.
If I add an index to the valid table of an incrementally loaded table, TimeXtender only wants to deploy the Valid Table Structure.
As you might have noted, it is the same deployment task as in the example with the non-incrementally loaded table. The difference here is that deploying the Valid Table Structure will not truncate the valid table if your table is incrementally loaded.
To summarize.
- If your table is incrementally loaded you can add indexes without having to worry about truncating the valid table.
- If your table is not incrementally loaded, adding an index will truncate the data.
- To add an index in this scenario, you would need to create some sort of backup table to store its data, add the index to your original table and deploy, and then add a table insert to your original table to insert the data from the backup table (or something similar).
I haven’t yet created it on the large table yet, because I’m apprahensive about it being truncated. I’m wondering if there are specific scenarios that we should be aware of when creating an index that will cause the table to be truncated?
Previously I’ve noticed that creating an index in certain cases truncates the table, although I don’t remember the exact table.
The table I want to create a non-clustered index on has these settings:
+5Thank you, but I don’t understand what you mean when you say “check what it wants to deploy”. How can I see if the table uses a specific type of index, or if it will truncate the valid table or not?
I find the difference between [Index Automation] vs [Index Generation] confusing...
Based on the settings below, does TimeXtender manage indexes or not?
- Edit Project → Advanced Settings → Index generation = Automatic
- (right-click) Project → Advanced → Index Automation (Manual)
I also notice that TX creates AutoIndex on the Raw table when there is a primary key set. But it does not always automatically create it on the Valid table unless I toggle “include/Exclude in primary key”. And the Index is not created as a unique index, which I find strange as long as the column(s) are set to be Primary Key…
This is what Xpilot says. To me it looks like the same concept..
Index Automation:
Index Automation is a feature in TimeXtender that manages the creation and maintenance of indexes for optimal performance in your data warehouse.
Index Generation:
Index Generation refers to the process of creating and managing indexes on tables in a data warehouse. TimeXtender allows users to manually create and manage indexes if they prefer more control over the indexing process.
These are the indexes on the large table today, and I want to create a new NonClustered index on the Valid table without it being truncated.
+5Hi
The AutoIndexes in your screenshot are the indexes automatically generated by TimeXtender.
If your table index settings are set to Automatic then the indexes above will automatically be added to the table (note: you can delete or edit these AutoIndexes and create other indexes to tables where the Index setting is set to Automatic). If you however add a new table in your DW, by right-clicking Tables and selecting Add table and set the index settings to manual for this table, and then add fields and mappings to the table, you will notice that no Indexes are present (i.e. no indexes have automatically been created), as shown below.
You can manually add indexes to this table by right-clicking on the table and selecting Index settings under advanced.
If you wanted TimeXtender to automatically add suggested Indexes to this table, even though the Index setting for this table is set to manual, you can do so by selecting Index Automation (Manual) under advanced.
If you have already enabled indexes on the table, by selecting Index settings under Advanced, you will find this option by right-clicking Indexes and selecting Index Automation (Manual). After selecting this the AutoIndexes will be added.
I performed several tests on a table with an incremental mapping (as well as table that was set to incremental load). The tests included removing, adding and changing indexes both for manually added indexes and the AutoIndexes. None of the tests seemed to result in truncation of the data.
Could you please provide an example of an incremental table where an addition, removal or change of an index results in truncation?
Hi
For non-incrementally loaded tables, adding or changing an index will make TimeXtender want to deploy the table structure. This is the case for both the valid and the raw table.
You can confirm this by manually adding an index to a table > right click and click deploy > click review tasks and note which boxes are checked.
Deploying the (valid/raw) table structure will truncate the (valid/raw) table.
For incrementally loaded tables however, there are different rules. They are inherently more protected against truncation, and the only thing that will truncate them is if “Full load valid table” is deployed.
If I add an index to the valid table of an incrementally loaded table, TimeXtender only wants to deploy the Valid Table Structure.
As you might have noted, it is the same deployment task as in the example with the non-incrementally loaded table. The difference here is that deploying the Valid Table Structure will not truncate the valid table if your table is incrementally loaded.
To summarize.
- If your table is incrementally loaded you can add indexes without having to worry about truncating the valid table.
- If your table is not incrementally loaded, adding an index will truncate the data.
- To add an index in this scenario, you would need to create some sort of backup table to store its data, add the index to your original table and deploy, and then add a table insert to your original table to insert the data from the backup table (or something similar).
+5Reply
Rich Text Editor, editor1
Editor toolbars
Press ALT 0 for help
Most helpful members this week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
