oracle 索引、索引组织表、临时表、外部表

oracle 索引和索引表

 一）索引

语法：

create unique|bitmap index <schema>.<index_name>on <schema>.<table_name>(<column_name>|<expression>asc |desc,<column_name |<expression>asc |desc,...)tablespace<tablespace_name>storeage<storage_settings>logging | nologgingcompute statisticesnocompress | compress<nn>nosort | reverseparition | global partition<partition_setting>;

操作步骤：

SCOTT@orcl#select index_name from user_indexes where table_name=upper('emp');未选定行SCOTT@orcl#create index emp_nomarl_index on emp(empno) tablespace test1;索引已创建。SCOTT@orcl#create unique index unique_index_emp on emp(ename) tablespace test1;create unique index unique_index_emp on emp(ename) tablespace test1                                        *第 1 行出现错误:ORA-01452: 无法 CREATE UNIQUE INDEX; 找到重复的关键字SCOTT@orcl#drop index emp_nomarl_index ; 索引已删除。SCOTT@orcl#create unique index unique_index_emp on emp(empno) tablespace test1;索引已创建。SCOTT@orcl#desc emp;   名称                                                                                          是否为空? 类型 --------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------- EMPNO                                                                                                  NUMBER(38) ENAME                                                                                                  VARCHAR2(10) JOB                                                                                                    VARCHAR2(9) MGR                                                                                                    NUMBER(4) HIREDATE                                                                                               DATE SAL                                                                                                    NUMBER COMM                                                                                                   NUMBER(7,2) DEPTNO                                                                                                 NUMBER(2) SEX                                                                                                    VARCHAR2(10)SCOTT@orcl#create bitmap index bitmap_index_emp on emp(sex) tablespace test1;索引已创建。SCOTT@orcl#create index composite_index on emp(empno,ename) tablespace test1;索引已创建。SCOTT@orcl#alter index composite_index compress;alter index composite_index compress                            *第 1 行出现错误:ORA-02243: ALTER INDEX 或 ALTER MATERIALIZED VIEW 选项无效SCOTT@orcl#alter index composite_index compress 2;alter index composite_index compress 2                            *第 1 行出现错误:ORA-02243: ALTER INDEX 或 ALTER MATERIALIZED VIEW 选项无效SCOTT@orcl#drop index composite_index ;索引已删除。SCOTT@orcl#create index composite_index on emp(empno,ename) compress 2 tablespace test1;索引已创建。SCOTT@orcl#desc emp; 名称                                                                                          是否为空? 类型 --------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------- EMPNO                                                                                                  NUMBER(38) ENAME                                                                                                  VARCHAR2(10) JOB                                                                                                    VARCHAR2(9) MGR                                                                                                    NUMBER(4) HIREDATE                                                                                               DATE SAL                                                                                                    NUMBER COMM                                                                                                   NUMBER(7,2) DEPTNO                                                                                                 NUMBER(2) SEX                                                                                                    VARCHAR2(10)SCOTT@orcl#create index function_index_emp on emp(to_char(HIREDATE,'yyyy-mm-dd')) tablespace test1;索引已创建。SCOTT@orcl#alter index function_index_emp coalesce deallocate unused;索引已更改。SCOTT@orcl#alter index bitmap_index_emp coalesce deallocate unused;索引已更改。SCOTT@orcl#alter index unique_Index_emp coalesce deallocate unused;索引已更改。SCOTT@orcl#alter index emp_test1 rebuild;alter index emp_test1 rebuild*第 1 行出现错误:ORA-01418: 指定的索引不存在SCOTT@orcl#alter index composite_index rebuild;索引已更改。SCOTT@orcl#SCOTT@orcl#SCOTT@orcl#SCOTT@orcl#alter index composite_index monitoring usage;索引已更改。SCOTT@orcl#desc v$object_usage; 名称                                                                                          是否为空? 类型 --------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------- INDEX_NAME                                                                                    NOT NULL VARCHAR2(30) TABLE_NAME                                                                                    NOT NULL VARCHAR2(30) MONITORING                                                                                             VARCHAR2(3) USED                                                                                                   VARCHAR2(3) START_MONITORING                                                                                       VARCHAR2(19) END_MONITORING                                                                                         VARCHAR2(19)SCOTT@orcl#select * from v$object_usage;INDEX_NAME                                                   TABLE_NAME                                                   MONITO USED------------------------------------------------------------ ------------------------------------------------------------ ------ ------START_MONITORING                       END_MONITORING-------------------------------------- --------------------------------------COMPOSITE_INDEX                                              EMP                                                          YES    NO05/16/2013 22:09:45SCOTT@orcl#col index_name for a40SCOTT@orcl#col table_name for a30SCOTT@orcl#col start_monitoring for a40SCOTT@orcl#col end_monitoring for a40;SCOTT@orcl#select * from v$object_usage;INDEX_NAME                               TABLE_NAME                     MONITO USED   START_MONITORING                         END_MONITORING---------------------------------------- ------------------------------ ------ ------ ---------------------------------------- ----------------------------------------COMPOSITE_INDEX                          EMP                            YES    NO     05/16/2013 22:09:45SCOTT@orcl#alter index composite_index nomonitoring usage;索引已更改。SCOTT@orcl#select * from v$object_usage;INDEX_NAME                               TABLE_NAME                     MONITO USED   START_MONITORING                         END_MONITORING---------------------------------------- ------------------------------ ------ ------ ---------------------------------------- ----------------------------------------COMPOSITE_INDEX                          EMP                            NO     NO     05/16/2013 22:09:45                      05/16/2013 22:11:21SCOTT@orcl#select index_name from user_indexes where table_name=upper('emp');INDEX_NAME----------------------------------------FUNCTION_INDEX_EMPCOMPOSITE_INDEXBITMAP_INDEX_EMPUNIQUE_INDEX_EMPSCOTT@orcl#create index reverse_index on emp(deptno) tablespace test1;索引已创建。SCOTT@orcl#select index_name from user_indexes where table_name=upper('emp');INDEX_NAME----------------------------------------REVERSE_INDEXFUNCTION_INDEX_EMPCOMPOSITE_INDEXBITMAP_INDEX_EMPUNIQUE_INDEX_EMP

 二）索引组织表

