Archives of the TeradataForum

Message Posted: Tue, 29 Oct 2002 @ 17:44:30 GMT

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

At my prior company we almost had to do a rollback, NCR came through with an e-fix. Somewhere between 4.0 and 4.1.3 a performance enhancement was dropped. In 4.0, when doing an insert select from one table to another empty table having a primary index that is a subset of the source table PI, the optimizer does not check for duplicate rows.

I don't know how similar the source and target tables need to be for the optimization to work. We had identical sets of columns with different PI's, one optimized for loads and one for reports and we didn't do any joins for the insert. The query on a 70 Gig / 1.5 billion row table 4 node 4850 took 15 minutes on 4.0, and we killed the regression test after 24 hours on 4.1.3. The hash buckets had lots of rows (100K's to millions), so our performance hit was likely an extreme case.

The upgrade to 4.1.3 was forced by a DR on 4.0 where queries were returning incorrect results under some circumstances when using derived tables. We were getting on the order of 10% of the expected rows out of the query, so the downstream processing was faster (but wrong) on 4.0.

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