使用bitand/views来处理有很多flag的表.

我们的系统每天都有一张大表, 主要是用来做报表处理的. 这个表的字段数目相当多, 目前已经达到50多个字段了, 其中有40个是标志字段. 受oracle系统表设计的影响, 我想到一下的方法来出里对应的表.

1. Oracle的相关系统表.
打开sql.bsq的原始文件, 我们随处可见Oracle的设计中, 尽量将相应的具有类西属性的字段通过数字的位数来进行处理, 而后又通过bitand与视图联合处理的方式显示给最终用户.
我下面要举的例子来自oracle的动态视图,v$sql_shared_cursor.
SQL> set long 5000
SQL> select view_definition
2 from v$fixed_view_definition
3 where view_name = 'V$SQL_SHARED_CURSOR'
4 /

VIEW_DEFINITION
------------------------------------------------------------------------------------------------------------------------
select ADDRESS , KGLHDPAR, UNBOUND_CURSOR, SQL_TYPE_MISMATCH, OPTIMIZER_MISMATCH, OUTLINE_MISMATCH, STATS_ROW_MISMA
TCH, LITERAL_MISMATCH, SEC_DEPTH_MISMATCH, EXPLAIN_PLAN_CURSOR, BUFFERED_DML_MISMATCH, PDML_ENV_MISMATCH,
INST_DRTLD_MISMATCH, SLAVE_QC_MISMATCH, TYPECHECK_MISMATCH, AUTH_CHECK_MISMATCH, BIND_MISMATCH, DESCRI
BE_MISMATCH, LANGUAGE_MISMATCH, TRANSLATION_MISMATCH, ROW_LEVEL_SEC_MISMATCH, INSUFF_PRIVS, INSUFF_P
RIVS_REM, REMOTE_TRANS_MISMATCH, LOGMINER_SESSION_MISMATCH, INCOMP_LTRL_MISMATCH, OVERLAP_TIME_MISMATCH, SQL_RE
DIRECT_MISMATCH, MV_QUERY_GEN_MISMATCH, USER_BIND_PEEK_MISMATCH, TYPCHK_DEP_MISMATCH, NO_TRIGGER_MISMAT
CH, FLASHBACK_CURSOR from GV$SQL_SHARED_CURSOR where inst_id = USERENV('Instance')


SQL> c/V$/GV$/
3* where view_name = 'GV$SQL_SHARED_CURSOR'
SQL> /

VIEW_DEFINITION
------------------------------------------------------------------------------------------------------------------------
select inst_id, kglhdadr, kglhdpar, decode(bitand(bitvector, POWER(2,0)), POWER(2, 0), 'Y','N'),decode(bitand(bitvector
, POWER(2,1)), POWER(2, 1), 'Y','N'),decode(bitand(bitvector, POWER(2,2)), POWER(2, 2), 'Y','N'),decode(bitand(bitvect
or, POWER(2,3)), POWER(2, 3), 'Y','N'),decode(bitand(bitvector, POWER(2,4)), POWER(2, 4), 'Y','N'),decode(bitand(bitve
ctor, POWER(2,5)), POWER(2, 5), 'Y','N'),decode(bitand(bitvector, POWER(2,6)), POWER(2, 6), 'Y','N'),decode(bitand(bit
vector, POWER(2,7)), POWER(2, 7), 'Y','N'),decode(bitand(bitvector, POWER(2,8)), POWER(2, 8), 'Y','N'),decode(bitand(b
itvector, POWER(2,9)), POWER(2, 9), 'Y','N'),decode(bitand(bitvector, POWER(2,10)), POWER(2, 10), 'Y','N'),decode(bitan
d(bitvector, POWER(2,11)), POWER(2, 11), 'Y','N'),decode(bitand(bitvector, POWER(2,12)), POWER(2, 12), 'Y','N'),decode(b
itand(bitvector, POWER(2,13)), POWER(2, 13), 'Y','N'),decode(bitand(bitvector, POWER(2,14)), POWER(2, 14), 'Y','N'),deco
de(bitand(bitvector, POWER(2,15)), POWER(2, 15), 'Y','N'),decode(bitand(bitvector, POWER(2,16)), POWER(2, 16), 'Y','N'),
decode(bitand(bitvector, POWER(2,17)), POWER(2, 17), 'Y','N'),decode(bitand(bitvector, POWER(2,18)), POWER(2, 18), 'Y','
N'),decode(bitand(bitvector, POWER(2,19)), POWER(2, 19), 'Y','N'),decode(bitand(bitvector, POWER(2,20)), POWER(2, 20), '
Y','N'),decode(bitand(bitvector, POWER(2,21)), POWER(2, 21), 'Y','N'),decode(bitand(bitvector, POWER(2,22)), POWER(2, 22
), 'Y','N'),decode(bitand(bitvector, POWER(2,23)), POWER(2, 23), 'Y','N'),decode(bitand(bitvector, POWER(2,24)), POWER(2
, 24), 'Y','N'),decode(bitand(bitvector, POWER(2,25)), POWER(2, 25), 'Y','N'),decode(bitand(bitvector, POWER(2,26)), POW
ER(2, 26), 'Y','N'),decode(bitand(bitvector, POWER(2,27)), POWER(2, 27), 'Y','N'),decode(bitand(bitvector, POWER(2,28)),
POWER(2, 28), 'Y','N'),decode(bitand(bitvector, POWER(2,29)), POWER(2, 29), 'Y','N'),decode(bitand(bitvector, POWER(2,3
0)), POWER(2, 30), 'Y','N') from x$kkscs

