Archives of the TeradataForum

Message Posted: Wed, 10 Mar 2004 @ 12:54:51 GMT

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

<-- Anonymously Posted: Wednesday, March 10, 2004 07:45 -->

Hi,

does anyone of you know why dropping of partitions doesn't also delete the underlying data but moves it to the "NO RANGE" partition?

Here is the table structure:

CREATE MULTISET TABLE test.test2
  ,NO FALLBACK
  ,NO BEFORE JOURNAL
  ,NO AFTER JOURNAL
(
   call_start_date         DATE FORMAT 'yyyy-mm-dd' NOT NULL
   ...
   ...
  ,managed_file_id         INTEGER                  NOT NULL
  ,managed_file_row_number INTEGER                  NOT NULL
  ,call_reference          VARCHAR(40)
)
PRIMARY INDEX ( managed_file_id ,managed_file_row_number ,call_reference
)
PARTITION BY RANGE_N(call_start_date  BETWEEN DATE '2003-07-01' AND DATE
'2006-12-31' EACH INTERVAL '1' DAY ,NO RANGE, UNKNOWN);

the drop partition statement...

alter table test.test2
modify primary index
drop range between DATE '2003-07-01' and DATE '2003-07-02' EACH INTERVAL
'1' DAY WITH DELETE;

....only deletes the partitions but moves the data to the "NO RANGE" partition. This takes ages.

Then I modified the table in that I created it without a "NO RANGE" partition.

After trying to delete the 2 partitions again with the same alter table statement listed above I then got a "Partition violation..." error message. It looks for me as if a "NO RANGE" partition in this case is needed to move the data of the deleted partitions into it. But why does it behave like this?

Anyone of you had similar problems?

Thanks in advance...

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