Delete using Mload

Tools
Enthusiast

Delete using Mload

Hi,

I am using mload to delete the data from a 10 TB table.
The table is partitioned based on date.
The table is having NUSI on two columns.

When we dropped the 20 partition without drop the NUSI. it taken 2 days.

so i planned to drop the NUSI first, afterwards i started the delete data using mload for 15 partitions.
But when i see the session details in QrySessn the delete is not using the partition to scan the rows, instead it is going for the full scan of the table.

Can anybody suggest.

Rgds,
ganmku
4 REPLIES

Re: Delete using Mload

Hi,
Can you check whether the stats are collected on the partition column and column PARTITION?

regards,
Rupesh
Enthusiast

Re: Delete using Mload

No stats on the partition column..
will it affect....
Senior Apprentice

Re: Delete using Mload

Why do you want to use MLoad to delete all the rows from a partition?
I don't think it's partition-aware, it's always going for a FTS.

Just delete the partitions using an SQL DELETE, this should be faster.

Could you post the PI/partitioning for that table?

Dieter
Enthusiast

Re: Delete using Mload

Hi Dieter,

Please see the below table defn:

CREATE MULTISET TABLE SA_TEST.VOICE_CDR_DATA ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
DTSTRTCHRG DATE FORMAT 'YY/MM/DD',
PLMNID VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
SERVICE VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC DEFAULT 'VOICE',
PLMNID_OPER VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
CIRCLE_CODE VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
SUBSCRIPTIONTYPE VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
CDRTYPE VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
AIRCELSUBSNO VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
AIRCELSUBSIMSI VARCHAR(16) CHARACTER SET LATIN NOT CASESPECIFIC,
AIRCELSUBSIMEI VARCHAR(16) CHARACTER SET LATIN NOT CASESPECIFIC,
OTHERPRTYNO VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
TIMEOFCHRG TIME(0),
CHARGEDURN SMALLINT,
CELLIDFIRSTCELL VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
CELLIDLASTCELL VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
SMSCENTERNO VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
MSCID VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
INCOMINGROUTE VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
OUTGOINGROUTE VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
FILE_NAME VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
JOB_ID VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
LOAD_DATE TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP(0),
RECSEQUENCENUMBER VARCHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC,
OTHERPRTYIMSI VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC,
OTHERPRTYIMEI VARCHAR(16) CHARACTER SET LATIN NOT CASESPECIFIC,
REDIRECTNO VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
MOBSTNROAMNO VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
ORIGLOCNNO VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
TRMLOCNNO VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
CUGINTERLOCKCODE VARCHAR(24) CHARACTER SET LATIN NOT CASESPECIFIC,
CUGINDEX VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
CALLCONFERENCEINDI VARCHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
SERVICEINFO VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
DIALEDDIGITS VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
VISITED_LOCATION VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
aircelimei VARCHAR(14) CHARACTER SET LATIN NOT CASESPECIFIC)
UNIQUE PRIMARY INDEX ( DTSTRTCHRG ,PLMNID ,SUBSCRIPTIONTYPE ,
CDRTYPE ,AIRCELSUBSNO ,OTHERPRTYNO ,TIMEOFCHRG ,CHARGEDURN ,INCOMINGROUTE ,
OUTGOINGROUTE )
PARTITION BY RANGE_N(DTSTRTCHRG BETWEEN DATE '2008-01-01' AND DATE '2015-12-31' EACH INTERVAL '1' DAY );

When i delete the records for the particular partition it is reading all the records.
I have absorbed it through QRYSESSN.
Can you explain in detail.