Externorderkey,
The type,
COMPANY,
ORDERDATE,
ORDERSTATUS,
Count (sku) countsku,
The sum (WEIGHT) WEIGHT,
The sum (CUBE) CUBE,
Carnum,
Carrierroutestatus
The from (select pk orderkey,
O.e xternorderkey,
(select the description
The from codelkup
Where listname='ORDERTYPE'
And code=o.t ype) type,
(select COMPANY
The from storer
Where type='2'
And storerkey=o. chua onsigneekey) COMPANY,
TO_CHAR (O.O RDERDATE, 'YYYY - MM - DD) ORDERDATE,
(select the DESCRIPTION
The from ORDERSTATUSSETUP
Where code=grown ORDERSTATUS tatus),
Od. SKU,
The SUM (PK. PDUDF3)/1000 WEIGHT,
The SUM (PK. CUBEUOM)/1000000 CUBE,
(select susr5 from the orders where orderkey.=pk orderkey) carnum,
O. chua arrierroutestatus
The from pickdetail pk, the orders o, named orderdetail od, sku sku, STORER S
Where pk. Orderkey=od. Orderkey
And pk. Orderlinenumber=od. Orderlinenumber
And od. Orderkey=o.o rderkey
And sku. The sku=od. Sku
And sku. Storerkey=od. Storerkey
And o. chua ONSIGNEEKEY=S.S TORERKEY
And S.T YPE='2'
-- and od. Orderkey='0000005238'
Group by pk orderkey,
O.e xternorderkey,
O.t ype,
O. chua onsigneekey,
TO_CHAR (O.O RDERDATE, '- DD YYYY - MM),
Grown tatus,
Od. Sku,
O. chua arrierroutestatus)
Group by orderkey,
Externorderkey,
The type,
COMPANY,
ORDERDATE,
ORDERSTATUS,
Carnum,
Carrierroutestatus
The order by orderkey desc
What a great god can help the younger brother to optimize the SQL, the query speed is too slow, don't come out
CodePudding user response:
1, reduce the number of nested table2, the appropriate increase index
More than 3, according to the execution plan targeted analysis
CodePudding user response:
Try firing a query into intermediate tableCodePudding user response:
1, a few scalar subquery to join queries, specific type, COMPANY, ORDERSTATUS carnum four fields (is not in the select, directly connected to the from of table)2, the two group by group is a meaningless repetition, and the second group can be removed completely (in the case of the first step to complete)
CodePudding user response:
If the efficiency is low, you'll have to see the table structure and the amount of data, this is no way to reply,,,, concrete analysis