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 -> IOT, INDEX_DESC and Rownum=1

IOT, INDEX_DESC and Rownum=1

From: Wizkid <hariharan.sam_at_wipro.com>
Date: 14 Aug 2004 06:38:59 -0700
Message-ID: <54626635.0408140538.19bb7cf8@posting.google.com> 





Hi Steve,



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. In terms of
records, the number of records is expected to be around 3.5 billion.
This is so because it is linked to a real time application which feeds
real-time data. The sample design of the table is such that it has the
following columns



Dataitem_id       Varchar2(50)



Applicable_at     date



Gntd_tmstp       date


Value                Number





Some points worth noting about this table are:



    
  
  1.   It is both write intensive and read intensive.
  
  2.   Most of the queries are centered around the
lastest day's records
  
  3.   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.
  
  4.   The number of dataitem_ids is expected to be
around 50,000. The cardinality of the dataitem_id would not be
uniform.
  
  5.   No updates would be performed. Every time the
value changes for a given dataitem_id+applicable_at combination, we
insert a new record.
  
  6.   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 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 and the 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','component12


','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','componen

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','comp
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','component23


','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','compone

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','component80
,'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','component12




','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','componen

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','comp
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','compone

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','component80
,'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 as well, by which



    
  
  1.   Make the table an IOT

  
  2.   Use INDEX_DESC hint

  
  3.   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 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 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 as well any
other tips on how we should approach tables with the characteristics I
had listed.



Rgds


Wizkid
Received on Sat Aug 14 2004 - 08:38:59 CDT












Original text of this message













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