Hello, In our EDW a table is defined with PI on a coumn and Primary key on two other columns . We are running into maintenance issues due to the PI and Primary key. Can someone sugest if this is a good modeling practice ? if yes is thre an alternate approach instead Primary key. USI is not an option since USI also causig overhead.
a Primary Key is implemented as a USI if there's a PI :-)
PKs are often not implemented in a warehouse, because they might not be used in WHERE/JOIN. And uniquenes must be guaranteed by the load job anyway.
Vanderberg... here is the ddl
CREATE MULTISET TABLE WW_DB01.SRC_TOT_TRL ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
PRE_CD CHAR(1) CHARACTER SET UNICODE CASESPECIFIC NOT NULL,
PRE_TST_DT DATE FORMAT 'YYYY-MM-DD' NOT NULL,
ACT_ID BIGINT NOT NULL,
PRE_HIRE_CD CHAR(36) CHARACTER SET UNICODE CASESPECIFIC NOT NULL,
PER_UNT_CD BIGINT NOT NULL,
IT_NET_CCL_TOT DECIMAL(23,3) COMPRESS 0.000 ,
INC_NFUND_CCL_TOT DECIMAL(23,3) COMPRESS 0.000 ,
REV_CCL_TOT DECIMAL(23,3) COMPRESS 0.000 ,
DLY_EOD_CCL_TOT DECIMAL(23,3) COMPRESS 0.000 ,
UT_PAID_CCL_TOT DECIMAL(23,3) COMPRESS 0.000,
PRIMARY KEY (PRE_CD ,PRE_TST_DT ,ACT_ID ,PRE_HIRE_CD ))
PRIMARY INDEX ( ACT_ID )
PARTITION BY RANGE_N(PRE_TST_DT BETWEEN DATE '2010-01-01' AND DATE '2020-12-31' EACH INTERVAL '1' DAY ,
NO RANGE OR UNKNOWN);
Dnoeth, based on above ddl, PK is working as USI ? Well, I belive the same. Is there any alternate approach to change the table structure without compromising Uniquenes.
simply drop the PK and trust your loading process :-)
You must ensure that the logical PK is not violated during load anyway. You never load a large number of rows if the PK is actually implemented on table level, because a single bad row will result in a rollback (and then you have to find that row).
The easiest way utilizes MERGE instead of INSERT/SELECT or UPDATE. Or you do the traditional approach using EXISTS/NOT EXISTS.
Dnoeth, for this table businees must wanted to enfore PK on the table . Since they don't trust the load process.
If I drop existing PI and change PK to UPI will it maintain the same uniquiness ? or is there any other workaround ? please advise..
A PK enforced as a UPI might fix your table maintenance issues, but it's going to mean that every time you join to it you'll be redistributing it by the ACT_ID, which means you now have performance issues, and they are generally more problematic. Whenever you use the DBMS to enforce table keys you have to compromise some part of the process. If the load process cannot be trusted that, for me, is a bigger problem and likely should be where your effort is applied.
That said, depending on how the table is loaded you may be able to define single table JI's that will essentially store the usable copy of the table data if you find you need to implement the PK as a UPI.
The Primary key is being used as a USI. If you run the following query,
select * from dbc.indices where trim(tablename) = 'src_tot_trl' and trim(databasename) = 'ww_db01'; you should see five result rows.
The row with IndexNumber =1 is the PI. The other four rows consitute what was defined as the PK. The index number for those four rows is 4, the lowest index number available for a secondary index in a table with a PI.
Dropping the PI and replacing it with the PK fields will maintain your uniqueness, but unless all four of those fields are in every query accessing this table, you'll lose your access path and things will get slow and slower depending upon the size of the table....
The first concern in selecting a PI is access path, NOT uniqueness
Thank you jkurdsjuk and Blaine for the datails. I did a Hash check for the proposed PI coulmns it is showing zero skew. But Yes Query access path will be impacted regardless of skew factor . Thank you for your replies...