performance - Best way to design for one-to-many code values in Oracle -

-

we receive several millions of records per day on temperature metrics. of pertinent metadata these records maintained in single partitioned table date (month). going start receiving 20 individual codes associated data. intent allow searching these codes.

what effective way store data minimize search response time? database oracle 11gr2.

some options taking consideration:

(1) create separate table main record id , code values. like

id        code --        ----  1        aa  1        bb  1        cc  2        aa  2        cc  2        dd

concerns:

  • would require bitmap index on code column, table highly transactional no bitmap
  • table huge on time 20 codes per main record

(2) create separate table partitioned code values.

concerns:

  • partition maintenance new codes
  • search performance

(3) add xmltype column existing table , format codes each record xml , create xmlindex on column: like:

<c1>aa</c1> <c2>bb</c2> <c3>cc</c3>

concerns:

  • query response time when searching on code poor

any recommendations welcome.

thanks.

you need benchmark different approaches. there's no way can give meaningful solutions without knowing more scenario. how many different codes there in total? what's average number of codes per reading? there noticeable skew in distribution of codes? access paths need support searching code?

then there's matter of how load data (batches? drip feed?). , benefits derive using partitioning.

anyway. here's 1 more approach, amalgamation of (1) , (2).

given main table partitioned month should partition child table same scheme. can subpartition code well. 

create table main_codes     ( reading_dt date not null       , main_id number not null       , code varchar2(2)       , constraint main_codes_pk primary key (code, reading_dt, main_id) using index local      ) partition range (reading_dt)  subpartition list (code)  subpartition template  (      subpartition sp_aa values ( 'aa' ),      subpartition sp_bb values ( 'bb' ),      subpartition sp_cc values ( 'cc' ),      subpartition sp_dd values ( 'dd' ) )  (     partition p_2015jan values less (date '2015-02-01' ),     partition p_2015feb values less (date '2015-03-01' ),     partition p_2015mar values less (date '2015-04-01' ) ) /

you'll want foreign on main table too:

alter table main_codes      add constraint code_main_fk foreign key (reading_dt, main_id)     references main_table (reading_dt, main_id) /  create index code_main_idx on  main_codes (entry_dt, id) local /

depending on number of codes have, creating subpartition template tedious. why nature gave cut'n'paste.

but whatever do, don't go down xml path.


Comments

Post a Comment

Popular posts from this blog

Java 3D LWJGL collision -

-
i making 3d java game lwjgl library, , wondering how add collision detection, player not go through models. i using obj models. here objloader class, loads models: package renderengine; import java.io.bufferedreader; import java.io.file; import java.io.filenotfoundexception; import java.io.filereader; import java.util.arraylist; import java.util.list; import models.rawmodel; import org.lwjgl.util.vector.vector2f; import org.lwjgl.util.vector.vector3f; public class objloader { public static rawmodel loadobjmodel(string filename, loader loader){ filereader fr = null; try { fr = new filereader(new file("res/"+filename+".obj")); } catch (filenotfoundexception e) { system.err.println("couldn't load file!"); e.printstacktrace(); } bufferedreader reader = new bufferedreader(fr); string line; list<vector3f> vertices = new arraylist<vector3f...
Read more

spring - SubProtocolWebSocketHandler - No handlers -

-
i have ugly error during deploying spring app on jboss. 18:11:16,025 error [org.apache.catalina.core.containerbase.[jboss.web].[default-host].[/consumer]] (msc service thread 1-7) exception sending context initialized event listener instance of class org.springframework.web.context.contextloaderlistener: org.springframework.context.applicationcontextexception: failed start bean 'subprotocolwebsockethandler'; nested exception java.lang.illegalargumentexception: no handlers @ org.springframework.context.support.defaultlifecycleprocessor.dostart(defaultlifecycleprocessor.java:176) [spring-context-4.1.3.release.jar:4.1.3.release] @ org.springframework.context.support.defaultlifecycleprocessor.access$200(defaultlifecycleprocessor.java:51) [spring-context-4.1.3.release.jar:4.1.3.release] @ org.springframework.context.support.defaultlifecycleprocessor$lifecyclegroup.start(defaultlifecycleprocessor.java:346) [spring-context-4.1.3.release.jar:4.1.3.release] @ org.s...
Read more

methods - python can't use function in submodule -

-
my folder structure this: pythonstuff/ program.py modulea/ __init__.py foo.py bar.py here's code bar.py : def hello(): print("hello, world!") here's code program.py : #!/usr/bin/env python3 modulea import bar bar.hello() i run $ python3 program.py somehow error: file "program.py", line 3, in <module> bar.hello() attributeerror: 'module' object has no attribute 'hello' edit: __init__.py file empty. edit2: after trying realized had bar.py in root directory contained hello() method. bar.py in modulea/ directory empty. add it import os __all__ = [] module in os.listdir(os.path.dirname(__file__)): if module != '__init__.py' , module[-3:] == '.py': __all__.append(module[:-3]) the init .py files required make python treat directories containing packages; done prevent directories common name, such string, unintentionally hiding v...
Read more