| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: IOT, INDEX_DESC and Rownum=1
Jonathan,
Thanx a lot for the response. Would await your response. Your reply
made me want to consolidate all my postings on this thread(online and
offline). Pl. find below the same:
Background:
• We are designing an application, at the heart of which is a large table expected to be of size 592 GB (493 GB Table and 100 GB index) at the end of 7 Years after granular purging.
• Number of records is expected to be around 3.5 billion
• The volume of data is essentially owing to it being linked to a real time application, which feeds real-time data. The sample design of the table is such that it would have the following columns:
Dataitem_id Varchar2 (50); -- PK Column1 Applicable_at date; -- PK Column2 Gntd_tmstp date; -- PK Column3
• We are on Oracle 9.2.0.3 and we use Oracle 9iAS (Rel2) J2EE container as the application server.
Considerations:
Some more considerations worth noting are:
• The system is an OLTP system
• The table is both write intensive and read intensive
• Most of the queries are centered around the lastest day's records
• 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 '<=' operator. This is also known as 'Report-By-Exception (RBE)' principle. By RBE, only when the value changes significantly beyond a certain limit a new record gets inserted. This 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 latest day there is also a significant portion of them which could be there anywhere in the near or in the distant past. Irrespective of where the applicable_at is, the principle of RBE still applies.
• The number of dataitem_ids is expected to be around 50,000
• The cardinality of the dataitem_id would not be uniform
• No updates would be performed. Every time a value change happens for a given dataitem_id+applicable_at combination, we would insert a new record with a different gntd_tmstp
• Predominant queries for a given dataitem_id would be a) Give me the latest value for an applicable_at b) Give me the latest values found between afrom_applicable_at and a to_applicable_at
c) Give me the latest value for a day.
d) Give me the latest value for each of the days for a range days. For each of the days, find the latest value available in the system by going as far as possible in the system.
• The queries could be for a range of dataitem_ids as well
• In the predominant number of queries, the dataitem_ids would be derived from lookup tables. In other queries, the dataitem_ids would be directly passed.
• This table is operated upon by a Java package made up of stateless session bean which calls plain JAVA Data Access Object (DAO) to retrieve data from the table mentioned above. The DAO class employs JDBC APIs to retrieve data from the table. A consistent set of interfaces are exposed by this java package and they employ a flat "<=" on the applicable_at column irrespective of where the applicable_at value is to carry out the RBE queries
• This Package is also responsible for inserting application produced records (not the ones coming from the real time application) in this table. Plenty of cyclic and time-specific batch programs pump data at a frenetic speed into this table.
• We also have a separate scheduled Oracle Routine which dequeues messages from the real time system to insert records in this table. A very high throughput requirement exists for consuming messages from the real time system.
• We also have Oracle Reports (Both RDFs and Report JSPs created using Oracle Reports Developer) accessing this table to perform similar queries as listed above. Most of the Oracle Reports make use of Table of Functions to retrieve data from the table in question
Problem Statement:
• The current setup of having a global non-partitioned index with
columns dataitem_id, applicable_at, gntd_tmstp (in that order) on a
monthly partitioned table is not meeting the performance requirements.
This is essentially because of the <= operator on the applicable_at
column, though only works on the global index, still doesn't provide
satisfactory performance.
Proposed Solution:
• We conducted a Proof Of Concept (POC) to test the resilience of this table by which it would provide optimum throughput and response time.
• The table was created as a heap table
• We Range partitioned the table on
the applicable_at column. The granularity of the partition was a
month.
• Partitions contain dataitems with different retention periods (Max of 7 years).
• We have a local prefixed unique index with columns applicable_at, dataitem_id and gntd_tmstp, in that order.
• We are designing a scheduled program which would run at the End-Of-Day(EOD) and would intentionally populate records which haven't changed for more than a pre-defined interval (To start with, Say 1 day). This is done because we want to limit the search of the Index range scans.
• The sub queries are being re-written to derive a lower bound for each of the range scan by subtracting one day (configurable) from the provided applicable_at value. These sub-queries as before would only operate on the local indexes. The sub-queries would get the max (applicable_at) for each of the dataitem. Because we are now operating on local indexes and because we pre-determining the lower bound for the range scans the queries are performing better.
• The dataitem_id, max(applicable_at) derived from sub query then get equated in the main query.
• In the main query, we then employ the analytical function rank with partition on dataitem_id with an order by on applicable_at desc and gntd_tmstp desc to get the latest value for each dataitem_id and applicable_at combination. For this we issue rank=1 on the result set returned after the rank function is issued.
• On a test instance with 300 GB of data in this table, this strategy is providing good results. The more the lower bound is closer to the upper bound, the lesser number of records get fetched and the performance becomes even better.
Pl. have a look at the query and its explain plan with the strategy as mentioned above employed:
SELECT a.object_id object_id, a.mapping_key mapping_key,
a.dataitem_id dataitem_id, a.dataitem_name dataitem_name,
a.dataitem_category dataitem_category, to_char(a.applicable_at,
'DD-MON-YYYY HH24:MI:SS') ts, to_char(a.gntd_tmstp,
'DD-MON-YYYY HH24:MI:SS') cs,
to_char(a.lt, 'DD-MON-YYYY HH24:MI:SS') lt,
a.source source, a.uom uom, a.alarm_status alarm_status,
a.ignore_tlmtry ignore_tlmtry, a.expired expired, a.reason
reason,
a.last_updated_by last_updated_by, a.value_name value_name,
a.value value, a.rbe_snapshot_flag rbe_snapshot_flag,
a.reason_code reason_code, a.application_owner
application_owner
FROM (SELECT d.nm_object_id object_id, d.mapping_key mapping_key,
b.dataitem_id dataitem_id, b.dataitem_name
dataitem_name, b.dataitem_category dataitem_category,
c.applicable_at applicable_at, c.gntd_tmstp gntd_tmstp,
c.last_updated_dt lt, c.source source, c.uom uom,
c.alarm_status alarm_status, c.ignore_tlmtry_yn ignore_tlmtry,
c.expired_yn expired, c.reason reason,
c.last_updated_by last_updated_by, c.value_name value_name,
c.value value, c.rbe_snapshot_flag rbe_snapshot_flag,
c.reason_code reason_code,
b.application_owner application_owner, rank()
OVER (PARTITION BY c.dataitem_id
ORDER BY c.applicable_at DESC, c.gntd_tmstp
DESC)
rank
FROM nm_dataitems b, com_received_values c,
nm_level_di_mappings d
WHERE d.dataitem_id = b.dataitem_id
AND c.dataitem_id = d.dataitem_id
AND d.mapping_key = 'ENDAPRESSURE'
AND d.nm_object_id IN ('component100', 'component101',
'component102', 'component103', 'component104',
'component105', 'component106', 'component107',
'component108', 'component109', 'component110',
'component111', 'component112', 'component113',
'component114', 'component115', 'component116',
'component117', 'component118', 'component119',
'component120', 'component121', 'component122',
'component123', 'component124', 'component125',
'component126', 'component127', 'component128',
'component129', 'component130', 'component131',
'component132', 'component133', 'component134',
'component135', 'component136', 'component137',
'component138', 'component139', 'component140',
'component141', 'component142', 'component143',
'component144', 'component145', 'component146',
'component147', 'component148', 'component149',
'component150', 'component151', 'component152',
'component153', 'component154', 'component155',
'component156', 'component157', 'component158',
'component159', 'component160', 'component161',
'component162', 'component163', 'component164',
'component165', 'component166', 'component167',
'component168', 'component169', 'component170',
'component171', 'component172', 'component173',
'component174', 'component175', 'component176',
'component177', 'component178', 'component179',
'component180', 'component181', 'component182',
'component183', 'component184', 'component185',
'component186', 'component187', 'component188',
'component189', 'component190', 'component191',
'component192', 'component193', 'component194',
'component195', 'component196', 'component197',
'component198', 'component199', 'component200',
'component201', 'component202', 'component203',
'component204', 'component205', 'component206',
'component207', 'component208', 'component209',
'component210', 'component211', 'component212',
'component213', 'component214', 'component215',
'component216', 'component217', 'component218',
'component219', 'component220', 'component221',
'component222', 'component223', 'component224',
'component225', 'component226', 'component227',
'component228', 'component229', 'component230',
'component231', 'component232', 'component233',
'component234', 'component235', 'component236',
'component237', 'component238', 'component239',
'component240', 'component241', 'component242',
'component243', 'component244', 'component245',
'component246', 'component247', 'component248',
'component249', 'component250', 'component251',
'component252', 'component253', 'component254',
'component255', 'component256', 'component257',
'component258', 'component259', 'component260',
'component261', 'component28', 'component29',
'component30', 'component31', 'component32',
'component33', 'component34', 'component35', 'component36',
'component37', 'component38', 'component39', 'component40',
'component41', 'component42', 'component43', 'component44',
'component45', 'component46', 'component47', 'component48',
'component49', 'component50', 'component51', 'component52',
'component53', 'component54', 'component55', 'component56',
'component57', 'component58', 'component59', 'component60',
AND d.deleted_yn = 'N'
AND (d.end_dt IS NULL
OR d.end_dt >= to_date('23-JUL-2003 06:00:00',
'DD-MON-YYYY HH24:MI:SS'))
AND d.start_dt <= to_date('23-JUL-2003 06:00:00',
'DD-MON-YYYY HH24:MI:SS')
AND c.gntd_tmstp <= to_date('23-JUL-2003 06:00:00',
'DD-MON-YYYY HH24:MI:SS')
AND (d.dataitem_id, c.applicable_at) IN
(SELECT crv.dataitem_id,
max(applicable_at)
AND nd.deleted_yn = 'N'
AND (nd.end_dt IS NULL
OR nd.end_dt >= to_date('23-JUL-2003 06:00:00',
COST ALL ROWS (optimizer: CHOOSE)
Total Cost:
3,515
Execution Steps:
Step #Step Name
22SELECT STATEMENT
21SORT [ORDER BY]
20ULYNTSOPS. VIEW
19WINDOW [SORT PUSHED RANK]
18NESTED LOOPS
15NESTED LOOPS
11HASH JOIN
3INLIST ITERATOR
2ULYNM.NM_LEVEL_DI_MAPPINGS TABLE ACCESS [BY INDEX ROWID]
1ULYNM.NM_NUI_MAPKEY_DI_MKEY_OBJID_SD INDEX [RANGE SCAN]
10SYS.VW_NSO_1 VIEW
9SORT [GROUP BY]
8HASH JOIN
6INLIST ITERATOR
5ULYNM.NM_LEVEL_DI_MAPPINGS TABLE ACCESS [BY INDEX ROWID]
4ULYNM.NM_NUI_MAPKEY_DI_MKEY_OBJID_SD INDEX [RANGE SCAN]
7ULYNTSOPS.LOC_PREF_CRV_IDX INDEX [RANGE SCAN]
14PARTITION RANGE [ITERATOR]
13ULYNTSOPS.COM_RECEIVED_VALUES TABLE ACCESS [BY LOCAL INDEX ROWID]
12ULYNTSOPS.LOC_PREF_CRV_IDX INDEX [RANGE SCAN]
17ULYNM.NM_DATAITEMS TABLE ACCESS [BY INDEX ROWID]
16ULYNM.NM_PK_DI INDEX [UNIQUE SCAN]
Step #Description Est.Cost Est.Rows Returned Est. KBytes Returned
1 This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index NM_NUI_MAPKEY_DI_MKEY_OBJID_SD. 2 7—
2 This plan step retrieves rows from table NM_LEVEL_DI_MAPPINGS through ROWID(s) returned by an index. 5 3 0.144
3 This plan step loops through the query's IN list predicate, executing its child step for each value found.
4 This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index NM_NUI_MAPKEY_DI_MKEY_OBJID_SD. 2 7—
5 This plan step retrieves rows from table NM_LEVEL_DI_MAPPINGS through ROWID(s) returned by an index. 5 30.144
6 This plan step loops through the query's IN list predicate, executing its child step for each value found.
7 This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index LOC_PREF_CRV_IDX. 3,480 458,321 13,427.373
8 This plan step accepts two sets of rows, each from a different table. A hash table is built using the rows returned by the first child. Each row returned by the second child is then used to probe the hash table to find row pairs which satisfy a condition specified in the query's WHERE clause. Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables. It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make the wrong choice.
3,488 300 23.145
9 This plan step accepts a set of rows from its child node, and
sorts
them into groups based on the columns specified in the query's GROUP
BY
clause. 3,493 300 23.145
10 This plan step represents the execution plan for the subquery defined by the view VW_NSO_1. 3,493 300 10.547
11 This plan step accepts two sets of rows, each from a different table. A hash table is built using the rows returned by the first child. Each row returned by the second child is then used to probe the hash table to find row pairs which satisfy a condition specified in the query's WHERE clause. Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables. It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make the wrong choice. 3,499 3 0.249
12 This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index LOC_PREF_CRV_IDX. 3 1 --
13 This plan step retrieves rows from table COM_RECEIVED_VALUES through ROWID(s) returned by a local partitioned index. 4 736 73.313
14 This plan step determines the approach to iterating over partitions of a range-partitioned table.
15 This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause.3,511 1 0.183
16 This plan step retrieves a single ROWID from the B*-tree index NM_PK_DI. -- 1 --
17 This plan step retrieves rows from table NM_DATAITEMS through ROWID(s) returned by an index.1 13,770 564.785
18 This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause.3,512 1 0.224
19 This plan step performs a windowing analytical function on a set of data which needs to be first sorted based on some criteria which includes RANK. 3,513 1 0.224
20 This plan step represents the execution plan for the subquery defined by the view. 3,513 1 0.396
21 This plan step accepts a set of rows from its child node, and sorts them on a per-column basis using the query's ORDER BY clause. 3,515 1 0.396
22 This plan step designates this statement as a SELECT
statement. 3,515 1 0.396
Further Options and Questions:
• You had suggested to go in for daily partitions with a non-prefixed
index with the column sequence 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 dataitem_ids are
got transitively from another table and no literals are used with
them. From some of the docs I had read, I understand I could use
cursor_sharing=similar to achieve partition pruning when the literals
are not used. But given some of the open bugs with cursor_sharing i'm
a little sceptical.
What do you suggest on this score?
• Also, the more number of times such queries get employed the more deteriorates the performance. 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 despite the benefits the snapshot program brings in.
Following are the two solutions I have in mind:
Solution a:
• Convert the table into an IOT (owing to the benefits of not having to depend upon the availability of the index)
• Create Weekly partitions. Weekly partitions would mean the B*Tree's breadth would be small. Weekly partitions instead of daily partitions would also mean avoiding the need of employing parallel queries with queries over a range of days.
• Don't have snapshot routines.
• Use INDEX_DESC hint and issue rownum=1 inside the subquery for each of the dataitems.
• I'm fully aware that I shouldn't build application
logic around hints and Oracle is not obliged to give me the top record
when I issue rownum=1. Still, the idea of bringing down the number of
records to be
fetched from the disk to perform the "most recent occurrence" type of
queries
appeals to me.
• 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.
• Would IOT scale up to such monstrous requirements?
Pl. note I haven't POCd this solution.
Solution b:
• Leave the table as a heap table
• Have snapshot routines and employ range queries with a flat lower bound of one day.
• Have weekly range partitions on applicable_at. The reason to have weekly range partitions is because daily partitions would mean parallel queries on multiple daily partitions for day based queries. I do recognize that the queries given in the beginning of the week would always probe the previous week as well. But I'm willing to take that hit. Also, I feel weekly partitions would be a little more manageable than the daily ones.
• Create sub-partitions using list partitioning with the help of a new column called measurement type. We would group dataitems into five broad measurement types. To do this, I would introduce a new column in the table. My existing code base would take a hit. But I feel it is worth doing it. Owing to this, only related dataitems would be there in a given data block.
• Another reason in favor of this option is that IOTs don't allow composite partitioning. Using weekly partitions and composite range-list partitioning would dramatically bring down the amount of data we would be looking at while issuing range scans.
What are your thoughts on this score?
• Convert the PK to include measurement_type. The PK sequence would be applicable_at, measurement_type, dataitem_id, gntd_tmstp. The PK constraint would be created using a local prefixed unique index.
• Run the snapshot routine at the very beginning of the morning. Employ ‘=' for the day based queries. If values are not available for some of the data items employ a ‘<=' query for the dataitems for which we didn't get values in the ‘=' query.
• We are currently going ahead with Solution b) as we are running out of time. Solution b) is being preferred over Solution a) as we are not comfortable using hints and rownum=1.
• In Solution b) we still are not sure as to whether we should use sub-partitioning or not. Initial results with sub-partitioning are not encouraging. This is primarily because it is going for Index Fast Full Scan. Another irritant is it expects me to use literals on the sub-partition key to achieve partition elimination.
Pl. have a look at the query on the sub-partitioned table and its corr. Explain plan:
SQL Statement:
SELECT a.object_id object_id, a.mapping_key mapping_key,
a.dataitem_id dataitem_id, a.dataitem_name dataitem_name,
a.dataitem_category dataitem_category, to_char(a.applicable_at,
'DD-MON-YYYY HH24:MI:SS') ts, to_char(a.gntd_tmstp,
'DD-MON-YYYY HH24:MI:SS') cs, to_char(a.lt, 'DD-MON-YYYY
HH24:MI:SS') lt,
a.source source, a.uom uom, a.alarm_status alarm_status,
a.ignore_tlmtry ignore_tlmtry, a.expired expired, a.reason
reason,
a.last_updated_by last_updated_by, a.value_name value_name,
a.value value, a.rbe_snapshot_flag rbe_snapshot_flag,
a.reason_code reason_code, a.application_owner
application_owner
FROM (SELECT d.nm_object_id object_id, d.mapping_key mapping_key,
b.dataitem_id dataitem_id, b.dataitem_name
dataitem_name,
b.dataitem_category dataitem_category,
c.applicable_at applicable_at, c.gntd_tmstp
gntd_tmstp,
c.last_updated_dt lt, c.source source, c.uom uom,
c.alarm_status alarm_status, c.ignore_tlmtry_yn
ignore_tlmtry,
c.expired_yn expired, c.reason reason,
c.last_updated_by last_updated_by, c.value_name
value_name,
c.value value, c.rbe_snapshot_flag rbe_snapshot_flag,
c.reason_code reason_code,
b.application_owner application_owner, rank()
OVER (PARTITION BY c.dataitem_id
ORDER BY c.applicable_at DESC, c.gntd_tmstp
DESC)
rank
FROM nm_dataitems b, com_received_values_temp c,
nm_level_di_mappings d
WHERE d.nm_object_id IN ('component100', 'component101',
'component102', 'component103', 'component104',
'component105', 'component106', 'component107',
'component108', 'component109', 'component110',
'component111', 'component112', 'component113',
'component114', 'component115', 'component116',
'component117', 'component118', 'component119',
'component120', 'component121', 'component122',
'component123', 'component124', 'component125',
'component126', 'component127', 'component128',
'component129', 'component130', 'component131',
'component132', 'component133', 'component134',
'component135', 'component136', 'component137',
'component138', 'component139', 'component140',
'component141', 'component142', 'component143',
'component144', 'component145', 'component146',
'component147', 'component148', 'component149',
'component150', 'component151', 'component152',
'component153', 'component154', 'component155',
'component156', 'component157', 'component158',
'component159', 'component160', 'component161',
'component162', 'component163', 'component164',
'component165', 'component166', 'component167',
'component168', 'component169', 'component170',
'component171', 'component172', 'component173',
'component174', 'component175', 'component176',
'component177', 'component178', 'component179',
'component180', 'component181', 'component182',
'component183', 'component184', 'component185',
'component186', 'component187', 'component188',
'component189', 'component190', 'component191',
'component192', 'component193', 'component194',
'component195', 'component196', 'component197',
'component198', 'component199', 'component200',
'component201', 'component202', 'component203',
'component204', 'component205', 'component206',
'component207', 'component208', 'component209',
'component210', 'component211', 'component212',
'component213', 'component214', 'component215',
'component216', 'component217', 'component218',
'component219', 'component220', 'component221',
'component222', 'component223', 'component224',
'component225', 'component226', 'component227',
'component228', 'component229', 'component230',
'component231', 'component232', 'component233',
'component234', 'component235', 'component236',
'component237', 'component238', 'component239',
'component240', 'component241', 'component242',
'component243', 'component244', 'component245',
'component246', 'component247', 'component248',
'component249', 'component250', 'component251',
'component252', 'component253', 'component254',
'component255', 'component256', 'component257',
'component258', 'component259', 'component260',
'component261', 'component28', 'component29',
'component30', 'component31', 'component32', 'component33',
'component34', 'component35', 'component36', 'component37',
'component38', 'component39', 'component40', 'component41',
'component42', 'component43', 'component44', component45',
'component46', 'component47', 'component48', 'component49',
'component50', 'component51', 'component52', 'component53',
'component54', 'component55', 'component56', 'component57',
'component58', 'component59', 'component60', 'component61',
AND d.mapping_key = 'ENDAPRESSURE'
AND d.deleted_yn = 'N'
AND (d.end_dt IS NULL
OR d.end_dt >= to_date('23-jan-2003 06:00:00',
'DD-MON-YYYY HH24:MI:SS'))
AND d.start_dt <= to_date('23-jan-2003 06:00:00',
'DD-MON-YYYY HH24:MI:SS')
AND b.dataitem_id = d.dataitem_id
AND c.dataitem_id = b.dataitem_id
AND c.measure_type = 'PRESSURE'
AND c.gntd_tmstp <= to_date('23-jan-2003 06:00:00',
'DD-MON-YYYY HH24:MI:SS')
AND (d.dataitem_id, c.applicable_at) IN (SELECT
crv.dataitem_id,
max(
applicable_at)
FROM com_received_values_temp crv,
nm_level_di_mappings nd, nm_dataitems
ndit
WHERE nd.nm_object_id IN ('component100',
'component101', 'component102', 'component103',
'component104', 'component105', 'component106',
'component107', 'component108', 'component109',
'component110', 'component111', 'component112',
'component113', 'component114', 'component115',
'component116', 'component117', 'component118',
'component119', 'component120', 'component121',
'component122', 'component123', 'component124',
'component125', 'component126', 'component127',
'component128', 'component129', 'component130',
'component131', 'component132', 'component133',
'component134', 'component135', 'component136',
'component137', 'component138', 'component139',
'component140', 'component141', 'component142',
'component143', 'component144', 'component145',
'component146', 'component147', 'component148',
'component149', 'component150', 'component151',
'component152', 'component153', 'component154',
'component155', 'component156', 'component157',
'component158', 'component159', 'component160',
'component161', 'component162', 'component163',
'component164', 'component165', 'component166',
'component167', 'component168', 'component169',
'component170', 'component171', 'component172',
'component173', 'component174', 'component175',
'component176', 'component177', 'component178',
'component179', 'component180', 'component181',
'component182', 'component183', 'component184',
'component185', 'component186', 'component187',
'component188', 'component189', 'component190',
'component191', 'component192', 'component193',
'component194', 'component195', 'component196',
'component197', 'component198', 'component199',
'component200', 'component201', 'component202',
'component203', 'component204', 'component205',
'component206', 'component207', 'component208',
'component209', 'component210', 'component211',
'component212', 'component213', 'component214',
'component215', 'component216', 'component217',
'component218', 'component219', 'component220',
'component221', 'component222', 'component223',
'component224', 'component225', 'component226',
'component227', 'component228', 'component229',
'component230', 'component231', 'component232',
'component233', 'component234', 'component235',
'component236', 'component237', 'component238',
'component239', 'component240', 'component241',
'component242', 'component243', 'component244',
'component245', 'component246', 'component247',
'component248', 'component249', 'component250',
'component251', 'component252', 'component253',
'component254', 'component255', 'component256',
'component257', 'component258', 'component259', AND crv.dataitem_id = ndit.dataitem_id AND crv.measure_type = 'PRESSURE' AND crv.applicable_at <= to_date(
COST ALL ROWS (optimizer: CHOOSE)
Total Cost:
212
Execution Steps:
Step #Step Name
26SELECT STATEMENT
25SORT [ORDER BY]
24ULYNTSOPS. VIEW
23WINDOW [SORT PUSHED RANK]
22ULYNM. VIEW
21WINDOW [CHILD PUSHED RANK]
20ULYNTSOPS.COM_RECEIVED_VALUES_TEMP TABLE ACCESS [BY LOCAL
INDEX ROWID]
19NESTED LOOPS
16NESTED LOOPS
13HASH JOIN
3INLIST ITERATOR
2ULYNM.NM_LEVEL_DI_MAPPINGS TABLE ACCESS [BY INDEX ROWID]
1ULYNM.NM_NUI_MAPKEY_DI_MKEY_OBJID_SD INDEX [RANGE SCAN]
12SYS.VW_NSO_1 VIEW
11SORT [GROUP BY]
10HASH JOIN
8NESTED LOOPS
6INLIST ITERATOR
5ULYNM.NM_LEVEL_DI_MAPPINGS TABLE ACCESS [BY INDEX ROWID]
4ULYNM.NM_NUI_MAPKEY_DI_MKEY_OBJID_SD INDEX [RANGE SCAN]
7ULYNM.NM_PK_DI INDEX [UNIQUE SCAN]
9ULYNTSOPS.LOC_PREF_CRVTEMP_IDX INDEX [FAST FULL SCAN]
15ULYNM.NM_DATAITEMS TABLE ACCESS [BY INDEX ROWID]
14ULYNM.NM_PK_DI INDEX [UNIQUE SCAN]
18PARTITION RANGE [ITERATOR]
17ULYNTSOPS.LOC_PREF_CRVTEMP_IDX INDEX [RANGE SCAN]
Step #Description Est.Cost Est.Rows Returned Est. KBytes Returned
1 This plan step retrieves one or more ROWIDs in ascending order by
scanning the B*-tree index NM_NUI_MAPKEY_DI_MKEY_OBJID_SD.2 7 --
2 This plan step retrieves rows from table NM_LEVEL_DI_MAPPINGS
through ROWID(s) returned by an index.5 3 0.144
3 This plan step loops through the query's IN list predicate, executing its child step for each value found.
4 This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index NM_NUI_MAPKEY_DI_MKEY_OBJID_SD.2 7 --
5 This plan step retrieves rows from table NM_LEVEL_DI_MAPPINGS through ROWID(s) returned by an index.5 3 0.144
6 This plan step loops through the query's IN list predicate, executing its child step for each value found.
7 This plan step retrieves a single ROWID from the B*-tree index NM_PK_DI. -- 1 0.013
8 This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause. 5 3 0.182
9 This plan step retrieves all of the ROWIDs of B*-tree index LOC_PREF_CRVTEMP_IDX by sequentially scanning the leaf nodes.1,501 32,219 1,227.091
10 This plan step accepts two sets of rows, each from a different table. A hash table is built using the rows returned by the first child. Each row returned by the second child is then used to probe the hash table to find row pairs which satisfy a condition specified in the query's WHERE clause. Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables. It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make the wrong choice. 1,506 8 0.789
11 This plan step accepts a set of rows from its child node, and sorts them into groups based on the columns specified in the query's GROUP BY clause. 1,508 8 0.789
12 This plan step represents the execution plan for the subquery defined by the view VW_NSO_1. 1,508 8 0.281
13 This plan step accepts two sets of rows, each from a different table. A hash table is built using the rows returned by the first child. Each row returned by the second child is then used to probe the hash table to find row pairs which satisfy a condition specified in the query's WHERE clause. Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables. It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make the wrong choice. 202 3 0.249
14 This plan step retrieves a single ROWID from the B*-tree index NM_PK_DI. -- 1 --
15 This plan step retrieves rows from table NM_DATAITEMS through ROWID(s) returned by an index. 1 1 0.041
16 This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause.203 3 0.372
17 This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index LOC_PREF_CRVTEMP_IDX. 1 1 --
18 This plan step determines the approach to iterating over partitions of a range-partitioned table.
19 This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause.205 1 0.232
20 This plan step retrieves rows from table COM_RECEIVED_VALUES_TEMP through ROWID(s) returned by a local partitioned index.2 100 10.84
21 208 1 0.232
22 This plan step represents the execution plan for the subquery defined by the view .------
23 This plan step performs a windowing analytical function on a set of data which needs to be first sorted based on some criteria which includes RANK.208 1 0.232
24 This plan step represents the execution plan for the subquery defined by the view . 208 1 0.396
25 This plan step accepts a set of rows from its child node, and sorts them on a per-column basis using the query's ORDER BY clause. 212 1 0.396
26 This plan step designates this statement as a SELECT statement. 212 1 0.396
• Currently going ahead with Solution b) as we are running out of time. Solution b) is being preferred over Solution a) as we are not comfortable using hints and rownum=1.
• One final issue we have is related to certain queries (a very small percentage) which don't make use of the partition key applicable_at at all and which makes use of the gntd_tmstp column. We are planning to create a non-prefixed non-unique local index with the foll. Sequence gntd_tmstp, dataitem_id. The worry is sometimes the optimizer ends up picking this index instead of the local prefixed unique index. What are your thoughts on this score?
Miscellaneous:
• To answer your another question, we would have some amount of data migration done but that only be made up of End of the day/Hourly values and it won't be instantaneous data. The migrated data would be put in a separate partition.
Pl. provide your suggestions at the earliest.
Rgds
Hari
Received on Mon Aug 30 2004 - 07:20:30 CDT
 |
 |