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).
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