enrique_aeo
asked on
oracle: better the perfomrnace. inner join vs partition by
I have 2 querys, the former takes more than 15 minutes.
query 1
select
ts1.CodUnico,
ts1.RazonSocial,
...
ts1.LineaCredTienda
from tmp_Solicitud ts1 inner join tmp_Solicitud ts2
on ts1.CODSOLICITUD = ts2.CODSOLICITUD
and ts2.codsolicitud = (select max(t.codsolicitud)
from tmp_Solicitud t
where ts2.CodUnico = t.CodUnico);
query 2
SELECT
codunico,
razonsocial,
numerodocumento,
codtipobanca,
codgrupo,
codcalificacionsbs,
codfeve,
rating,
codsegmentacion,
codejecutivonegocios,
desejecutivonegocios,
lineacredtienda
FROM (SELECT codunico,
razonsocial,
numerodocumento,
CASE codtipobanca
WHEN 1 THEN 'Corporativa BC'
WHEN 2 THEN 'Empresa BE'
WHEN 3 THEN 'Institucional BI'
WHEN 4 THEN 'Leasing LS'
END
codtipobanca,
codgrupo,
codcalificacionsbs,
codfeve,
rating,
CASE codsegmentacion WHEN '1' THEN '1' ELSE '0' END codsegmentacion,
codejecutivonegocios,
desejecutivonegocios,
lineacredtienda,
RANK() OVER (PARTITION BY codunico ORDER BY codsolicitud DESC) r
FROM tmp_solicitud)
WHERE r = 1
Thanks to you, the query was modified and now takes less than 2 seconds, my question is why?
Deputy respective execution plans.
Greetings
partitionBY.jpg
INNERjoin.jpg
query 1
select
ts1.CodUnico,
ts1.RazonSocial,
...
ts1.LineaCredTienda
from tmp_Solicitud ts1 inner join tmp_Solicitud ts2
on ts1.CODSOLICITUD = ts2.CODSOLICITUD
and ts2.codsolicitud = (select max(t.codsolicitud)
from tmp_Solicitud t
where ts2.CodUnico = t.CodUnico);
query 2
SELECT
codunico,
razonsocial,
numerodocumento,
codtipobanca,
codgrupo,
codcalificacionsbs,
codfeve,
rating,
codsegmentacion,
codejecutivonegocios,
desejecutivonegocios,
lineacredtienda
FROM (SELECT codunico,
razonsocial,
numerodocumento,
CASE codtipobanca
WHEN 1 THEN 'Corporativa BC'
WHEN 2 THEN 'Empresa BE'
WHEN 3 THEN 'Institucional BI'
WHEN 4 THEN 'Leasing LS'
END
codtipobanca,
codgrupo,
codcalificacionsbs,
codfeve,
rating,
CASE codsegmentacion WHEN '1' THEN '1' ELSE '0' END codsegmentacion,
codejecutivonegocios,
desejecutivonegocios,
lineacredtienda,
RANK() OVER (PARTITION BY codunico ORDER BY codsolicitud DESC) r
FROM tmp_solicitud)
WHERE r = 1
Thanks to you, the query was modified and now takes less than 2 seconds, my question is why?
Deputy respective execution plans.
Greetings
partitionBY.jpg
INNERjoin.jpg
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>but it's not actually related to the specific reason why query 2 is better than query 1.
As they are currently written, I agree and should have commented on that.
enrique_aeo,
please do not award any points to my post on RBO versus CBO.
As they are currently written, I agree and should have commented on that.
enrique_aeo,
please do not award any points to my post on RBO versus CBO.
I noticed you are using Rule-Based Optimization. If possible and you aren't using it 'by choice', I would look into using Cost-Based Optimization.
Check the online documentation for your version.
Here's the 11Gr2 link (It's what I have bookmarked):
http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/ds_appdev004.htm#ADMIN12198