Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › 关于位图索引

张沛

0 积分	1 好友	29 主题

发消息

[RAC性能调优] 关于位图索引

1^#

发表于 2013-1-21 10:24:45 | 查看: 3760| 回复: 6

数据库：
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

现在有3张表，数据量都在100万左右，我通过select from group by找出每张表都有四五列左右适合建位图索引，表空间不是很充足，从节省表空间的角度我是对每一列建位图索引好还是把每张表，适合建的所有列，结合起来进能更节省一些空间？

找出来觉得适合的列都是像vat_tax_id
org_id
uom_code
accounting_rule_id
line_type
这些不经常增删改的列，而且返回结果最多几十行的

分享0

收藏0 回复只看该作者道具举报

fengbao

2^#

发表于 2013-1-21 10:30:54

建索引跟需求有关系的，没必要都建吧？

回复只看该作者道具举报

张沛

3^#

发表于 2013-1-21 10:55:45

fengbao 发表于 2013-1-21 10:30
建索引跟需求有关系的，没必要都建吧？

这个我知道，现在有个查询就是查询这些列，执行计划走的全表扫描，完了我就看这个列
例如org_id只有103,104,105三个值，我觉得要是建位图索引应该会好，但是问题是生产库表空间使用90%了，我正拿测试库试，就是不知道建完会多大，有多大影响？

回复只看该作者道具举报

张沛

4^#

发表于 2013-1-21 10:58:30

CREATE BITMAP INDEX AR.IDX$$_zpbitmap1 ON AR.RA_CUSTOMER_TRX_LINES_ALL(vat_tax_id) tablespace APPS_TS_TX_IDX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);
CREATE BITMAP INDEX AR.IDX$$_zpbitmap2 ON AR.RA_CUSTOMER_TRX_LINES_ALL(org_id) tablespace APPS_TS_TX_IDX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);
CREATE BITMAP INDEX AR.IDX$$_zpbitmap3 ON AR.RA_CUSTOMER_TRX_LINES_ALL(uom_code) tablespace APPS_TS_TX_IDX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);

复制代码

创建位图索引如上，这是三个列都建位图的，我想要是只在一列上创建位图索引，完了再在这列和另外两列创建复合索引会不会节约一些空间或者更高效一点
应用中select查询用到的就是这三列，执行计划走的全表扫描

回复只看该作者道具举报

张沛

5^#

发表于 2013-1-21 11:10:30

