创建B树索引

索引有分B树索引，位图索引等，我们常用的就是B树索引，对于数据量大的表，有索引
能够提高查询的效率。其中B树索引也有份普通的B树索引，唯一性（B树）索引。
以下是创建B树索引的过程，其实索引创建过程比较简单，主要是能够回运用索引就不简单。

----B树索引：
--创建普通索引：

---查看用户suxing模式下所有的索引信息：

suxing@PROD>col INDEX_NAME for a15

suxing@PROD>col TABLE_NAME for a15

suxing@PROD>col INDEX_TYPE for a15

suxing@PROD>select index_name,table_name,index_type,status

  2  from user_indexes;

INDEX_NAME      TABLE_NAME      INDEX_TYPE      STATUS

--------------- --------------- --------------- --------

IDX_T4          T4              NORMAL          VALID

IDX_T3          T3              NORMAL          VALID

IND_MYTEST      MYTEST          NORMAL          VALID

 

---查看用户suxing模式下所拥有的表：

suxing@PROD>select tname from tab;

 

TNAME

------------------------------

COURSES

MYTEST

STUDENTS

SUTAB

... ...

TNAME

------------------------------

YOURTEST

12 rows selected.

 

---创建普通索引：

--给表students创建普通索引：

suxing@PROD>desc students;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 STU_ID                                             NUMBER(4)

 STU_NAME                                           VARCHAR2(10)

 CREDIT                                             NUMBER(2)

 

suxing@PROD>select * from students;

    STU_ID STU_NAME       CREDIT

---------- ---------- ----------

      1121 susu                3

      1122 sufi                3

      1131 sike                2

      1131 sike                2

--创建索引： 

suxing@PROD>create index ind_stu on students(stu_id)

  2  tablespace myspace;

Index created.

#索引创建完成。

 

--此时查看用户的索引信息：

suxing@PROD>select index_name,table_name,index_type,status

  2  from user_indexes;

INDEX_NAME      TABLE_NAME      INDEX_TYPE      STATUS

--------------- --------------- --------------- --------

IDX_T4          T4              NORMAL          VALID

IDX_T3          T3              NORMAL          VALID

IND_STU         STUDENTS        NORMAL          VALID

IND_MYTEST      MYTEST          NORMAL          VALID

#可以看到索引创建成功。

 

---给表courses创建唯一性索引：

--查看表courses的表结构与数据记录：

suxing@PROD>desc courses;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 COU_ID                                             NUMBER(4)

 COU_NAME                                           VARCHAR2(10)

 CREDIT                                             NUMBER(2)
 

suxing@PROD>select * from courses;

    COU_ID COU_NAME       CREDIT

---------- ---------- ----------

      2212 china               4

      2213 english             3

      2214 computer            5

      2215 C language          4

--创建唯一性索引：

suxing@PROD>create unique index

  2  ind_cou on courses(cou_id)

  3  tablespace myspace;

Index created.

#唯一性索引创建完成。

 

--查看用户索引的信息：

suxing@PROD>select index_name,table_name,index_type,status

  2  from user_indexes;

INDEX_NAME      TABLE_NAME      INDEX_TYPE      STATUS

--------------- --------------- --------------- --------

IDX_T4          T4              NORMAL          VALID

IDX_T3          T3              NORMAL          VALID

IND_STU         STUDENTS        NORMAL          VALID

IND_MYTEST      MYTEST          NORMAL          VALID

IND_COU         COURSES         NORMAL          VALID

#可见表courses的索引创建成功，索引的信息中未能体现唯一性。

 

--尝试往表中插入一条重复ID号的记录：

suxing@PROD>insert into courses values(2215,'Clanguage',4);

insert into courses values(2215,'Clanguage',4)

*

ERROR at line 1:

ORA-00001: unique constraint (SUXING.IND_COU) violated

#可见不能插入该条记录，因为cou_id字段创建了唯一性索引。

--换成另外的id号：

suxing@PROD>insert into courses values(2216,'Clanguage',4);

1 row created.

#插入成功。

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

转载于:http://blog.itpub.net/31392094/viewspace-2128661/