Archives of the TeradataForum
Message Posted: Thu, 24 Apr 2003 @ 23:07:58 GMT
sorry for the long mail, but there's no simple way to shorten it.
Now running under V2R5 we implemented STJI instead of NUSIs on very large tables.
As we all know: NUSIs especially decrease performance while inserting ...
If you have the case, that you are inserting most of the records in a timely manner.
Say, as example :
Your table holds 12 month data and you are inserting (a couple of times a day) records from today or maybe only a few days old. Then it is better to create a sparse index holding all the columns where NUSIs have been defined before and just exclude e.g. the last week.
The performance of insert-process is not degraded, because most of your records are not older than a week. (This is typical for CDRs at a Telco ...)
We see a performance improvement of about 60-65% for the insert-process. (2 NUSIs under V2R4 vs. sparse index V2R5)
Old version( under V2R4.1 ):
create table t1 ( c1 varchar(), c2 varchar(), c3 varchar(), start_date date ) primary index( c1, start_date ) index (c2), index (c3);
New version( under V2R5 ):
1.) create table t1 ( c1 varchar(), c2 varchar(), c3 varchar(), start_date date ) primary index( c1, start_date ) PARTITION BY RANGE_N(start_date BETWEEN DATE '2003-01-01' AND '2003-01-31'(DATE) EACH INTERVAL '1' DAY , DATE '2003-02-01' AND '2003-02-28'(DATE) EACH INTERVAL '1' DAY , DATE '2003-03-01' AND '2003-03-31'(DATE) EACH INTERVAL '1' DAY , DATE '2003-04-01' AND '2003-04-30'(DATE) EACH INTERVAL '1' DAY );
Now the data is partitioned on a daily base.
/* create the sparse index covering all former NUSIs but NOT the last 7 days */ create join index jit1 as select c2,c3,rowid from t1 where startdate between date '2003-01-01' and '2003-04-18'; /* the enddate is date - 7 or so */ primary index(c2), index(c3);
replace view v_t1 as /* this forces the optimizer to use the ji */ select * from t1 where startdate between date '2003-01-01' and '2003-04-18' union all /* this is the rest of the gang, but with partition elimination this maximal 7 partitions to be searched through, if you maintain the ji on a weekly base */ select * from t1 where start_date > '2003-04-18';
For ad-hoc queries asking for c2 or c3 values, the performance via the view v_t1 is not as fast as with NUSIs, but results are available within 10-15 seconds. (We talk about a 1,2 billion row table spending 300GB of diskspace on a 2-node machine !!!)
The only point is the maintenance of the JI, but we had to do that anyway under V2R4 (Get rid of the oldest records, etc... ), where we had to drop the NUSIs, mload delete, and create the NUSIs again. So there's no additional effort in the new implementation.
Also you will eventually need a little bit more disk space for the sparse index compared to the NUSIs, but with the multivalue column compression under V2R5 you are still better off.
Hope this helps, have fun and query the world
makeItdone IT Services
D.I. Christian Schiefer
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|