日期功能中以下查询的SQL查询优化

Question

我创建查询是为了从数据库中获取数据，但当我在其他角色上使用它时，加载需要很长时间。

我正在尝试优化以下查询。

SELECT
*
FROM
(
SELECT
ROWNUM AS RN,
*
FROM
(        select
t.closedate,
CASE when t.AbbrevType = 'INV' THEN 'Accounts Receivable' ELSE 'Accounts Payable' End as Account,
BUILTIN.DF(t.Entity) as Name,
BUILTIN.DF(tl.subsidiary) as Subsidiary,
t.Tranid AS SourceTransactionNumber,
t.TranDate as SourceTransactionDate,
CASE when t.AbbrevType = 'BILL' THEN 'BILL' WHEN t.AbbrevType = 'INV' THEN 'INVOICE' ELSE 'BILL PAYMENT' End as SourceTransactionType,
ap.periodname AS UnrealizedGainPostingPeriod,
CASE WHEN (TO_CHAR (t.TranDate, 'YYYY')) >= (TO_CHAR (ap.startdate, 'YYYY'))
AND (TO_CHAR (t.TranDate, 'MM')) >= (TO_CHAR (ap.startdate, 'MM')) THEN t.exchangerate WHEN (TO_CHAR (t.TranDate, 'YYYY')) > (TO_CHAR (ap.startdate, 'YYYY'))
AND (TO_CHAR (t.TranDate, 'MM')) <= (TO_CHAR (ap.startdate, 'MM')) THEN t.exchangerate ELSE cr1.Exchangerate END as BeginningFxRate,
cr2.Exchangerate as EndFXRate,
BUILTIN.DF(t.currency) as ForeignCurrency,
Round(ForeignTotal, 2) as ForeignCurrencyBalance,
Round(
(
(
cr2.Exchangerate - CASE WHEN (TO_CHAR (t.TranDate, 'YYYY')) >= (TO_CHAR (ap.startdate, 'YYYY'))
AND (TO_CHAR (t.TranDate, 'MM')) >= (TO_CHAR (ap.startdate, 'MM')) THEN t.exchangerate WHEN (TO_CHAR (t.TranDate, 'YYYY')) > (TO_CHAR (ap.startdate, 'YYYY'))
AND (TO_CHAR (t.TranDate, 'MM')) <= (TO_CHAR (ap.startdate, 'MM')) THEN t.exchangerate ELSE cr1.Exchangerate END
) * ForeignTotal
),
2
) AS NetGainLoss,
cexr.averagerate as ConsolidatedExchangeRate,
Round(
(
(
cr2.Exchangerate - CASE WHEN (TO_CHAR (t.TranDate, 'YYYY')) >= (TO_CHAR (ap.startdate, 'YYYY'))
AND (TO_CHAR (t.TranDate, 'MM')) >= (TO_CHAR (ap.startdate, 'MM')) THEN t.exchangerate WHEN (TO_CHAR (t.TranDate, 'YYYY')) > (TO_CHAR (ap.startdate, 'YYYY'))
AND (TO_CHAR (t.TranDate, 'MM')) <= (TO_CHAR (ap.startdate, 'MM')) THEN t.exchangerate ELSE cr1.Exchangerate END
) * (ForeignTotal * cexr.averagerate)
),
2
) AS ConsolidatedGainLoss,
from
Transaction t
INNER JOIN TransactionLine tl ON (tl.Transaction = t.ID)
AND (tl.MainLine = 'T')
AND (
(t.CloseDate IS NULL)
OR (
t.CloseDate >= TO_DATE('5/01/2019', 'MM-DD-YYYY')
)
AND (t.TranDate <= TO_DATE('5/31/2019', 'MM/DD/YYYY'))
)
INNER JOIN Subsidiary sb ON sb.id = tl.subsidiary
INNER JOIN AccountingPeriod ap ON (
(
ap.id BETWEEN (298)
AND (298)
)
AND ap.isposting = 'T'
)
INNER JOIN CurrencyRate cr1 ON (
cr1.BaseCurrency = sb.currency
AND cr1.TransactionCurrency = t.Currency
AND cr1.EffectiveDate = TO_DATE(ap.startdate, 'MM/DD/YYYY')
)
INNER JOIN CurrencyRate cr2 ON (
cr2.BaseCurrency = sb.currency
AND cr2.TransactionCurrency = t.Currency
AND cr2.EffectiveDate = TO_DATE(ap.enddate, 'MM/DD/YYYY')
)
INNER JOIN consolidatedexchangerate cexr ON (
cexr.postingperiod = t.PostingPeriod
AND cexr.fromsubsidiary = tl.subsidiary
AND cexr.tosubsidiary = 1
)
Where
(

t.AbbrevType LIKE  ('%INV')
OR t.AbbrevType LIKE ('%BILL')         


AND 
tl.subsidiary IN (
1,
2,
3,
4,
5,
6,
7,
10,
11,
12,
13,
19,
21,
30,
32,
33,
34,
35,
39,
42,
49,
52,
55,
63,
64,
65,
66,
68,
8,
9,
15,
16,
31,
36,
37,
41,
43,
47,
48,
54,
56,
57,
58,
59,
69,
70
)
)
AND 
t.CloseDate IS NULL
OR 
t.CloseDate >= TO_DATE('7/01/2021', 'MM-DD-YYYY')

AND t.TranDate <= TO_DATE('7/31/2021', 'MM/DD/YYYY')
)
)
WHERE
(
RN BETWEEN 1
AND 5000
)

它所承受的负载在区域

AND 
t.CloseDate IS NULL
OR 
t.CloseDate >= TO_DATE('7/01/2021', 'MM-DD-YYYY')

AND t.TranDate <= TO_DATE('7/31/2021', 'MM/DD/YYYY')

我发现查询中的日期功能需要更多的负载来获取数据。

还有其他可以优化的领域吗？

Answer 1

我需要那些"表格"；以及"；数据"；以全面测试和优化该查询，因此我可以查看该查询并向您解释对其性能产生负面影响的部分。

1. 当您使用"；OR"；在where子句中，则DB不能使用索引字段。该表将进行全面扫描。这是一场糟糕的演出。您可以使用UNION而不是OR。例如：

   select * from products
   where price = 25 or price = 10;
   select * from products
   where price = 25 
   union all 
   select * from products
   where price = 10;
   

2. 您在联接条件中也使用了OR，这是非常糟糕的性能。

3. 当条件中总是使用两个或三个字段时，因此为组合这些字段创建一个索引比为每个字段创建单独的索引具有更高的性能，例如：

   CREATE UNIQUE INDEX products_name_idx ON products USING btree (name, price);