script_67.txt

orale培训教材包括了所有的sql说明和实例

---------- sm_rowid.txt ----------
/*
 * 范例名称：ROWID伪列
 * 文件名称：sm_rowid.txt
 */

SELECT  ROWID,NAME FROM SM_EMP;
SELECT  ROWID,NAME FROM SM_EMP;

select ROWID,ename FROM emp ;

--用ROWID做WHERE条件,最快的定位方法！
select ROWID,ename FROM emp where ROWID ='AAAGDaAABAAAIRKAAA';

--显示记录的物理位置为更可读的方法
SELECT ROWID, 
       SUBSTR(ROWID,1,6) "OBJECT", 
       SUBSTR(ROWID,7,3) "FIL", 
       SUBSTR(ROWID,10,6) "BLOCK", 
       SUBSTR(ROWID,16,3) "ROW" ,
       name
       FROM sm_emp; 



---建立索引---------------------------------------
create index sm_emp_name  
on sm_emp(name);


create table sm_person
(last_name varchar2(10),
first_name varchar2(10));
create index   PERSON_NAME_INDEX 
on   SM_PERSON(LAST_NAME, FIRST_NAME);




---------- sm_bitmap_index.txt ----------
/*
 * 范例名称：位图索引
 * 文件名称：sm_bitmap_index.txt
 */


drop table sm_emp_sex;

create table sm_emp_sex
(EmpID char(10) ,
Name   varchar2(10),
salary number(8,2),
TelNo  char(8),
sex  char(2));

insert into SM_EMP_sex values('000000001','张飞',100,'62613546','男');
--先建立表SM_EMP_sex

DESC SM_EMP_SEX;



create bitmap index sm_emp_sex  
on sm_emp_sex(sex);

DROP INDEX sm_emp_sex;


  
---------- sm_dd.txt ----------
/*
 * 范例名称：关于索引的系统表DD
 * 文件名称：sm_dd.txt
 */

DROP INDEX sm_emp_sex;

create index sm_emp_sex  
on sm_emp_sex(sex);

DESC USER_INDEXES;
SELECT * FROM USER_INDEXES 
WHERE INDEX_NAME='sm_emp_sex';

SELECT * FROM USER_INDEXES WHERE TABLE_NAME='sm_emp_sex';
--??为什么没有数据？
SELECT * FROM USER_INDEXES WHERE TABLE_NAME='SM_EMP_SEX';

SELECT index_name,table_name FROM 
USER_INDEXES WHERE TABLE_NAME='SM_EMP_SEX';

SELECT index_name,table_name FROM 
USER_INDEXES WHERE TABLE_NAME like 'SM_%';
--OK!TABLE名必须大写
--OK!TABLE名必须大写

--USER_IND_COLUMNS
SELECT * FROM USER_IND_COLUMNS 
WHERE INDEX_NAME='sm_emp_sex';

SELECT * FROM USER_IND_COLUMNS 
WHERE INDEX_NAME='SM_EMP_SEX';


--查询所有用sm_emp开头的表上的index
SELECT * FROM USER_IND_COLUMNS WHERE TABLE_NAME  LIKE 'SM_%';
--ERR!

SELECT * FROM USER_IND_COLUMNS WHERE lower(TABLE_NAME)  LIKE 'sm_emp%';
--用LIKE %

SELECT * FROM USER_IND_COLUMNS WHERE TABLE_NAME='sm_emp_sex';
--ERR!大写