bad cardinality estimates for metadata queries

[taoufik ben abdellatif]

cardinality estimation for metadata queries is not correct. Specifically for the case of an index join and the join is on the index column. The cardinality of the join should reflect that of the left child of the join.

Example of a metadata table that has the issue:

prepare st1 from select T.CONSTRAINT_TYPE, o.OBJECT_NAME from trafodion."_MD_".table_constraints T, trafodion."_MD_".objects O where T.table_uid = o.object_uid;

>>explain options 'f' st1;

LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------

3 . 4 root 5.00E+003
2 1 3 hybrid_hash_join 5.00E+003
. . 2 trafodion_index_scan OBJECTS 1.00E+002
. . 1 trafodion_scan TABLE_CONSTRAINTS 1.00E+002

--- SQL operation complete.
>>

[taoufik ben abdellatif (taoufik-abdellatif)]

Changes were made to method HistogramCache::createColStatsList in optimizer/NATable.cpp to look for columns that have unique indices specified on them and flag them as unique. The FetchHistograms logic in hs_read.cpp uses this flag to set the uec of the column to be the same as the table rowcount.

[Julie Thai (julie-y-thai)]

Verified on workstation, daily build 20140807_0830.
Verified cardinality of hybrid_hash_join reflects left child of the join.
>>control query shape hybrid_hash_join(scan(TABLE 'T', path 'TRAFODION."_MD_".TABLE_CONSTRAINTS', forward
, blocks_per_access 1 , mdam off),
scan(TABLE 'O', path 'TRAFODION."_MD_".OBJECTS_UNIQ_IDX', forward
, blocks_per_access 1 , mdam off));
+>+>+>
--- SQL operation complete.
>>prepare st1 from select T.CONSTRAINT_TYPE, o.OBJECT_NAME from trafodion."_MD_".table_constraints T, trafodion."_MD_".objects O where T.table_uid = o.object_uid;

--- SQL command prepared.
>>explain options 'f' st1;

LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------

3 . 4 root 1.00E+002
2 1 3 hybrid_hash_join u 1.00E+002
. . 2 trafodion_scan TABLE_CONSTRAINTS 1.00E+002
. . 1 trafodion_index_scan OBJECTS 1.00E+002

--- SQL operation complete.