Archives of the TeradataForum
Message Posted: Thu, 05 Feb 2004 @ 16:35:49 GMT
Just to add some clarity about what the actual difference is between hard and soft RI, I thought a couple of exmples might be useful.
Below are two explains from a V2R5 system with soft RI applied to the MyOrders table. Below the explains I have included the show table after I applied the soft RI. Only the MyOrders table had to be altered for soft RI to work. Notice in both explains that the customer table is never accessed.
explain select m_custkey,m_orderdate,m_totalprice from MyOrders, customer where m_custkey = c_custkey and c_custkey between 14 and 24; *** Help information returned. 16 rows. *** Total elapsed time was 1 second.
explain select c_custkey, sum(m_totalprice) from customer, MyOrders where m_custkey = c_custkey and c_custkey between 14 and 24 group by c_custkey; *** Help information returned. 21 rows. *** Total elapsed time was 1 second.
With soft RI we don't have to access the customer table because we have essentially told the optimizer when we established soft RI that for every m_custkey value in the MyOrders table there will be a c_custkey value in the customer table. So the optimizer knows that "c_custkey between 14 and 24" is equivalent to "m_custkey between 14 and 24". Thus the join is not needed because the selection criteria can be taken from the customer table and applied to the order table. Of course, if there were other selection criteria on customer, we would have to perform the join.
The reason there is no overhead with soft RI is that we never attempt to validate the relationships when data is updated. Soft RI means you trust the ETL end to guarentee that the relationships always have integrity. The optimizer simply accepts, trusts, that the integrity is there, but the database never enforces it.
This is only something useful to sites that have an extremely high level of data integrity and can guarantee that relationships between tables are always kept in sync.
Here is how the MyOrders looks with soft RI defined.
show table MyOrders; *** Text of DDL statement returned. *** Total elapsed time was 1 second. --------------------------------------------------------------------------- CREATE MULTISET TABLE CAB.MyOrders ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL ( M_ORDERKEY INTEGER NOT NULL, M_CUSTKEY INTEGER NOT NULL, M_ORDERSTATUS CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL, M_TOTALPRICE DECIMAL(15,2) NOT NULL, M_ORDERDATE DATE FORMAT 'yyyy-mm-dd' NOT NULL, M_ORDERPRIORITY CHAR(15) CHARACTER SET LATIN CASESPECIFIC NOT NULL, M_CLERK CHAR(15) CHARACTER SET LATIN CASESPECIFIC NOT NULL, M_SHIPPRIORITY INTEGER NOT NULL, M_COMMENT VARCHAR(79) CHARACTER SET LATIN CASESPECIFIC NOT NULL, FOREIGN KEY ( M_CUSTKEY ) REFERENCES WITH NO CHECK OPTION CAB.CUSTOMER ( C_CUSTKEY )) UNIQUE PRIMARY INDEX ( M_ORDERKEY );
|Copyright 2016 - All Rights Reserved|
|Last Modified: 15 Jun 2023|