这二个视图分别统计的是哪些库中的表和视图的总量(其中是否包含视图)
有没有做过 检测: 实际累加计算过?
将每一个库的表(或视图)的个数找出来。进行计算总个数
tables是一张视图,是__all_table的子集。
视图的sql如下
CREATE VIEW `TABLES` AS select /*+ leading(a) no_use_nl(ts)*/ cast('def' as char(512)) as TABLE_CATALOG, cast(b.database_name as char(64)) as TABLE_SCHEMA, cast(a.table_name as char(64)) as TABLE_NAME, cast(case when (a.database_id = 201002 or a.table_type = 1) then 'SYSTEM VIEW' when a.table_type in (0, 2) then 'SYSTEM TABLE' when a.table_type = 4 then 'VIEW' else 'BASE TABLE' end as char(64)) as TABLE_TYPE, cast(NULL as char(64)) as ENGINE, cast(NULL as unsigned) as VERSION, cast(NULL as char(10)) as ROW_FORMAT, cast( coalesce(ts.row_cnt,0) as unsigned) as TABLE_ROWS, cast( coalesce(ts.avg_row_len,0) as unsigned) as AVG_ROW_LENGTH, cast( coalesce(ts.data_size,0) as unsigned) as DATA_LENGTH, cast(NULL as unsigned) as MAX_DATA_LENGTH, cast(NULL as unsigned) as INDEX_LENGTH, cast(NULL as unsigned) as DATA_FREE, cast(NULL as unsigned) as AUTO_INCREMENT, cast(a.gmt_create as datetime) as CREATE_TIME, cast(a.gmt_modified as datetime) as UPDATE_TIME, cast(NULL as datetime) as CHECK_TIME, cast(d.collation as char(32)) as TABLE_COLLATION, cast(NULL as unsigned) as CHECKSUM, cast(NULL as char(255)) as CREATE_OPTIONS, cast(case when a.table_type = 4 then 'VIEW' else a.comment end as char(2048)) as TABLE_COMMENT from ( select cast(0 as signed) as tenant_id, c.database_id, c.table_id, c.table_name, c.collation_type, c.table_type, usec_to_time(d.schema_version) as gmt_create, usec_to_time(c.schema_version) as gmt_modified, c.comment from oceanbase.__all_virtual_core_all_table c join oceanbase.__all_virtual_core_all_table d on c.tenant_id = d.tenant_id and d.table_name = '__all_core_table' where c.tenant_id = effective_tenant_id() union all select tenant_id, database_id, table_id, table_name, collation_type, table_type, gmt_create, gmt_modified, comment from oceanbase.__all_table) a join oceanbase.__all_database b on a.database_id = b.database_id and a.tenant_id = b.tenant_id join oceanbase.__tenant_virtual_collation d on a.collation_type = d.collation_type left join ( select tenant_id, table_id, row_cnt, avg_row_len, row_cnt * avg_row_len as data_size from oceanbase.__all_table_stat where partition_id = -1 or partition_id = table_id) ts on a.table_id = ts.table_id and a.tenant_id = ts.tenant_id where a.tenant_id = 0 and a.table_type in (0, 1, 2, 3, 4, 14) and b.database_name != '__recyclebin' and b.in_recyclebin = 0 and 0 = sys_privilege_check('table_acc', effective_tenant_id(), b.database_name, a.table_name)