With the increasingly development of large data, the model of data database has been on the analysis of the data platform application more and more, especially in tianjin ntu general gbase 8 a database, it is understood that the majority of China mobile have been applied in different business deployment of warehouse, and has won multiple expansion high recognition in many provinces, with the increase of supporting business, however, in use process, we found that the number of table after reaching a certain degree, large quantities of query tables such as tables and views the system tables, queries inefficient condition, is this why?
According to the official engineer, because this a few tables belong to virtual table, and there is no table, when you batch query, the system will scan files for the corresponding information comprised of the result set, a drain during the process of scanning the IO and memory, if the table number above 400000, do not add any filter conditions, basic can't query, in order to solve this problem, gbase officials launched a physical system tables: gbase. Table_distribution, the table and the common physical table, record all the information such as name of the table, can be a one-time query all the table information in the outbound, but carefully found, the table just record information, there is no record view, if I want to query the repository list all views, views, and has no batch query, what do we do?
After the author in-depth study, still found a way to really, but this method can only be the DBA background, method, under one management node gcluster/metadata, will record all the object files, each object has a FRM file, through the find find out after filtering out the names of all the objects, and then through the gbase. Table_distribution system tables for all tables listing, by a command comm exclude tables for two listing file name, you can get all the views in the current library list, I validated the method, real and effective, but not to the research and development of the official patch, don't know whether I can use in any scenario, great spirit of welcome to have a research point out more!