Oracle 中的数据库备份 - 导出数据库还是使用其他工具？

Question

Matt D

Asked: 2015-12-03 08:08:37 +0800 CST2015-12-03 08:08:37 +0800 CST 2015-12-03 08:08:37 +0800 CST

更有效地使用 Union

772

寻找一种更有效的方法来实现我的观点。此视图将表连接到自身以显示数据库中每个部分的使用位置。换句话说，如果我是某个部分的一部分，请将我列为子部分和部分。它为 10 个级别的子部分执行此操作。

我没有创建此视图，但结果将 33 万条记录变成了超过 200 万条。生成的视图随后用于各种报告。该操作目前需要 13 多分钟才能运行。我认为这个视图在 2000 个零件的位置时是个好主意，但在 75,000 个时它现在无法使用。

这是 SQL（在 Oracle 10g 上）：

CREATE OR REPLACE VIEW PART_COMPLETE_WU
(wu_part, lvl, type, top_part)
AS
SELECT P.ID, 1,'P',R1.WORKORDER_BASE_ID FROM PART P, REQUIREMENT R1
WHERE R1.WORKORDER_TYPE = 'M'
AND R1.PART_ID = P.ID
UNION
SELECT P.ID, 2,'P', R2.WORKORDER_BASE_ID FROM PART P, REQUIREMENT R1, REQUIREMENT R2
WHERE R2.WORKORDER_TYPE = 'M'
AND R1.PART_ID = P.ID
AND R2.PART_ID = R1.WORKORDER_BASE_ID
UNION
SELECT P.ID, 3,'P', R3.WORKORDER_BASE_ID FROM PART P, REQUIREMENT R1, REQUIREMENT R2, REQUIREMENT R3
WHERE R3.WORKORDER_TYPE = 'M'
AND R1.PART_ID = P.ID
AND R2.PART_ID = R1.WORKORDER_BASE_ID
AND R3.PART_ID = R2.WORKORDER_BASE_ID
UNION
SELECT P.ID, 4,'P', R4.WORKORDER_BASE_ID FROM PART P, REQUIREMENT R1, REQUIREMENT R2, REQUIREMENT R3, REQUIREMENT R4
WHERE R4.WORKORDER_TYPE = 'M'
AND R1.PART_ID = P.ID
AND R2.PART_ID = R1.WORKORDER_BASE_ID
AND R3.PART_ID = R2.WORKORDER_BASE_ID
AND R4.PART_ID = R3.WORKORDER_BASE_ID
UNION
SELECT P.ID, 5,'P', R5.WORKORDER_BASE_ID FROM PART P, REQUIREMENT R1, REQUIREMENT R2, REQUIREMENT R3, REQUIREMENT R4, REQUIREMENT R5
WHERE R5.WORKORDER_TYPE = 'M'
AND R1.PART_ID = P.ID
AND R2.PART_ID = R1.WORKORDER_BASE_ID
AND R3.PART_ID = R2.WORKORDER_BASE_ID
AND R4.PART_ID = R3.WORKORDER_BASE_ID
AND R5.PART_ID = R4.WORKORDER_BASE_ID
UNION
SELECT P.ID, 6,'P', R6.WORKORDER_BASE_ID FROM PART P, REQUIREMENT R1, REQUIREMENT R2, REQUIREMENT R3, REQUIREMENT R4, REQUIREMENT R5, REQUIREMENT R6
WHERE R6.WORKORDER_TYPE = 'M'
AND R1.PART_ID = P.ID
AND R2.PART_ID = R1.WORKORDER_BASE_ID
AND R3.PART_ID = R2.WORKORDER_BASE_ID
AND R4.PART_ID = R3.WORKORDER_BASE_ID
AND R5.PART_ID = R4.WORKORDER_BASE_ID
AND R6.PART_ID = R5.WORKORDER_BASE_ID
UNION
SELECT P.ID, 7,'P', R7.WORKORDER_BASE_ID FROM PART P, REQUIREMENT R1, REQUIREMENT R2, REQUIREMENT R3, REQUIREMENT R4, REQUIREMENT R5,
REQUIREMENT R6, REQUIREMENT R7
WHERE R7.WORKORDER_TYPE = 'M'
AND R1.PART_ID = P.ID
AND R2.PART_ID = R1.WORKORDER_BASE_ID
AND R3.PART_ID = R2.WORKORDER_BASE_ID
AND R4.PART_ID = R3.WORKORDER_BASE_ID
AND R5.PART_ID = R4.WORKORDER_BASE_ID
AND R6.PART_ID = R5.WORKORDER_BASE_ID
AND R7.PART_ID = R6.WORKORDER_BASE_ID
UNION
SELECT P.ID,8,'P', R8.WORKORDER_BASE_ID FROM PART P, REQUIREMENT R1, REQUIREMENT R2, REQUIREMENT R3, REQUIREMENT R4, REQUIREMENT R5,
REQUIREMENT R6, REQUIREMENT R7, REQUIREMENT R8
WHERE R8.WORKORDER_TYPE = 'M'
AND R1.PART_ID = P.ID
AND R2.PART_ID = R1.WORKORDER_BASE_ID
AND R3.PART_ID = R2.WORKORDER_BASE_ID
AND R4.PART_ID = R3.WORKORDER_BASE_ID
AND R5.PART_ID = R4.WORKORDER_BASE_ID
AND R6.PART_ID = R5.WORKORDER_BASE_ID
AND R7.PART_ID = R6.WORKORDER_BASE_ID
AND R8.PART_ID = R7.WORKORDER_BASE_ID
UNION
SELECT P.ID,9,'P', R9.WORKORDER_BASE_ID FROM PART P, REQUIREMENT R1, REQUIREMENT R2, REQUIREMENT R3, REQUIREMENT R4, REQUIREMENT R5,
REQUIREMENT R6, REQUIREMENT R7, REQUIREMENT R8, REQUIREMENT R9
WHERE R9.WORKORDER_TYPE = 'M'
AND R1.PART_ID = P.ID
AND R2.PART_ID = R1.WORKORDER_BASE_ID
AND R3.PART_ID = R2.WORKORDER_BASE_ID
AND R4.PART_ID = R3.WORKORDER_BASE_ID
AND R5.PART_ID = R4.WORKORDER_BASE_ID
AND R6.PART_ID = R5.WORKORDER_BASE_ID
AND R7.PART_ID = R6.WORKORDER_BASE_ID
AND R8.PART_ID = R7.WORKORDER_BASE_ID
AND R9.PART_ID = R8.WORKORDER_BASE_ID
UNION
SELECT P.ID,10,'P', R10.WORKORDER_BASE_ID FROM PART P, REQUIREMENT R1, REQUIREMENT R2, REQUIREMENT R3, REQUIREMENT R4, REQUIREMENT R5,
REQUIREMENT R6, REQUIREMENT R7, REQUIREMENT R8, REQUIREMENT R9, REQUIREMENT R10
WHERE R10.WORKORDER_TYPE = 'M'
AND R1.PART_ID = P.ID
AND R2.PART_ID = R1.WORKORDER_BASE_ID
AND R3.PART_ID = R2.WORKORDER_BASE_ID
AND R4.PART_ID = R3.WORKORDER_BASE_ID
AND R5.PART_ID = R4.WORKORDER_BASE_ID
AND R6.PART_ID = R5.WORKORDER_BASE_ID
AND R7.PART_ID = R6.WORKORDER_BASE_ID
AND R8.PART_ID = R7.WORKORDER_BASE_ID
AND R9.PART_ID = R8.WORKORDER_BASE_ID
AND R10.PART_ID = R9.WORKORDER_BASE_ID
UNION
SELECT D.ID, 1, 'D', D1.WORKORDER_BASE_ID FROM DOCUMENT D, DOCUMENT_REF_WO D1
WHERE D1.WORKORDER_TYPE = 'M'
AND D1.DOCUMENT_ID = D.ID
UNION
SELECT D.ID, 2,'D', R2.WORKORDER_BASE_ID FROM DOCUMENT D, DOCUMENT_REF_WO D1,  REQUIREMENT R2
WHERE R2.WORKORDER_TYPE = 'M'
AND D1.DOCUMENT_ID = D.ID
AND R2.PART_ID =D1.WORKORDER_BASE_ID
UNION
SELECT D.ID, 3, 'D', R3.WORKORDER_BASE_ID FROM DOCUMENT D, DOCUMENT_REF_WO D1,  REQUIREMENT R2, REQUIREMENT R3
WHERE  R3.WORKORDER_TYPE = 'M'
AND D1.DOCUMENT_ID = D.ID
AND R2.PART_ID =D1.WORKORDER_BASE_ID
AND R3.PART_ID =R2.WORKORDER_BASE_ID
UNION
SELECT D.ID, 4, 'D', R4.WORKORDER_BASE_ID FROM DOCUMENT D, DOCUMENT_REF_WO D1,  REQUIREMENT R2, REQUIREMENT R3, REQUIREMENT R4
WHERE R4.WORKORDER_TYPE = 'M'
AND D1.DOCUMENT_ID = D.ID
AND R2.PART_ID = D1.WORKORDER_BASE_ID
AND R3.PART_ID = R2.WORKORDER_BASE_ID
AND R4.PART_ID = R3.WORKORDER_BASE_ID
UNION
SELECT D.ID, 5, 'D', R5.WORKORDER_BASE_ID FROM DOCUMENT D, DOCUMENT_REF_WO D1,  REQUIREMENT R2, REQUIREMENT R3, REQUIREMENT R4, REQUIREMENT R5
WHERE R5.WORKORDER_TYPE = 'M'
AND D1.DOCUMENT_ID = D.ID
AND R2.PART_ID = D1.WORKORDER_BASE_ID
AND R3.PART_ID = R2.WORKORDER_BASE_ID
AND R4.PART_ID = R3.WORKORDER_BASE_ID
AND R5.PART_ID = R4.WORKORDER_BASE_ID
UNION
SELECT D.ID, 6, 'D', R6.WORKORDER_BASE_ID FROM DOCUMENT D, DOCUMENT_REF_WO D1,  REQUIREMENT R2, REQUIREMENT R3, REQUIREMENT R4, REQUIREMENT R5,
REQUIREMENT R6
WHERE R6.WORKORDER_TYPE = 'M'
AND D1.DOCUMENT_ID = D.ID
AND R2.PART_ID = D1.WORKORDER_BASE_ID
AND R3.PART_ID = R2.WORKORDER_BASE_ID
AND R4.PART_ID = R3.WORKORDER_BASE_ID
AND R5.PART_ID = R4.WORKORDER_BASE_ID
AND R6.PART_ID = R5.WORKORDER_BASE_ID
UNION
SELECT D.ID, 7, 'D', R7.WORKORDER_BASE_ID FROM DOCUMENT D, DOCUMENT_REF_WO D1,  REQUIREMENT R2, REQUIREMENT R3, REQUIREMENT R4, REQUIREMENT R5,
REQUIREMENT R6, REQUIREMENT  R7
WHERE R7.WORKORDER_TYPE = 'M'
AND D1.DOCUMENT_ID = D.ID
AND R2.PART_ID = D1.WORKORDER_BASE_ID
AND R3.PART_ID = R2.WORKORDER_BASE_ID
AND R4.PART_ID = R3.WORKORDER_BASE_ID
AND R5.PART_ID = R4.WORKORDER_BASE_ID
AND R6.PART_ID = R5.WORKORDER_BASE_ID
AND R7.PART_ID = R6.WORKORDER_BASE_ID
UNION
SELECT D.ID, 8, 'D', R8.WORKORDER_BASE_ID FROM DOCUMENT D, DOCUMENT_REF_WO D1,  REQUIREMENT R2, REQUIREMENT R3, REQUIREMENT R4, REQUIREMENT R5,
REQUIREMENT R6, REQUIREMENT  R7, REQUIREMENT R8
WHERE R8.WORKORDER_TYPE = 'M'
AND D1.DOCUMENT_ID = D.ID
AND R2.PART_ID = D1.WORKORDER_BASE_ID
AND R3.PART_ID = R2.WORKORDER_BASE_ID
AND R4.PART_ID = R3.WORKORDER_BASE_ID
AND R5.PART_ID = R4.WORKORDER_BASE_ID
AND R6.PART_ID = R5.WORKORDER_BASE_ID
AND R7.PART_ID = R6.WORKORDER_BASE_ID
AND R8.PART_ID = R7.WORKORDER_BASE_ID
UNION
SELECT D.ID, 9, 'D', R9.WORKORDER_BASE_ID FROM DOCUMENT D, DOCUMENT_REF_WO D1,  REQUIREMENT R2, REQUIREMENT R3, REQUIREMENT R4, REQUIREMENT R5,
REQUIREMENT R6, REQUIREMENT  R7, REQUIREMENT R8, REQUIREMENT R9
WHERE R9.WORKORDER_TYPE = 'M'
AND D1.DOCUMENT_ID = D.ID
AND R2.PART_ID = D1.WORKORDER_BASE_ID
AND R3.PART_ID = R2.WORKORDER_BASE_ID
AND R4.PART_ID = R3.WORKORDER_BASE_ID
AND R5.PART_ID = R4.WORKORDER_BASE_ID
AND R6.PART_ID = R5.WORKORDER_BASE_ID
AND R7.PART_ID = R6.WORKORDER_BASE_ID
AND R8.PART_ID = R7.WORKORDER_BASE_ID
AND R9.PART_ID = R8.WORKORDER_BASE_ID
UNION
SELECT D.ID, 10, 'D', R10.WORKORDER_BASE_ID FROM DOCUMENT D, DOCUMENT_REF_WO D1,  REQUIREMENT R2, REQUIREMENT R3, REQUIREMENT R4, REQUIREMENT R5,
REQUIREMENT R6, REQUIREMENT  R7, REQUIREMENT R8, REQUIREMENT R9, REQUIREMENT R10
WHERE R10.WORKORDER_TYPE = 'M'
AND D1.DOCUMENT_ID = D.ID
AND R2.PART_ID = D1.WORKORDER_BASE_ID
AND R3.PART_ID = R2.WORKORDER_BASE_ID
AND R4.PART_ID = R3.WORKORDER_BASE_ID
AND R5.PART_ID = R4.WORKORDER_BASE_ID
AND R6.PART_ID = R5.WORKORDER_BASE_ID
AND R7.PART_ID = R6.WORKORDER_BASE_ID
AND R8.PART_ID = R7.WORKORDER_BASE_ID
AND R9.PART_ID = R8.WORKORDER_BASE_ID
AND R10.PART_ID = R9.WORKORDER_BASE_ID;

