You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@trafodion.apache.org by Roberta Marton <ro...@esgyn.com> on 2017/05/30 21:59:08 UTC
Update statistics and metadata tables
Today, we do not create histogram tables in schemas that contains system metadata tables such as "_MD_" and "_PRIVMGR_MD_". There is code in several places that check for system metadata tables and skip operations related to histograms - such as readHistograms. The intent, I think, is to not allow update statistics on system metadata tables.
However, you can "update statistics on table trafodion."_MD_".<system table>" and the update statistics code goes out and creates the necessary histogram tables. So we are not preventing update statistics. However, code is not using statistics for these tables.
So, should we allow update statistics on metadata tables?
Roberta
RE: Update statistics and metadata tables
Posted by Hans Zeller <ha...@esgyn.com>.
+1
Hans
-----Original Message-----
From: Dave Birdsall [mailto:dave.birdsall@esgyn.com]
Sent: Tuesday, May 30, 2017 3:14 PM
To: dev@trafodion.incubator.apache.org
Subject: RE: Update statistics and metadata tables
I don't think the code can use statistics on metadata tables. At least not as presently constructed.
The binder code is recursive. When we bind a particular table reference in a query, we read in the metadata for that table and its statistics if any exist. To read in metadata and histogram information, we prepare and execute queries against the metadata and histogram tables. When we bind *those* queries, we stop the recursion by not attempting to retrieve metadata or histogram information for them. Instead, we use hard-coded metadata and default histogram data.
If we want to use statistics on metadata tables, we'd have to let this recursion go one more level down. That is, while preparing a metadata query, we allow it to generate the same metadata query (but for the metadata table instead), and when preparing *that* query, use hard coded information.
Another thing we'd have to explore is the multitude of queries in the sqlcomp component. Some of these depend on particular plans being chosen. For some of those, we have code to force the plans, but I'll bet there are others where it isn't forced but things just happen to work right. If there are statistics, we could get different plans, with different concurrency characteristics and so on. There are monsters lurking there, I think.
For the time being, it seems safest not to use histogram data for such queries.
Dave
-----Original Message-----
From: Roberta Marton [mailto:roberta.marton@esgyn.com]
Sent: Tuesday, May 30, 2017 2:59 PM
To: dev@trafodion.incubator.apache.org
Subject: Update statistics and metadata tables
Today, we do not create histogram tables in schemas that contains system metadata tables such as "_MD_" and "_PRIVMGR_MD_". There is code in several places that check for system metadata tables and skip operations related to histograms - such as readHistograms. The intent, I think, is to not allow update statistics on system metadata tables.
However, you can "update statistics on table trafodion."_MD_".<system table>" and the update statistics code goes out and creates the necessary histogram tables. So we are not preventing update statistics. However, code is not using statistics for these tables.
So, should we allow update statistics on metadata tables?
Roberta
RE: Update statistics and metadata tables
Posted by Dave Birdsall <da...@esgyn.com>.
I don't think the code can use statistics on metadata tables. At least not as presently constructed.
The binder code is recursive. When we bind a particular table reference in a query, we read in the metadata for that table and its statistics if any exist. To read in metadata and histogram information, we prepare and execute queries against the metadata and histogram tables. When we bind *those* queries, we stop the recursion by not attempting to retrieve metadata or histogram information for them. Instead, we use hard-coded metadata and default histogram data.
If we want to use statistics on metadata tables, we'd have to let this recursion go one more level down. That is, while preparing a metadata query, we allow it to generate the same metadata query (but for the metadata table instead), and when preparing *that* query, use hard coded information.
Another thing we'd have to explore is the multitude of queries in the sqlcomp component. Some of these depend on particular plans being chosen. For some of those, we have code to force the plans, but I'll bet there are others where it isn't forced but things just happen to work right. If there are statistics, we could get different plans, with different concurrency characteristics and so on. There are monsters lurking there, I think.
For the time being, it seems safest not to use histogram data for such queries.
Dave
-----Original Message-----
From: Roberta Marton [mailto:roberta.marton@esgyn.com]
Sent: Tuesday, May 30, 2017 2:59 PM
To: dev@trafodion.incubator.apache.org
Subject: Update statistics and metadata tables
Today, we do not create histogram tables in schemas that contains system metadata tables such as "_MD_" and "_PRIVMGR_MD_". There is code in several places that check for system metadata tables and skip operations related to histograms - such as readHistograms. The intent, I think, is to not allow update statistics on system metadata tables.
However, you can "update statistics on table trafodion."_MD_".<system table>" and the update statistics code goes out and creates the necessary histogram tables. So we are not preventing update statistics. However, code is not using statistics for these tables.
So, should we allow update statistics on metadata tables?
Roberta