¿ÉÒÔʹÓÃÓë±íÏàͬµÄ·ÖÇø¼üºÍ·¶Î§½çÏÞÀ´¶Ô±¾µØË÷Òý·ÖÇø£¬
·ÖÇøË÷Òý£¨¶þ£©±¾µØ·ÖÇøË÷Òý
¡£Ã¿¸ö±¾µØË÷ÒýµÄ·ÖÇøÖ»°üº¬ÁËËüËù¹ØÁªµÄ±í·ÖÇøµÄ¼üºÍROWID¡£±¾µØË÷Òý¿ÉÒÔÊÇBÊ÷»òλͼË÷Òý¡£Èç¹ûÊÇBÊ÷Ë÷Òý£¬Ëü¿ÉÒÔÊÇΨһ»ò²»Î¨Ò»µÄË÷Òý¡£ÕâÖÖÀàÐ͵ÄË÷ÒýÖ§³Ö·ÖÇø¶ÀÁ¢ÐÔ£¬Õâ¾ÍÒâζ×ŶÔÓÚµ¥¶ÀµÄ·ÖÇø£¬¿ÉÒÔ½øÐÐÔö¼Ó¡¢½ØÈ¡¡¢É¾³ý¡¢·Ö¸î¡¢ÍÑ»úµÈ´¦Àí£¬¶ø²»ÓÃͬʱɾ³ý»òÖؽ¨Ë÷Òý¡£Oracle×Ô¶¯Î¬»¤ÕâЩ±¾µØË÷Òý¡£±¾µØË÷Òý·ÖÇø»¹¿ÉÒÔ±»µ¥¶ÀÖؽ¨£¬¶øÆäËû·ÖÇø²»»áÊܵ½Ó°Ïì¡£
£¨1£©ÓÐǰ׺µÄË÷Òý
ÓÐǰ׺µÄË÷Òý°üº¬ÁËÀ´×Ô·ÖÇø¼üµÄ¼ü£¬²¢°ÑËüÃÇ×÷ΪË÷ÒýµÄÇ°µ¼¡£ÀýÈ磬ÈÃÎÒÃÇÔٴλعËparticipant±í¡£ÔÚ´´½¨¸Ã±íºó£¬Ê¹ÓÃsurvey_idºÍsurvey_dateÕâÁ½¸öÁнøÐз¶Î§·ÖÇø£¬È»ºóÔÚsurvey_idÁÐÉϽ¨Á¢Ò»¸öÓÐǰ׺µÄ±¾µØË÷Òý¡£Õâ¸öË÷ÒýµÄËùÓзÖÇø¶¼±»µÈ¼Û»®·Ö£¬¾ÍÊÇ˵Ë÷ÒýµÄ·ÖÇø¶¼Ê¹ÓñíµÄÏàͬ·¶Î§½çÏÞÀ´´´½¨¡£
¼¼ÇÉ£º±¾µØµÄÓÐǰ׺Ë÷Òý¿ÉÒÔÈÃOracle¿ìËÙÌÞ³ýһЩ²»±ØÒªµÄ·ÖÇø¡£Ò²¾ÍÊÇ˵ûÓаüº¬WHEREÌõ¼þ×Ó¾äÖÐÈκÎÖµµÄ·ÖÇø½«²»»á±»·ÃÎÊ£¬ÕâÑùÒ²Ìá¸ßÁËÓï¾äµÄÐÔÄÜ¡£
£¨2£©ÎÞǰ׺µÄË÷Òý
ÎÞǰ׺µÄË÷Òý²¢Ã»ÓаѷÖÇø¼üµÄÇ°µ¼ÁÐ×÷ΪË÷ÒýµÄÇ°µ¼ÁС£ÈôʹÓÃÓÐͬÑù·ÖÇø¼ü(survey_idºÍsurvey_date)µÄÏàͬ·ÖÇø±í£¬½¨Á¢ÔÚsurvey_dateÁÐÉϵÄË÷Òý¾ÍÊÇÒ»¸ö±¾µØµÄÎÞǰ׺Ë÷Òý¡£¿ÉÒÔÔÚ±íµÄÈÎÒ»ÁÐÉÏ´´½¨±¾µØÎÞǰ׺Ë÷Òý£¬µ«Ë÷ÒýµÄÿ¸ö·ÖÇøÖ»°üº¬±íµÄÏàÓ¦·ÖÇøµÄ¼üÖµ¡£
Èç¹ûÒª°ÑÎÞǰ׺µÄË÷ÒýÉèΪΨһË÷Òý£¬Õâ¸öË÷Òý¾Í±ØÐë°üº¬·ÖÇø¼üµÄ×Ó¼¯¡£ÔÚÕâ¸öÀý×ÓÖУ¬ÎÒÃDZØÐë°Ñ°üº¬survey ºÍ(»ò)survey_id µÄÁнøÐÐ×éºÏ(Ö»Òªsurvey_id²»ÊÇË÷ÒýµÄµÚÒ»ÁУ¬Ëü¾ÍÊÇÒ»¸öÓÐǰ׺µÄË÷Òý)¡£
¼¼ÇÉ£º¶ÔÓÚÒ»¸öΨһµÄÎÞǰ׺Ë÷Òý£¬Ëü±ØÐë°üº¬·ÖÇø¼üµÄ×Ó¼¯¡£
£¨3£©×¢ÒâÊÂÏ
1)¾Ö²¿Ë÷ÒýÒ»¶¨ÊÇ·ÖÇøË÷Òý£¬·ÖÇø¼üµÈͬÓÚ±íµÄ·ÖÇø¼ü¡£
2)ǰ׺ºÍ·Çǰ׺Ë÷Òý¶¼¿ÉÒÔÖ§³ÖË÷Òý·ÖÇøÏû³ý£¬Ç°ÌáÊDzéѯµÄÌõ¼þÖаüº¬Ë÷Òý·ÖÇø¼ü¡£
3)¾Ö²¿Ë÷ÒýÖ»Ö§³Ö·ÖÇøÄÚµÄΨһÐÔ£¬ÎÞ·¨Ö§³Ö±íÉϵÄΨһÐÔ£¬Òò´ËÈç¹ûÒªÓþֲ¿Ë÷ÒýÈ¥¸ø±í×öΨһÐÔÔ¼Êø£¬ÔòÔ¼ÊøÖбØÐëÒª°üÀ¨·ÖÇø¼üÁС£
4)¾Ö²¿·ÖÇøË÷ÒýÊǶԵ¥¸ö·ÖÇøµÄ£¬Ã¿¸ö·ÖÇøË÷ÒýÖ»Ö¸ÏòÒ»¸ö±í·ÖÇø£»È«¾ÖË÷ÒýÔò²»È»£¬Ò»¸ö·ÖÇøË÷ÒýÄÜÖ¸Ïòn¸ö±í·ÖÇø£¬Í¬Ê±£¬Ò»¸ö±í·ÖÇø£¬Ò²¿ÉÄÜÖ¸Ïòn¸öË÷Òý·ÖÇø£¬¶Ô·ÖÇø±íÖеÄij¸ö·ÖÇø×ötruncate »òÕßmove£¬shrinkµÈ£¬¿ÉÄÜ»áÓ°Ïìµ½n¸öÈ«¾ÖË÷Òý·ÖÇø£¬ÕýÒòΪÕâµã£¬¾Ö²¿·ÖÇøË÷Òý¾ßÓиü¸ßµÄ¿ÉÓÃÐÔ¡£
5)λͼË÷Òý±ØÐëÊǾֲ¿·ÖÇøË÷Òý¡£
6)¾Ö²¿Ë÷Òý¶àÓ¦ÓÃÓÚÊý¾Ý²Ö¿â»·¾³ÖС£
£¨4£©Ê¾Àý£º
?
1
sql> create index ix_custaddr_local_id on custaddr(id) local;
Ë÷ÒýÒÑ´´½¨¡£
ºÍÏÂÃæSQL Ч¹ûÏàͬ£¬ÒòΪlocalË÷Òý¾ÍÊÇ·ÖÇøË÷Òý£º
?
1
2
3
4
5
6
7
8
9
10
11
12
13
create index ix_custaddr_local_id_p on custaddr(id)
local (
partition t_list556 tablespace icd_service,
partition p_other tablespace icd_service
)
SQL> create index ix_custaddr_local_areacode on custaddr(areacode) local;
Ë÷ÒýÒÑ´´½¨£¬
µçÄÔ×ÊÁÏ
¡¶·ÖÇøË÷Òý£¨¶þ£©±¾µØ·ÖÇøË÷Òý¡·(https://www.unjs.com)¡£ÑéÖ¤2¸öË÷ÒýµÄÀàÐÍ£º
?
1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> select index_name,table_name,partitioning_type,locality,ALIGNMENT from
user_part_indexes where table_name='CUSTADDR';
index_name table_name partition locali alignment
------------------------------ ---------- --------- ------ ------------
ix_custaddr_local_areacode custaddr list local prefixed
ix_custaddr_local_id custaddr list local non_prefixed
ÒòΪÎÒÃǵÄcustaddr ±íÊÇ°´areacode ½øÐзÖÇøµÄ£¬ËùÒÔË÷Òýix_custaddr_local_areacode ÊÇÓÐǰ׺µÄË÷Òý£¨prefixed£©¡£¶ø ix_custaddr_local_idÊÇ·Çǰ׺Ë÷Òý¡£