VIEW_DEFINITION
------------------------------------------------------------------------------------------------------------------------


SQL>

V$SQL_SHARED_CURSOR

This view explains why a particular child cursor is not shared with existing child cursors. Each column identifies a specific reason why the cursor cannot be shared.

KGLHDPAR	RAW(4 | 8)	Address of the parent cursor
UNBOUND_CURSOR	VARCHAR2(1)	(Y|N) The existing child cursor was not fully built (in other words, it was not optimized)
SQL_TYPE_MISMATCH	VARCHAR2(1)	(Y|N) The SQL type does not match the existing child cursor
OPTIMIZER_MISMATCH	VARCHAR2(1)	(Y|N) The optimizer environment does not match the existing child cursor
OUTLINE_MISMATCH	VARCHAR2(1)	(Y|N) The outlines do not match the existing child cursor
STATS_ROW_MISMATCH	VARCHAR2(1)	(Y|N) The existing statistics do not match the existing child cursor
LITERAL_MISMATCH	VARCHAR2(1)	(Y|N) Non-data literal values do not match the existing child cursor
SEC_DEPTH_MISMATCH	VARCHAR2(1)	(Y|N) Security level does not match the existing child cursor
EXPLAIN_PLAN_CURSOR	VARCHAR2(1)	(Y|N) The child cursor is an explain plan cursor and should not be shared
BUFFERED_DML_MISMATCH	VARCHAR2(1)	(Y|N) Buffered DML does not match the existing child cursor
PDML_ENV_MISMATCH	VARCHAR2(1)	(Y|N) PDML environment does not match the existing child cursor
INST_DRTLD_MISMATCH	VARCHAR2(1)	(Y|N) Insert direct load does not match the existing child cursor
SLAVE_QC_MISMATCH	VARCHAR2(1)	(Y|N) The existing child cursor is a slave cursor and the new one was issued by the coordinator (or, the existing child cursor was issued by the coordinator and the new one is a slave cursor)
TYPECHECK_MISMATCH	VARCHAR2(1)	(Y|N) The existing child cursor is not fully optimized
AUTH_CHECK_MISMATCH	VARCHAR2(1)	(Y|N) Authorization/translation check failed for the existing child cursor
BIND_MISMATCH	VARCHAR2(1)	(Y|N) The bind metadata does not match the existing child cursor
DESCRIBE_MISMATCH	VARCHAR2(1)	(Y|N) The typecheck heap is not present during the describe for the child cursor
LANGUAGE_MISMATCH	VARCHAR2(1)	(Y|N) The language handle does not match the existing child cursor
TRANSLATION_MISMATCH	VARCHAR2(1)	(Y|N) The base objects of the existing child cursor do not match
ROW_LEVEL_SEC_MISMATCH	VARCHAR2(1)	(Y|N) The row level security policies do not match
INSUFF_PRIVS	VARCHAR2(1)	(Y|N) Insufficient privileges on objects referenced by the existing child cursor
INSUFF_PRIVS_REM	VARCHAR2(1)	(Y|N) Insufficient privileges on remote objects referenced by the existing child cursor
REMOTE_TRANS_MISMATCH	VARCHAR2(1)	(Y|N) The remote base objects of the existing child cursor do not match
LOGMINER_SESSION_MISMATCH	VARCHAR2(1)	(Y|N)
INCOMP_LTRL_MISMATCH	VARCHAR2(1)	(Y|N)