1
2 -----------------------------------------------------------------------------------------------------------------------------
3 | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
4 -----------------------------------------------------------------------------------------------------------------------------
5 | 0 | SELECT STATEMENT | | 194 | 174K| | 28743 (3)|
6 |* 1 | HASH JOIN RIGHT OUTER | | 194 | 174K| | 28743 (3)|
7 |* 2 | TABLE ACCESS FULL | MTL_UNITS_OF_MEASURE_TL | 17 | 221 | | 5 (0)|
8 | 3 | NESTED LOOPS | | 194 | 171K| | 28738 (3)|
9 | 4 | NESTED LOOPS | | 194 | 167K| | 28543 (3)|
10 | 5 | NESTED LOOPS | | 194 | 159K| | 28349 (3)|
11 |* 6 | HASH JOIN RIGHT OUTER | | 194 | 155K| | 28155 (3)|
12 | 7 | TABLE ACCESS BY INDEX ROWID | FND_LOOKUP_VALUES | 1 | 71 | | 5 (0)|
13 |* 8 | INDEX RANGE SCAN | FND_LOOKUP_VALUES_U1 | 1 | | | 3 (0)|
14 |* 9 | HASH JOIN RIGHT OUTER | | 194 | 142K| | 28150 (3)|
15 | 10 | TABLE ACCESS FULL | AR_VAT_TAX_ALL_B | 14 | 266 | | 5 (0)|
16 | 11 | NESTED LOOPS OUTER | | 194 | 138K| | 28144 (3)|
17 | 12 | NESTED LOOPS OUTER | | 194 | 135K| | 27562 (3)|
18 | 13 | NESTED LOOPS OUTER | | 194 | 132K| | 27561 (3)|
19 |* 14 | HASH JOIN OUTER | | 194 | 112K| | 27179 (3)|
20 | 15 | NESTED LOOPS OUTER | | 194 | 108K| | 27154 (3)|
21 |* 16 | HASH JOIN | | 194 | 104K| | 27153 (3)|
22 | 17 | TABLE ACCESS BY INDEX ROWID | FND_LOOKUP_VALUES | 1 | 59 | | 5 (0)|
23 |* 18 | INDEX RANGE SCAN | FND_LOOKUP_VALUES_U1 | 1 | | | 3 (0)|
24 |* 19 | HASH JOIN | | 372 | 179K| | 27147 (3)|
25 | 20 | TABLE ACCESS FULL | RA_CUST_TRX_TYPES_ALL | 246 | 7626 | | 5 (0)|
26 |* 21 | HASH JOIN | | 1741 | 787K| | 27142 (3)|
27 | 22 | TABLE ACCESS FULL | RA_BATCH_SOURCES_ALL | 78 | 2184 | | 5 (0)|
28 |* 23 | HASH JOIN | | 4240 | 1801K| 13M| 27136 (3)|
29 |* 24 | HASH JOIN | | 38158 | 13M| | 15113 (3)|
30 |* 25 | TABLE ACCESS FULL | FND_LOOKUP_VALUES | 13561 | 781K| | 2189 (2)|
31 |* 26 | HASH JOIN RIGHT OUTER | | 1358K| 400M| | 12911 (4)|
32 | 27 | TABLE ACCESS FULL | JTF_RS_SALESREPS | 504 | 11592 | | 5 (0)|
33 |* 28 | HASH JOIN | | 1358K| 370M| | 12892 (3)|
34 | 29 | NESTED LOOPS | | 1 | 177 | | 9 (0)|
35 | 30 | NESTED LOOPS | | 1 | 118 | | 5 (0)|
36 | 31 | TABLE ACCESS BY INDEX ROWID| FND_LOOKUP_VALUES | 1 | 59 | | 3 (0)|
37 |* 32 | INDEX UNIQUE SCAN | FND_LOOKUP_VALUES_U1 | 1 | | | 2 (0)|
38 | 33 | TABLE ACCESS BY INDEX ROWID| FND_LOOKUP_VALUES | 1 | 59 | | 2 (0)|
39 |* 34 | INDEX UNIQUE SCAN | FND_LOOKUP_VALUES_U1 | 1 | | | 1 (0)|
40 | 35 | TABLE ACCESS BY INDEX ROWID | FND_LOOKUP_VALUES | 1 | 59 | | 4 (0)|
41 |* 36 | INDEX RANGE SCAN | FND_LOOKUP_VALUES_U1 | 1 | | | 2 (0)|
42 |* 37 | TABLE ACCESS FULL | RA_CUST_TRX_LINE_GL_DIST_ALL | 2609K| 271M| | 12857 (3)|
43 | 38 | TABLE ACCESS FULL | RA_CUSTOMER_TRX_ALL | 454K| 29M| | 9628 (2)|
44 | 39 | TABLE ACCESS BY INDEX ROWID | GL_DAILY_CONVERSION_TYPES | 1 | 19 | | 1 (0)|
45 |* 40 | INDEX UNIQUE SCAN | GL_DAILY_CONVERSION_TYPES_U1 | 1 | | | 0 (0)|
46 | 41 | TABLE ACCESS FULL | FND_DOCUMENT_SEQUENCES | 3976 | 87472 | | 25 (0)|
47 |* 42 | TABLE ACCESS BY INDEX ROWID | RA_CUSTOMER_TRX_LINES_ALL | 1 | 105 | | 3 (0)|
48 |* 43 | INDEX RANGE SCAN | RA_CUSTOMER_TRX_LINES_U1 | 1 | | | 2 (0)|
49 | 44 | TABLE ACCESS BY INDEX ROWID | RA_RULES | 1 | 18 | | 1 (0)|
50 |* 45 | INDEX UNIQUE SCAN | RA_RULES_U1 | 1 | | | 0 (0)|
51 |* 46 | TABLE ACCESS BY INDEX ROWID | RA_CUSTOMER_TRX_LINES_ALL | 1 | 14 | | 3 (0)|
52 |* 47 | INDEX RANGE SCAN | RA_CUSTOMER_TRX_LINES_U1 | 1 | | | 2 (0)|
53 | 48 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_ACCOUNTS | 1 | 21 | | 1 (0)|
54 |* 49 | INDEX UNIQUE SCAN | HZ_CUST_ACCOUNTS_U1 | 1 | | | 0 (0)|
55 | 50 | TABLE ACCESS BY INDEX ROWID | HZ_PARTIES | 1 | 40 | | 1 (0)|
56 |* 51 | INDEX UNIQUE SCAN | HZ_PARTIES_U1 | 1 | | | 0 (0)|
57 | 52 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_SITE_USES_ALL | 1 | 25 | | 1 (0)|
58 |* 53 | INDEX UNIQUE SCAN | HZ_CUST_SITE_USES_U1 | 1 | | | 0 (0)|
59 -----------------------------------------------------------------------------------------------------------------------------

