Application of SOFT RIs in Teradata

Database
Enthusiast

Application of SOFT RIs in Teradata

Hi,

I am new to this forum and I would like to know views of experts on a couple of things.

1. What is the application of Soft RIs in Designing business view layers on a 3NF EDW. I know the concept of Soft RI and how they work and the concept of Join elimination and how it is done.

I would just like to know how we can use that effectively while defining complex views on top of say 20 tables. I am just not able to articulate its usage.

2. Is it a good practice to COMPRESS nulls in TIMESTAMP columns or not ?

Would appreciate your comments!!!

Thanks
Jasrotia
3 REPLIES
Enthusiast

Re: Application of SOFT RIs in Teradata

Assuming that you are building a view by joining 20 tables together (I think that's what you're saying), soft RI would allow you to put all of the columns from all of the tables in the view. If a user submits an SQL statement that only references columns from 10 of the tables, soft RI would allow the optimizer not to access the other 10 tables (depending on the situation). For example, if some of the tables are code tables that are being joined in to obtain a description, then the optimizer doesn't need to access those tables unless the description is referenced in the SQL.

Regarding the question about compressing TIMESTAMP columns, like other data types, it's a good idea to compress values that are used often in the table. So, if you have a particular TIMESTAMP value that is used often, such as a high water mark ('9999-12-31 23:59:59'), then it would be good to compress on that since you would save 10 bytes per row when that value was used (minus the compression bit).
Enthusiast

Re: Application of SOFT RIs in Teradata

Thanks for the revert Barry :) Even I think like wise but was just unsure that whether joins are still eliminated if we mention the columns from the referencing tables in the select list but don't access that while accessing the view. Please just confirm what I understood:

Considering the below tables with SOFT RI defined on PROD_ID and CUST_ID
SALES_FACT( SALES_ID , PROD_ID , CUST_ID)
CUSTOMER(CUST_ID,CUST_NM)
PRODUCT(PROD_ID,PROD_NM)

View Definition
-------------------
Create View Sales_Vw
As
(Select
sf.Sales_Id , sf.Prod_Id , pd.Prod_Nm , sf.Cust_Id , cuCust_Nm
From
Sales_Fact sf , Customer cu , Product Pd
where sf.prod_id = pd.prod_id
and sf.cust_id = pd.cust_id
)

Qry1: (will use Join Elimination)
-------
Select Sales_id , Prod_Id , Cust_Id
from sales_vw

Qry2: (will not use Join Elimination and will join all the three tables)
-------
Select Sales_id , Prod_Id , Cust_Id, Prod_Nm , Cust_Nm
from sales_vw

Right ????
Enthusiast

Re: Application of SOFT RIs in Teradata

Yes, that's correct.