The select A.c ode, NVL ((select sum (c.a.) from C where c.a. id=A.a id and mount id=B.b id), 0) from A, B, where A.c ode=biggest ode, C table data may be empty, the efficiency is very slow, can optimize SQL

CodePudding user response:

Fast unhappy associated with data features, is not up to the index

 select a.a _code, 
NVL (sum (c.a.), 0) 
The from (
The select A.c ode a_code, 
Biggest ode b_code 
From A, B 
Where A.c ode=biggest ode 
) a, 
C 
Where a.a _code=c.a. id (+) 
And a. _code=mount id (+) 
Group by a.a _code 
; 

CodePudding user response:

The query will not fast, basically for two tables are full table in connection, unless A, B two tables with A small table, and after two table join the amount of data is small, so you can also use index optimization,
In addition, the connection of C table fields must be indexed, otherwise the scalar subquery will be overturned,,,

CodePudding user response:

Is there a indexed?

CodePudding user response:

Has been indexed, the effect is not big

CodePudding user response:

Firing A query equivalent transformation for the main query, if A table column B A lot, built two composite index (code, aid) (code, the bid)

CodePudding user response:

The original poster on execution plan, and the SQL you want to seconds out, basically is unlikely, have to be very happen to data distribution,

CodePudding user response:

The building Lord this typical scalar subquery, rewrite it:
C.s. m, select A.c ode, NVL (0), from A, B, (select aid, the bid, the sum (c.a.) sm from group C by aid of the bid) C
Where A.c ode=biggest ode
And a.a id=c.a. id (+)
And b.b id=mount id (+);
Probably like this change,

CodePudding user response:

Select A.c ode, (select NVL (sum (c.a.), 0) where the from C
The exists (select 0 from A where clause A.a id=c.a. id) and
The exists (select 0 from B where B.a id=c.a. id)
) from A
Where the exists (select 0 from B where biggest ode=A.c ode);