We have a process that maps data from one table to another and it fails when the statistics on the source table drop from FULL to ROWCOUNT. We can monitor this with selects on projection_columns. It seems when the data has changed enough by inserts/deletes that the stats become invalid and the db defaults them down to ROWCOUNT. When it does this the select on the source table in the mapper fails. Is there any documentation on what the difference is between FULL and ROWCOUNT? Has anyone else had this issue and are then any suggestions as what we can do maybe with hints on the select statement so it will work with just ROWCOUNT stats? The select joins between 4 other tables where the on conditions all use simple id = id conditions. When it fails it returns the following
Join inner did not fit in memory [(v_temp_schema.prequery x PROPBM.claimwarehouse) using prequery_b0 and claimwarehouse_new2_b0 (PATH ID: 9)].
We could enable JOIN_SPILL but that seems like a band-aid and not really solving the problem when proper statistics make it work.
As I write this I am thinking I could do an explain/profile when it inserts properly and see if I can give hints that mimic the good query plan. Your thoughts would be appreciated.
We are running Vertica 9.0