Archives of the TeradataForum

Message Posted: Thu, 24 Apr 2003 @ 23:07:58 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

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.

2.)

/* 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);

3.)

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

Christian

makeItdone IT Services

D.I. Christian Schiefer

www.makeitdone.com

	<Prev	Next>		<<First	<Prev	Next>	Last>>