如何调整这个视图？获取时间需要 9.968，但我想要 0.5。那么如何提供更好的性能

发布于 2024-10-21 02:50:10 字数 1037 浏览 0 评论 0原文

SELECT
/*+ INDEX(ID_BL_REF_NO REF_number_BL_idx*/ DECODE(BL_TYPE,'E',BL_ORIGIN_NAME,'I',BL_FINAL_NAME) FROM_PORT,
 DECODE(BL_TYPE,'I',BL_ORIGIN_NAME,'E',BL_FINAL_NAME) TO_PORT,
(BL_VESSEL_CONNECT||'/'||BL_VOYAGE_CONNECT||'/'||BL_PORT_CONNECT) Mother_vessel_voyage_port,
SUM(BLC_SIZE) No_of_20s,
SUM(BLC_SIZE)  No_of_40s,
SUM(DECODE(BLC_SIZE,'20',1,'40',2)) Teus,
SUM(BLC_GROSSWT) GrossWt,
round((BLC_GROSSWT/SUM(DECODE(BLC_SIZE,'20',1,'40',2))),2) AverageWt,
SUM(DECODE(BLF_MODE,'P',BLF_LOCAL_AMOUNT)) PREPAID,
SUM(DECODE(BLF_MODE,'C',BLF_LOCAL_AMOUNT)) COLLECT,
SUM(DECODE(BLF_MODE,'E',BLF_LOCAL_AMOUNT)) ELSEWHERE,
(SUM(DECODE(BLF_MODE,'P',BLF_LOCAL_AMOUNT)+DECODE(BLF_MODE,'C',BLF_LOCAL_AMOUNT)+DECODE(BLF_MODE,'E',BLF_LOCAL_AMOUNT))/SUM(DECODE(BLC_SIZE,'20',1,'40',2))) AVERAGE
FROM ID_BL_DETAILS,id_bl_containers,ID_BL_FREIGHT
WHERE BL_REFNO=BLC_REFNO
AND BLF_REFNO=BLC_REFNO
GROUP BY BL_VESSEL_CONNECT,BL_VOYAGE_CONNECT,BL_PORT_CONNECT,BL_ORIGIN_NAME,BL_LODPORT,BL_DISPORT,BL_FINAL_NAME,BLC_GROSSWT,BL_TYPE

原文

SELECT
/*+ INDEX(ID_BL_REF_NO REF_number_BL_idx*/ DECODE(BL_TYPE,'E',BL_ORIGIN_NAME,'I',BL_FINAL_NAME) FROM_PORT,
 DECODE(BL_TYPE,'I',BL_ORIGIN_NAME,'E',BL_FINAL_NAME) TO_PORT,
(BL_VESSEL_CONNECT||'/'||BL_VOYAGE_CONNECT||'/'||BL_PORT_CONNECT) Mother_vessel_voyage_port,
SUM(BLC_SIZE) No_of_20s,
SUM(BLC_SIZE)  No_of_40s,
SUM(DECODE(BLC_SIZE,'20',1,'40',2)) Teus,
SUM(BLC_GROSSWT) GrossWt,
round((BLC_GROSSWT/SUM(DECODE(BLC_SIZE,'20',1,'40',2))),2) AverageWt,
SUM(DECODE(BLF_MODE,'P',BLF_LOCAL_AMOUNT)) PREPAID,
SUM(DECODE(BLF_MODE,'C',BLF_LOCAL_AMOUNT)) COLLECT,
SUM(DECODE(BLF_MODE,'E',BLF_LOCAL_AMOUNT)) ELSEWHERE,
(SUM(DECODE(BLF_MODE,'P',BLF_LOCAL_AMOUNT)+DECODE(BLF_MODE,'C',BLF_LOCAL_AMOUNT)+DECODE(BLF_MODE,'E',BLF_LOCAL_AMOUNT))/SUM(DECODE(BLC_SIZE,'20',1,'40',2))) AVERAGE
FROM ID_BL_DETAILS,id_bl_containers,ID_BL_FREIGHT
WHERE BL_REFNO=BLC_REFNO
AND BLF_REFNO=BLC_REFNO
GROUP BY BL_VESSEL_CONNECT,BL_VOYAGE_CONNECT,BL_PORT_CONNECT,BL_ORIGIN_NAME,BL_LODPORT,BL_DISPORT,BL_FINAL_NAME,BLC_GROSSWT,BL_TYPE

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

