您好,欢迎访问三七文档
当前位置:首页 > 商业/管理/HR > 质量控制/管理 > 海量数据库的设计及应用_EN-CORE_郑保卫
Ⅰ•公司名称:EN-CORECO.,LTD,恩核(北京)信息技术有限公司•公司网站:((韩国总公司)(中国分公司)•法人代表:李华植•公司地址:韩国首尔,中国北京•成立日:1997年11月13日(韩国总公司),2012年1月18日(中国分公司)•业种:IT•经营范围:咨询服务/软件开发,书籍出版/书籍销售/技术培训ⅠConsultingSolutionAcademy综合服务ISP/EAP新一代系统构建系统监控数据仓库及商业智能BAMConsultingService数据架构构建数据库设计及性能优化元数据管理系统构建数据仓库构建企业数据管理体系构建培训课程数据架构数据库建模数据库性能优化海量数据库解决方案SQL主数据管理企业委托培训LabNewsEducationBooksOpenClubNewTechRecruiting信息服务™书籍出版书籍Reports国际出版EN-COREDataWare™DataWare™DADataWare™RepositoryDataWare™IDODataWare™DQDataWare™ImpactDataWare™MetaDataWare™ETLDataWare™AltibaseDataWare™ChakraDataWare™ReporterⅠⅠⅠⅠⅠⅠⅠⅠⅠⅠⅠTABLEACCESSBitmapConversion(ROWID)BitmapMINUSBitmapIndex(COL2=‘ABC’)BitmapIndex(COL1=123)BitmapIndex(COL2ISNULL)BitmapMINUSBitmapIndex(COL3100)BitmapMERGEBitmapORGreen0006Hunt0008=HuntHuntAllen0007Clarke0003Davis0002Ford0005Jones0004King0001=FordFord=ClarkClarkGreen0006Hunt0008=HuntHuntAllen0007Clarke0003Davis0002Ford0005Jones0004King0001=FordFord=ClarkClarkCBOFirst_rowsAll_rowsRBOVersionNonKeyColumnKeyColumnRowHeader분리형테이블일체형테이블NonKeyColumnKeyColumnRowHeader분리형테이블일체형테이블SGADatabaseBufferCacheSharedPoolSharedSQLAreaRedoLogBuffer계급계급구간도수계급값1-0.5~4.582.01252.824.5~9.537.0169.439.5~14.5612.037.9414.5~19.5717.043.3519.5~24.5522.0280.2624.5~29.5527.0779.5729.5~34.5032.00.0834.5~39.5137.0505.6합353068.70.0000.0500.1000.1500.2000.2500.3000.35001234560.0000.0500.1000.1500.2000.2500.3000.3500123456Ⅰ转换ⅠⅡTAB1TAB2............TAB1TAB2............Ⅱ............INDEXPRODUCT(ITEM)INDEXPRODUCT(ITEM)........................Ⅱ条件1数据处理_2数据处理_1条件2SELECT_2UPDATE_1INSERT_1LOOP_1SELECT_3SUBROUTINECALLLOOP_2SELECT_1表连接复合处理ArrayINSERTArrayUPDATESELECT_3SELECT_2SELECT_1ⅡⅡⅡⅡⅢ123…SELECT………………………………………FROMTAB1x,TAB2y,TAB3zWHEREx.A1=y.B1ANDz.C1=y.B2ANDx.A2=’10’ANDy.B2LIKE‘AB%’1TAB1→TAB2→TAB32TAB2→TAB3→TAB13TAB3→TAB2→TAB1ⅢSELECT*FROMORDERWHEREORDNOBETWEEN1AND10000ANDCUSTNOLIKE‘DN01%’运输单位INDEX(ORDNO)x....√TABLE(ORDER)xxxxCUSTNOLIKE‘DN01%’过滤条件运输单位INDEX(CUSTNO)√TABLE(ORDER)xxORDNObetween1and10000过滤条件运输单位x√TABLE(ORDER)xxxxORDNObetween1and10000,CUSTNOLIKE‘DN01%’过滤条件处理数万行后返回1行处理几行后返回1行处理几十万行后返回1行ⅢSELECT………………………FROMTAB1WHERECOL2=‘12345’andCOL3LIKE‘2011%’andCOL4200;SELECT………………………FROMTAB1WHERECOL1=‘ABC’andCOL2like‘12%’andCOL3=‘1234’andCOL410000;COL1COL2COL3COL1COL3COL2COL3COL2COL4COL2COL3COL1IndexDrivingrange•COL1=‘ABC’•COL2like‘12%’Valuecheck•COL3=‘201103’TableValuecheck•COL410000IndexDrivingrange•COL1=‘ABC’•COL3=‘201103’•COL2like‘12%’TableValuecheck•COL410000IndexDrivingrange•COL3=‘201103’•COL2like‘12%’Valuecheck•COL410000TableValuecheck•COL1=‘ABC’IndexDrivingrange•COL2like‘12%’Valuecheck•COL3=‘ABC’•COL1=‘201103’TableValuecheck•COL410000……ⅢⅣABB5510ABC6715ABC8926ABC10028ABC12332ABC18067BCA10012BCA10027BCA12348BCA8585CBA12335CBA12349CBA21464CBA21499……INDEX212034123712391231212332123461235212356123621236412368123751239932148………SELECT*FROMtab1WHEREcol1=‘ABC’ANDcol2=123;TAB132ABC123…ABB10ABC15ABC26ABC28ABC32ABC67BCA12BCA27BCA48BCA85CBA35CBA49CBA64CBA99………INDEX1INDEX3(COL1+COL2)323232灵活性成本高成本低速度快Ⅳ尽量不要对离散度差别比较大的索引进行合并对离散度比较好的索引进行合并,通常情况下能取得较好效果禁止基于离散度不好的列创建索引离散度好的索引与不好的索引合并,反倒影响离散度比较好的索引能力的发挥让离散度比较好的列始终扮演重要角色离散度不好的列始终都会产生不良影响INDEX1TABLE1INDEX2INDEX1xoⅣ……………113A23113A54113B67113B34113C39113C42113D67113D83114B64114………115A26114A25114A29114B52A11425A11429A11526A………A11111A1125A11218A11323A11354A11530A11619A…...…B11141B11145B11265114A25114A29114B52A11425A11429COL2COL1ROWIDSELECT*FROMtab1WHEREcol1=‘A’ANDcol2=114;TableaccessTableaccessIFCOL1‘A’A11526COL1COL2ROWIDIfCOL2114Ⅳ……………113A23113A54113B67113B34113C39113C42113D67113D83114A25114A29114B52114B64114………115A26A11323A11354A11425A11429A………A11111A1125A11218B11265A11526A11530A11619A…...…B11141B11145SELECT*FROMtab1WHEREcol1=‘A’ANDcol2BETWEEN113AND114;TableaccessTableaccessXXXXXXXXA11323A11354A11425A11429A11526COL1COL2ROWIDIfCOL2114113A23113A54113B67113B34113C39113C42113D67113D83114A25114A29114B52114B64114………115A26COL2COL1ROWIDIFCOL1‘A’IfCOL2114XⅣ110A10110B41111A11111B65111C96111D5112A73112B18112C45113A22COL2COL1ROWIDSELECT*FROMTAB1WHERECOL1=‘A’ANDCOL2BETWEEN111AND112SELECT*FROMTAB1WHERECOL1=‘A’ANDCOL2IN(111,112)110A10110B41111A11111B65111C96111D5112A73COL2COL1ROWID112B18112C45113A22ⅣSELECT………FROMSALESWHEREPROD=‘PRINTER’ANDDEPTCDlike‘%’ANDSALEDATE=‘20110326’SELECT………FROMSALESWHEREPROD=‘PRINTER’ANDDEPTCDIN(SELECTDEPTCDFROMDEPT)ANDSALEDATE=‘20110326’PRINTER111020110301PRINTER111020110302PRINTER111020110303PRINTER111020110304…………………………………………………PRINTER111020110326PRINTER112020110301PRINTER112020110302PRINTER112020110303…………………………………………………PRINTER112020110323PRINTER112020110324PRINTER112020110325PRINTER112020110326…………………………………………………PRINTER912020110301PRINTER912020110302PRINTER912020110303PRINTER912020110304…………………………………………………PRINTER912020110326ROUTER112020110301ROUTER112020110302…………………………………………………ROUTER112020110303…………………………………………………PRODDEPTCDSALEDATE……TableaccessTableaccessTableaccessSELECT………FROMSALESWHEREPROD=‘PRINTER’ANDSALEDATE=‘20110326’Ⅳ12010C1C2ROWID35021230112305724034240582407611…200…34…400…57…300…58…500…76…600
本文标题:海量数据库的设计及应用_EN-CORE_郑保卫
链接地址:https://www.777doc.com/doc-6099956 .html