2 个回答

Voted

Balazs Papp · Answer 1 · 2015-12-03T11:00:37+08:00

Best Answer

Balazs Papp

2015-12-03T11:00:37+08:002015-12-03T11:00:37+08:00

这看起来像一个典型的分层查询，但它是以“手动”方式实现的。我认为你不应该关注UNION这里。您应该使用CONNECT BY子句将其重写为真正的分层查询。像这样的东西（只是一个例子，不一定是你需要的）：

CREATE OR REPLACE VIEW PART_COMPLETE_WU
(wu_part, lvl, type, top_part)
SELECT
  P.ID, LEVEL ,'P', R.WORKORDER_BASE_ID
FROM
  PART P, REQUIREMENT R
WHERE
  R.WORKORDER_TYPE = 'M'
  AND R.PART_ID = P.ID
  AND LEVEL <= 10
CONNECT BY PRIOR R.WORKORDER_BASE_ID = R.PART_ID
UNION
SELECT
  D.ID, LEVEL, 'D', R.WORKORDER_BASE_ID
FROM
  DOCUMENT D, DOCUMENT_REF_WO D1, REQUIREMENT R
WHERE
  R.WORKORDER_TYPE = 'M'
  AND D1.DOCUMENT_ID = D.ID
  AND R.PART_ID = D1.WORKORDER_BASE_ID
  AND LEVEL <= 10
CONNECT BY PRIOR R.WORKORDER_BASE_ID = R.PART_ID;

有关分层查询的更多信息：此处

1

byrdzeye · Answer 2 · 2015-12-03T08:17:25+08:00

byrdzeye

2015-12-03T08:17:25+08:002015-12-03T08:17:25+08:00

如果您的条件不能返回另一个返回的记录，那么 a UNION ALLvs aUNION将有很大帮助。

0

更有效地使用 Union

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

更有效地使用 Union

2 个回答

相关问题