白馒头 2024-10-28 02:50:10

您的 WHERE 子句仅包含联接。没有过滤器。这意味着您的查询需要考虑至少一个表中的所有行。由此可见，您的查询应该对至少一个表执行全表扫描，而不是索引读取。全表扫描是获取表中所有行的最有效方法。

因此，不要修复 INDEX 提示的语法，而是将其删除。

接下来，找出哪个表应该驱动您的查询。这是业务逻辑。可能你的要求是这样的

“总结 BL_DETAILS 和 BL_FREIGHT
对于 BL_CONTAINERS 中的每一行。”

在这种情况下，您可能认为需要对 BL_CONTAINERS 进行全表扫描。但是，如果 BL_FREIGHT 的行数多于 BL_CONTAINERS，并且每个 BLF_REF_NO 都与 BL_REF_NO 匹配（即 BL_FREIGHT.BLF_REF_NO 上有一个外键引用 BL_CONTAINERS BL_REF_NO）从 BL_FREIGHT 驱动可能会更好。

请注意，如果您只对具有匹配 BL_FREIGHT 行的 BL_CONTAINERS 感兴趣，但如果您想包含尚未使用的容器（即它们没有匹配的 BL_FREIGHT）。）。

当您将 BL_DETAILS 放入其中时，您的报告似乎基于 BL_DETAILS 类别（正如 Jeffrey 观察到的那样，我们很难理解您的记录。

如您所见，调整需要深入了解业务逻辑和数据模型的详细信息您拥有当地知识，而我们没有。

有一些工具可以帮助您。 Oracle 有 EXPLAIN PLAN，它将向您展示数据库将如何执行查询。每个版本的查询优化器都会变得更好，因此您使用的数据库版本很重要。这是10g 的文档。

需要注意的是，你需要给数据库提供准确的统计数据，以便它得出一个好的计划。了解更多信息。

Your WHERE clause contains only joins. There are no filters. This means your query needs to consider all the rows in at least one table. From this it follows that your query should execute a FULL TABLE SCAN of at least one of your tables, not an indexed read. A full table scan is the most efficient way of getting all the rows in a table.

So don't fix the syntax of your INDEX hint, get rid of it.

Next, figure out which table should drive your query. This is business logic. Probably your requirement is something like

"Summarise BL_DETAILS and BL_FREIGHT
for every row in BL_CONTAINERS."

In which case you might think you need a full table scan of BL_CONTAINERS. But if BL_FREIGHT has more rows than BL_CONTAINERS and every BLF_REF_NO matches a BL_REF_NO (i.e. there is a foreign key on BL_FREIGHT.BLF_REF_NO referencing BL_CONTAINERS.BL_REF_NO) it would probably be better to drive from BL_FREIGHT.

Note that this is true if you are only interested in BL_CONTAINERS which have matching BL_FREIGHT rows. But if you want to include containers which have not been used (i.e. they have no matching no BL_FREIGHT records) you need to use outer joins and drive off the BL_CONTAINERS table.

These considerations get more complicated when you throw BL_DETAILS into the mix. Your report seems to be based around the BL_DETAILS categories (as Jeffrey observes it is hard for us to understand your query without aliases or describes). So perhaps BL_DETAILS is the right candidate for driving table.

As you can see, tuning requires insight into the business logic and the details of the data model. You have that local knowledge, we do not.

There are tools which can help you. Oracle has EXPLAIN PLAN which will show you how the database will execute the query. The query optimizer gets better with each release so it matters which version of the database you're using. Here is the documentation for 10g.

The important thing to note is that you need to give the database accurate statistics, in order for it to come up with a good plan. Find out more.

回复收藏 0 原文