Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

Re: IOT, INDEX_DESC and Rownum=1

From: Wizkid <hariharan.sam_at_wipro.com>
Date: 15 Aug 2004 23:06:13 -0700
Message-ID: <54626635.0408152206.f8c9ffa@posting.google.com>


All,

Wanted to add few more facts regarding the question I had stated on Saturday:

  1. The range partitions planned would be monthly partitions
  2. Partitions would contain dataitems with different retention periods
  3. The system is an OLTP system.
  4. 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.
  5. 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:

  1. Would IOT scale up to such monstrous requirements?

Rgds
Hari

hariharan.sam_at_wipro.com (Wizkid) wrote in message news:<54626635.0408140538.19bb7cf8_at_posting.google.com>...
> Hi,
>
> We are designing an application, at the heart of which is a very 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 is employed. Number of records is expected
> to be around 3.5 billion.

> The volume of data is essentially owing to the system 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)
>
> Applicable_at date
>
> Gntd_tmstp date
>
> Value Number
>
> Some points worth noting about this table are:
>
> a) It is both write intensive and read intensive.
> b) Most of the queries are centered around the
> lastest day's records
> c) One driving principle is that the application
> works on the latest known good value resulting in ‘most recent
> occurrence' type of queries for the dataitem_id + applicable_at
> combination. i.e the queries work using the ‘<=' clause.
> d) The number of dataitem_ids is expected to be
> around 50,000. The cardinality of the dataitem_id would not be
> uniform.
> e) No updates would be performed. Every time
> value changes happen for a given dataitem_id+applicable_at combination, we
> would insert a new record.
> f) Predominant queries for a given dataitem_id
> would be i) give me the latest value for an applicable_at ii) give the
> latest values found between a from_applicable_at and a to_applicable_at
> ii) give me the latest value for a day. Ofcourse, the queries could be
> for a range of dataitem_ids as well.
>
> Currently, we are conducting a POC to test the resilience of this
> table so that it provides optimum throughput and response time. We are
> employing a strategy by which
> a) The table is created as a heap table
> b) We have Range partitioned the table on
> applicable_at
> c) We have a local prefixed index on applicable_at
> d) We employ an inline view/sub query which only
> works on the local prefixed index and brings out the min of the
> maximum applicable_at for the dataitem_ids and a given applicable_at
> combination. The min(max(applicable_at)) then feeds as the lower bound
> for the table to be queried. On a test instance with 300 GB of data
> this strategy is providing good results.
> e) The more the lower bound is closer to the upper
> bound, the lesser number of records get fetched and the performance
> becomes even better.
> f) We then employ the rank function 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 by issuing rank=1.
>
> Pl. have a look at the query given below and itz corr. explain plan for more
> details.
>
> explain plan for
> 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.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','component
2
> ','component126','component127','component128','component129','component130','component131','component132','component133','component134','component135','component136','component137','component138','component139','component140','component141','component142','component143','component144','component145','component146','component147','component148','componen149','component150','component151','compon
n
> 152','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','co
p
> nent179','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'
'
> omponent206','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','component
3
> ','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','compo
e
> t57','component58','component59','component60','component61','component62','component63','component64','component649','component64','component65','component6500','component650','component650','component66','component67','component68','component69','component70','component71','component72','component73','component74','component75','component76','component77','component78','component79','component
0
> ,'component81','component82','component83','component84','component85','component86','component87','component88','component90','component91','component92','component93','component94','component95','component96','component97','component98','component99')
>
> AND D.DELETED_YN = 'N'
>
> AND D.DATAITEM_ID = B.DATAITEM_ID
>
> AND B.DATAITEM_ID = C.DATAITEM_ID
>
> AND (
>
> (D.END_DT IS NULL OR D.END_DT >=TO_DATE('23-MAY-2003
> 05:44:00', 'DD-MON-YYYY HH24:MI:SS'))
>
> AND D.START_DT <= TO_DATE('23-MAY-2003 05:44:00',
> 'DD-MON-YYYY HH24:MI:SS')
>
> )
>
> AND C.GNTD_TMSTP <= TO_DATE('23-MAY-2003 05:44:00',
> 'DD-MON-YYYY HH24:MI:SS')
>
> AND C.APPLICABLE_AT <=TO_DATE('23-MAY-2003
> 05:44:00','DD-MON-YYYY HH24:MI:SS')
>
> AND C.APPLICABLE_AT >=
>
> ----------
>
> (SELECT MIN(T2.APPLICABLE_AT) from
>
> (
>
> SELECT MAX(CRV.APPLICABLE_AT) APPLICABLE_AT
>
> FROM COM_RECEIVED_VALUES CRV,
>
> NM_LEVEL_DI_MAPPINGS N
>
> WHERE
>
> N.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','component
2
> ','component126','component127','component128','component129','component130','component131','component132','component133','component134','component135','component136','component137','component138','component139','component140','component141','component142','component143','component144','component145','component146','component147','component148','componen149','component150','component151','compon
n
> 152','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','co
p
> nent179','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'
'
> omponent206','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','compo
e
> t57','component58','component59','component60','component61','component62','component63','component64','component649','component64','component65','component6500','component650','component650','component66','component67','component68','component69','component70','component71','component72','component73','component74','component75','component76','component77','component78','component79','component
0
> ,'component81','component82','component83','component84','component85','component86','component87','component88','component90','component91','component92','component93','component94','component95','component96','component97','component98','component99')
>
> AND N.MAPPING_KEY = 'ENDAPRESSURE'
>
> AND N.DELETED_YN = 'N'
>
> AND (
>
> (N.END_DT IS NULL OR N.END_DT >=TO_DATE('23-MAY-2003
> 05:44:00', 'DD-MON-YYYY HH24:MI:SS'))
>
> AND N.START_DT <= TO_DATE('23-MAY-2003 05:44:00',
> 'DD-MON-YYYY HH24:MI:SS')
>
> )
>
> AND crv.dataitem_id=N.dataitem_id
>
> AND crv.APPLICABLE_AT <= TO_DATE('23-MAY-2003
> 05:44:00','DD-MON-YYYY HH24:MI:SS')
>
> AND crv.APPLICABLE_AT >= TO_DATE('22-MAY-2003
> 17:44:00','DD-MON-YYYY HH24:MI:SS')
>
> AND crv.GNTD_TMSTP <= TO_DATE('23-MAY-2003 05:44:00',
> 'DD-MON-YYYY HH24:MI:SS')
>
> group by crv.dataitem_id
>
> ) t2 )
>
> -----------
>
> ) A WHERE rank=1 ORDER BY 1,2,6;
>
> PLAN_TABLE_OUTPUT
>
> --------------------------------------------------------------------------------------------------------------------------------------------
> -----------------------------------------------------------------------------------------------------------------------------------
>
> | Id | Operation | Name
> | Rows | Bytes |TempSpc| Cost | Pstart| Pstop |
>
> -----------------------------------------------------------------------------------------------------------------------------------
>
> | 0 | SELECT STATEMENT |
> | 404 | 160K| | 94123 | | |
>
> | 1 | SORT ORDER BY |
> | 404 | 160K| 376K| 94123 | | |
>
> | 2 | VIEW |
> | 404 | 160K| | 94098 | | |
>
> | 3 | WINDOW SORT PUSHED RANK |
> | 404 | 77972 | | 94098 | | |
>
> | 4 | HASH JOIN |
> | 404 | 77972 | | 94085 | | |
>
> | 5 | NESTED LOOPS |
> | 3 | 273 | | 8 | | |
>
> | 6 | INLIST ITERATOR |
> | | | | | | |
>
>
>
> PLAN_TABLE_OUTPUT
>
> --------------------------------------------------------------------------------------------------------------------------------------------
>
> | 7 | TABLE ACCESS BY INDEX ROWID | NM_LEVEL_DI_MAPPINGS
> | 3 | 147 | | 5 | | |
>
> | 8 | INDEX RANGE SCAN |
> NM_NUI_MAPKEY_DI_MKEY_OBJID_SD | 7 | | | 2 |
> | |
>
> | 9 | TABLE ACCESS BY INDEX ROWID | NM_DATAITEMS
> | 1 | 42 | | 1 | | |
>
> | 10 | INDEX UNIQUE SCAN | NM_PK_DI
> | 1 | | | | | |
>
> | 11 | PARTITION RANGE ITERATOR |
> | | | | | KEY | 6 |
>
> | 12 | TABLE ACCESS BY LOCAL INDEX ROWID | COM_RECEIVED_VALUES
> | 1610K| 156M| | 94054 | KEY | 6 |
>
> | 13 | INDEX RANGE SCAN | LOC_PREF_CRV_IDX
> | 10M| | | 58449 | KEY | 6 |
>
> | 14 | SORT AGGREGATE |
> | 1 | 9 | | | | |
>
> | 15 | VIEW |
> | 150 | 1350 | | 1813 | | |
>
> | 16 | SORT GROUP BY |
> | 150 | 11850 | | 1813 | | |
>
> | 17 | HASH JOIN |
> | 150 | 11850 | | 1810 | | |
>
>
>
> PLAN_TABLE_OUTPUT
>
> --------------------------------------------------------------------------------------------------------------------------------------------
>
> | 18 | INLIST ITERATOR |
> | | | | | | |
>
> | 19 | TABLE ACCESS BY INDEX ROWID| NM_LEVEL_DI_MAPPINGS
> | 3 | 147 | | 5 | | |
>
> | 20 | INDEX RANGE SCAN |
> NM_NUI_MAPKEY_DI_MKEY_OBJID_SD | 7 | | | 2 |
> | |
>
> | 21 | INDEX RANGE SCAN | LOC_PREF_CRV_IDX
> | 229K| 6738K| | 1803 | 6 | 6 |
>
> -----------------------------------------------------------------------------------------------------------------------------------
>
> I am thinking about an alternate option, by which
>
> a) I would make the table an IOT
>
> b) I would use INDEX_DESC hint
>
> c) And issue a rownum=1
>
> Making the table an IOT would ensure that I wouldn't be dependent on
> the availability of the index. 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 is appealing to me.
>
> I have simulated the same with a mock table:
> 18:22:16 SQL> desc crv
> Name
> ------------------------------------------------------------------------------------
>
> APPLICABLE_AT
>
> DATAITEM_ID
>
> GNTD_TMSTP
>
> VALUE
> The IOT ?s PK sequence is as follows:
> applicable_at,dataitem_id,gntd_tmstp
>
> 18:22:18 SQL> select * from crv
> 18:22:22 2 /
>
> APPLICABL DATAITEM_ID GNTD_TMST
> VALUE
>
> --------- -------------------------------------------------- ---------
> ----------
>
> 12-AUG-04 d1 12-AUG-04
> 10
>
> 12-AUG-04 d2 12-AUG-04
> 20
>
> 13-AUG-04 d1 13-AUG-04
> 10
>
> 13-AUG-04 d2 13-AUG-04
> 20
>
> 14-AUG-04 d1 14-AUG-04
> 10
>
> 14-AUG-04 d2 14-AUG-04
> 20
>
> 6 rows selected.
> Elapsed: 00:00:00.00
>
> 18:26:15 SQL> select /*+INDEX_DESC (crv, SYS_IOT_TOP_48210) */ * from
> crv where dataitem_id='d1' and
>
> 18:26:23 2 applicable_at <= to_date('15-AUG-2004
> 18:00:00','DD-MON-YYYY HH24:MI:SS')
>
> 18:26:23 3 and rownum=1
>
> 18:26:23 4 /
>
>
>
> APPLICABL DATAITEM_ID GNTD_TMST
> VALUE
>
> --------- -------------------------------------------------- ---------
> ----------
>
> 14-AUG-04 d1 14-AUG-04
> 10
>
>
>
> Elapsed: 00:00:00.00
>
>
>
> Execution Plan
>
> ----------------------------------------------------------
>
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=58)
>
> 1 0 COUNT (STOPKEY)
>
> 2 1 INDEX (RANGE SCAN DESCENDING) OF 'SYS_IOT_TOP_48210' (UN
>
> IQUE) (Cost=2 Card=4 Bytes=232)
>
>
>
> Pl. provide your suggestions on the alternate solution. Also provide me any
> other suggestions on how I should approach tables with the characteristics I
> had listed.
>
> Rgds
> Hari
Received on Mon Aug 16 2004 - 01:06:13 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US