ããä¸ãå建ååºè¡¨
ãã1ãèå´ååº
ããæ ¹æ®æ°æ®è¡¨å段å¼çèå´è¿è¡ååº
ãã举个ä¾åï¼æ ¹æ®å¦ççä¸ååæ°å¯¹åæ°è¡¨è¿è¡ååºï¼å建ä¸ä¸ªååºè¡¨å¦ä¸ï¼ããcreate table range_fraction(
ããid number(8),
ããname varchar2(20),
ããfraction number(3),
ããgrade number(2)
ãã)
ããpartition by range(fraction)
ãã(
ããpartition fraction_60 values less than(60), --ä¸åæ ¼partition fraction_80 values less than(85), --åæ ¼partition fraction_100 values less than(maxvalue) --ä¼ç§)å建å®ååºè¡¨åå表ä¸æ·»å ä¸äºæ°æ®ï¼ããdeclarename varchar2(10);
ããfraction number(5);
ããgrade number(5);
ããi number(8):=1;
ããbegin
ããfor i in 1..100000 LOOP
ããSELECT CHR (ROUND (DBMS_RANDOM.VALUE (97, 122))) INTO NAME FROM DUAL;SELECT ABS(MOD(DBMS_RANDOM.RANDOM,101)) into fraction FROM DUAL;SELECT ABS(MOD(DBMS_RANDOM.RANDOM,10))+1 into grade FROM DUAL;insert into range_fraction values(seq_range_fraction.nextval ,name,fraction,grade);END LOOP;end;
ããæ¥è¯¢ååºè¡¨ï¼ãã--åå«æ¥è¯¢ææçï¼ä¸åæ ¼çï¼ä¸ççï¼ä¼ç§çæ绩select * from range_fraction;select * from range_fraction partition(fraction_60) ;select * from range_fraction partition(fraction_80) ;select * from range_fraction partition(fraction_100) ;å½æ们çæ¥è¯¢è¯å¥ä¸æå®ååºçæ¶åï¼å¦æååºå段åºç°å¨whereæ¡ä»¶ä¹åï¼Oracleä¼èªå¨æ ¹æ®å段å¼çèå´æ«æååºçååºï¼
ããselect * from range_fraction where fraction<30; è¿å¥SQLæ§è¡çæ¶ååªä¼æ«æä¸åæ ¼çååºselect * from range_fraction where fraction<80; è¿å¥SQLæ§è¡çæ¶åä¼æ«æä¸åæ ¼åä¸ç两个ååº2ãæ£åååºå¨èå´ååºä¸ï¼ååºå段çè¿ç»å¼é常åºç°å¨ä¸ä¸ªååºå
ï¼èå¨æ£åååºä¸ï¼è¿ç»çå段å¼ä¸ä¸å®åå¨å¨ç¸åçååºä¸ãæ£åååºæè®°å½åå¸å¨æ¯èå´ååºæ´å¤çååºä¸ï¼è¿åå°äºI/Oäºç¨çå¯è½æ§ã
ãã为äºå建ä¸ä¸ªæ£åååºï¼åºè¯¥ç¨partition by hashè¯å¥ä»£æ¿partition by rangeåå¥ï¼å¦ä¸æ示ï¼
ãã第ä¸ç§ä¸ºå个ååºæå®ä¸åç表空é´ï¼è¡¨ç©ºé´æ°éä¸ç¨çäºååºæ°éï¼å½è¡¨ç©ºé´æ°é大äºååºæ°éçæ¶åä¼å¾ªç¯åå
¥å个表空é´ï¼
ããcreate table range_fraction1
ãã(
ããid number(8),
ããname varchar2(20),
ããfraction number(3),
ããgrade number(2)
ãã)
ããpartition by hash(fraction)
ããpartitions 8
ããstore in (users,tbs_haicheng)
ãã第äºç§ä¸ºæ¯ä¸ªååºæå®ä¸ä¸ªååºå称并为å
¶æå®è¡¨ç©ºé´ï¼ããcreate table range_fraction1(
ããid number(8),
ããname varchar2(20),
ããfraction number(3),
ããgrade number(1)
ãã)
ããpartition by hash(fraction)
ãã(
ããpartition p1 tablespace tbs_haicheng ,
ããpartition p2 tablespace users
ãã);
ãã3ãå表ååº
ããè¿å¯ä»¥ä½¿ç¨å表ååºä»£æ¿èå´ååºåæ£åååºãå¨å表ååºä¸ï¼åè¯Oracleææå¯è½çå¼ï¼å¹¶æå®åºå½æå
¥ç¸åºè¡çååºã
ããæ们å°1ã2ã3ã4ç级çæ°æ®æ¾å¨ä¸ä¸ªååºï¼å°6ã7ã8çæ°æ®æ¾å¨ä¸ä¸ªååºï¼å°å
¶ä»çåæ¾å¨ä¸ä¸ªååºï¼å»ºè¡¨å¦ä¸ï¼
ããcreate table range_fraction1
ãã(
ããid number(8),
ããname varchar2(20),
ããfraction number(3),
ããgrade number(2)
ãã)
ããpartition by list(grade)
ãã(
ããpartition p1 values(1,2,3,4) tablespace tbs_haicheng ,partition p2 values(5,6,7,8) tablespace users,?
ãã1
ããpartition p3 values(default)
ãã4ãç»åååº(å建åååº)
ããå³ååºçååºãä¾å¦å¯ä»¥å
è¿è¡èå´ååºï¼å对å个èå´ååºå建å表ååºã
ãã对äºé常大ç表æ¥è¯´ï¼è¿ç§ç»åååºæ¯ä¸ç§ææ°æ®åæå¯ç®¡çåå¯è°æ´çç»æé¨åçæææ¹æ³ã
ãã举个ä¾åï¼æç
§åæ°èå´ååºååå°IDæ£åååºï¼
ããcreate table range_fraction1
ãã(
ããid number(8),
ããname varchar2(20),
ããfraction number(3),
ããgrade number(1)
ãã)
ããpartition by range(fraction)
ããsubpartition by hash(id)
ããsubpartitions 4
ãã(
ããpartition fraction_60 values less than(60), --ä¸åæ ¼partition fraction_80 values less than(85), --åæ ¼partition fraction_100 values less than(maxvalue) --ä¼ç§)äºãç´¢å¼ååº
ããå¨ååºè¡¨ä¸å¯ä»¥å»ºç«ä¸ç§ç±»åçç´¢å¼ï¼1åæ®é表ä¸æ ·çå
¨å±ç´¢å¼ï¼2.å
¨å±ååºç´¢å¼ï¼3.æ¬å°ååºç´¢å¼1.建ç«æ®éçç´¢å¼ããcreate index index_fraction on range_fraction(fraction);2.建ç«æ¬å°ååºç´¢å¼ï¼å°±æ¯ä¸ä¸ªç´¢å¼ååºåªè½å¯¹åºä¸ä¸ªè¡¨ååºï¼?
ããcreate index local_index_fraction on range_fraction(fraction) local;3.建ç«å
¨å±ååºç´¢å¼ï¼å±äºæ£åç´¢å¼ååºï¼å°±æ¯ä¸ä¸ªç´¢å¼ååºå¯è½æåå¤ä¸ªè¡¨ååºï¼?
ããcreate index global_index_fraction on range_fraction(fraction)GLOBAL partition by range(fraction)(
ããpart_01 values less than(1000),
ããpart_02 values less than(MAXVALUE)
ãã);
ããä¸ã管çååºè¡¨
ãã1ãå¢å ååº
ãã对äºèå´ååºæ¥è¯´ï¼æ·»å ä¸ä¸ªååºï¼å¿
须该ååºåå®ççéé«äºåæ¥çæ大çéï¼ä¹å°±æ¯è¯´åªè½å¾ä¸å ï¼ä¸è½å¾ä¸å ãé£ä¹å¯¹äºç¨maxvalueå
³é®åå建çèå´ååºå°±ä¸è½å¢å ååºäºä¸¾ä¾ï¼
ããcreate table range_fraction
ãã(
ããid number(8),
ããname varchar2(20),
ããfraction number(3),
ããgrade number(2)
ãã)
ããpartition by range(fraction)
ãã(
ããpartition fraction_60 values less than(40), --ä¸åæ ¼partition fraction_80 values less than(60), --åæ ¼partition fraction_100 values less than(80) --ä¼ç§)对äºè¯¥ååºæ们å¢å ä¸ä¸ªååºï¼
ããALTER TABLE range_fraction ADD PARTITION fraction_100 VALUES LESS THAN (100);为å表ååºæ·»å ä¸ä¸ªååºï¼
ããcreate table range_fraction
ãã(
ããid number(8),
ããname varchar2(20),
ããfraction number(3),
ããgrade number(2)
ãã)
ããpartition by list(grade)
ãã(
ããpartition p1 values(1,2,3) tablespace tbs_haicheng ,partition p2 values(4,5,6) tablespace users);ALTER TABLE range_fraction ADD partition p3 VALUES (7,8);æ们å为p3ååºæ°å¢ä¸¤ä¸ªè¡¨ååºå¼ï¼
ããALTER TABLE range_fraction MODIFY PARTITION p3 ADD VALUES(9,10);ç¶ååå°p3ååºç表ååºå¼ä¸ç10å æï¼
ããALTER TABLE range_fraction MODIFY PARTITION p3 DROP VALUES(10);为åå¸ååºæ·»å ä¸ä¸ªåååºï¼
ããALTER TABLE TABLENAME ADD PARTITION PARTNAME;æ·»å ä¸ä¸ªåååºçæ ¼å¼ï¼
ããALTER TABLE TABLENAME MODIFY PARTITION PARTNAME ADD SUBPARTITION SUBPARTNAME;2ãå é¤ååºå é¤ååºæ¯è¾ç®åï¼æ ¼å¼å¦ä¸ï¼
ããALTER TABLE ... DROP PARTITION part_name;3ãååºå并å并ç¶ååºæ ¼å¼ï¼ããALTER TABLE TABLENAME MERGE PARTITIONS p1-1, p1-2 INTO PARTITION p1 UPDATE INDEXES;å¦æçç¥äºUPDATE INDEXES çè¯éè¦ä¸ºåå½±åçååºé建索å¼å并åååºçæ ¼å¼ï¼
ããALTER TABLE TABLENAME
ããMERGE SUBPARTITIONS part_1_sub_2, part_1_sub_3 INTO SUBPARTITION part_1_sub_2 UPDATE INDEXES;4ã转æ¢ååºå¯ä»¥å°ååºè¡¨è½¬æ¢æéååºè¡¨ï¼æè
å ç§ä¸åååºè¡¨ä¹é´ç转æ¢ãå¦ä¸ï¼
ããCREATE TABLE hash_part02 AS SELECT * FROMhash_example WHERE 1=2;ALTER TABLE hash_example EXCHANGE PARTITIONpart02 WITH TABLE hash_part02;è¿æ¶ï¼ååºè¡¨hash_exampleä¸çpart02ååºçèµæå°è¢«è½¬ç§»å°hash_part02è¿ä¸ªéååºè¡¨ä¸ã
温馨提示:答案为网友推荐,仅供参考