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;
Thanks a lot. I will try this query tomorrow.
I will not see which function based index can i use with IS NULL or != 'X' in the where clause.
Could you explain me ?
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.
Is this query the same?
It reduces the result set to the rows matching the predicate "z.ch_pending is null or z.ch_pending != 'X'", which the origin query doesnt.
There all rows with ch_seqno in max(ch_seqno) are returned.
Depends on which part is the bottleneck (the filter on CH_SEQNO or the filter on z.CH_PENDING IS NULL OR z.CH_PENDING != 'X'),
but did you try an index on CH_SEQNO ?
Possibly not, I went though it quickly last night before going home. No sample data to test on. I should probably move the ch_pending filters to the order by. I was auuming that ch_seq was the primary key.
Thanks for your help !
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;