Thanks for the question, Martin.
Asked: January 24, 2017 - 4:48 pm UTC
Last updated: January 27, 2017 - 3:45 pm UTC
Version: Oracle 11
Viewed 1000+ times
You Asked
Hi everyone, i have de query below, and i am having timeout issues running it. The table br_movimientoctacte has 205.000 files.
I think is not a big number for oracle.
This query is part of a view, but i analyze it an the problema is in the query attached
Can you help me to solve this issue?
Regards,
Here is the query:
select Decode(NVL((CC.ImpMeCuota - NVL((Select Sum(A.ImpMeCuota)
From Br_MovimientoCtaCte A
Where A.IdMovimientoHeaderAplicado = CC.IdMovimientoHeader AND
A.NroCuota = CC.NroCuota AND
A.CodCuentaCorrienteRegistro = 2), 0)),0),NULL,'Saldada',
0, 'SALDADA', CC.ImpMeCuota, 'IMPAGA', 'PARCIALMENTEPAGA')
From Br_MovimientoCtaCte CC
as you requeste, here is the query plan
ID
PARENT_ID
OPERATION
OPTIONS
COST
OBJECT_OWNER
OBJECT_NAME
OBJECT_TYPE
CARDINALITY
BYTES
CPU COST
IO COST
OPTIMIZER
ACCESS PREDICATES
FILTER PREDICATES
QB LOCK NAME
DISTRIBUTION
OBJECT ALIAS
OBJECT INSTANCE
OBJECT NODE
PARTITION ID
PARTITION START
PARTITION STOP
POSITION
PROJECTION
REMARKS
SEARCH COLUMNS
TEMP SPACE
TIME
OTHER
OTHER_TAG
0 [NULL] SELECT STATEMENT [NULL] 132 [NULL] [NULL] [NULL] 192205 2883075 68689183 121 CHOOSE [NULL] [NULL] [NULL] [NULL] [NULL] [NULL] [NULL] [NULL] [NULL] [NULL] 132 [NULL] [NULL] [NULL] [NULL] 1 [NULL] [NULL]
1 0 SORT AGGREGATE [NULL] [NULL] [NULL] [NULL] 1 16 [NULL] [NULL] [NULL] [NULL] [NULL] SEL$2 [NULL] [NULL] [NULL] [NULL] [NULL] [NULL] [NULL] 1 (#keys=0) SUM("A"."IMPMECUOTA")[22] [NULL] [NULL] [NULL] [NULL] [NULL] [NULL]
2 1 TABLE ACCESS FULL 136 DBO BR_MOVIMIENTOCTACTE TABLE 1 16 97520086 121 ANALYZED [NULL] "A"."IDMOVIMIENTOHEADERAPLICADO"=:B1 AND "A"."NROCUOTA"=:B2 AND "A"."CODCUENTACORRIENTEREGISTRO"=2 SEL$2 [NULL] A@SEL$2 1 [NULL] [NULL] [NULL] [NULL] 1 "A"."IMPMECUOTA"[NUMBER,22] [NULL] [NULL] [NULL] 1 [NULL] [NULL]
3 0 TABLE ACCESS FULL 132 DBO BR_MOVIMIENTOCTACTE TABLE 192205 2883075 68689183 121 ANALYZED [NULL] [NULL] SEL$1 [NULL] CC@SEL$1 2 [NULL] [NULL] [NULL] [NULL] 2 "CC"."IDMOVIMIENTOHEADER"[NUMBER,22], "CC"."IMPMECUOTA"[NUMBER,22], "CC"."NROCUOTA"[NUMBER,22] [NULL] [NULL] [NULL] 1 [NULL] [NULL]
and Chris said...
Gaaaah! Please. When posting plans ensure you use the < code > tags and keep the original formatting so we can see how everything lines up. They're a complete #%&! to read otherwise.
Secondly, make sure this is an
execution plan. this includes columns like A(ctual) rows, buffers, etc. To see how to do this read:
https://blogs.oracle.com/sql/entry/how_to_create_an_execution Anyway, you're reading all the rows from br_movimientoctacte, then full scanning it again for each row this returns! No wonder this is taking a while.
I'm not really sure what you're trying to do with the sum subquery. But an index on (codcuentacorrienteregistro, idmovimientoheaderaplicado, nrocuota)
might help.
I suspect you'll get bigger gains by rewriting the query though. You may be able to change it so it only accesses br_movimientoctacte once using analytics. You'll need to give us more background (create table + sample inserts + expected output) for us to help you with this though.