Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: IOT, INDEX_DESC and Rownum=1
Hari,
I'm a little pushed for time at the moment. I'll get back to you off-line.
As a point to consider, though, my comments about not prefixing were based on the facts that
Reading through your original post, I didn't see anything that indicated a real necessity for prefixing - but in a relatively short post, the amount that gets left unsaid can easily contain surprisingly important information.
I'll get back to you in a few days.
-- Regards Jonathan Lewis http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated July 27th "Wizkid" <hariharan.sam_at_wipro.com> wrote in message news:54626635.0408170041.1f497729_at_posting.google.com...Received on Sun Aug 22 2004 - 04:30:14 CDT
> Jonathan Lewis,
>
> Thanks a lot for your reply. Let me add few considerations and try to
> see your reply in that light.
>
> a) As i had written earlier "One driving principle is that the
> application
> works on the latest known good value resulting 'most recent
> occurrence' type of queries for the dataitem_id + applicable_at
> combination. i.e the queries work using the '<=' predicate.". This is
> also know as Report-By-Exception (RBE) principle. By RBE, only when
> the value changes significantly beyond a certain limit a new record
> gets inserted. Which also means, a given record's value is applicable
> till the time the next record comes in.
>
> Seen in this light, Though majority of the requirements revolve around
> the last day there is also a significant portion of them which could
> be there anywhere in the near and the distant past. Irrespective of
> where the applicable_at is, the principle of RBE still applies. We are
> planning to get around that problem by having a scheduled program
> which run at the EOD and intentionally populates records which haven't
> changed for more than a pre-defined interval (Say 3 days). This is
> where the min(max(applicable_at)) helps as it does only a range scan
> on the index.
>
> Now, you had suggested to go in for daily partitions with a
> non-prefixed index on dataitem_id,applicable_at,gntd_tmstp. Won't this
> kick start parallel index probing on 7*365 local non-prefixed
> indexes?. Especially given the fact that we get dataitem_ids are
> transitively from another table and no literals are used with them.
> Yes, i could use cursor_sharing=similar but given some of the open
> bugs with cursor_sharing i'm little sceptical.
>
> b) Also, can you pl. let me know your thoughts on using IOT,
> INDEX_DESC hint and rownum=1 to work on most recent occurrence type of
> queries.
>
> c) As i had written earlier, the more the lower bound is away from the
> upper bound the more bigger the range scan is on the local index. For
> e.g, if the query is such that we need to get the latest value for
> each of the days between Jan1 till Jan31 for dataitem1 then owing to
> the RBE principle, we find the latest value for each of the days. This
> results in a large of number of blocks being retrieved for each of the
> day. This is inspite of the benefits the snapshotting program brings.
>
> Foll. is the solution i have in mind:
>
> a) Convert the table to be an IOT (owing to the benefits of not
> depending upon the availability of the index)
>
> b) Have monthly range partitions and create sub-partitions on the
> measurement type using list partitioning. Owing to this, only related
> dataitems would be there in a given data block. The reason to have
> monthly range partitions is because daily partitions would mean
> parallel queries on multiple daily partitions.
>
> c) Convert the PK to include measurement_type. The PK sequence would
> be applicable_at,measurement_type,dataitem_id,gntd_tmstp.
>
> d) Use INDEX_DESC hint and issue rownum=1 inside the subquery for each
> of the dataitems.
>
> Pl. provide your suggestions at the earliest as we need to decide
> fairly early on whether we should break this table into multiple
> physical tables and have our J2EE domain do the merging of the result
> set or retain it as a single table and exploit the VLDB features
> provided by Oracle.
>
> Rgds
> Hari
>
>