海量数据库解决方案_郑保卫_en-core（公开）

DTCC2011DTCC2011 Ⅰ Ⅰ Ⅰ Ⅰ Ⅰ DTCC2011DTCC2011 DTCC2011DTCC2011 Ⅰ TABLE ACCESS Bitmap Conversion (ROWID) Bitmap MINUS Bitmap Index (COL2=‘ABC’) Bitmap Index (COL1=123) Bitmap Index (COL2 IS NULL) Bitmap MINUS Bitmap Index (COL3 < 100) Bitmap MERGE Bitmap OR Green 0006 Hunt 0008 <= Hunt > Hunt Allen 0007 Clarke 0003 Davis 0002 Ford 0005 Jones 0004 King 0001 <= Ford > Ford <= Clark > Clark Green 0006 Hunt 0008 <= Hunt > Hunt Allen 0007 Clarke 0003 Davis 0002 Ford 0005 Jones 0004 King 0001 <= Ford > Ford <= Clark > Clark CBO First_rows All_rows RBO Version Non Key Column Key Column Row Header 분리형 테이블 일체형 테이블 Non Key Column Key Column Row Header 분리형 테이블 일체형 테이블 SGA Database Buffer Cache Shared Pool Shared SQL Area Redo Log Buffer 계급 계급구간 도수 계급값 1 -0.5~4.5 8 2.0 1252.8 2 4.5~9.5 3 7.0 169.4 3 9.5~14.5 6 12.0 37.9 4 14.5~19.5 7 17.0 43.3 5 19.5~24.5 5 22.0 280.2 6 24.5~29.5 5 27.0 779.5 7 29.5~34.5 0 32.0 0.0 8 34.5~39.5 1 37.0 505.6 합 35 3068.7 0.000 0.050 0.100 0.150 0.200 0.250 0.300 0.350 0 1 2 3 4 5 6 0.000 0.050 0.100 0.150 0.200 0.250 0.300 0.350 0 1 2 3 4 5 6 DTCC2011DTCC2011 Ⅰ f(x) Secondary Index Cluster Index General Index General Index Overflow Area General Index DTCC2011DTCC2011 Ⅰ 6 堆组织 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 比较合适 DTCC2011DTCC2011 Ⅰ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ Access Pattern Count Range Old New (1) (2) (3) (4) (5) (6) (1) (2) (3) (4) (5) (6) Column Name Kinds AVG MAX 特 記 事 項 1 SALENO(=), ITEM(=) 2 SALEDATE(=), SALEDEPT(=) 3 SALEDATE(like), SALEDEPT(=) 4 CUSTNO(=), SALEDATE(between) 5 SALEDATE(like), STATUS(=60), CUSTNO(like) 6 STATUS(in), [AGENTNO(like)] 7 ITEM(=), SALEDATE(like), SALETYPE(=) 8 SALEDATE(like), STATUS(=), group by CUSTNO 9 SALEDEPT(=), SALEDATE(like), SALETYPE(=), order by SALEDATE, SALETYPE 10 SALEDATE(between), CUSTNO(=) , (STATUS(=) or SALETYPE(=)) 11 AGENTNO(=), SALEDATE(between), ITEM(like) saleno 20000 11 100 saledate 1500 130 800 月平均(5000件), 月末 集中 Saledept,saledate 11000 20 180 1個月 平均(500), 1年 以內 使用 status 25 8000 56000 60,90 : 90%, Others : 平均 300 custno 3200 63 300 agentno 250 1000 4500 O ld In d e x SALENO SALEDATE STATUS SALEDEPT CUSTNO ITEM ITEM SALEDEPT SALEDATE SALETYPE 1 1 1 180 2 2 3000 4 2 300 5 3 1200 3 4 400 3 4 1400 5 5 1000 3 4 3000 4 2 300 5 3 800 6 SALENO SALEDEPT CUSTNO ITEM SALEDATE (CLUSTER) STATUS ITEM AGENTNO SALEDATE SALEDATE SALEDATE AGENTNO • 6蕃 액세스는 STATUS가 60, 90인 경우는 부분범위 처리가 되도록 할 것 • 2.9번 액세스는 최대 12개월 이내로 처리범위를 제한할 것 N e w In d e x 主 意 事 項 TABLE_NAME SALES 賣出情報 管理 Table TOTAL ROWS 201,500 月 平均 增加量 5,000 分 布 圖 分 析 DTCC2011DTCC2011 Ⅰ DTCC2011DTCC2011 Ⅰ DTCC2011DTCC2011 DTCC2011DTCC2011 Ⅱ DTCC2011DTCC2011 Ⅱ DTCC2011DTCC2011 Ⅱ 局 部 范 围 扫 描 . . . . 整 体 范 围 扫 描 . . . . o x 临 时 存 储 2 次 处 理 o x DTCC2011DTCC2011 Ⅱ 14 Rows Execution Plan ---------- -------------------------------------------------------------------------------- 900400 NESTED LOOPS 500 VIEW 1680 SORT (GROUP BY) 3100 TABLE ACCESS (FULL) OF ‘SURVEY’ 900400 TABLE ACCESS (BY ROWID) OF ‘DA100T’ 901600 INDEX (RANGE SCAN) OF ‘DA100_IDX1’(UNIQUE) B A T C H DA100T SURVEY GROUP BY 临 时 存 储 S O R T x o . . . x x . . . DA100_IDX1 o x 20 NESTED LOOPS 500 VIEW 1680 SORT (GROUP BY) 3100 TABLE ACCESS (FULL) OF ‘SURVEY’ 20 TABLE ACCESS (BY ROWID) OF ‘DA100T’ 25 INDEX (RANGE SCAN) OF ‘DA100_IDX1’(UNIQUE) O N L I N E 3100 1680 500 25 20 20 DTCC2011DTCC2011 Ⅱ 15 INDEX (ITEM_CD) TABLE (PRODUCT) . . . . x INDEX (ITEM_CD) TABLE (PRODUCT) o x o o o o o . . . . SELECT * FROM PRODUCT WHERE ITEM_CD LIKE ‘AB%’ AND CATEGORY LIKE ‘C%’ ORDER BY ITEM_CD DESC SELECT /*+ INDEX_DESC(PRODUCT item_idx)*/ * FROM PRODUCT WHERE ITEM_CD LIKE ‘AB%’ AND CATEGORY LIKE ‘C%’ SELECT ord_dept, ordqty * 1000 FROM order WHERE ord_date like ‘2010%’ ORDER BY ord_dept DESC SELECT /*+ INDEX_DESC(a ord_dept_index) */ ord_dept, orgqty * 1000 FROM order a WHERE ord_date like ‘2010%’ AND ord_dept > ‘ ’ S O R T DTCC2011DTCC2011 Ⅱ SELECT /*+ INDEX_DESC(order pk_order) */ NVL(MAX(seq), 0) + 1 FROM order WHERE deptno = ‘12300’ AND ROWNUM = 1 INDEX (DEPTNO + SEQ) NVL(MAX(SEQ),0) +1 Execution Plan ---------------------------------------------------------------------------------------------- SELECT STATEMENT SORT (AGGREGATE) FIRST ROW INDEX (RANGE SCAN(MIN/MAX)) OF ‘PK_ORDER’ (UNIQUE) SELECT MAX(seq) + 1 FROM order WHERE deptno = ‘12300’ INDEX (DEPTNO+SEQ) . . . . o DTCC2011DTCC2011 Ⅱ INDEX (ORD_DATE) TABLE (ORDER) SELECT ord_date, SUM(qty) FROM order WHERE ord_date LIKE ‘201010%’ GROUP BY ord_date INDEX (ORD_DATE + QTY) . . . . . . . . . . . . G R O U P B Y G R O U P B Y DTCC2011DTCC2011 Ⅱ INDEX (DEPT) TABLE (ITEM_TAB) INDEX (DEPT) TABLE (ITEM_TAB) SELECT COUNT(*) INTO :CNT FROM ITEM_TAB WHERE DEPT = ‘101’ AND SEQ > 100; ………………………………………… IF CNT > 0 …. SELECT 1 INTO :CNT FROM DUAL WHERE EXISTS (SELECT ‘X’ FROM ITEM_TAB WHERE DEPT = ’101’ AND SEQ > 100 ); …………………………………………… IF CNT > 0 …. x Execution Plan --------------------------------------------------------------------------------------------- SELECT STATEMENT FILTER TABLE ACCESS (FULL) OF ‘DUAL’ TABLE ACCESS (BY INDEX ROWID) OF ‘ITEM_TAB’ INDEX (RANGE SCAN) OF ‘ITEM_DEPT_IDX’ (NOT UNIQUE) . . . . . . . o x o o SORT (aggregate) o DTCC2011DTCC2011 Ⅱ INDEX (DEPTNO) TABLE (PRODUCT) . . . . . . . 1 2 3 50 1 2 3 . . . SELECT deptno, sum(qty) FROM product WHERE deptno like ‘120%’ AND rownum <= 50 GROUP BY deptno SELECT deptno, totqty FROM (SELECT ROWNUM as RNUM, deptno, totqty FROM (SELECT deptno, SUM(qty) totqty FROM product WHERE deptno like ‘120%’ AND ROWNUM <= 50 GROUP BY deptno ) ) WHERE RNUM = 5 AND ROWNUM = 1 4 n c a b G R O U P B Y DTCC2011DTCC2011 Ⅱ SELECT /*+ ORDERED USE_NL(x y) */ a.dept_name, b.empno, b.emp_name, c.sal_ym, c.sal_tot FROM ( SELECT a.dept_name, b.hire_date, b.empno, b.emp_name FROM department a, employee b WHERE b.deptno = a.deptno AND a.loc = ‘BEIJING’ AND b.job = ‘MANAGER’ ORDER BY a.dept_name, b.hire_date ) x, salary y WHERE y.empno = x.empno AND y.sal_ym l ike ‘2010%’ SELECT a.dept_name, b.empno, b.emp_name, c.sal_ym, c.sal_tot FROM department a, employee b, salary c WHERE b.deptno = a.deptno AND c.empno = b.empno AND a.loc = ‘BEIJING’ AND b.job = ‘MANAGER’ AND c.sal_like ‘2010%’ ORDER BY a.dept_name, b.hire_date DTCC2011DTCC2011 Ⅱ SELECT sal_dept, agent_name, Jan_tot, Feb_tot,….., Dec_tot FROM (SELECT sal_dept, sum(decode(substr(sal_date,5,2),’01’,sal_amt)) Jan_tot, sum(decode(substr(sal_date,5,2),’02’,sal_amt)) Feb_tot, ……………………………………………………….… sum(decode(substr(sal_date,5,2),’12’,sal_amt)) Dec_tot FROM sales WHERE sal_date l ike :year|| ’%’ GROUP BY sal_dept ORDER BY Jan_tot DESC ) s, agent a WHERE a.agent_no = s.sal_dept AND a.loc = :b1; CREATE or REPLACE FUNCTION GET_AGENT_SALAMT (v_agent in varchar2, v_yymm in varchar2) RETURN number is RET_VAL number(14); BEGIN SELECT nvl(sum(sal_amt),0) into RET_VAL FROM sales WHERE sal_dept = v_agent AND sal_date like v_yymm | | ’%’; RETURN RET_VAL; END GET_AGENT_SALAMT; 存 储 函 数 SELECT sal_dept, agent_name, Jan_tot, GET_AGENT_SALAMT(sal_dept, :year|| ’02’) Feb_tot, ………………………………………………………………… GET_AGENT_SALAMT(sal_dept, :year|| ’12’) Dec_tot FROM ( SELECT sal_dept, nvl(sum(sal_amt),0) Jan_tot, max(agent_name) agent_name FROM sales s, agent a WHERE s.sal_dept = a.agent_no AND a.loc = :b1 AND s.sal_date l ike :year|| ’01’ GROUP BY sal_dept ORDER BY Jan_tot DESC); DTCC2011DTCC2011 DTCC2011DTCC2011 Ⅲ SELECT a.product_cd, product_name, (SELECT AVG(stock_qty) FROM stock b WHERE b.product_cd = a.product_cd AND stock_date between :b1 and :b2 ) avg_stock FROM product a WHERE category_cd = ’20’ and product_cd IN ( SELECT item_cd FROM item_req_t WHERE req_date = ‘20100807’ ) o o o x 운반 단위 CREATE FUNCTION FUNC1 (v_empno varchar2) RETURN varchar2 is V_avg_amt varchar2(30); BEGIN SELECT avg(salamt) into v_avg_amt FROM sales WHERE empno = v_empno and sale_dt like ‘2010%’ ; RETURN v_avg_amt; END FUNC1 ; SELECT empno, ename, job, deptno, ………, FUNC1(empno) FROM employee WHERE deptno = ‘1100’ ; DTCC2011DTCC2011 Ⅲ . . . . TAB1 . . . . TAB1 TAB2 . . . . TAB1 . . . . TAB2 . . . . GROUP BY JOIN TAB1 TAB2 GROUP BY TAB2 GROUP BY FECTH . . . . GROUP BY JOIN 最 优 化 随 着 具 体 情 况 的 不 同 而 不 同 DTCC2011DTCC2011 Ⅲ INSERT into 交易帐目表 (帐目编号, 生成日期, 项目, 金额, ... ) SELECT sequence1.currval+y.NO, to_char(sysdate,’yyyymmdd’), decode(y.no, 1, ‘1234’, 2, ’5678’, 3, ’9876’), decode(y.no, 1, 返还金, 2, 违约金, 3, 拆除费), ... FROM 合同结算表 x, COPY_T y WHERE x.解除日 = :input_date and y.NO <= 3 ; INSERT into 交易帐目表 (帐目编号, 生成日期, 项目, 金额, ... ) SELECT sequence1.currval+y.NO, to_char(sysdate,’yyyymmdd’), ... , decode(y.no, 1, ‘1234’, 2, ’5678’, 3, ’9876’), decode(y.no, 1, 返还金, 2, 违约金, 3, 拆除费), ... FROM 合同结算表 x, COPY_T y WHERE x.解除日 = :input_date and y.NO in (decode(返还金额,0,null,1), decode(违约金额,0,null,2), decode(拆除费,0,null,3)) ; CREATE table copy_t (NO, NO2) as SELECT rownum, substr(to_char(rownum,’09’),2,2) FROM any_table WHERE rownum <= 31 ; C O P Y _ T DTCC2011DTCC2011 Ⅲ 1101 100501 100520 100925 5,000,000 1102 100601 100615 100805 3,800,000 1103 100701 100712 101020 6,780,000 1104 100501 100507 100718 2,890,000 1101 1005 429,687 …… 1101 1006 1,171,875 …… 1101 1007 1,210,937 …… 1101 1008 1,210,937 …… 1101 1009 976,564 …… INSERT into 月销售收入 (流水号, 日期, 销售收入, ... ) SELECT 流水号, substr(:月份,1,4)||y.NO2, (decode(y.NO2, to_char(结束日期,’mm’), to_char(结束日期,’dd’), to_char(last_day(to_date(substr(:年月,1,4) ||y.NO2), ‘yyyymm’)),’dd’)) – decode(y.NO2, to_char(开始日期,’mm’), to_char(开始日期,’dd’),‘01’) +1) * (预付款/(结束日期-开始日期)+1)), … FROM 预付款 x, COPY_T y WHERE x.日期 like :年月||’%’ and y.NO2 between to_char(开始日期,’mm’) and to_char(结束日期,’mm’) ; DTCC2011DTCC2011 Ⅲ SELECT item, decode(NO,1,grade,’total’), sum(m_qty), sum(m_amt), sum(s_qty), sum(s_amt) FROM ( SELECT item, grade, sum(m_qty) m_qty, sum(m_amt) m_amt, sum(s_qty) s_qty, sum(s_amt) s_amt FROM TAB1 WHERE yymm = :in_daye and saup = :saup GROUP BY item, grade ) x, COPY_T y WHERE y.NO <= 2 GROUP BY item, NO, decode( NO,1, grade, ’total’ ) ; 제제 单 位 北京工厂 查询日期 2010/06 PA101 A1 345,00 25,234,200 311,200 21,212,400 A2 723,000 62,190,230 498,000 45,125,610 A3 278,560 192,52,200 226,000 16,819,020 total 1,346,560 106,646,630 1,035,200 83,157,030 PA201 A2 567,400 45,298,700 484,750 39,899,242 A3 368,140 36,442,240 333,200 32,455,670 A4 89,000 7,0 67,800 60,010 4,867,000 B1 60,700 4,689,020 50,440 3,992,098 B3 100,700 82,181,145 80,440 6,765,012 total 1,185,940 175,678,905 1,018,830 87,979,022 产 品 等 级 产 量 制造成本 销售量 销售成本 DTCC2011DTCC2011 Ⅲ SELECT …, Greatest (a.始点, b.始点) 合并始点, Least (a.终点, b.终点) 合并终点 FROM 公司 a, 职位 b WHERE b.始点 <= a.终点 AND b.终点>= a.始点 A公司 02 19 Merge 25 20 24 31 14 19 02 13 19 14 Greatest Least 表连接的 线段 合并的 线段 A公司 02 20 31 19 B公司 02 14 25 31 13 24 14 24 DTCC2011DTCC2011 Ⅲ SELECT 员工编号, SUM( (合并终点 – 合并始点 + 1) * 奖金 标准 excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载 ) FROM (SELECT 员工编号, 职位, 工作地, Greatest (‘20080301’, a.始点, b.始点) 合并始点, Least (‘20080331’, a.终点, b.终点) 合并终点 FROM 职位 a, 工作地 b WHERE b.员工编号 = a.员工编号 AND b.始点 <= a.终点 AND b.终点>= a.始点 AND a.始点 <= ‘20080331’ and a.终点>= ‘20080301’ AND b.始点<= ‘20080331’ and b.终点 >= ‘20080301’ ) x, 奖金标准 y WHERE y.职位 = x.职位 AND y.工作地 = x.工作地 GROUP BY 员工编号 职位 工作地 奖金标准 文秘 首尔 0 文秘 巨济 35000 文秘 蔚山 12000 科长 首尔 0 科长 巨济 40000 科长 蔚山 15000 文秘 首尔 巨济 蔚山 科长 2010/03/23 2010/03/08 2010/03/27 2010/03/01 文秘, 首尔 文秘, 巨济 科长, 巨济 科长, 蔚山 0 1 5 0 0 0 4 0 0 0 0 3 5 0 0 0 7일 15일 4일 5일 奖金标准 线段表连接 处理范围 合并线段 基准表连接 计算面积 按照员工编号 分组 2010/03/31 DTCC2011DTCC2011 Ⅲ A 1 100 1001 A 2 250 1002 A 3 150 1001 B 1 100 1001 B 2 120 1001 B 3 200 1003 D 1 100 1001 D 2 300 1001 D 3 200 1002 … … …... …... A 45 1101 B 30 1101 D 20 1201 … … …... CD SQ AT YM QT DS CD SQ AT YM QT DS A 1 100 1001 45 1101 A 2 250 1002 45 1101 A 3 150 1001 45 1101 B 1 100 1001 30 1101 B 2 120 1001 30 1101 B 3 200 1003 30 1101 D 1 100 1001 20 1201 D 2 300 1001 20 1201 D 3 200 1002 20 1201 .. … …... …… … …… CD AT QT A 500 45 B 420 30 D 600 20 … ...… ... TAB3 CD DP BT YM A 10 200 1001 A 11 150 1002 B 11 150 1001 B 15 300 1003 D 10 200 1001 D 21 100 1001 D 23 250 1002 … … … …… TAB1  CD QT DS A 45 1101 B 30 1103 C 50 1102 D 20 1201 … … …… TAB2 CD SQ AT YM A 1 100 1001 A 2 250 1002 A 3 150 1001 B 1 100 1001 B 2 120 1001 B 3 200 1003 D 1 100 1001 D 2 300 1001 D 3 200 1002 … … … … DTCC2011DTCC2011 Ⅲ CD SQ AT BT YM A 1 100 1001 A 2 250 1002 A 3 150 1001 B 1 100 1001 B 2 120 1001 B 3 200 1003 D 1 100 1001 D 2 300 1001 D 3 200 1002 … … … …… A 10 200 1001 A 11 150 1002 B 11 150 1001 B 15 300 1003 D 10 200 1001 D 21 100 1001 D 23 250 1002 … … … …… CD AT B