CREATE TABLE MY_TEST_TABLE (KEY1 VARCHAR2(50 BYTE) , KEY2 VARCHAR2(50 BYTE) NOT NULL, KEY3 VARCHAR2(50 BYTE) , last_record_date VARCHAR2(50 BYTE) NOT NULL) PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( BUFFER_POOL DEFAULT ) NOCACHE MONITORING ENABLE ROW MOVEMENT PARTITION BY RANGE (last_record_date) ( PARTITION MY_TEST_TABLE_201112 VALUES LESS THAN ('20120101') PCTFREE 10 INITRANS 1 MAXTRANS 255 NOLOGGING, PARTITION MY_TEST_TABLE_201201 VALUES LESS THAN ('20120201') PCTFREE 10 INITRANS 1 MAXTRANS 255 NOLOGGING, PARTITION MY_TEST_TABLE_201202 VALUES LESS THAN ('20120301') PCTFREE 10 INITRANS 1 MAXTRANS 255 NOLOGGING, PARTITION def VALUES LESS THAN (MAXVALUE) PCTFREE 10 INITRANS 1 MAXTRANS 255 NOLOGGING ) NOPARALLEL / -- Constraints for MY_TEST_TABLE ALTER TABLE MY_TEST_TABLE ADD CHECK ("KEY1" IS NOT NULL) / ALTER TABLE MY_TEST_TABLE ADD CHECK ("KEY2" IS NOT NULL) / ALTER TABLE MY_TEST_TABLE ADD CHECK ("KEY3" IS NOT NULL) / ALTER TABLE MY_TEST_TABLE ADD CONSTRAINT pk_MY_TEST_TABLE PRIMARY KEY (KEY1, KEY2, KEY3) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 /
naoseionome wrote:Are you really using a VARCHAR data type to store date information? That would be my #1 concern out of anything you've posted.
last_record_date VARCHAR2(50 BYTE) NOT NULL)
select * from v$version;
naoseionome wrote:Well, the first thing you should do is change the data type from a varchar to a date. The major impact being that a date is not a string, it's a date. If you have processes adding data in different date formats (possible when you use a character to store the information) you're going to be very sad (good luck converting it back to an actual date).
My version is:
Oracle Database 11g Enterprise Edition Release 220.127.116.11.0 - 64bit Production
This is the DDL I used to create the database.
The merge is frequent during each day for about 10M records.
There are several processes doing the update but they are doing it sequencially in order to avoid deadlocks ( because I cannot control the source and each process may update the same keys.
The update uses "KEY1,KEY2,KEY3" in order to update value "LAST_RECORD_DATE" does it have impact in being date or varchar2?
I'm still waiting to receive permissions in order to be able to track the issue, but in meanwhile I'm trying to implement this workaround in order to speedup an development in case It would be required! The only feedback I have for now is that It is not being able to process the total amount of records in the proper time (backlog is being generated).
Assuming the client passes the column as a DATE Oracle will have to do a conversion (implicit) in order to store the data as a string, which will rely on the current NLS settings for the session. This also makes it possible for someone to put completely non-date information into the column (super bad). It's just really bad form to use strings to store dates, numbers, anything that isn't naturally a string.
ME_TUBBZ?select to_char(sysdate) from dual; TO_CHAR(SYSDATE) ----------------------------- 12-jan-2012 10 55:33 1 row selected. Elapsed: 00:00:00.01 ME_TUBBZ?alter session set nls_date_format = 'yyyy-mon-dd'; Session altered. Elapsed: 00:00:00.01 ME_TUBBZ?select to_char(sysdate) from dual; TO_CHAR(SYSDATE) -------------------- 2012-jan-12 1 row selected. Elapsed: 00:00:00.00
naoseionome wrote:Well, you know the data better than i, but i don't see how that could work. If the process is continually issuing updates to the date column based on the primary key, how can you know when it's "safe" to remove that data since the date seems to be constantly changing? Is there something else in the data (perhaps the PK columns) that allows you to make this determination?
I have partitions just because of housekeeping!
I will have to delete millions of rows every months or I just drop the previous month partition...
naoseionome wrote:That'd be great, i'll put a watch on this thread so i don't forget to check back :)
I will share the feedback as soon as I have some results.
Thank you so much for the help it helped my finding the best way to follow,
CREATE TABLE fms_r_imsi_imei_msc_hua (KEY1 VARCHAR2(50 BYTE) , KEY2 VARCHAR2(50 BYTE) , KEY3 VARCHAR2(50 BYTE) , last_record_date VARCHAR2(50 BYTE) , CONSTRAINT PK_FMS_IMSI_IMEI_MSC_HUA PRIMARY KEY (KEY1, KEY2, KEY3)) ORGANIZATION INDEX NOPARALLEL LOGGING /