二）索引组织表SYS@orcl#SYS@orcl#create table index_table  2  (id  3  number primary key,  4  name varchar2(10)  5  )  6  organization index  7  tablespace test1;表已创建。SYS@orcl#create table index_table_two  2  (  3  id number primary key,  4  name varchar2(10),  5  factory varchar2(10),  6  description varchar2(20)  7  )  8  organization index  9  including name 10  pctthreshold 20 11  overflow 12  tablespace test1;表已创建。SYS@orcl#SYS@orcl#create table rhys(  2  id number primary key,  3  name varchar2(20),  4  sex varchar2(10)  5  )  6  organization index  7  tablespace test1  8  including name  9  pctthreshold 30 10  overflow tablespace test1;表已创建。SYS@orcl#SYS@orcl#select table_name,tablespace_name from user_tables where table_name=upper('index_table_two');TABLE_NAME                                                   TABLESPACE_NAME------------------------------------------------------------ ------------------------------------------------------------INDEX_TABLE_TWOSYS@orcl#CONN SCOTT/ROOTERROR:ORA-01017: invalid username/password; logon denied警告: 您不再连接到 ORACLE。SYS@orcl#conn scott/root已连接。SCOTT@orcl#create table rhys(  2  id number primary key,  3  name varchar2(20),  4  sex varchar2(10)  5  )  6  organization index  7  tablespace test1  8  including name  9  pctthreshold 30 10  overflow tablespace test1;表已创建。SCOTT@orcl#SCOTT@orcl#create table admin_docindex(  2  token char(20),  3  doc_id number,  4  token_frequency number,  5  token_offsets varchar2(2000),  6  constraint pk_admin_docindex primary key (token,doc_id))  7  organization index  8  tablespace test1  9  pctthreshold 20 10  overflow tablespace users;表已创建。已用时间:  00: 00: 01.63SCOTT@orcl#insert into admin_docindex values('xiaohai',1,1,'iot table');已创建 1 行。已用时间:  00: 00: 00.01SCOTT@orcl#commit;提交完成。已用时间:  00: 00: 00.08SCOTT@orcl#select table_name,tablespace_name,iot_name,iot_type from user_tables  2  where table_name='ADMIN_DOCINDEX';TABLE_NAME                                                   TABLESPACE_NAME                                              IOT_NAME------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------IOT_TYPE------------------------ADMIN_DOCINDEXIOT已用时间:  00: 00: 00.03SCOTT@orcl#SELECT SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME FROM USER_SEGMENTS WHERE  2  SEGMENT_NAME='ADMIN_DOCINDEX';未选定行已用时间:  00: 00: 00.61SCOTT@orcl#COL INDEX_NAME FOR A20         SCOTT@orcl#COL INDEX_TYPE FOR A10SCOTT@orcl#COL TABLE_NAME FOR A20SCOTT@orcl#COL TABLESPACE_NAME FOR A30SCOTT@orcl#COL PCT_THRESHOLD FOR A20SCOTT@orcl#SELECT INDEX_NAME,INDEX_TYPE,TABLE_NAME,TABLESPACE_NAME,TABLE_TYPE,PCT_THRESHOLD FROM  2  USER_INDEXES WHERE TABLE_NAME='ADMIN_DOCINDEX';INDEX_NAME           INDEX_TYPE TABLE_NAME           TABLESPACE_NAME                TABLE_TYPE             PCT_THRESHOLD-------------------- ---------- -------------------- ------------------------------ ---------------------- -------------PK_ADMIN_DOCINDEX    IOT - TOP  ADMIN_DOCINDEX       TEST1                          TABLE                     ##########已用时间:  00: 00: 00.94SCOTT@orcl#COL PCT_THRESHOLD FOR 99999999999SCOTT@orcl#R  1  SELECT INDEX_NAME,INDEX_TYPE,TABLE_NAME,TABLESPACE_NAME,TABLE_TYPE,PCT_THRESHOLD FROM  2* USER_INDEXES WHERE TABLE_NAME='ADMIN_DOCINDEX'INDEX_NAME           INDEX_TYPE TABLE_NAME           TABLESPACE_NAME                TABLE_TYPE             PCT_THRESHOLD-------------------- ---------- -------------------- ------------------------------ ---------------------- -------------PK_ADMIN_DOCINDEX    IOT - TOP  ADMIN_DOCINDEX       TEST1                          TABLE                             20已用时间:  00: 00: 00.04SCOTT@orcl#SELECT INDEX_NAME,INDEX_TYPE,TABLE_NAME,TABLE_TYPE,TABLESPACE_NAME FROM   2  USER_INDEXES WHERE INDEX_NAME=UPPER('pk_admin_docindex');INDEX_NAME           INDEX_TYPE TABLE_NAME           TABLE_TYPE             TABLESPACE_NAME-------------------- ---------- -------------------- ---------------------- ------------------------------PK_ADMIN_DOCINDEX    IOT - TOP  ADMIN_DOCINDEX       TABLE                  TEST1已用时间:  00: 00: 01.58三）临时表

