ÉîÈëѧϰOracle·ÖÇø±í¼°·ÖÇøË÷Òý
http://www.csip.com.cn/88/n-52488.html
¹ØÓÚ·ÖÇø±íºÍ·ÖÇøË÷Òý(About Partitioned Tables and Indexes)¶ÔÓÚ10gR2¶øÑÔ£¬»ù±¾ÉÏ¿ÉÒԷֳɼ¸Àࣺ
Range(·¶Î§)·ÖÇø
Hash(¹þÏ£)·ÖÇø
List(Áбí)·ÖÇø
ÒÔ¼°×éºÏ·ÖÇø£ºRange-Hash,Range-List¡£
¶ÔÓÚ±í¶øÑÔ(³£¹æÒâÒåÉϵĶÑ×éÖ¯±í)£¬ÉÏÊö·ÖÇøÐÎʽ¶¼¿ÉÒÔÓ¦ÓÃ(ÉõÖÁ¿ÉÒÔ¶Ôij¸ö·ÖÇøÖ¸¶¨compressÊôÐÔ)£¬Ö»²»¹ý·ÖÇøÒÀÀµÁв»ÄÜÊÇlob,longÖ®ÀàÊý¾ÝÀàÐÍ£¬Ã¿¸ö±íµÄ·ÖÇø»ò×Ó·ÖÇøÊýµÄ×ÜÊý²»Äܳ¬¹ý1023¸ö¡£
¶ÔÓÚË÷Òý×éÖ¯±í£¬Ö»Äܹ»Ö§³ÖÆÕͨ·ÖÇø·½Ê½£¬²»Ö§³Ö×éºÏ·ÖÇø£¬³£¹æ±íµÄÏÞÖƶÔÓÚË÷Òý×éÖ¯±íͬÑùÓÐЧ£¬³ý´ËÖ®ÍâÄØ£¬»¹ÓÐһЩÆäʵµÄÏÞÖÆ£¬±ÈÈçÒªÇóË÷Òý×éÖ¯±íµÄ·ÖÇøÒÀÀµÁбØÐëÊÇÖ÷¼ü²Å¿ÉÒԵȡ£
×¢£º±¾ÆªËùÓÐʾÀý½öÕë¶Ô³£¹æ±í£¬¼´¶Ñ×éÖ¯±í£¡
¶ÔÓÚË÷Òý£¬ÐèÒªÇø·Ö´´½¨µÄÊÇÈ«¾ÖË÷Òý£¬»ò±¾µØË÷Òý£º
l È«¾ÖË÷Òý(global index)£º¼´¿ÉÒÔ·ÖÇø£¬Ò²¿ÉÒÔ²»·ÖÇø¡£¼´¿ÉÒÔ½¨range·ÖÇø£¬Ò²¿ÉÒÔ½¨hash·ÖÇø£¬¼´¿É½¨ÓÚ·ÖÇø±í£¬ÓÖ¿É´´½¨ÓÚ·Ç·ÖÇø±íÉÏ£¬¾ÍÊÇ˵£¬È«¾ÖË÷ÒýÊÇÍêÈ«¶ÀÁ¢µÄ£¬Òò´ËËüÒ²ÐèÒªÎÒÃǸü¶àµÄά»¤²Ù×÷¡£
l ±¾µØË÷Òý(local index)£ºÆä·ÖÇøÐÎʽÓë±íµÄ·ÖÇøÍêÈ«Ïàͬ£¬ÒÀÀµÁÐÏàͬ£¬´æ´¢ÊôÐÔÒ²Ïàͬ¡£¶ÔÓÚ±¾µØË÷Òý£¬ÆäË÷Òý·ÖÇøµÄά»¤×Ô¶¯½øÐУ¬¾ÍÊÇ˵Äãadd/drop/split/truncate±íµÄ·ÖÇøʱ£¬±¾µØË÷Òý»á×Ô¶¯Î¬»¤ÆäË÷Òý·ÖÇø¡£
Oracle½¨ÒéÈç¹ûµ¥¸ö±í³¬¹ý2G¾Í×îºÃ¶ÔÆä½øÐзÖÇø£¬¶ÔÓÚ´ó±í´´½¨·ÖÇøµÄºÃ´¦ÊÇÏÔ¶øÒ×¼ûµÄ£¬ÕâÀï²»¶àÂÛÊöwhy£¬¶ø½«Öصã·ÅÔÚwhenÒÔ¼°how¡£
WHEN
Ò»¡¢WhenʹÓÃRange·ÖÇø
Range·ÖÇøÄØÊÇÓ¦Ó÷¶Î§±È½Ï¹ãµÄ±í·ÖÇø·½Ê½£¬ËüÊÇÒÔÁеÄÖµµÄ·¶Î§À´×öΪ·ÖÇøµÄ»®·ÖÌõ¼þ£¬½«¼Ç¼´æ·Åµ½ÁÐÖµËùÔÚµÄrange·ÖÇøÖУ¬±ÈÈç°´ÕÕʱ¼ä»®·Ö£¬2008Äê1¼¾¶ÈµÄÊý¾Ý·Åµ½a·ÖÇø£¬08Äê2¼¾¶ÈµÄÊý¾Ý·Åµ½b·ÖÇø£¬Òò´ËÔÚ´´½¨µÄʱºòÄØ£¬ÐèÒªÄãÖ¸¶¨»ùÓÚµÄÁУ¬ÒÔ¼°·ÖÇøµÄ·¶Î§Öµ£¬Èç¹ûijЩ¼Ç¼ÔÝÎÞ·¨Ô¤²â·¶Î§£¬¿ÉÒÔ´´½¨maxvalue·ÖÇø£¬ËùÓв»ÔÚÖ¸¶¨·¶Î§ÄڵļǼ¶¼»á±»´æ´¢µ½maxvalueËùÔÚ·ÖÇøÖУ¬²¢ÇÒÖ§³ÖÖ¸¶¨¶àÁÐ×öΪÒÀÀµÁУ¬ºóÃæÔÚ½²howµÄʱºò»áÏêϸ̸µ½¡£
¶þ¡¢WhenʹÓÃHash·ÖÇø
ͨ³£ÄØ£¬¶ÔÓÚÄÇЩÎÞ·¨ÓÐЧ»®·Ö·¶Î§µÄ±í£¬¿ÉÒÔʹÓÃhash·ÖÇø£¬ÕâÑù¶ÔÓÚÌá¸ßÐÔÄÜ»¹ÊÇ»áÓÐÒ»¶¨µÄ°ïÖú¡£hash·ÖÇø»á½«±íÖеÄÊý¾Ýƽ¾ù·ÖÅäµ½ÄãÖ¸¶¨µÄ¼¸¸ö·ÖÇøÖУ¬ÁÐËùÔÚ·ÖÇøÊÇÒÀ¾Ý·ÖÇøÁеÄhashÖµ×Ô¶¯·ÖÅ䣬Òò´ËÄã²¢²»ÄÜ¿ØÖÆÒ²²»ÖªµÀÄÄÌõ¼Ç¼»á±»·Åµ½Äĸö·ÖÇøÖУ¬hash·ÖÇøÒ²¿ÉÒÔÖ§³Ö¶à¸öÒÀÀµÁС£
Èý¡¢WhenʹÓÃList·ÖÇø
List·ÖÇøÓërange·ÖÇøºÍhash·ÖÇø¶¼ÓÐÀàËÆÖ®´¦£¬¸Ã·ÖÇøÓ
Ïà¹ØÎĵµ£º
1. ASCII
·µ»ØÓëÖ¸¶¨µÄ×Ö·û¶ÔÓ¦µÄÊ®½øÖÆÊý;
SQL> select ascii(A) A,ascii(a) a,ascii(0) zero,ascii( ) space from dual;
A A ZERO SPACE
--------- --------- --------- ---------
65 97 48 32
2. CHR
¸ø³öÕûÊý,·µ»Ø¶ÔÓ¦µÄ×Ö·û;
SQL> select chr(54740) zhao,chr(65) chr65 from dual;
ZH ......
http://cheneyph.javaeye.com/blog/453492
ÓÃoracle sql¶ÔÊý×Ö½øÐвÙ×÷: È¡ÉÏÈ¡Õû¡¢ÏòÏÂÈ¡Õû¡¢±£ÁôNλСÊý¡¢ËÄÉáÎåÈë¡¢Êý×Ö¸ñʽ»¯
È¡Õû£¨ÏòÏÂÈ¡Õû£©£º
select floor(5.534) from dual;
select trunc(5.534) from dual;
ÉÏÃæÁ½ÖÖÓ÷¨¶¼¿ÉÒÔ¶ÔÊý×Ö5.534ÏòÏÂÈ¡Õû£¬½á¹ûΪ5.
Èç¹ûÒªÏòÉÏÈ¡Õû £¬µÃµ½½á¹ûÎ ......
ËäÈ»±¾ÈËûÓÐÑéÖ¤¹ý,²»¹ý¾Ý˵ͨ¹ýADODB·ÃÎÊOracle¶ÔÐÔÄÜÓ°ÏìºÜС.¶øÇÒADODB±ÈOCI8Ò²Òª·½±ãºÜ¶à,±Ï¾¹Ðí¶à¶«Î÷¶¼Ôٴηâ×°¹ý.²»¹ý²»¹ÜÔõô˵ÕⶼÔÚÖмäÓÖ¼ÓÉÏÁËÒ»²ã,ÀíÂÛÉÏÀ´Ëµ³öÏÖÎÊÌâµÄ¼¸Âʺͽâ¾öÎÊÌâµÄÄѶȶ¼±ä´óÁË.¶øOracleÓÖ¾³£ÓÃÔÚ²»ÄÜËæ±ã³öÎÊÌâµÄ»·¾³,ËùÒÔÏë±ØÏëÒªÖ±½ÓʹÓÃOCIµÄÇé¿öÒ²²»ÉÙ.ÒÔϾÍÊÇÎÒµÄÀý×Ó:
<?p ......
//¼ÆËãºÁÃë²î(Á½¸ödateÀàÐ͵ÄÏà¼õΪÌìÊý²î±ð£¬È»ºóת»»ÎªºÁÃë)
select ceil(to_date('209-11-17 13:00:12','yyyy-mm-dd hh24:mi-ss')-to_date(2009-11-18 14:00:12','yyyy-mm-dd hh24:mi-ss') )from dual;
//¼ÆËãÏà²îÔ·Ý
select (EXTRACT(year from to_date('209-11-17','yyyy-mm-dd'))-EXTRACT(year from  ......
1. ÒýÑÔ
2. ´¥·¢Æ÷µÄ¸ÅÄîºÍÀàÐÍ
´¥·¢Æ÷ÊÇÒ»ÖÖÌØÊâµÄ´æ´¢¹ý³Ì£¬ËüÔÚ²åÈ룬ɾ³ý»òÐÞ¸ÄÌض¨±íÖеÄÊý¾Ýʱ´¥·¢Ö´ÐУ¬Ëü±ÈÊý¾Ý¿â±¾Éí±ê×¼µÄ¹¦ÄÜÓиü¾«Ï¸ºÍ¸ü¸´ÔÓµÄÊý¾Ý¿ØÖÆÄÜÁ¦¡£Êý¾Ý¿â´¥·¢Æ÷ÓÐÒÔϵÄ×÷Óãº
* °²È«ÐÔ¡£¿ÉÒÔ»ùÓÚÊý¾Ý¿âµÄÖµ ......