CN102662968A - Optimization method for Oracle massive data storage - Google Patents
Optimization method for Oracle massive data storage Download PDFInfo
- Publication number
- CN102662968A CN102662968A CN2012100607890A CN201210060789A CN102662968A CN 102662968 A CN102662968 A CN 102662968A CN 2012100607890 A CN2012100607890 A CN 2012100607890A CN 201210060789 A CN201210060789 A CN 201210060789A CN 102662968 A CN102662968 A CN 102662968A
- Authority
- CN
- China
- Prior art keywords
- data
- indicator
- subregion
- oracle
- space
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
- 238000000034 method Methods 0.000 title claims abstract description 13
- 238000005457 optimization Methods 0.000 title claims abstract description 4
- 238000013500 data storage Methods 0.000 title abstract description 6
- 238000005192 partition Methods 0.000 claims description 15
- 238000004891 communication Methods 0.000 claims description 6
- 230000003203 everyday effect Effects 0.000 claims description 5
- 238000000638 solvent extraction Methods 0.000 claims description 4
- 230000005540 biological transmission Effects 0.000 description 4
- 241001269238 Data Species 0.000 description 2
- 230000000694 effects Effects 0.000 description 2
- 238000005516 engineering process Methods 0.000 description 2
- 230000014759 maintenance of location Effects 0.000 description 2
- 230000009286 beneficial effect Effects 0.000 description 1
- 230000003111 delayed effect Effects 0.000 description 1
- 238000010586 diagram Methods 0.000 description 1
- 230000001747 exhibiting effect Effects 0.000 description 1
- 238000007726 management method Methods 0.000 description 1
- 230000008447 perception Effects 0.000 description 1
Images
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention relates to an optimization scheme for Oracle in massive data storage. The method is suitable for use when the data storage volume is relatively large,the data is queried frequently, and massive data operation is needed. The method is able to balance I/O and to increase the system data throughput by utilizing Oracle partitioned mode, and has some help for saving query time and cost.
Description
Technical field
The present invention relates to a kind of based on oracle database under the big data quantity storage condition, for data storage a kind of optimal solution is provided, improve data security and search efficiency.Specifically a kind of for Oracle big data quantity optimized storage method.
Background technology
Be accompanied by the progressively expansion of 3G network, communication network constantly develops, and website, number of cells further increase, and the communication service data scale is also increasing, and this is just on the safety of data storage and the inquiry velocity further requirement being arranged.And for the communications industry, the monitor network performance data are very important for client perception even prediction network risks in real time.The increase of data volume undoubtedly can delayed data inquiry, cause real-time property not guarantee.In order to accelerate data exhibiting speed, it is very necessary that optimal Storage seems.
The traditional data library storage is that data all are stored in the table, depends merely on index and improves inquiry velocity.But for limited magnetic disc i/o, still data jamming can take place under the big data quantity situation.So the storage policy of choosing, balance I/O distribute, be very important for representing of real time data.
Summary of the invention
The purpose of this invention is to provide a kind of for Oracle big data quantity optimized storage method.
The objective of the invention is to realize in the following manner, adopt the Oracle partitioning strategies, to improve data query speed; The partitioned mode that Oracle provides has: the combination of Range, List, Hash and above-mentioned several method according to the communication network data characteristic, all can have performance data to produce every day; So it is first-selected carrying out the Range subregion according to the date, the date subregion with different cycles is divided on the different physical disks then;
At field of telecommunications; Network element is divided according to the area; Divide after the subregion in time; According to carrying out the Oracle child partition again by the area from different places under the network element, on time and space two dimensions, carry out subregion like this after, it is just very simple in the data of certain time point to locate a network element entity;
Concrete optimization method is following:
In database, set up a plurality of tables of data space; To show division and be dispersed in each table space, the data file with table space is dispersed on the different disks then, is dispersed on each disk with regard to the data that guaranteed each subregion like this; Data query is only inquired about from specific table subregion; Guarantee can obtain data from each disk when concurrent, balance handling up of I/O, concrete sql is following:
Create table INDICATOR_20000
(
MOENTITYID VARCHAR2(128)，
STARTDAY NUMBER(8)?not?null，
STARTTIME NUMBER(6)?not?null，
PERIOD NUMBER?not?null，
BHID VARCHAR2(1200)，
INSTANCEID NUMBER(2)，
INDICATOR_20000_001?NUMBER，
INDICATOR_20000_002?NUMBER，
INDICATOR_20000_003?NUMBER，
INDICATOR_20000_004?NUMBER，
INDICATOR_20000_005?NUMBER，
INDICATOR_20000_006?NUMBER，
INDICATOR_20000_007?NUMBER，
INDICATOR_20000_008?NUMBER，
INDICATOR_20000_009?NUMBER，
INDICATOR_20000_010?NUMBER
)
partition?by?range(startday)?subpartition?by?list(moentityid)
(partition?p_20000_20110816?values?less?than?(20110817)
(subpartition?p_20000_20110816_r1?values?('mo1')，
subpartition?p_20000_20110816_r2?values?(default)))
tablespace tabspace1；
Through above sql; Set up Table I NDICATOR_20000 at the tabspace1 table space; This table is to carry out the Range subregion through the date, in each subregion, distinguishes child partition according to the area then, and division is dispersed in the different table spaces; Come to dwindle step by step query context according to time and space then, to improve inquiry velocity.
The bright beneficial effect of this law is: adopt the Oracle storage policy of this document, can under the big data quantity situation, improve inquiry velocity effectively, practice thrift cost, system performance is improved significantly.Can be for Oracle big data quantity optimized storage scheme.Support the storage of big data quantity.Improve the data query speed under the big data quantity environment.Improve the data storage security.In order to address this problem, just need be under the situation of system's concurrent access balance I/O, so just need a table be demarcated.If a plurality of program parallelization visit datas are not the same subregions that points to, can be divided into these subregions on the different physical disks.Be placed on the different disks, can effectively reduce the magnetic disc i/o conflict, the single channel transmission data by former become the hyperchannel transmission, the advantage of maximum performance Oracle partition table, and this moment, the handling capacity of disk will promote at double, shown in accompanying drawing 1.Simultaneously subregion is divided on the different disks, and effect that yet can protected data if there is a disk out of joint, can not influence the visit of other data yet, has improved safety of data.
Description of drawings
Accompanying drawing 1 is an Oracle big data quantity optimized storage synoptic diagram.
Embodiment
Explanation at length below with reference to Figure of description method of the present invention being done.
Oracle itself provides the notion of partition table, is used for exactly the script big data quantity is dispersed in the different table subregions, so the only inquiry and needn't inquiring about whole table in subregion of inquiry time.Through dividing subregion, can be with 1,000,000, the data volume of millions splits into fraction, in sub-fraction, carries out query manipulation at every turn.But under limited magnetic disc i/o situation, a plurality of programs I/O of system under concurrent access will become bottleneck.
In order to address this problem, just need be under the situation of system's concurrent access balance I/O, so just need a table be demarcated.If a plurality of program parallelization visit datas are not the same subregions that points to, can be divided into these subregions on the different physical disks.Be placed on the different disks, can effectively reduce the magnetic disc i/o conflict, the single channel transmission data by former become the hyperchannel transmission, the advantage of maximum performance Oracle partition table, and this moment, the handling capacity of disk will promote at double, shown in accompanying drawing 1.Simultaneously subregion is divided on the different disks, and effect that yet can protected data if there is a disk out of joint, can not influence the visit of other data yet, has improved safety of data.
Adopt the Oracle partitioning strategies, can improve data query speed, the partitioning strategies that need take.The partitioned mode that Oracle provides has: the combination of Range, List, Hash and above-mentioned several method etc.According to the communication network data characteristic, all can there be every day performance data to produce, be first-selected so carry out the Range subregion according to the date.Can the date subregion of different cycles be divided on the different physical disks then.Such as data retention cycle in database is 1 year, then can be divided in subregion on the different disk quarterly; The data retention cycle is 1 month, then can subregion is first-class by being divided in different disks week.
In addition, at field of telecommunications, network element can be divided according to the area.Divide in time after the subregion, can be based on carrying out the Oracle child partition again by the area from different places under the network element.After on time and space two dimensions, carrying out subregion like this, it is just very simple in the data of certain time point to locate a network element entity.
In field of telecommunications, other network element data data volume of sub-district or carrier frequency level is very big, in the base station several about 10000; The sub-district can reach 30000---40000 data volume; If the data by one day 24 hours 60 minutes granularity are calculated, just having 1,000,000 grades of other data every day needs storage, and index Data Update every day expense is all bigger; If do not carry out subregion, data query speed can become bottleneck.Specifically use below in conjunction with the field of telecommunications network management.
Can in database, set up a plurality of tables of data space; To show division is dispersed in each table space; Data file with table space is dispersed on the different disks then, is dispersed on each disk with regard to the data that guaranteed each subregion like this, and data query is only inquired about from specific table subregion; Guarantee can obtain data from each disk when concurrent, balance handling up of I/O.
Concrete sql is following:
Create table INDICATOR_20000
(
MOENTITYID VARCHAR2(128)，
STARTDAY NUMBER(8)?not?null，
STARTTIME NUMBER(6)?not?null，
PERIOD NUMBER?not?null，
BHID VARCHAR2(1200)，
INSTANCEID NUMBER(2)，
INDICATOR_20000_001?NUMBER，
INDICATOR_20000_002?NUMBER，
INDICATOR_20000_003?NUMBER，
INDICATOR_20000_004?NUMBER，
INDICATOR_20000_005?NUMBER，
INDICATOR_20000_006?NUMBER，
INDICATOR_20000_007?NUMBER，
INDICATOR_20000_008?NUMBER，
INDICATOR_20000_009?NUMBER，
INDICATOR_20000_010?NUMBER
)
partition?by?range(startday)?subpartition?by?list(moentityid)
(partition?p_20000_20110816?values?less?than?(20110817)
(subpartition?p_20000_20110816_r1?values?('mo1')，
subpartition?p_20000_20110816_r2?values?(default)))
tablespace tabspace1；
Through above sql, set up Table I NDICATOR_20000 at the tabspace1 table space, this table is to carry out the Range subregion through the date, in each subregion, distinguishes child partition according to the area then.Division is dispersed in the different table spaces, comes to dwindle step by step query context based on time and space then, can improve inquiry velocity.
Except that the described technical characterictic of instructions, be the known technology of those skilled in the art.
Claims (1)
1. one kind for Oracle big data quantity optimized storage method, it is characterized in that adopting the Oracle partitioning strategies, to improve data query speed; The partitioned mode that Oracle provides has: the combination of Range, List, Hash and above-mentioned several method based on the communication network data characteristic, all can have performance data to produce every day; So it is first-selected carrying out the Range subregion according to the date, the date subregion with different cycles is divided on the different physical disks then;
At field of telecommunications; Network element is divided according to the area; Divide after the subregion in time; According to carrying out the Oracle child partition again by the area from different places under the network element, on time and space two dimensions, carry out subregion like this after, it is just very simple in the data of certain time point to locate a network element entity;
Concrete optimization method is following:
In database, set up a plurality of tables of data space; To show division and be dispersed in each table space, the data file with table space is dispersed on the different disks then, is dispersed on each disk with regard to the data that guaranteed each subregion like this; Data query is only inquired about from specific table subregion; Guarantee can obtain data from each disk when concurrent, balance handling up of I/O, concrete sql is following:
Create table INDICATOR_20000
(
MOENTITYID VARCHAR2(128)，
STARTDAY NUMBER(8)?not?null，
STARTTIME NUMBER(6)?not?null，
PERIOD NUMBER?not?null，
BHID VARCHAR2(1200)，
INSTANCEID NUMBER(2)，
INDICATOR_20000_001?NUMBER，
INDICATOR_20000_002?NUMBER，
INDICATOR_20000_003?NUMBER，
INDICATOR_20000_004?NUMBER，
INDICATOR_20000_005?NUMBER，
INDICATOR_20000_006?NUMBER，
INDICATOR_20000_007?NUMBER，
INDICATOR_20000_008?NUMBER，
INDICATOR_20000_009?NUMBER，
INDICATOR_20000_010?NUMBER
)
partition?by?range(startday)?subpartition?by?list(moentityid)
(partition?p_20000_20110816?values?less?than?(20110817)
(subpartition?p_20000_20110816_r1?values?('mo1')，
subpartition?p_20000_20110816_r2?values?(default)))
tablespace tabspace1；
Through above sql; Set up Table I NDICATOR_20000 at the tabspace1 table space; This table is to carry out the Range subregion through the date, in each subregion, distinguishes child partition according to the area then, and division is dispersed in the different table spaces; Come to dwindle step by step query context according to time and space then, to improve inquiry velocity.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN2012100607890A CN102662968A (en) | 2012-03-09 | 2012-03-09 | Optimization method for Oracle massive data storage |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN2012100607890A CN102662968A (en) | 2012-03-09 | 2012-03-09 | Optimization method for Oracle massive data storage |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN102662968A true CN102662968A (en) | 2012-09-12 |
Family
ID=46772459
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN2012100607890A Pending CN102662968A (en) | 2012-03-09 | 2012-03-09 | Optimization method for Oracle massive data storage |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN102662968A (en) |
Cited By (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN103279582A (en) * | 2013-06-24 | 2013-09-04 | 浙江宇天科技股份有限公司 | Oracle partition based data storing method and device |
| CN103309939A (en) * | 2013-04-23 | 2013-09-18 | 税友软件集团股份有限公司 | Dynamic retrieval method and device based on metadata |
| CN104270340A (en) * | 2014-09-01 | 2015-01-07 | 北京锐安科技有限公司 | Voice signaling data storage method and rapid early warning method |
| CN104731864A (en) * | 2015-02-26 | 2015-06-24 | 国家计算机网络与信息安全管理中心 | Data storage method for mass unstructured data |
Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20030018868A1 (en) * | 2001-07-19 | 2003-01-23 | Chung Shine C. | Method and apparatus for using smart memories in computing |
| CN101118477A (en) * | 2007-08-24 | 2008-02-06 | 成都索贝数码科技股份有限公司 | Process for enhancing magnetic disc data accessing efficiency |
| CN101916261A (en) * | 2010-07-28 | 2010-12-15 | 北京播思软件技术有限公司 | Data partitioning method for distributed parallel database system |
| CN102063490A (en) * | 2010-12-20 | 2011-05-18 | 大唐移动通信设备有限公司 | Database partition method and device |
-
2012
- 2012-03-09 CN CN2012100607890A patent/CN102662968A/en active Pending
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20030018868A1 (en) * | 2001-07-19 | 2003-01-23 | Chung Shine C. | Method and apparatus for using smart memories in computing |
| CN101118477A (en) * | 2007-08-24 | 2008-02-06 | 成都索贝数码科技股份有限公司 | Process for enhancing magnetic disc data accessing efficiency |
| CN101916261A (en) * | 2010-07-28 | 2010-12-15 | 北京播思软件技术有限公司 | Data partitioning method for distributed parallel database system |
| CN102063490A (en) * | 2010-12-20 | 2011-05-18 | 大唐移动通信设备有限公司 | Database partition method and device |
Cited By (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN103309939A (en) * | 2013-04-23 | 2013-09-18 | 税友软件集团股份有限公司 | Dynamic retrieval method and device based on metadata |
| CN103309939B (en) * | 2013-04-23 | 2016-07-20 | 税友软件集团股份有限公司 | A kind of dynamic retrieval method based on metadata and device |
| CN103279582A (en) * | 2013-06-24 | 2013-09-04 | 浙江宇天科技股份有限公司 | Oracle partition based data storing method and device |
| CN104270340A (en) * | 2014-09-01 | 2015-01-07 | 北京锐安科技有限公司 | Voice signaling data storage method and rapid early warning method |
| CN104270340B (en) * | 2014-09-01 | 2017-10-27 | 北京锐安科技有限公司 | A kind of voice signaling date storage method and the method for quick early warning |
| CN104731864A (en) * | 2015-02-26 | 2015-06-24 | 国家计算机网络与信息安全管理中心 | Data storage method for mass unstructured data |
| CN104731864B (en) * | 2015-02-26 | 2018-05-29 | 国家计算机网络与信息安全管理中心 | A kind of date storage method of magnanimity unstructured data |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US9372882B2 (en) | Partitioning online databases | |
| US11449509B2 (en) | Workflow driven database partitioning | |
| CN107408114B (en) | Identifying join relationships based on transactional access patterns | |
| CN103699660B (en) | A kind of method of large scale network stream data caching write | |
| US20120323867A1 (en) | Systems and methods for querying column oriented databases | |
| US9195611B2 (en) | Efficiently updating and deleting data in a data storage system | |
| CN106471501B (en) | Data query method, data object storage method and data system | |
| CN102662968A (en) | Optimization method for Oracle massive data storage | |
| CN104407879A (en) | A power grid timing sequence large data parallel loading method | |
| US10558665B2 (en) | Network common data form data management | |
| Alsubaiee et al. | Lsm-based storage and indexing: An old idea with timely benefits | |
| Peixoto et al. | Scalable and fast top-k most similar trajectories search using mapreduce in-memory | |
| Yang et al. | GCOTraj: A storage approach for historical trajectory data sets using grid cells ordering | |
| US9305045B1 (en) | Data-temperature-based compression in a database system | |
| Xu et al. | Balancing reducer workload for skewed data using sampling-based partitioning | |
| Abadi et al. | SQL-on-hadoop systems: tutorial | |
| Lee et al. | A big data management system for energy consumption prediction models | |
| Shen et al. | Meteorological sensor data storage mechanism based on timescaledb and kafka | |
| CN104112024A (en) | Method and device for high-performance query of database | |
| Martin et al. | Low cost energy forecasting for smart grids using Stream Mine 3G and Amazon EC2 | |
| Zhu et al. | Building Big Data and Analytics Solutions in the Cloud | |
| Koutroumanis et al. | Scalable Spatio-temporal Indexing and Querying over a Document-oriented NoSQL Store. | |
| Zheng et al. | Timo: In‐memory temporal query processing for big temporal data | |
| Ge et al. | Cinhba: A secondary index with hotscore caching policy on key-value data store | |
| Cui et al. | A packaging approach for massive amounts of small geospatial files with HDFS |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C06 | Publication | ||
| PB01 | Publication | ||
| C10 | Entry into substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| WD01 | Invention patent application deemed withdrawn after publication | ||
| WD01 | Invention patent application deemed withdrawn after publication |
Application publication date: 20120912 |