复制代码

回复只看该作者道具举报

张沛

6^#

发表于 2013-1-21 11:10:47

61 Predicate Information (identified by operation id):
62 ---------------------------------------------------
63
64 1 - access("UOM_CODE"(+)="CTL"."UOM_CODE")
65 2 - filter("LANGUAGE"(+)=USERENV('LANG'))
66 6 - access("LV"."LOOKUP_CODE"(+)="CTL"."LINE_TYPE")
67 8 - access("LV"."LOOKUP_TYPE"(+)='STD_LINE_TYPE' AND "LV"."VIEW_APPLICATION_ID"(+)=222 AND
68 "LV"."SECURITY_GROUP_ID"(+)=0 AND "LV"."LANGUAGE"(+)=USERENV('LANG'))
69 filter("LV"."LANGUAGE"(+)=USERENV('LANG') AND "LV"."SECURITY_GROUP_ID"(+)=0)
70 9 - access("AVT"."VAT_TAX_ID"(+)="CTL"."VAT_TAX_ID" AND NVL("AVT"."ORG_ID"(+),(-99))=NVL("CTL"."ORG_ID",(-99)))
71 14 - access("CT"."DOC_SEQUENCE_ID"="FD"."DOC_SEQUENCE_ID"(+))
72 16 - access("LV"."LOOKUP_CODE"="CTT"."TYPE")
73 18 - access("LV"."LOOKUP_TYPE"='INV/CM' AND "LV"."VIEW_APPLICATION_ID"=222 AND "LV"."SECURITY_GROUP_ID"=0 AND
74 "LV"."LANGUAGE"=USERENV('LANG'))
75 filter("LV"."LANGUAGE"=USERENV('LANG') AND "LV"."SECURITY_GROUP_ID"=0)
76 19 - access("CT"."CUST_TRX_TYPE_ID"="CTT"."CUST_TRX_TYPE_ID" AND
77 NVL("CT"."ORG_ID",(-99))=NVL("CTT"."ORG_ID",(-99)))
78 21 - access("CT"."BATCH_SOURCE_ID"="BS"."BATCH_SOURCE_ID" AND NVL("CT"."ORG_ID",(-99))=NVL("BS"."ORG_ID",(-99)))
79 23 - access("CT"."CUSTOMER_TRX_ID"="CTLGD"."CUSTOMER_TRX_ID" AND
80 NVL("CT"."ORG_ID",(-99))=NVL("CTLGD"."ORG_ID",(-99)))
81 filter(NVL("CT"."ORG_ID",(-99))=DECODE(TO_CHAR("S"."SALESREP_ID"),NULL,NVL("CT"."ORG_ID",(-99)),NVL("S"."ORG_I
82 D",(-99))))
83 24 - access("LV"."LOOKUP_TYPE"=DECODE(TO_CHAR("CTLGD"."COLLECTED_TAX_CCID"),NULL,'AUTOGL_TYPE','DISTRIBUTION_SOURCE
84 _TYPE') AND "LV"."LOOKUP_CODE"=DECODE(TO_CHAR("CTLGD"."COLLECTED_TAX_CCID"),NULL,NVL("CTLGD"."ACCOUNT_CLASS",'REV'),'
85 DEFERRED_TAX'))
86 25 - filter("LV"."VIEW_APPLICATION_ID"=222 AND "LV"."LANGUAGE"=USERENV('LANG') AND "LV"."SECURITY_GROUP_ID"=0)
87 26 - access("S"."SALESREP_ID"(+)="CTLGD"."CUST_TRX_LINE_SALESREP_ID")
88 28 - access("LV"."LOOKUP_CODE"=DECODE("CTLGD"."POSTING_CONTROL_ID",(-3),'N','Y'))
89 32 - access("LV"."LOOKUP_TYPE"='VIEW_ACCOUNTING' AND "LV"."VIEW_APPLICATION_ID"=222 AND "LV"."LOOKUP_CODE"='LINE'
90 AND "LV"."SECURITY_GROUP_ID"=0 AND "LV"."LANGUAGE"=USERENV('LANG'))
91 34 - access("LV"."LOOKUP_TYPE"='VIEW_ACCOUNTING' AND "LV"."VIEW_APPLICATION_ID"=222 AND
92 "LV"."LOOKUP_CODE"='DETAIL_LINE' AND "LV"."SECURITY_GROUP_ID"=0 AND "LV"."LANGUAGE"=USERENV('LANG'))
93 36 - access("LV"."LOOKUP_TYPE"='YES/NO' AND "LV"."VIEW_APPLICATION_ID"=222 AND "LV"."SECURITY_GROUP_ID"=0 AND
94 "LV"."LANGUAGE"=USERENV('LANG'))
95 filter("LV"."LANGUAGE"=USERENV('LANG') AND "LV"."SECURITY_GROUP_ID"=0)
96 37 - filter("CTLGD"."ACCOUNT_SET_FLAG"='N')
97 40 - access("CT"."EXCHANGE_RATE_TYPE"="GLCT"."CONVERSION_TYPE"(+))
98 42 - filter(NVL("CTLGD"."ORG_ID",(-99))=NVL("CTL"."ORG_ID"(+),(-99)))
99 43 - access("CTLGD"."CUSTOMER_TRX_LINE_ID"="CTL"."CUSTOMER_TRX_LINE_ID"(+))
100 45 - access("RR"."RULE_ID"(+)="CTL"."ACCOUNTING_RULE_ID")
101 46 - filter(NVL("CTL"."ORG_ID",(-99))=NVL("CTL2"."ORG_ID"(+),(-99)))
102 47 - access("CTL"."LINK_TO_CUST_TRX_LINE_ID"="CTL2"."CUSTOMER_TRX_LINE_ID"(+))
103 49 - access("CT"."BILL_TO_CUSTOMER_ID"="CUST_ACCT"."CUST_ACCOUNT_ID")
104 51 - access("CUST_ACCT"."PARTY_ID"="PARTY"."PARTY_ID")
105 53 - access("CT"."BILL_TO_SITE_USE_ID"="SU"."SITE_USE_ID")
106
107 Note
108 -----
109 - 'PLAN_TABLE' is old version

复制代码

回复只看该作者道具举报

张沛

7^#

发表于 2013-1-21 11:19:55

本帖最后由张沛于 2013-1-21 11:25 编辑

一条回复贴不全，分的两次，例如9,21,42,46中org_id我觉得就比较适合建位图索引，不知道对不对？

SQL> set lines 200
SQL> select vat_tax_id,org_id,uom_code,accounting_rule_id,line_type
2 from ra_customer_trx_lines_all
3 group by vat_tax_id,org_id,uom_code,accounting_rule_id,line_type;
VAT_TAX_ID ORG_ID UOM_CO ACCOUNTING_RULE_ID LINE_TYPE
---------- ---------- ------ ------------------ ------------------------------
--------
136 PCS LINE
10021 136 PCS LINE
10023 136 TAX
10003 103 TAX
10007 103 PCS LINE
136 LINE
10023 136 PCS LINE
10063 136 PCS LINE
10023 136 LINE
10005 103 TAX
10003 103 PCS LINE
VAT_TAX_ID ORG_ID UOM_CO ACCOUNTING_RULE_ID LINE_TYPE
---------- ---------- ------ ------------------ ------------------------------
--------
10009 104 PCS LINE
10021 136 LINE
10063 136 TAX
10101 103 LINE
103 PCS LINE
104 LINE
10005 103 PCS LINE
10101 103 TAX
10003 103 LINE
10007 103 TAX
10009 104 TAX
VAT_TAX_ID ORG_ID UOM_CO ACCOUNTING_RULE_ID LINE_TYPE
---------- ---------- ------ ------------------ ------------------------------
--------
10063 136 LINE
103 LINE
10021 136 TAX
10007 103 LINE
已选择26行。

复制代码

回复只看该作者道具举报

返回列表

		自动登录	找回密码
密码			注册