oracle - 改进连接时的搜索。簇表？

Question

假设我们有三个表:
-建筑物
-房间
-人

一栋建筑可以有 1 到 30 个房间(假设平均为 3 个)
一栋建筑可以容纳 0 到 30 人(平均为 3 人)
一个房间和一个人只能属于一个建筑物。

每个月我们都会向数据库添加约 50,000 座新建筑及其房间和人员。
我们可以删除 2 年以上的数据，因此我们将拥有大约 120 万个建筑物行。

主要问题是我们想要搜索并返回通常(但并非总是)包含至少两个表(建筑物始终存在)的数据，因此我们必须执行联接。

我研究了 3 个解决方案。

• 拥有标准化数据(由于连接和可扩展性较低而导致性能低下)
• 复制“房间”和“人员”表中的建筑数据。 (速度很快，但我一般不喜欢非规范化)
• Oracle 集群表。 (似乎提供了良好的性能并且数据仍然标准化)

所以问题是:
Oracle Cluster适合这种情况吗？
向这样的 Cluster 连续添加行可以吗？
如果您不推荐 Cluster，为什么以及什么更适合？

详细信息:

集群:

SELECT *
FROM
  (SELECT *
    /*+ FIRST_ROWS(200)*/
  FROM BUILDING_C R
  INNER JOIN PEOPLE_C C
  ON (R.BUILDING_id = C.BUILDING_id)  
  INNER JOIN ROOM_C S
  ON (S.BUILDING_id = R.BUILDING_id)
  WHERE S.OPEN_DATE               >= SYSDATE - 60 -1
  AND S.OPEN_DATE               <= SYSDATE - 60
  ORDER BY S.OPEN_DATE
  )
WHERE rownum < 200;--17 consistent gets

标准化:

SELECT *
FROM
  (SELECT *
    /*+ FIRST_ROWS(200)*/
  FROM BUILDING_N R
  INNER JOIN PEOPLE_N C
  ON (R.BUILDING_id = C.BUILDING_id)  
  INNER JOIN ROOM_N S
  ON (S.BUILDING_id = R.BUILDING_id)
  WHERE S.OPEN_DATE               >= SYSDATE - 60 -1
  AND S.OPEN_DATE               <= SYSDATE - 60
  ORDER BY S.OPEN_DATE
  )
WHERE rownum < 200;--44 consistent gets

Answer 1

集群是一种存储密切相关且经常连接到磁盘上同一区域的表的方法。簇键是在查询中通常用来连接表以节省 IO 的一列或多列。但是，如果单个集群行中所有表行的总大小超过磁盘 block 的大小，那么您最终将陷入链接，有一天，您将失去集群的所有优势。在我看来，最好避免，因为考虑到集群中的所有 3 个表的滚动量为 1.2 M，这将是一种开销，这显然会对 HWM 产生影响。

最好选择 JOINS。

例如。

CREATE TABLE BUILDING_C ( BUILDING_ID NUMBER PRIMARY KEY,
                     ADDRESS_FIELD VARCHAR2 ( 25 ) );

CREATE TABLE PEOPLE_C ( BUILDING_ID NUMBER PRIMARY KEY,
                    CUSTOMER_ID NUMBER,
                    ROOM_ID NUMBER,
                    CUSTOMER_DETAILS VARCHAR2 ( 25 ) );

CREATE TABLE ROOM_C ( BUILDING_ID NUMBER PRIMARY KEY,
                  ROOM_ID NUMBER,
                  OPEN_DATE DATE,
                  CURRENT_OCCUPANCY CHAR ( 1 ) );

BEGIN
    DBMS_STATS.SET_TABLE_STATS ( OWNNAME     => 'REALSPIRITUALS',
                            TABNAME  => 'BUILDING_C',
                            NUMROWS  => 20000000 );
END;
/

BEGIN
    DBMS_STATS.SET_TABLE_STATS ( OWNNAME     => 'REALSPIRITUALS',
                            TABNAME  => 'PEOPLE_C',
                            NUMROWS  => 20000000 );
END;
/

BEGIN
    DBMS_STATS.SET_TABLE_STATS ( OWNNAME     => 'REALSPIRITUALS',
                            TABNAME  => 'ROOM_C',
                            NUMROWS  => 20000000 );
END;
/

在您的查询中，您的提示将不会生效，因为您使用了 SELECT */*+ FIRST_ROWS(200)*/ 而不是 SELECT/*+ FIRST_ROWS(200) */* 所以你最终会进入 OPTIMIZER MODE=ALL_ROWS 而不是 OPTIMIZER MODE=FIRST_ROWS

SET AUTOTRACE ON

SELECT
      *
FROM
      (SELECT
            /*+ FIRST_ROWS(200)*/
            *
       FROM
            BUILDING_C R
            INNER JOIN PEOPLE_C C
                ON ( R.BUILDING_ID = C.BUILDING_ID )
            INNER JOIN ROOM_C S
                ON ( S.BUILDING_ID = R.BUILDING_ID )
       WHERE
            S.OPEN_DATE >=   SYSDATE - 60 - 1
            AND S.OPEN_DATE <= SYSDATE - 60
       ORDER BY
            S.OPEN_DATE)
WHERE
      ROWNUM < 200;


Execution Plan
----------------------------------------------------------
   0       SELECT STATEMENT Optimizer Mode=HINT: FIRST_ROWS (Cost=54189 Card=199 Bytes=38 K)
   1    0    COUNT STOPKEY
   2    1      VIEW (Cost=54189 Card=50 K Bytes=9 M)
   3    2        SORT ORDER BY STOPKEY (Cost=54189 Card=50 K Bytes=9 M)
   4    3          FILTER
   5    4            NESTED LOOPS
   6    5              NESTED LOOPS (Cost=52041 Card=50 K Bytes=9 M)
   7    6                MERGE JOIN (Cost=2020 Card=50 K Bytes=5 M)
   8    7                  TABLE ACCESS BY INDEX ROWID REALSPIRITUALS.BUILDING_C (Cost=826 Card=20 M Bytes=1G)
   9    8                    INDEX FULL SCAN REALSPIRITUALS.SYS_C00504893 (Cost=26 Card=20 M)
  10    7                  SORT JOIN (Cost=1194 Card=50 K Bytes=1 M)
  11   10                    TABLE ACCESS FULL REALSPIRITUALS.ROOM_C (Cost=660 Card=50 K Bytes=1 M)
  12    6                INDEX UNIQUE SCAN REALSPIRITUALS.SYS_C00504894 (Cost=0 Card=1)
  13    5              TABLE ACCESS BY INDEX ROWID REALSPIRITUALS.PEOPLE_C (Cost=1 Card=1 Bytes=91)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  spare statistic 3
          0  gcs messages sent
          0  db block gets from cache
          0  physical reads direct (lob)
          0  queue position update
          0  queue single row
          0  queue ocp pages
          0  HSC OLTP Compressed Blocks
          0  HSC IDL Compressed Blocks
          0  rows processed

建议:

1. 在 OPEN_DATE 列上使用索引
2. 如果需要加速，请使用并行提示/*+ parallel (table,n) */
3. 尝试范围分区