笔记081114
2008-11-15 上午2:34 发布人 tiger chang
************************************
NAME :CHANGTIGER
DATE :13:37 2008-11-14
EMAIL :o52tiger@yahoo.com.cn
************************************
@@@@@@@@@@@ INDEX @@@@@@@@@@@@@@@
就像你的书本的目录一样，，，，，
在索引里找到位置，在找你的页面去吧 ！！！
SELECT * FROM ABC WHERE NAME = 'DDD';
这样的查询效率很低，要遍历整个表，，，，，，，，
索引INDEX相当于表的目录了，可以提高你的查询速率。
自动创建的索引 表的主键约束或唯一约束都会被自动建立一个索引.
用户自定义索引
ORACLE 自动维护索引 不必用户来管理
索引并不是越多越好，
常给条件的列 给有链接查询的列添加。
列里面的值范围比较大（值比较多，空置比较多），建议有索引
可以给一列建一个索引，也可以是多个列建一个索引。
经常要更新的表---索引也要改，不要建索引
CREATE INDEX INDEX_NAME
ON TABLE_NAME (COLUMN[,COLUMN].....);
--------------------------------创建一个索引--------------------------------------
SQL> CREATE INDEX INDEX_STU
2 ON STUDENT2 (SNAME);
Index created.
------------------------------我有些什么样的索引--------------------------------------
SQL> SELECT INDEX_NAME , TABLE_NAME FROM USER_INDEXES;
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
EMP_EMAIL_UK EMPLOYEES_DEMO
ID_PK TIGER9
INDEX_STU STUDENT2 -------------------->我自定义的索引
LIUML_ID_PK TEST_PK1
LIUML_S_PK LIUML_STUDENT
PK_DEPT DEPT
PK_EMP EMP
SYS_C002732 TEST_PK
SYS_C002739 T_PK
SYS_C002746 TIGER8
SYS_C002883 TEST_NULL
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
SYS_C002887 TEST_UNIQUE
SYS_C002888 TEST_UNIQUE
SYS_C002973 CLASS3
SYS_C002974 CLASS3
SYS_C002985 STUDENT3
SYS_C003065 TIGER11
SYS_C003207 TEST_CLASS
SYS_C003208 TEST_CLASS
SYS_C003210 TEST_STUDENT
SYS_IL0000030584C00001$$ TEST3
SYS_IL0000030587C00001$$ TIGER6
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
SYS_IL0000030625C00001$$ TIGER7
SYS_IL0000030631C00001$$ TEST_LOB
SYS_IL0000030637C00001$$ TEST_BLOB
S_PK LIUML_INFO1
S_PK1 CLASS2
S_UNIQUE TIGER11
28 rows selected.
-------------------索引字典的结构-------------------------------------
SQL> DESC USER_INDEXES;
Name Null? Type
----------------------------------------- -------- ---------------------
INDEX_NAME NOT NULL VARCHAR2(30)
INDEX_TYPE VARCHAR2(27)
TABLE_OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
TABLE_TYPE VARCHAR2(11)
UNIQUENESS VARCHAR2(9)
COMPRESSION VARCHAR2(8)
PREFIX_LENGTH NUMBER
TABLESPACE_NAME VARCHAR2(30)
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
PCT_THRESHOLD NUMBER
INCLUDE_COLUMN NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
PCT_FREE NUMBER
LOGGING VARCHAR2(3)
BLEVEL NUMBER
LEAF_BLOCKS NUMBER
DISTINCT_KEYS NUMBER
AVG_LEAF_BLOCKS_PER_KEY NUMBER
AVG_DATA_BLOCKS_PER_KEY NUMBER
CLUSTERING_FACTOR NUMBER
STATUS VARCHAR2(8)
NUM_ROWS NUMBER
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
DEGREE VARCHAR2(40)
INSTANCES VARCHAR2(40)
PARTITIONED VARCHAR2(3)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
BUFFER_POOL VARCHAR2(7)
USER_STATS VARCHAR2(3)
DURATION VARCHAR2(15)
PCT_DIRECT_ACCESS NUMBER
ITYP_OWNER VARCHAR2(30)
ITYP_NAME VARCHAR2(30)
PARAMETERS VARCHAR2(1000)
GLOBAL_STATS VARCHAR2(3)
DOMIDX_STATUS VARCHAR2(12)
DOMIDX_OPSTATUS VARCHAR2(6)
FUNCIDX_STATUS VARCHAR2(8)
JOIN_INDEX VARCHAR2(3)
SQL>
----------------------------删除索引-------------------------------------
SQL> DROP INDEX INDEX_STU;
Index dropped.
-----------------------------BINMAP 索引--------------------------------------
CREATE BITMAP INDEX BINDEX ON STUDENT2(SNAME);
SQL> CREATE BITMAP INDEX BINDEX ON STUDENT2(SNAME);
Index created.
SQL> SELECT INDEX_NAME ,TABLE_NAME FROM USER_INDEXES WHERE INDEX_NAME LIKE 'BIND
EX';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
BINDEX STUDENT2
@@@@@@@@@@@@ VIEW @@@@@@@@@@@@@
VIEW 试图
试图是一个虚表；
复杂链接查询
SQL> SELECT * FROM STUDENT3 S , CLASS3 C WHERE S.CID = C.ID ;
SID SNAME CID ID NAME
---------- ---------- ---------- ---------- ----------
2 TIGER2 101 101 C++
建立一个试图来替代复杂的查询
SQL> CREATE VIEW VIEW_CS AS SELECT * FROM STUDENT3 S, CLASS3 C WHERE S.CID = C.I
D;
View created.
SQL>
查看试图字典的格式
SQL> DESC USER_VIEWS;
Name Null? Type
----------------------------------------- -------- --------------------------
VIEW_NAME NOT NULL VARCHAR2(30)
TEXT_LENGTH NUMBER
TEXT LONG
TYPE_TEXT_LENGTH NUMBER
TYPE_TEXT VARCHAR2(4000)
OID_TEXT_LENGTH NUMBER
OID_TEXT VARCHAR2(4000)
VIEW_TYPE_OWNER VARCHAR2(30)
VIEW_TYPE VARCHAR2(30)
SUPERVIEW_NAME VARCHAR2(30)
查看用户的试图信息
SQL> SELECT VIEW_NAME , TEXT FROM USER_VIEWS;
VIEW_NAME
------------------------------
TEXT
--------------------------------------------------------------------------------
VIEW_CS
SELECT "SID","SNAME","CID","ID","NAME" FROM STUDENT3 S, CLASS3 C WHERE S.CID = C
SQL>
查看用户试图的格式
SQL> DESC VIEW_CS;
Name Null? Type
----------------------------------------- -------- ---------------
SID NOT NULL NUMBER(3)
SNAME NOT NULL VARCHAR2(10)
CID NUMBER(3)
ID NOT NULL NUMBER(3)
NAME VARCHAR2(10)
应用试图
SQL> SELECT * FROM VIEW_CS;
SID SNAME CID ID NAME
---------- ---------- ---------- ---------- ----------
2 TIGER2 101 101 C++
-------------------------------CREATE OR REPLACE ------------------------------------------
SQL> CREATE OR REPLACE VIEW VIEW_CS AS SELECT ID ,SNAME , CID FROM STUDENT3 S ,
CLASS3 C WHERE S.CID = C.ID ;
View created.
SQL> DESC VIEW_CS;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(3)
SNAME NOT NULL VARCHAR2(10)
CID NUMBER(3)
----------------------------VIEW INSERT INTO --------------------------------------
SQL> CREATE OR REPLACE VIEW VIEW_SC
2 AS
3 SELECT ID , SNAME ,AGE FROM STUDENT2;
View created.
SQL> INSERT INTO VIEW_SC VALUES(1005,'VIEWDATA',36);
1 row created.
SQL> SELECT * FROM VIEW_SC;
ID SNAME AGE
---------- -------------------- ----------
1001 GOOGL DDD 34
1005 VIEWDATA 36
1003 SHANGHAI 34
1004 BEIJING 34
1002 NANCHANG 34
SQL>
______________________WITH READ ONLY ___________________________________
SQL> CREATE OR REPLACE VIEW VIEW_SC
2 AS
3 SELECT ID , SNAME ,AGE FROM STUDENT2
4 WITH READ ONLY;
View created.
SQL>
现在不允许插入数据了
SQL> INSERT INTO VIEW_SC VALUES(1006,'VIEWDATA',36);
INSERT INTO VIEW_SC VALUES(1006,'VIEWDATA',36)
*
ERROR at line 1:
ORA-01733: virtual column not allowed here
不允许更新了
SQL> UPDATE VIEW_SC SET SNAME = 'GOOGLE' WHERE ID = 1001;
UPDATE VIEW_SC SET SNAME = 'GOOGLE' WHERE ID = 1001
*
ERROR at line 1:
ORA-01733: virtual column not allowed here
可以查询
SQL> SELECT * FROM VIEW_SC;
ID SNAME AGE
---------- -------------------- ----------
1001 GOOGL DDD 34
1005 VIEWDATA 36
1003 SHANGHAI 34
1004 BEIJING 34
1002 NANCHANG 34
--------------------WITH CHECK OPTION --------------------
插入数据要满足查询的条件
SQL> CREATE OR REPLACE VIEW VIEW_S
2 AS
3 SELECT * FROM STUDENT2
4 WHERE AGE > 24
5 WITH CHECK OPTION CONSTRAINT VIEW_CK;
View created.
满足子查询条件的可以被插入数据
SQL> INSERT INTO VIEW_S VALUES(1008,'CHECKOP',102,25);
1 row created.
不满足条件的不能被插入
SQL> INSERT INTO VIEW_S VALUES(1008,'CHECKOP',102,24);
INSERT INTO VIEW_S VALUES(1008,'CHECKOP',102,24)
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
不满足条件的更新也是不可以的
SQL> UPDATE VIEW_S SET AGE = 24 WHERE ID = 1008;
UPDATE VIEW_S SET AGE = 24 WHERE ID = 1008
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
-------------------------------------------------------------------
复杂试图(一张或多张表)是不能操作 增删改 的 (学了PLSQL就可以实现了)
只有简单试图(一张表)可以做 增删改。
简单试图查询不能包含一些函数，GROUP BY 。
复杂试图可以包含函数和GROUP BY 。
--------------------------------------------------------------------
YOU CANNOT ADD DATA IF THE VIEW CONTAINS
ANY OF THE ABOVE CONDITIONS
ANY NOT NULL COLUMNS NOT SELECTED BY THE VIEW.
建立一个测试表
SQL> CREATE TABLE TIGER15(
2 ID NUMBER(3) PRIMARY KEY,
3 NAME VARCHAR2(10),
4 AGE NUMBER(3) NOT NULL
5 );
Table created.
建立测试试图
SQL> CREATE VIEW VIEW_T15
2 AS SELECT ID , NAME FROM TIGER15 ;
View created.
插入数据测试
SQL> INSERT INTO VIEW_T15 VALUES(122,'HELLO');
INSERT INTO VIEW_T15 VALUES(122,'HELLO')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."TIGER15"."AGE")
-----------------------------------------------------------------------
@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@