Re: IOT, INDEX_DESC and Rownum=1

Jonathan Lewis,

Thanks a lot for your reply. Let me add few considerations and try to see your reply in that light.

1. 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:

1. Convert the table to be an IOT (owing to the benefits of not depending upon the availability of the index)
2. 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.
3. Convert the PK to include measurement_type. The PK sequence would be applicable_at,measurement_type,dataitem_id,gntd_tmstp.
4. 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

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<cfr0j9$7ff$1_at_titan.btinternet.com>...
> IOTs will scale to the sort of volume you want.
>
> You may need some care to ensure that you
> have identified all the critical classes of query
> otherwise you may find that your structure
> imposes an unacceptable overhead
> on some queries.
>
> Based on your current comments, I would
> seriously consider range-partitioning by DAY
> rather than month, and not prefixing the index -
> it looked from your main example as if the
> date-stamp would be more effective as the
> last column in the index rather than the first.
>
> Of the 7 year data set, how much will you
> have as you go live ? 7 * 365 partitions is
> a little close to the sensible limit at present,
> but 9.2.0.5 and 10.1 have a couple of features
> that make partition handling much less stressful.
>
>
> --
> 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.0408152206.f8c9ffa_at_posting.google.com...
> > All,
> >
> > Wanted to add few more facts regarding the question I had stated on
> > Saturday:
> >
> > a) The range partitions planned would be monthly partitions
> >
> > b) Partitions would contain dataitems with different retention periods
> >
> > c) The system is an OLTP system.
> >
> > d) Plenty of cyclic and time-specific batch programs pump data at a
> > frenetic speed along with the real time data input via AQ into the
> > table in questions. A very high throughput requirement is there for
> > consuming messages from the real time system.
> >
> > e) Usage of sub-partitioning (range-list) is being considered to group
> > related dataitems together within a monthly partition.
> >
> > I have an additional question as well:
> >
> > a) Would IOT scale up to such monstrous requirements?
> >
> >
> > Rgds
> > Hari
> >
> >
Received on Tue Aug 17 2004 - 03:41:10 CDT