管理员登陆：
C:\>sqlplus "system/manager@orcl"
SQL*Plus: Release 10.2.0.1.0 - Production on 星期日 1月 5 19:30:54 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options显示当前连接用户：
SQL> show user
USER 为 "SYSTEM"查看系统拥有哪些用户：
SQL> select * from all_users;
USERNAME USER_ID CREATED
------------------------------ ---------- --------------
USER1 61 01-1月 -14
BI 60 01-1月 -14
PM 59 01-1月 -14
SH 58 01-1月 -14
IX 57 01-1月 -14
OE 56 01-1月 -14
HR 55 01-1月 -14
SCOTT 54 30-8月 -05
MGMT_VIEW 53 30-8月 -05
MDDATA 50 30-8月 -05
SYSMAN 51 30-8月 -05
USERNAME USER_ID CREATED
------------------------------ ---------- --------------
MDSYS 46 30-8月 -05
SI_INFORMTN_SCHEMA 45 30-8月 -05
ORDPLUGINS 44 30-8月 -05
ORDSYS 43 30-8月 -05
OLAPSYS 47 30-8月 -05
ANONYMOUS 39 30-8月 -05
XDB 38 30-8月 -05
CTXSYS 36 30-8月 -05
EXFSYS 34 30-8月 -05
WMSYS 25 30-8月 -05
DBSNMP 24 30-8月 -05
USERNAME USER_ID CREATED
------------------------------ ---------- --------------
TSMSYS 21 30-8月 -05
DMSYS 35 30-8月 -05
DIP 19 30-8月 -05
OUTLN 11 30-8月 -05
SYSTEM 5 30-8月 -05
SYS 0 30-8月 -05
已选择28行。新建用户并授权：
SQL> CREATE USER USER1
2 IDENTIFIED BY USER1
3 DEFAULT TABLESPACE USERS
4 TEMPORARY TABLESPACE TEMP
5 PROFILE DEFAULT;
用户已创建。
SQL> GRANT CONNECT,RESOURCE TO USER1;
授权成功。连接到用户user1：
SQL> CONN USER1/USER1;
已连接。创建表：
SQL> CREATE TABLE TB1(ID INTEGER PRIMARY KEY);
表已创建。查询表结构：
SQL> DESC TB1;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(38)修改用户密码：
SQL> ALTER USER USER1 IDENTIFIED BY USER1;
用户已更改。增加字段：
SQL> ALTER TABLE TB1 ADD (USERNAME VARCHAR2(10) NULL);
表已更改。
SQL> DESC TB1;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(38)
USERNAME VARCHAR2(10)
修改字段：
SQL> ALTER TABLE TB1 MODIFY (USERNAME VARCHAR2(20));
表已更改。创建索引：
SQL> CREATE INDEX TB1_USERNAME_INDEX ON TB1 (USERNAME);
索引已创建。删除索引：
SQL> DROP INDEX TB1_USERNAME_INDEX;
索引已删除。删除字段：
SQL> ALTER TABLE TB1 DROP (USERNAME);
表已更改。删除表：
SQL> DROP TABLE TB1;
表已删除。删除用户：
SQL> CONN SYSTEM/MANAGER
已连接。
SQL> DROP USER USER1;
用户已删除。创建主键自增长的表：
CREATE TABLE TB_STUDENT (
ID INTEGER PRIMARY KEY,
STUDENT_NAME VARCHAR2(20) NULL
);CREATE SEQUENCE SE_STUDENT
START WITH 1
INCREMENT BY 1
CACHE 20
NOMAXVALUE;CREATE OR REPLACE TRIGGER TRI_STUDENT
BEFORE INSERT ON TB_STUDENT FOR EACH ROW
BEGIN
SELECT SE_STUDENT.NEXTVAL INTO:NEW.ID FROM DUAL;
END;存储过程（不带参数）：
CREATE OR REPLACE PROCEDURE PRO_PRINT AS
STUDENT_COUNT INTEGER;
BEGIN
SELECT COUNT(1) INTO STUDENT_COUNT FROM TB_STUDENT;
IF STUDENT_COUNT>1 THEN
DBMS_OUTPUT.PUT_LINE(STUDENT_COUNT);
ELSE
DBMS_OUTPUT.PUT_LINE('no student found');
END IF;
END;执行存储过程：
BEGIN
PRO_PRINT;
END;
或者用CALL，一定要加括号：
CALL PRO_STUDENT_INSERT();随机数函数：
1、dbms_random.normal
这个函数不带参数，能返回normal distribution的一个number类型，所以基本上随机数会在-1到1之间。
2、dbms_random.random
这个也没有参数，返回一个从-power(2,31)到power(2,31)的整数值
3、dbms_random.value
这个函数分为两种，一种是没有参数，则直接返回0-1之间的38位小数
第二种是加上两个参数a、b，则返回值在a、b之间的38位小数
4、dbms_random.string
这个函数必须带有两个参数，前面的字符指定类型，后面的数值指定位数(最大60)
类型说明：
'u','U' : upper case alpha characters only
'l','L' : lower case alpha characters only
'a','A' : alpha characters only (mixed case)
'x','X' : any alpha-numeric characters (upper)
'p','P' : any printable characters
随机数函数测试：
CREATE OR REPLACE PROCEDURE PRO_RANDOM(RANDOM_FROM IN INTEGER, RANDOM_TO IN INTEGER, RANDOM_STRING OUT VARCHAR2)
AS
RANDOM_RANDOM INTEGER;
RANDOM_NORMAL INTEGER;
RANDOM_VALUE INTEGER;
BEGIN
SELECT DBMS_RANDOM.RANDOM INTO RANDOM_RANDOM FROM DUAL;
SELECT DBMS_RANDOM.NORMAL INTO RANDOM_NORMAL FROM DUAL;
IF RANDOM_FROM > RANDOM_TO THEN
SELECT DBMS_RANDOM.VALUE(RANDOM_TO, RANDOM_FROM) INTO RANDOM_VALUE FROM DUAL;
ELSE
SELECT DBMS_RANDOM.VALUE(RANDOM_FROM, RANDOM_TO) INTO RANDOM_VALUE FROM DUAL;
END IF;
SELECT DBMS_RANDOM.STRING('U', 20) INTO RANDOM_STRING FROM DUAL;
DBMS_OUTPUT.PUT_LINE('RANDOM_RANDOM='||RANDOM_RANDOM);
DBMS_OUTPUT.PUT_LINE('RANDOM_NORMAL='||RANDOM_NORMAL);
DBMS_OUTPUT.PUT_LINE('RANDOM_VALUE='||RANDOM_VALUE);
DBMS_OUTPUT.PUT_LINE('RANDOM_STRING='||RANDOM_STRING);
END;执行：
DECLARE
RANDOM_FROM INTEGER;
RANDOM_TO INTEGER;
RANDOM_STRING_OUT VARCHAR2(20);
BEGIN
RANDOM_FROM:=1;
RANDOM_TO:=100;
PRO_RANDOM(RANDOM_FROM,RANDOM_TO, RANDOM_STRING_OUT);
DBMS_OUTPUT.PUT_LINE('RANDOM_STRING_OUT='||RANDOM_STRING_OUT);
END;结果（每次都不同）：
RANDOM_RANDOM=-33085305
RANDOM_NORMAL=-1
RANDOM_VALUE=17
RANDOM_STRING=MADGMQSZXJAHWPJNMWCS
RANDOM_STRING_OUT=MADGMQSZXJAHWPJNMWCS获得系统时间：
SELECT SYSDATE FROM DUAL输出：
2014-1-13 22:42:38格式化时间：
SELECT TO_CHAR(SYSDATE, 'yyyy-MM-dd HH24:mi:ss') FROM DUAL输出：
2014-01-13 22:43:29SELECT TO_DATE('2013-01-04 12:12:12', 'yyyy-MM-dd HH24:mi:ss') FROM DUAL;输出：
2013-1-4 12:12:12存储过程循环插入数据：
CREATE OR REPLACE PROCEDURE PRO_STUDENT_INSERT AS
BEGIN
FOR I IN 1..10000 LOOP
INSERT INTO TB_STUDENT (STUDENT_NAME) VALUES ('aaa');
END LOOP;
END;执行：
BEGIN
PRO_STUDENT_INSERT;
END;用游标遍历数据：
DECLARE
CURSOR C1 IS SELECT STUDENT_NAME FROM TB_STUDENT;
BEGIN
FOR STUDENT_ROW IN C1 LOOP
DBMS_OUTPUT.PUT_LINE(STUDENT_ROW.STUDENT_NAME);
END LOOP;
END;IN和EXISTS性能比较：
插入100万条数据：
CREATE OR REPLACE PROCEDURE PRO_STUDENT_INSERT AS
RANDOM_STRING VARCHAR2(20);
BEGIN
FOR I IN 1..1000000 LOOP
SELECT DBMS_RANDOM.STRING('U', 20) INTO RANDOM_STRING FROM DUAL;
INSERT INTO TB_STUDENT (STUDENT_NAME) VALUES (RANDOM_STRING);
END LOOP;
END;BEGIN
PRO_STUDENT_INSERT;
END;
显示执行时间：
set timing on另外创建两张表：
CREATE TABLE TB_STU1 AS SELECT * FROM TB_STUDENT WHERE ID >= 32500 AND ID <= 102800;
CREATE TABLE TB_STU2 AS SELECT * FROM TB_STUDENT WHERE ID >= 32500 AND ID <= 102800;
不创建索引，执行：
SQL> SELECT COUNT(STUDENT_NAME) FROM TB_STUDENT A WHERE EXISTS (SELECT STUDENT_N
AME FROM TB_STU1 B WHERE A.STUDENT_NAME=B.STUDENT_NAME);
COUNT(STUDENT_NAME)
-------------------
70301
已用时间: 00: 00: 01.56
SQL> SELECT COUNT(STUDENT_NAME) FROM TB_STUDENT WHERE STUDENT_NAME IN (SELECT ST
UDENT_NAME FROM TB_STU2);
COUNT(STUDENT_NAME)
-------------------
70301
已用时间: 00: 00: 00.70
SQL>网上说EXISTS比IN好，可是结果IN更快，后来多测试了几次：
CREATE TABLE TB_STU1 AS SELECT * FROM TB_STUDENT WHERE ID >= 150000 AND ID <= 260000;
CREATE TABLE TB_STU2 AS SELECT * FROM TB_STUDENT WHERE ID >= 150000 AND ID <= 260000;
结果：
SELECT STUDENT_NAME FROM TB_STUDENT A WHERE EXISTS (SELECT STUDENT_NAME FROM TB_STU1 B WHERE A.STUDENT_NAME=B.STUDENT_NAME);
已用时间: 00: 00: 22.45
SELECT STUDENT_NAME FROM TB_STUDENT WHERE STUDENT_NAME IN (SELECT STUDENT_NAME FROM TB_STU2);
已用时间: 00: 00: 15.64有几次，时间是差不多的，但是IN快的次数更多。
现在创建索引：
CREATE INDEX IDX1 ON TB_STU1(STUDENT_NAME);
CREATE INDEX IDX2 ON TB_STU2(STUDENT_NAME);
CREATE INDEX IDX ON TB_STUDENT(STUDENT_NAME);
测试结果：
当外面是TB_STUDENT的时候，IN比EXISTS快。
SELECT STUDENT_NAME FROM TB_STUDENT A WHERE EXISTS (SELECT STUDENT_NAME FROM TB_STU1 B WHERE A.STUDENT_NAME=B.STUDENT_NAME);
已用时间: 00: 05: 11.90
SELECT STUDENT_NAME FROM TB_STUDENT WHERE STUDENT_NAME IN (SELECT STUDENT_NAME FROM TB_STU2);
已用时间: 00: 02: 24.50
当里面是TB_STUDENT的时候，EXISTS比IN快。
SELECT STUDENT_NAME FROM TB_STU1 A WHERE EXISTS (SELECT STUDENT_NAME FROM TB_STUDENT B WHERE A.STUDENT_NAME=B.STUDENT_NAME);
已用时间: 00: 01: 43.53
SELECT STUDENT_NAME FROM TB_STU2 WHERE STUDENT_NAME IN (SELECT STUDENT_NAME FROM TB_STUDENT);
已用时间: 00: 03: 56.59TB_STUDENT的数据多，其他表数据少，不过，经过多次测试，并非每次都这样。
性能测试：SELECT * 和SELECT 字段名
插入100万条数据
CREATE TABLE TB_STUDENT (
ID INTEGER PRIMARY KEY,
STUDENT_NAME VARCHAR2(20) NOT NULL,
SEX VARCHAR2(1) NOT NULL,
AGE INTEGER NOT NULL
);
CREATE SEQUENCE SE_STUDENT
START WITH 1
INCREMENT BY 1
CACHE 20
NOMAXVALUE;
CREATE OR REPLACE TRIGGER TRI_STUDENT
BEFORE INSERT ON TB_STUDENT FOR EACH ROW
BEGIN
SELECT SE_STUDENT.NEXTVAL INTO:NEW.ID FROM DUAL;
END;
CREATE OR REPLACE PROCEDURE PRO_STUDENT_INSERT AS
BEGIN
FOR I IN 1..1000000 LOOP
IF I <= 10000 THEN
INSERT INTO TB_STUDENT (STUDENT_NAME, SEX, AGE) VALUES ('aaaaa', 'B', 11);
ELSIF I<=100000 THEN
INSERT INTO TB_STUDENT (STUDENT_NAME, SEX, AGE) VALUES ('bbbbb', 'G', 12);
ELSIF I<=500000 THEN
INSERT INTO TB_STUDENT (STUDENT_NAME, SEX, AGE) VALUES ('ccccc', 'B', 13);
ELSE
INSERT INTO TB_STUDENT (STUDENT_NAME, SEX, AGE) VALUES ('ddddd', 'G', 14);
END IF;
END LOOP;
COMMIT;
END;
CALL PRO_STUDENT_INSERT();
测试结果：
SELECT * FROM TB_STUDENT WHERE ID <= 100000;
已用时间: 00: 00: 32.92
SELECT ID, STUDENT_NAME, SEX, AGE FROM TB_STUDENT WHERE ID <= 100000;
已用时间: 00: 00: 32.50
SELECT * FROM TB_STUDENT WHERE ID >= 100001 AND ID <=200000;
已用时间: 00: 00: 25.12
SELECT ID, STUDENT_NAME, SEX, AGE FROM TB_STUDENT WHERE ID >= 100001 AND ID <=200000;
已用时间: 00: 00: 24.45
SELECT * FROM TB_STUDENT;
已用时间: 00: 03: 34.21
SELECT ID, STUDENT_NAME, SEX, AGE FROM TB_STUDENT;
已用时间: 00: 01: 53.95
SELECT * FROM TB_STUDENT;
已用时间: 00: 02: 14.85
SELECT ID, STUDENT_NAME, SEX, AGE FROM TB_STUDENT;
已用时间: 00: 02: 04.62上面看到查100万条记录，第二次时间远远比第一次少，这是因为缓存的关系，下面清理一下缓存：
ALTER SYSTEM FLUSH BUFFER_CACHE;
再次测试：
SELECT * FROM TB_STUDENT;
已用时间: 00: 03: 22.29
SELECT ID, STUDENT_NAME, SEX, AGE FROM TB_STUDENT;
已用时间: 00: 02: 47.64看到直接写出字段名和用*，显然是直接写字段名快，但是快了没多少。
多测试几次，每次都清缓存，如下：
SELECT * FROM TB_STUDENT;
已用时间: 00: 02: 15.59
SELECT ID, STUDENT_NAME, SEX, AGE FROM TB_STUDENT;
已用时间: 00: 02: 14.17
SELECT * FROM TB_STUDENT;
已用时间: 00: 02: 24.21
SELECT ID, STUDENT_NAME, SEX, AGE FROM TB_STUDENT;
已用时间: 00: 02: 18.48然后增加10列再测试一下：
ALTER TABLE TB_STUDENT ADD (
COL1 VARCHAR2(10) NULL,
COL2 VARCHAR2(10) NULL,
COL3 VARCHAR2(10) NULL,
COL4 VARCHAR2(10) NULL,
COL5 VARCHAR2(10) NULL,
COL6 VARCHAR2(10) NULL,
COL7 VARCHAR2(10) NULL,
COL8 VARCHAR2(10) NULL,
COL9 VARCHAR2(10) NULL,
COL10 VARCHAR2(10) NULL
);测试结果：
SELECT * FROM TB_STUDENT;
已用时间: 00: 06: 16.53
SELECT ID, STUDENT_NAME, SEX, AGE, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10 FROM TB_STUDENT;
已用时间: 00: 06: 00.76
SELECT * FROM TB_STUDENT;
已用时间: 00: 05: 52.68
SELECT ID, STUDENT_NAME, SEX, AGE, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10 FROM TB_STUDENT;
已用时间: 00: 06: 18.56所以，感觉两条SQL语句，并没有很明显的时间上的差别，也就30秒左右的差别。测试数据量是100万条。
4795
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