处理之后, 再通过给不同的flag字段进行说明, 这样就将后台的逻辑设计与前台的设计分别开来, 这样处理效果是可以大大降低对应数据所占用的磁盘空间,相应的是数据库处理的效率得到大幅度的提高. 这一点我将在后面的例子中进一步说明.

2. 我自己的实例.

我就在我的基础表中取出一部分来进行示范了:)
SQL> create table tjw_test_0328 pctfree 0 nologging as
2 select tele_num,high_fee_flag,gprs_flag,gprs_flag_3,gprs_flag_5,
3 gprs_flag_2,gprs_flag_4,gprs_flag_6,gprs_flag_7,save_flag,
4 old_gprs_flag,prepay_phone_flag,reward_flag,reward_flag_1,
5 g_prepay_flag,g_prepay_flag_1
6 from anal.tjw_group_0810_final
7 /

Table created.

SQL> set echo on
SQL> @bbb.sql
SQL> create table tjw_test_0328_1 pctfree 0 nologging as
2 select tele_num,
3 decode(high_fee_flag,1,power(2,1),0) + /*high_fee_flag*/
4 decode(gprs_flag,1,power(2,2),0) + /*gprs_flag*/
5 decode(gprs_flag_3,1,power(2,3),0) + /*gprs_flag_3*/
6 decode(gprs_flag_5,1,power(2,4),0) + /*gprs_flag_5*/
7 decode(gprs_flag_2,1,power(2,5),0) + /*gprs_flag_2*/
8 decode(gprs_flag_4,1,power(2,6),0) + /*gprs_flag_4*/
9 decode(gprs_flag,6,1,power(2,7),0) + /*gprs_flag_6*/
10 decode(gprs_flag_7,1,power(2,8),0) + /*save_flag*/
11 decode(save_flag,1,power(2,9),0) + /*save_flag*/
12 decode(old_gprs_flag,1,power(2,10),0) + /*old_gprs_flag*/
13 decode(prepay_phone_flag,1,power(2,11),0) + /*prepay_phone_flag*/
14 decode(reward_flag,1,power(2,12),0) + /*reward_flag*/
15 decode(reward_flag_1,power(2,13),0) + /*reward_flag_1*/
16 decode(g_prepay_flag,1,power(2,14),0) + /*g_prepay_flag*/
17 decode(g_prepay_flag_1,1,power(2,15),0) /*g_prepay_flag_1*/
18 bit_flags
19 from tjw_test_0328
20 /

Table created.

SQL> col segment_name format a30
SQL> select segment_name,blocks,bytes
2 from user_segments
3 where segment_name in ('TJW_TEST_0328','TJW_TEST_0328_1')
4 /

SEGMENT_NAME BLOCKS BYTES
------------------------------ ---------- ----------
TJW_TEST_0328 14208 116391936
TJW_TEST_0328_1 4992 40894464

SQL> select 40894464 / 116391936 from dual
2 /

40894464/116391936
------------------
.351351351

