Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: IOT, INDEX_DESC and Rownum=1

Re: IOT, INDEX_DESC and Rownum=1

From: Jonathan Lewis <>
Date: Sun, 22 Aug 2004 09:30:14 +0000 (UTC)
Message-ID: <cg9p36$c58$>


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

  1. prefixing is often done automatically and unnecessarily because the Oracle manuals tend to suggest the need - so I was really trying to highlight the fact that there were serious alternatives.
  2. if you want to access the data by 'thing_id' and date range, then as a general rule the index column order that is likely to be most efficient is (thing_id, date_value)
  3. with a fine enough granularity on partitioning, the date based value may become completely redundant as far as precision of access is concerned (though may still be necessary for PK reasons, of course).

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.


Jonathan Lewis
The Co-operative Oracle Users' FAQ
Optimising Oracle Seminar - schedule updated July 27th

"Wizkid" <> wrote in message

> 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
Received on Sun Aug 22 2004 - 04:30:14 CDT

Original text of this message