Let suppose that for our two tables, cookbook_t.EXPstats1 has 100 rows and cookbook_t.EXPstats2 has 100 rows. This means when we join these two tables, rows should be 100*100 = 10,000.
The following query will be used to collect statistics on the expression:
/*Query to collect statistics*/SEL A.col3 , B.Col1 FROM cookbook_t.EXPstats1 A Product JOIN cookbook_t.EXPstats2 B ON ( CASE WHEN SUBSTRING( A.col2 FROM 1 FOR 4) ='this' THEN 'Y' ELSE 'N' END ) = ( CASE WHEN SUBSTRING( B.col2 FROM 1 FOR 4) ='test' THEN 'Y' ELSE 'N' END );
Explain plan shown in following block is from a query without having any statistic on expression or any other column:
Explanation‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐We ...
