IT分销/经销数据库

select syscat.tables 很慢的一个摄取量

dblook 导出表结构很慢，很慢查看了执行了的sql，这个sql执行了几百次，感觉上导出一张表，就执行下面的sql一次怀疑是下面这个sql执行的：DECLARE THIS_TABLE_ALIAS CURSOR FOR with iv (tabname, tabschema, b...显示全部

dblook 导出表结构很慢，很慢
查看了执行了的sql，这个sql执行了  几百次，感觉上导出一张表，就执行下面的sql一次
怀疑是下面这个sql执行的：
DECLARE THIS_TABLE_ALIAS CURSOR FOR
with iv (tabname, tabschema, base_tabname, base_tabschema, remarks,create_time, definer) as
   ((SELECT  tabname, tabschema, base_tabname, base_tabschema, remarks,create_time, definer
               FROM SYSCAT.TABLES
               WHERE type = 'A' and base_tabname = :H00005
               and                   base_tabschema = :H00007 )
   union all
   (select st.tabname, st.tabschema, st.base_tabname, st.base_tabschema, st.remarks,st.create_time, st.definer
               from iv,  SYSCAT.TABLES st
               where iv.tabname = st.base_tabname and  iv.tabschema = st.base_tabschema and  st.type = 'A'))
select tabname,
tabschema,
base_tabname,
base_tabschema,
remarks,
create_time,
definer
from iv
ORDER BY tabschema, tabname FOR READ ONLY

我执行了一下这个sql的部分
Statement:
  DECLARE C1 CURSOR
  FOR
   SELECT tabname, tabschema, base_tabname, base_tabschema, remarks,
         create_time, definer
   FROM SYSCAT.TABLES
   WHERE type ='A' and base_tabname ='a' and base_tabschema ='a'

Section Code Page = 1208
Estimated Cost = 56141.507812             --这么高的cost
Estimated Cardinality = 0.000000
( 2) Access Table Name = SYSIBM.SYSTABLES  ID = 0,5
      |  Index Scan:  Name = SYSIBM.INDTABLES09  ID = 9
      |  |  Regular Index (Not Clustered)
      |  |  Index Columns:
      |  |  |  1: AUDITPOLICYID (Ascending)
      |  #Columns = 7
      |  Evaluate Block/Data Predicates Before Locking Committed Row
      |  Evaluate Predicates Before Locking for Committed Key
      |  #Key Columns = 0
      |  |  Start Key: Beginning of Index
      |  |  Stop Key: End of Index
      |  Data Prefetch: Eligible 3506
      |  Index Prefetch: Eligible 3506
      |  Lock Intents
      |  |  Table: Intent Share
      |  |  Row  : Next Key Share
      |  Sargable Predicate(s)
      |  |  #Predicates = 3
( 1) |  |  Return Data to Application
      |  |  |  #Columns = 7
( 1) Return Data Completion
End of section

Optimizer Plan:
         Rows
      Operator
         (ID)
         Cost

      3.27867e-10
         n/a
      RETURN
         ( 1)
      56141.5
         |
      3.27867e-10
         n/a
         FETCH
         ( 2)
      56141.5
   /
55226    55226
   n/a       n/a
IXSCAN    Table:
( 3)    SYSIBM
395.307 SYSTABLES
   |
   1
Index:
SYSIBM
INDTABLES09

导出很慢很慢，想这个sql 这么优化呢？

附件：

附件图标 db2trc.part01.rar (8.58 MB)