This content has been marked as final. Show 9 replies
Something like this:
Select p.ID as PARTICIPANTID,
decode(concat(pp.SALUTATION_CODE,p.LANG),'1de','Dear Sir','2de','Dear Madam','1fr','Cher Monsieur','2fr','Ch<E8>re Madame','1it','Egregio Signor','
2it','Gentile Signora','NA') as SALUTATION_TEXT,
decode(p.VERIFIED, 0, p.EMAIL, 1, p.EMAIL, 2, p.ISL_EMAIL, 3, p.EMAIL,'NA') as EMAIL,
p.LANG, TO_CHAR(pp.BIRTHDAY, 'dd.mm.yyyy') as BIRTHDAY,
pa.STREET as Street, pa.HOUSENUMBER as NR,
pa.ZIP as PLZ, pa.CITY as ORT,
decode(p.verified, 0, 'verified', 1, 'Not verified', 2, 'Not Migrated', 3, 'Rejected', 4, 'changed to another participant', 5,
'MoRo-Migrated', 6, 'ISL-User Asset', 'unknown') verified,
decode(p.state, 0, 'eligible', 1, 'closed', 2, 'blocked', 'unknown') state
from PARTICIPANT p
left join PERSINFO pp on p.ID = pp.PLAYER_ID
left join ADDRESS pa on p.ID = pa.PLAYER_ID
left join (select t.GAME_PARTICIPANT, TO_CHAR(MAX(t.PLAYED), 'dd.mm.yyyy HH:MI:SS') LAST_SPA
from ticket t,
where t.GAME_PARTICIPANT = pp.ID
and TO_CHAR(pp.BIRTHDAY, 'mm') = TO_CHAR(ADD_MONTHS(CURRENT_DATE, -1), 'mm')
group by t.GAME_PARTICIPANT) v_last_spa
on p.id = v_last_spa.GAME_PARTICIPANT
where p.ENABLED = 1
AND pp.STATUS = 1
AND pa.STATUS = 1
AND TO_CHAR(pp.BIRTHDAY, 'mm') = TO_CHAR(ADD_MONTHS(CURRENT_DATE, -1), 'mm')
Maybe you could start with this all predicates pushed version as some predicates might reduce the number of rows to an extent where the use of indexes would not be absolutely necessary.
It might not make things better as the optimizer is usually very good at predicate pushing but as well, you might be successful at undoing some and retaining some other pushed predicates.
select p.id participantid, decode(concat(pp.salutation_code,p.lang),'1de','Dear Sir', '2de','Dear Madam', '1fr','Cher Monsieur', '2fr','Ch<E8>re Madame', '1it','Egregio Signor', '2it','Gentile Signora','NA' ) salutation_text, pp.firstname, pp.lastname, p.email, p.lang, to_char(pp.birthday,'dd.mm.yyyy') birthday, pa.street, pa.housenumber nr, pa.zip plz, pa.city ort, t.last_asp, pp.verified, p.state from (select id, verified, lang, decode(verified,0,email,1,email,2,isl_email,3,email,'na') email, decode(state,0,'eligible',1,'closed',2,'blocked','unknown') state from participant where enabled = 1 ) p left join (select player_id, firstname, lastname, salutation_code, birthday, decode(verified,0,'verified', 1,'Not verified', 2,'Not Migrated', 3,'Rejected', 4,'changed to another participant', 5,'MoRo-Migrated', 6,'ISL-User Asset', 'unknown' ) verified from persinfo where to_char(pp.birthday,'mm') = to_char(add_months(current_date,-1),'mm') and status = 1 ) pp on p.id = pp.player_id left join (select game_participant,max(played) last_asp from ticket group by game_participant ) t on p.id = t.game_participant left join (select player_id, street, housenumber, zip, city from address where status = 1 ) pa on p.id = pa.player_id
thanks to all for you input I tried user648773 choice and it made some improvments but no a great deal.
In the end these indexes were created.
CREATE INDEX DWE_New1 ON PERSINFO ( STATUS, TO_CHAR(BIRTHDAY, 'mm'))
CREATE INDEX DWE_New2 ON PARTICIPANT ( ID, ENABLED, LANG, EMAIL, ISL_EMAIL, VERIFIED, STATE)
and it did make some difference.
But I am not convinced about adding these indexes for a single query.
Ideas most welcomed
1- The first index is a great idea. Maybe the most helpfull it seems to be the birthday column that appear to be more selective than status.
Is there alredy any index on the birthday coumn?
Maybe the index could be just only on the column birthday and change the condition like this:
By this way you are creating an index that could be use for more querys in the future.
Birthday between to_date(to_char(trunc(trunc(ADD_MONTHS(CURRENT_DATE, -1),'month')),'ddmm')||(to_char(birthday,'yyyy')),'ddmmyyyy') and to_date(to_char(trunc(CURRENT_DATE,'month'),'ddmm')||(to_char(birthday,'yyyy')),'ddmmyyyy')
Of course you can add the status column if it is helpful.
2- The second index have many columns. Oracle recomend not create index on more than three columns. Index with many columns increment the contention and time when you insert or update the table.
I understand that by this index you only access to the index and you don't need to acces the table, that is faster.
Maybe accessing by an index on the id column is enough.
Edited by: user648773 on 10/04/2013 10:22: I made a change in the condition
Without the plan it's kind of guessing, but you maay try to replace the subquery with a join
Edited by: chris227 on 10.04.2013 06:39
change (select TO_CHAR(MAX(t.PLAYED), 'dd.mm.yyyy HH:MI:SS') from ticket t where t.GAME_PARTICIPANT = p.ID) as LAST_SPA, in TO_CHAR(t.LAST_SPA, 'dd.mm.yyyy HH:MI:SS') LAST_SPA and add left outer join (select GAME_PARTICIPANT, max(played) last_spa from ticket group by GAME_PARTICIPANT) t on t.GAME_PARTICIPANT = p.ID