This content has been marked as final. Show 10 replies
This may give the same results you want with 1 full table scan:
if the filter z.ch_pending is null or z.ch_pending != 'X' is selective enough to benefit from an index lookup then you will need a function based index and change your query to use that.
select co_id ,ch_seqno ,ch_status ,ch_reason ,ch_validfrom ,entdate ,userlastmod ,rec_version ,request from (select co_id ,ch_seqno ,ch_status ,ch_reason ,ch_validfrom ,entdate ,userlastmod ,rec_version ,request ,row_number() over (partition by co_id order by ch_seqno desc ) rn from contract_history where z.ch_pending is null or z.ch_pending != 'X' ) where rn = 1;
it looks like are facing a typical problem of making "slicing" queries for timeseries data, i.e. when you need your query to return the state of things as of a certain moment in time (an important special case -- as of now). If you are making many such queries, and if your DML activities aren't too intensive, then you can consider a solution involving some redundant columns to quickly identify latest entries in each class, e.g. a flag IS_LATEST which would have to be updated every time a new latest value is saved. This would require creating some logic for DML operations (which can be implemented via triggers) and will increase their cost, so you need to weigh costs and benefits very carefully. Once you have the flag in place, you can put latest records on one partition to further simplify manipulating them.
As you filter is effectively on:
you can create an index
DECODE(ch_pending,'X',0,1) = 1
Then uss the filter as I have put it. The effectiveness of this will depend on how selective this index will be.
CREATE INDEX contact_history_fx01 ON contact_history (DECODE(ch_pending,'X',0,1));
As Nikolay says, it might be beneficial to maintain a latest_version column that is maintain by triggers (you will have to be careful to enforce serailisation correctly), or a fast refresh mv (or separate table) that only holds the latest version for each co_id.
Are you gettin a hash join between the two tables?
Here is another alternative, but possible with the same explain plan. Also, it has two full scan.
SELECT CO_ID , CH_SEQNO , CH_STATUS , CH_REASON , CH_VALIDFROM , ENTDATE , USERLASTMOD , REC_VERSION , REQUEST
FROM CONTRACT_HISTORY x,
(SELECT z.CO_ID, max( z.CH_SEQNO ) CH_SEQNO
FROM CONTRACT_HISTORY z
WHERE (z.CH_PENDING IS NULL OR z.CH_PENDING != 'X'
group by z.CO_ID) aux
WHERE x.CH_SEQNO = aux.CH_SEQNO;