SQL> @aaa.sql
SQL> create or replace view v_tjw_test_0328 as
2 select tele_num,
3 decode(bit_flags,power(2,1),power(2,1),1,0) high_fee_flag, /*high_fee_flag*/
4 decode(bit_flags,power(2,2),power(2,2),1,0) gprs_flag, /*gprs_flag*/
5 decode(bit_flags,power(2,3),power(2,3),1,0) gprs_flag_3, /*gprs_flag_3*/
6 decode(bit_flags,power(2,4),power(2,4),1,0) gprs_flag_5, /*gprs_flag_5*/
7 decode(bit_flags,power(2,5),power(2,5),1,0) gprs_flag_2, /*gprs_flag_2*/
8 decode(bit_flags,power(2,6),power(2,6),1,0) gprs_flag_4, /*gprs_flag_4*/
9 decode(bit_flags,power(2,7),power(2,7),1,0) gprs_flag_6, /*gprs_flag_6*/
10 decode(bit_flags,power(2,8),power(2,8),1,0) gprs_flag_7, /*gprs_flag_7*/
11 decode(bit_flags,power(2,9),power(2,9),1,0) save_flag, /*save_flag*/
12 decode(bit_flags,power(2,10),power(2,10),1,0) old_gprs_flag, /*old_gprs_flag*/
13 decode(bit_flags,power(2,11),power(2,11),1,0) prepay_phone_flag, /*prepay_phone_flag*/
14 decode(bit_flags,power(2,12),power(2,12),1,0) reward_flag, /*reward_flag*/
15 decode(bit_flags,power(2,13),power(2,13),1,0) reward_flag_1, /*reward_flag_1*/
16 decode(bit_flags,power(2,14),power(2,14),1,0) g_prepay_flag, /*g_prepay_flag*/
17 decode(bit_flags,power(2,15),power(2,15),1,0) g_prepay_flag_1 /*g_prepay_flag_1*/
18 from tjw_test_0328_1
19 /

View created.

SQL> desc tjw_test_0328
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
TELE_NUM VARCHAR2(15)
HIGH_FEE_FLAG NUMBER
GPRS_FLAG NUMBER
GPRS_FLAG_3 NUMBER
GPRS_FLAG_5 NUMBER
GPRS_FLAG_2 NUMBER
GPRS_FLAG_4 NUMBER
GPRS_FLAG_6 NUMBER
GPRS_FLAG_7 NUMBER
SAVE_FLAG NUMBER
OLD_GPRS_FLAG NUMBER
PREPAY_PHONE_FLAG NUMBER
REWARD_FLAG NUMBER
REWARD_FLAG_1 NUMBER
G_PREPAY_FLAG NUMBER
G_PREPAY_FLAG_1 NUMBER

SQL> desc v_tjw_test_0328
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
TELE_NUM VARCHAR2(15)
HIGH_FEE_FLAG NUMBER
GPRS_FLAG NUMBER
GPRS_FLAG_3 NUMBER
GPRS_FLAG_5 NUMBER
GPRS_FLAG_2 NUMBER
GPRS_FLAG_4 NUMBER
GPRS_FLAG_6 NUMBER
GPRS_FLAG_7 NUMBER
SAVE_FLAG NUMBER
OLD_GPRS_FLAG NUMBER
PREPAY_PHONE_FLAG NUMBER
REWARD_FLAG NUMBER
REWARD_FLAG_1 NUMBER
G_PREPAY_FLAG NUMBER
G_PREPAY_FLAG_1 NUMBER

SQL> select count(*) from tjw_test_0328;

Elapsed: 00:00:03.93

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
13514 consistent gets
13384 physical reads
0 redo size
306 bytes sent via SQL*Net to client
495 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select count(*) from v_tjw_test_0328;

Elapsed: 00:00:01.87

Statistics
----------------------------------------------------------
17 recursive calls
0 db block gets
4882 consistent gets
4863 physical reads
0 redo size
306 bytes sent via SQL*Net to client
495 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
在此, 我们可以看到对应的数据所占用的空间减少了有65%, 相应的数据处理速度也提高了一倍多, 同时, 也节约了大量的db cache的空间.:)
我们再在这个新的表的基础上创建对应的视图, 使得对应的视图查询与使用原来的表具有相同的结构.

同样的如果我们能够进一步的挖掘Oracle数据字典的设计, 我们可以发现更多的宝藏:-)
[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/94317/viewspace-794211/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/94317/viewspace-794211/