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 | nologging
compute statistices
nocompress | compress<nn>
nosort | reverse
parition | 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 NO
05/16/2013 22:09:45
SCOTT@orcl#col index_name for a40
SCOTT@orcl#col table_name for a30
SCOTT@orcl#col start_monitoring for a40
SCOTT@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:45
SCOTT@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:21
SCOTT@orcl#select index_name from user_indexes where table_name=upper('emp');
INDEX_NAME
----------------------------------------
FUNCTION_INDEX_EMP
COMPOSITE_INDEX
BITMAP_INDEX_EMP
UNIQUE_INDEX_EMP
SCOTT@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_INDEX
FUNCTION_INDEX_EMP
COMPOSITE_INDEX
BITMAP_INDEX_EMP
UNIQUE_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_TWO
SYS@orcl#CONN SCOTT/ROOT
ERROR:
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.63
SCOTT@orcl#insert into admin_docindex values('xiaohai',1,1,'iot table');
已创建 1 行。
已用时间: 00: 00: 00.01
SCOTT@orcl#commit;
提交完成。
已用时间: 00: 00: 00.08
SCOTT@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_DOCINDEX
IOT
已用时间: 00: 00: 00.03
SCOTT@orcl#SELECT SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME FROM USER_SEGMENTS WHERE
2 SEGMENT_NAME='ADMIN_DOCINDEX';
未选定行
已用时间: 00: 00: 00.61
SCOTT@orcl#COL INDEX_NAME FOR A20
SCOTT@orcl#COL INDEX_TYPE FOR A10
SCOTT@orcl#COL TABLE_NAME FOR A20
SCOTT@orcl#COL TABLESPACE_NAME FOR A30
SCOTT@orcl#COL PCT_THRESHOLD FOR A20
SCOTT@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.94
SCOTT@orcl#COL PCT_THRESHOLD FOR 99999999999
SCOTT@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.04
SCOTT@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 xiaohai
SCOTT@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 xiaohai
SCOTT@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 a10
SCOTT@orcl#col directory_name a40
SP2-0158: 未知的 COLUMN 选项 "a40"
SCOTT@orcl#col directory_name for a40
SCOTT@orcl#col directory_path for a80
SCOTT@orcl#select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
---------- ---------------------------------------- --------------------------------------------------------------------------------
SYS EXPDP /opt/oracle/orabak/expdb
SYS 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/bak
SYS IDR_DIR /opt/oracle/diag/rdbms/orcl/orcl/ir
SYS 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.01
SCOTT@orcl#create directory csdata as '/opt/oracle/oradata/csdata';
目录已创建。
已用时间: 00: 00: 00.49
SCOTT@orcl#select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
---------- ---------------------------------------- --------------------------------------------------------------------------------
SYS CSDATA /opt/oracle/oradata/csdata
SYS EXPDP /opt/oracle/orabak/expdb
SYS 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/bak
SYS IDR_DIR /opt/oracle/diag/rdbms/orcl/orcl/ir
SYS 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.01
SCOTT@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.07
SCOTT@orcl#select * from csdata where rownum<6;
NAME PASSWORD
------------------------------------------------------------ ----------------------------------------------------------------------------------------------------
EMAIL
--------------------------------------------------------------------------------------------------------------------------------------------
zdg 12344321
zdg@csdn.net
LaoZheng 670203313747
chengming_zheng@163.com
fstao 730413
fstao@tom.com
NAME PASSWORD
------------------------------------------------------------ ----------------------------------------------------------------------------------------------------
EMAIL
--------------------------------------------------------------------------------------------------------------------------------------------
huwolf 2535263
hujiye@263.net
cadcjl KIC43dk6!
ccedcjl@21cn.com
已用时间: 00: 00: 01.13
SCOTT@orcl#col name for a20
SCOTT@orcl#r
1* select * from csdata where rownum<6
NAME PASSWORD
-------------------- ----------------------------------------------------------------------------------------------------
EMAIL
--------------------------------------------------------------------------------------------------------------------------------------------
zdg 12344321
zdg@csdn.net
LaoZheng 670203313747
chengming_zheng@163.com
fstao 730413
fstao@tom.com
NAME PASSWORD
-------------------- ----------------------------------------------------------------------------------------------------
EMAIL
--------------------------------------------------------------------------------------------------------------------------------------------
huwolf 2535263
hujiye@263.net
cadcjl KIC43dk6!
ccedcjl@21cn.com
已用时间: 00: 00: 00.03
SCOTT@orcl#!
oracle@oracle:~>
oracle@oracle:~>
oracle@oracle:~> exit
exit
SCOTT@orcl#
SCOTT@orcl#
SCOTT@orcl#
SCOTT@orcl#drop table csdata;
表已删除。
已用时间: 00: 00: 02.48
SCOTT@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.61
SCOTT@orcl#select * from csdata where rownum<20;
NAME PASSWORD
-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
EMAIL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
zdg 12344321
zdg@csdn.net
LaoZheng 670203313747
chengming_zheng@163.com
fstao 730413
fstao@tom.com
NAME PASSWORD
-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
EMAIL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
huwolf 2535263
hujiye@263.net
cadcjl KIC43dk6!
ccedcjl@21cn.com
netsky s12345
songmail@21cn.com
NAME PASSWORD
-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
EMAIL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Michael apple
appollp@netease.com
siclj lj7202
junlu@peoplemail.com.cn
jinbuhuan 12345
jinbuhuan@163.net
NAME PASSWORD
-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
EMAIL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Eie hebeibdh
fwg@jxfw.com
mainroad 8398518
mainroad@public.cta.cq.cn
river99 priverhe
priver1999@netease.com
NAME PASSWORD
-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
EMAIL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
leigong 12345
leigong@21cn.com
CrazyDragon kingdom
chzhy1@263.net
chenzhuangyuan wangjie
chzhy1@263.net___csdn_1
NAME PASSWORD
-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
EMAIL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
rss today
rss@tjmail.com
Alan 6crx99tj
alan-x@21cn.com___csdn_1
Aquila smart1010
aquila@21cn.com
NAME PASSWORD
-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
EMAIL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
onion 980527
onion_hm@sina.com
已选择19行。
已用时间: 00: 00: 00.45