SCOTT@orcl#create global temporary table global_table(  2  id number,  3  name varchar2(20)  4  )  5  on commit delete rows;表已创建。SCOTT@orcl#SCOTT@orcl#insert into global_table values(1,'xiaohai');已创建 1 行。SCOTT@orcl#select * from global_table;        ID NAME---------- ----------------------------------------         1 xiaohaiSCOTT@orcl#commit;提交完成。SCOTT@orcl#select * from global_table;未选定行SCOTT@orcl#SCOTT@orcl#create global temporary table global_table_sessin(  2  id number,  3  name varchar2(20)  4  )  5  on commit preserve rows; 表已创建。SCOTT@orcl#insert into global_table_sessin values(1,'xiaohai');已创建 1 行。SCOTT@orcl#commit;提交完成。SCOTT@orcl#select * from global_table_sessin;        ID NAME---------- ----------------------------------------         1 xiaohaiSCOTT@orcl#conn sys/root as sysdba;已连接。SYS@orcl#conn scott/root已连接。SCOTT@orcl#select * from global_table_sessin;未选定行

四）外部表

SCOTT@orcl#desc dba_directories; 名称                                                                                                              是否为空? 类型 ----------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------------------- OWNER                                                                                                             NOT NULL VARCHAR2(30) DIRECTORY_NAME                                                                                                    NOT NULL VARCHAR2(30) DIRECTORY_PATH                                                                                                             VARCHAR2(4000)SCOTT@orcl#col owner for a10SCOTT@orcl#col directory_name a40SP2-0158: 未知的 COLUMN 选项 "a40"SCOTT@orcl#col directory_name for a40 SCOTT@orcl#col directory_path for a80SCOTT@orcl#select * from dba_directories;OWNER      DIRECTORY_NAME                           DIRECTORY_PATH---------- ---------------------------------------- --------------------------------------------------------------------------------SYS        EXPDP                                    /opt/oracle/orabak/expdbSYS        QUEST_SOO_UDUMP_DIR                      /opt/oracle/diag/rdbms/orcl/orcl/trace/SYS        QUEST_SOO_CDUMP_DIR                      /opt/oracle/diag/rdbms/orcl/orcl/cdump/SYS        QUEST_SOO_BDUMP_DIR                      /opt/oracle/diag/rdbms/orcl/orcl/trace/SYS        QUEST_SOO_ADUMP_DIR                      /opt/oracle/admin/orcl/adump/SYS        oracle_bak                               /opt/oracle/bakSYS        IDR_DIR                                  /opt/oracle/diag/rdbms/orcl/orcl/irSYS        AUDIT_DIR                                /tmp/SYS        DATA_PUMP_DIR                            /opt/oracle/admin/orcl/dpdump/SYS        ORACLE_OCM_CONFIG_DIR                    /opt/oracle/product/10.2/db_1/ccr/state已选择10行。已用时间:  00: 00: 00.01SCOTT@orcl#create directory csdata as '/opt/oracle/oradata/csdata';目录已创建。已用时间:  00: 00: 00.49SCOTT@orcl#select * from dba_directories;OWNER      DIRECTORY_NAME                           DIRECTORY_PATH---------- ---------------------------------------- --------------------------------------------------------------------------------SYS        CSDATA                                   /opt/oracle/oradata/csdataSYS        EXPDP                                    /opt/oracle/orabak/expdbSYS        QUEST_SOO_UDUMP_DIR                      /opt/oracle/diag/rdbms/orcl/orcl/trace/SYS        QUEST_SOO_CDUMP_DIR                      /opt/oracle/diag/rdbms/orcl/orcl/cdump/SYS        QUEST_SOO_BDUMP_DIR                      /opt/oracle/diag/rdbms/orcl/orcl/trace/SYS        QUEST_SOO_ADUMP_DIR                      /opt/oracle/admin/orcl/adump/SYS        oracle_bak                               /opt/oracle/bakSYS        IDR_DIR                                  /opt/oracle/diag/rdbms/orcl/orcl/irSYS        AUDIT_DIR                                /tmp/SYS        DATA_PUMP_DIR                            /opt/oracle/admin/orcl/dpdump/SYS        ORACLE_OCM_CONFIG_DIR                    /opt/oracle/product/10.2/db_1/ccr/state已选择11行。已用时间:  00: 00: 00.01SCOTT@orcl#SCOTT@orcl#r  1  create table csdata(  2  name varchar2(30),  3  password char(50),  4  email varchar2(70)  5  )  6  organization external(  7  type oracle_loader  8  default directory csdata  9  access parameters( 10  fields terminated by '#' 11  ) 12  location ('csdata.txt') 13* )表已创建。已用时间:  00: 00: 00.07SCOTT@orcl#select * from csdata where rownum<6;NAME                                                         PASSWORD------------------------------------------------------------ ----------------------------------------------------------------------------------------------------EMAIL--------------------------------------------------------------------------------------------------------------------------------------------zdg                                                           12344321 [e-mail]                                                      670203313747 [e-mail]                                                         730413 [e-mail]                                                         PASSWORD------------------------------------------------------------ ----------------------------------------------------------------------------------------------------EMAIL--------------------------------------------------------------------------------------------------------------------------------------------huwolf                                                        2535263 [e-mail]                                                        KIC43dk6! [e-mail]已用时间:  00: 00: 01.13SCOTT@orcl#col name for a20SCOTT@orcl#r                       1* select * from csdata where rownum<6NAME                 PASSWORD-------------------- ----------------------------------------------------------------------------------------------------EMAIL--------------------------------------------------------------------------------------------------------------------------------------------zdg                   12344321 [e-mail]              670203313747 [e-mail]                 730413 [e-mail]                 PASSWORD-------------------- ----------------------------------------------------------------------------------------------------EMAIL--------------------------------------------------------------------------------------------------------------------------------------------huwolf                2535263 [e-mail]                KIC43dk6! [e-mail]已用时间:  00: 00: 00.03SCOTT@orcl#!oracle@oracle:~> oracle@oracle:~> oracle@oracle:~> exitexitSCOTT@orcl#      SCOTT@orcl#SCOTT@orcl#SCOTT@orcl#drop table csdata;表已删除。已用时间:  00: 00: 02.48SCOTT@orcl#SCOTT@orcl#SCOTT@orcl#SCOTT@orcl#SCOTT@orcl#SCOTT@orcl#SCOTT@orcl#create table csdata(  2  name varchar2(40),  3  password varchar2(80),  4  email varchar2(120)  5  )  6  organization external(  7  type oracle_loader  8  default directory csdata  9  access parameters( 10  records delimited by newline 11  badfile 'badfile.txt' 12  fields terminated by '#') 13  location ('csdata.txt') 14  ) 15  reject limit unlimited;表已创建。已用时间:  00: 00: 00.61SCOTT@orcl#select * from csdata where rownum<20;NAME                 PASSWORD-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------EMAIL--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------zdg                   12344321 [e-mail]              670203313747 [e-mail]                 730413 [e-mail]                 PASSWORD-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------EMAIL--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------huwolf                2535263 [e-mail]                KIC43dk6! [e-mail]                s12345 [e-mail]                 PASSWORD-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------EMAIL--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Michael               apple [e-mail]                 lj7202 [e-mail]             12345 [e-mail]                 PASSWORD-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------EMAIL--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Eie                   hebeibdh [e-mail]              8398518 [e-mail]99               priverhe [e-mail]                 PASSWORD-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------EMAIL--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------leigong               12345 [e-mail]           kingdom [e-mail]        wangjie [e-mail]___csdn_1NAME                 PASSWORD-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------EMAIL--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------rss                   today [e-mail]                  6crx99tj [e-mail]___csdn_1Aquila                smart1010 [e-mail]                 PASSWORD-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------EMAIL--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------onion                 980527 [e-mail]已选择19行。已用时间:  00: 00: 00.45