Select sum (NVL (a.y sje, 0) - NVL (a.s kje, 0)) ye, a.c ustcode, a.u nitcode
The from (select decode (zb DJDL, ys, sum (fb) Jfbbje), '0') ysje,
Decode (zb) DJDL, sk, sum (fb) ybye), '0') skje,
Bas custcode,
Corp., unitcode
The from arap_djzb zb
Left the join arap_djfb fb
On the zb. VOUCHID=fb. VOUCHID
Left the join bd_cumandoc man
On man. Pk_cumandoc=fb. Ksbm_cl
Left the join bd_cubasdoc bas
On bas. Pk_cubasdoc=man. Pk_cubasdoc
Left the join bd_corp corp
On corp., pk_corp=zb. DWBM
Where bas. Custcode='211200127'
- substr (bas. The custcode, 0, 2)='11'
And the zb. DJZT='2'
And corp., unitcode='10301'
And NVL (zb. Dr, 0)=0
-- and the zb. Dr=0
And NVL (fb. Dr, 0)=0
Group by bas. Custcode, zb DJDL, corp., unitcode) a
Group by a.c ustcode, a.u nitcode

Associated with a few tables, there is a data query results, but the efficiency is very low, to more than ten seconds, is there any place can optimize the great god

CodePudding user response:

For the driver table, column is indexed to the local predicates,
To association for table driven, indexed, and at the same time also want to consider to be the driver table of local predicate columns added to the index,

CodePudding user response:

Bas. Custcode='211200127'
And corp., unitcode='10301'
Written in the book on the back of the accordingly

CodePudding user response:

On the basis of the following proper index creation,
Select sum (NVL (decode (zb DJDL, ys, sum (fb) Jfbbje), '0'), 0) - NVL (decode (zb DJDL, sk, sum (fb) ybye), '0'), 0)) ye,
Bas custcode,
Corp., unitcode
The from arap_djzb zb
Left the join arap_djfb fb
On the zb. VOUCHID=fb. VOUCHID
Left the join bd_cumandoc man
On man. Pk_cumandoc=fb. Ksbm_cl
Left the join bd_cubasdoc bas
On bas. Pk_cubasdoc=man. Pk_cubasdoc
Left the join bd_corp corp
On corp., pk_corp=zb. DWBM
Where bas. Custcode='211200127'
- substr (bas. The custcode, 0, 2)='11'
And the zb. DJZT='2'
And corp., unitcode='10301'
And NVL (zb. Dr, 0)=0
-- and the zb. Dr=0
And NVL (fb. Dr, 0)=0
Group by bas. Custcode, zb DJDL, corp., unitcode;

CodePudding user response:

The

refer to the original poster qq_41391731 response:

select sum (NVL (a.y sje, 0) - NVL (a.s kje, 0)) ye, a.c ustcode, a.u nitcode
The from (select decode (zb DJDL, ys, sum (fb) Jfbbje), '0') ysje,
Decode (zb) DJDL, sk, sum (fb) ybye), '0') skje,
Bas custcode,
Corp., unitcode
The from arap_djzb zb
Left the join arap_djfb fb
On the zb. VOUCHID=fb. VOUCHID
Left the join bd_cumandoc man
On man. Pk_cumandoc=fb. Ksbm_cl
Left the join bd_cubasdoc bas
On bas. Pk_cubasdoc=man. Pk_cubasdoc
Left the join bd_corp corp
On corp., pk_corp=zb. DWBM
Where bas. Custcode='211200127'
- substr (bas. The custcode, 0, 2)='11'
And the zb. DJZT='2'
And corp., unitcode='10301'
And NVL (zb. Dr, 0)=0
-- and the zb. Dr=0
And NVL (fb. Dr, 0)=0
Group by bas. Custcode, zb DJDL, corp., unitcode) a
Group by a.c ustcode, a.u nitcode

Associated with a few tables, there is a data query results, but the efficiency is very low, is there any place to 10 seconds, can optimize the great god of

1, no execution plan and talk about SQL optimization data environment, not the basis, the actual is to guess;
2, the original poster said only a data, this is a group, perhaps in this set of data a lot,