sql - HSQL 2.2.4，错误的查询计划

为什么IR1需要全扫描？

table=INTR
alias=IR1
access=FULL SCAN

IR2不需要完全扫描：

table=INTR
alias=IR2
access=INDEX PRED

它是完全相同的连接：

EXPLAIN PLAN FOR SELECT *
FROM DTMS.INTR_SUB s
JOIN DTMS.INTR ir1 ON s.CLASS_1 = ir1.CLASS
JOIN DTMS.NDC_INDEX n1 ON ir1.KDC1 = n1.KDC1
JOIN DTMS.INTR ir2 ON s.CLASS_2 = ir2.CLASS
JOIN DTMS.NDC_INDEX n2 ON ir2.KDC1 = n2.KDC1
WHERE n1.NDC = 378204701 AND n2.NDC = 378204701

以下是我的索引：

 s INDEX 1 CLASS_1
 s INDEX 2 CLASS_2
 ir PRIMARY KEY(KDC1,CLASS)
 ir INDEX (CLASS)

如果我禁用s.class_1上的索引（通过添加0），
hsql决定它可以对ir1使用相同的索引
和IR2。

 alias=S
 access=FULL SCAN

 table=INTR
 alias=IR2
 access=INDEX PRED

 table=INTR
 alias=IR1
 access=INDEX PRED

 EXPLAIN PLAN FOR SELECT *
 FROM DTMS.INTR_SUB s
 JOIN DTMS.INTR ir1 ON s.CLASS_1+0 = ir1.CLASS
 JOIN DTMS.NDC_INDEX n1 ON ir1.KDC1 = n1.KDC1
 JOIN DTMS.INTR ir2 ON s.CLASS_2 = ir2.CLASS
 JOIN DTMS.NDC_INDEX n2 ON ir2.KDC1 = n2.KDC1
 WHERE n1.NDC = 378204701 AND n2.NDC = 378204701

这是架构。在数据库管理器中，我必须复制/粘贴
这三个分别创建表以生成表：

 CREATE SCHEMA DTMS AUTHORIZATION SA;

 SET SCHEMA DTMS;

 CREATE CACHED TABLE DTMS.INTR(KDC1 NUMERIC(5) NOT NULL,CLASS NUMERIC(5) NOT NULL,PRIMARY KEY(KDC1,CLASS));

 CREATE CACHED TABLE DTMS.NDC_INDEX(NDC NUMERIC(11) PRIMARY KEY,KDC1 NUMERIC(5) NOT NULL,KDC2 NUMERIC(2) NOT NULL,KDC3 NUMERIC(3) NOT NULL,ACTIVITY_CODE NUMERIC(1) NOT NULL,ROUTE_ABRV CHARACTER(2) NOT NULL);

 CREATE CACHED TABLE DTMS.INTR_SUB(CLASS_1 NUMERIC(5) NOT NULL,DURATION_1 NUMERIC(3) NOT NULL,SCHEDULE_1 NUMERIC(3) NOT NULL,ACTIVITY_CODE_1 NUMERIC(1) NOT NULL,CLASS_2 NUMERIC(5) NOT NULL,DURATION_2 NUMERIC(3) NOT NULL,SCHEDULE_2 NUMERIC(3) NOT NULL,ACTIVITY_CODE_2 NUMERIC(1) NOT NULL,ONSET_CODE NUMERIC(1) NOT NULL,SEVERITY_CODE NUMERIC(1) NOT NULL,DOC_CODE NUMERIC(1) NOT NULL,MGMT_CODE NUMERIC(1) NOT NULL,FILE_POS NUMERIC(15) NOT NULL);

 CREATE INDEX INTR_SUB_CLASS_1 ON DTMS.INTR_SUB(CLASS_1);
 CREATE INDEX INTR_SUB_CLASS_2 ON DTMS.INTR_SUB(CLASS_2);
 CREATE INDEX INTR_CLASS ON DTMS.INTR(CLASS);

最佳答案

此问题已在2.2.5中修复，其中报告了以下内容。

  ][range variable 2
  join type=INNER
  table=INTR
  alias=IR1
  access=INDEX PRED

  ][range variable 4
  join type=INNER
  table=INTR
  alias=IR2
  access=INDEX PRED

关于databasemanager的使用，您可以将整个模式及其表和索引定义为单个sql语句。分号只在结尾处使用。此语句完全由DatabaseManager执行，因为它是SQL中的单个CREATE SCHEMA语句：

CREATE SCHEMA DTMS AUTHORIZATION SA
 CREATE CACHED TABLE DTMS.INTR(KDC1 NUMERIC(5) NOT NULL,CLASS NUMERIC(5) NOT NULL,PRIMARY KEY(KDC1,CLASS))
 CREATE CACHED TABLE DTMS.NDC_INDEX(NDC NUMERIC(11) PRIMARY KEY,KDC1 NUMERIC(5) NOT NULL,KDC2 NUMERIC(2) NOT NULL,KDC3 NUMERIC(3) NOT NULL,ACTIVITY_CODE NUMERIC(1) NOT NULL,ROUTE_ABRV CHARACTER(2) NOT NULL)
 CREATE CACHED TABLE DTMS.INTR_SUB(CLASS_1 NUMERIC(5) NOT NULL,DURATION_1 NUMERIC(3) NOT NULL,SCHEDULE_1 NUMERIC(3) NOT NULL,ACTIVITY_CODE_1 NUMERIC(1) NOT NULL,CLASS_2 NUMERIC(5) NOT NULL,DURATION_2 NUMERIC(3) NOT NULL,SCHEDULE_2 NUMERIC(3) NOT NULL,ACTIVITY_CODE_2 NUMERIC(1) NOT NULL,ONSET_CODE NUMERIC(1) NOT NULL,SEVERITY_CODE NUMERIC(1) NOT NULL,DOC_CODE NUMERIC(1) NOT NULL,MGMT_CODE NUMERIC(1) NOT NULL,FILE_POS NUMERIC(15) NOT NULL)
 CREATE INDEX INTR_SUB_CLASS_1 ON DTMS.INTR_SUB(CLASS_1)
 CREATE INDEX INTR_SUB_CLASS_2 ON DTMS.INTR_SUB(CLASS_2)
 CREATE INDEX INTR_CLASS ON DTMS.INTR(CLASS);

关于sql - HSQL 2.2.4，错误的查询计划，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/6737266/