oracle Ë÷Òý
2009-07-16 10:59
Ë÷Òý´´½¨²ßÂÔ
1.µ¼ÈëÊý¾ÝºóÔÙ´´½¨Ë÷Òý
2.²»ÐèҪΪºÜСµÄ±í´´½¨Ë÷Òý
3.¶ÔÓÚÈ¡Öµ·¶Î§ºÜСµÄ×ֶΣ¨±ÈÈçÐÔ±ð×ֶΣ©Ó¦µ±½¨Á¢Î»Í¼Ë÷Òý
4.ÏÞÖƱíÖеÄË÷ÒýµÄÊýÄ¿
5.ΪË÷ÒýÉèÖúÏÊʵÄPCTFREEÖµ
6.´æ´¢Ë÷ÒýµÄ±í¿Õ¼ä×îºÃµ¥¶ÀÉ趨
´´½¨²»Î¨Ò»Ë÷Òý
create index emp_ename on employees(ename)
tablespace users
storage(......)
pctfree 0;
´´½¨Î¨Ò»Ë÷Òý
create unique index emp_email on employees(email)
tablespace users;
´´½¨Î»Í¼Ë÷Òý
create bitmap index emp_sex on employees(sex)
tablespace users;
´´½¨·´ÐòË÷Òý
create unique index order_reinx on orders(order_num,order_date)
tablespace users
reverse;
´´½¨º¯ÊýË÷Òý(º¯ÊýË÷Òý¼´¿ÉÒÔÊÇÆÕͨµÄBÊ÷Ë÷Òý£¬Ò²¿ÉÒÔÊÇλͼË÷Òý)
create index emp_substr_empno
on employees(substr(empno,1,2))
tablespace users;
ÐÞ¸ÄË÷Òý´æ´¢²ÎÊý(Óë±íÀàËÆ£¬INITIALºÍMINEXTENTS²ÎÊýÔÚË÷Òý½¨Á¢ÒÔºó²»ÄÜÔٸıä)
alter index emp_ename storage(pctincrease 50); ÓÉÓÚ¶¨ÒåÔ¼ÊøʱÓÉoracle×Ô¶¯½¨Á¢µÄË÷Òýͨ³£ÊDz»ÖªµÀÃû³ÆµÄ£¬¶ÔÕâÀàË÷ÒýµÄÐ޸ľ³£ÊÇÀûÓÃalter table ..using indexÓï¾ä½øÐеÄ,¶ø²»ÊÇalter indexÓï¾ä
ÀûÓÃÏÂÃæµÄÓï¾ä½«employees±íÖÐprimary keyÔ¼Êø¶ÔÓ¦µÄË÷ÒýµÄPCTFREE²ÎÊýÐÞ¸ÄΪ5
alter table employees enable primary key using index pctfree 5;
ÇåÀíË÷ÒýËéƬ
1.ºÏ²¢Ë÷Òý(Ö»ÊǼòµ¥µÄ½«BÊ÷Ò¶½áµãÖеĴ洢ËéƬºÏ²¢ÔÚÒ»Æ𣬲¢²»»á¸Ä±äË÷ÒýµÄÎïÀí×éÖ¯½á¹¹£©
alter index emp_pk coalesce;
2.Öؽ¨Ë÷Òý(²»½öÄܹ»Ïû³ý´æ´¢ËéƬ,»¹¿ÉÒԸıäË÷ÒýµÄÈ«²¿´æ´¢²ÎÊýÉèÖ㬲¢ÇÒ¿ÉÒÔ½«Ë÷ÒýÒƶ¯µ½ÆäËüµÄ±í¿Õ¼äÖÐ,Öؽ¨Ë÷Òý
ʵ¼ÊÉϾÍÊÇÔÙÖ¸¶¨µÄ±í¿Õ¼äÖÐÖØн¨Á¢Ò»¸öеÄË÷Òý,È»ºóɾ³ýÔÀ´µÄË÷Òý)
alter index emp_pk rebuild;
ɾ³ýË÷Òý
drop index emp_ename;
Èç¹ûË÷ÒýÖаüº¬Ë𻵵ÄÊý¾Ý¿é£¬»òÕß°üº¬¹ý¶àµÄ´æ´¢ËéƬ£¬ÐèÒªÊ×ÏÈɾ³ýÕâ¸öË÷Òý£¬È»ºóÔÙÖؽ¨Ëü.
Èç¹ûË÷ÒýÊÇÔÚ´´½¨Ô¼ÊøʱÓÉoracle×Ô¶¯²úÉúµÄ,¿ÉÒÔͨ¹ý½ûÓÃÔ¼Êø»òɾ³ýÔ¼ÊøµÄ·½·¨À´É¾³ý¶ÔÓ¦µÄË÷Òý.
ÔÚɾ³ýÒ»¸ö±íʱ,oracle»á×Ô¶¯É¾³ýËùÓÐÓë¸Ã±íÏà¹ØµÄË÷Òý.
Ë÷ÒýÊý¾Ý×Öµä
all_indexes/dba_indexes/user_indexes Ë÷ÒýµÄ»ù±¾ÐÅÏ¢
all_ind_columns/dba_ind_columns/user_ind_columns Ë÷Òý¶ÔÓ¦µÄ×Ö¶ÎÐÅÏ¢
create unique index Ë÷ÒýÃû on ±íÃû(×Ö¶ÎÃû)
create
Ïà¹ØÎĵµ£º
1¡¢ÅäÖÃhibernate£º
1.1¡¢ÅäÖÃhibernateµÄ³Ö¾ÃÀàÎļþÖжÔÓ¦µÄ×Ö¶ÎΪbyte[]ÀàÐÍ
2.2¡¢ÅäÖÃhibernateµÄÀàÓ³ÉäÎļþÖжÔÓ¦µÄ×Ö¶ÎtypeΪ
org.springframework.orm.hibernate3.support.BlobByteArray ......
½¨Á¢±íÈçÏÂ:
ѧÉú»ù±¾ÐÅÏ¢±í
CREATE Student(
[Studentid][Int]IDENTITY(1,1)NOT NULL primary key,--Ö÷¼ü
[StudentName][char]NOT NULL
)
¿Î³ÌÐÅÏ¢±í
CREATE Subject(
[SubjectID][char]NOT NULL primary key, --Ö÷¼ü
[SubjectName][char]NOT NULL
)
³ ......
Êý¾Ý¿â¶ÔÏó
<1>±í(Ô¼Êø)
<2>ÈçºÎ×Ô¶¯±àºÅ
SQLserver
--IDENTITYÊôÐÔ
create table test(
xh int identity(1,2) primary key,
name varchar(20)
);
insert into test(name) values ('mike');
ORACLE
Ò»¸ö¶ÔÏó(ÐòÁÐsequenc ......
ORACLEÊý¾Ý¿âÀï±íµ¼ÈëSQL ServerÊý¾Ý¿â
1¡¢ÔÚÄ¿µÄSQL ServerÊý¾Ý¿â·þÎñÆ÷ÉÏ°²×°ORACLE ClientÈí¼þ»òÕßORACLE ODBC Driver.
ÔÚ$ORACLE_HOME\network\admin\tnsnames.oraÀïÅäÖÃORACLEÊý¾Ý¿âµÄ±ðÃû(service name)¡£
2¡¢ÔÚWIN2000»òÕßwin200 ......
¶ÔÓÚrownumÀ´ËµËüÊÇoracleϵͳ˳Ðò·ÖÅäΪ´Ó²éѯ·µ»ØµÄÐеıàºÅ£¬·µ»ØµÄµÚÒ»ÐзÖÅäµÄÊÇ1£¬µÚ¶þÐÐÊÇ2£¬ÒÀ´ËÀàÍÆ£¬Õâ¸öα×ֶοÉÒÔÓÃÓÚÏÞÖƲéѯ·µ»ØµÄ×ÜÐÐÊý£¬¶øÇÒrownum²»ÄÜÒÔÈκαíµÄÃû³Æ×÷Ϊǰ׺¡£
¾ÙÀý˵Ã÷£º
ÀýÈç±í£ºstudent(ѧÉú)±í£¬±í½á¹¹Îª£º
ID¡¡¡¡¡¡ char(6)¡¡¡¡¡¡¡¡¡¡ --ѧºÅ
name¡¡¡¡¡¡¡¡VA ......