ITPub博客

首页 > 数据库 > Oracle > 课程实践(二)续

课程实践(二)续

原创 Oracle 作者:luisedalian 时间:2014-01-20 09:55:01 0 删除 编辑
案例十五

点击(此处)折叠或打开

  1. --nic_setup.sql
  2. SET ECHO ON
  3. DROP CLUSTER emp_dept including tables;
  4. DROP TABLE emp purge;
  5. DROP TABLE dept PURGE;

  6. CREATE TABLE emp
  7. (
  8.   empno NUMBER(7),
  9.   ename VARCHAR2(15) NOT NULL,
  10.   job VARCHAR2(9),
  11.   mgr NUMBER(7),
  12.   hiredate DATE,
  13.   sal NUMBER(7),
  14.   comm NUMBER(7),
  15.   deptno NUMBER(3)
  16. );

  17. CREATE TABLE dept
  18. (
  19.   deptno NUMBER(3),
  20.   dname VARCHAR2(14),
  21.   loc VARCHAR2(14),
  22.   c VARCHAR2(500)
  23. );

  24. CREATE INDEX idx_emp_deptno ON emp(deptno)
  25. TABLESPACE USERS STORAGE(INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 10 PCTINCREASE 33);
  26. CREATE INDEX idx_dept ON dept(deptno)
  27. TABLESPACE USERS STORAGE(INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 10 PCTINCREASE 33);

  28. BEGIN
  29.   FOR i IN 1..999 LOOP
  30.     INSERT INTO dept VALUES(i, \'D\' || i , \'L\' || i, dbms_random.string(\'u\', 500));
  31.   END LOOP;
  32.   COMMIT;
  33. END;
  34. /
  35. BEGIN
  36.   FOR i IN 1..500000 LOOP
  37. INSERT INTO emp
  38. VALUES(i, dbms_random.string(\'u\', 15), dbms_random.string(\'u\', 9), i, SYSDATE, i, i, MOD(i, 999));
  39.   END LOOP;
  40.   COMMIT;
  41. END;
  42. /
  43. EXEC dbms_stats.gather_schema_stats(\'SH\');

  44. --nic_query.sql
  45. --在emp表和dept表之间进行连接,优化程序使用索引来解析连接
  46. SET ECHO ON
  47. SET TIMING ON
  48. SET AUTOTRACE TRACEONLY
  49. SET PAGESIZE 1000
  50. ALTER SESSION SET WORKAREA_SIZE_POLICY=MANUAL;
  51. ALTER SESSION SET SORT_AREA_SIZE=5000;
  52. @flush.sql

  53. SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno AND emp.deptno > 800;

  54. SET AUTOTRACE OFF
  55. SET TIMING OFF




点击(此处)折叠或打开

  1. --下面演示建立簇表
  2. --ic_setup.sql
  3. SET ECHO ON
  4. DROP TABLE emp PURGE;
  5. DROP TABLE dept PURGE;
  6. DROP CLUSTER emp_dept INCLUDING TABLES;
  7. CREATE CLUSTER emp_dept
  8. (
  9.   deptno NUMBER(3)
  10. )
  11. SIZE 600
  12. TABLESPACE USERS
  13. STORAGE(INITIAL 200K NEXT 300K MINEXTENTS 2 PCTINCREASE 33);

  14. CREATE TABLE emp
  15. (
  16.   empno NUMBER(7),
  17.   ename VARCHAR2(15) NOT NULL,
  18.   job VARCHAR2(9),
  19.   mgr NUMBER(7),
  20.   hiredate DATE,
  21.   sal NUMBER(7),
  22.   comm NUMBER(7),
  23.   deptno NUMBER(3)
  24. )CLUSTER emp_dept(deptno);

  25. CREATE TABLE dept
  26. (
  27.   deptno NUMBER(3),
  28.   dname VARCHAR2(14),
  29.   loc VARCHAR2(14),
  30.   c VARCHAR2(500)
  31. )CLUSTER emp_dept(deptno);

  32. CREATE INDEX idx_emp_dept
  33.   ON CLUSTER emp_dept
  34.   TABLESPACE USERS
  35.   STORAGE(INITIAL 50K
  36.           NEXT 50K
  37.           MINEXTENTS 2
  38.           MAXEXTENTS 10
  39.           PCTINCREASE 33);
  40.           
  41. BEGIN
  42.   FOR i IN 1..999 LOOP
  43.     INSERT INTO dept VALUES(i, \'D\' || i , \'L\' || i, dbms_random.string(\'u\', 500));
  44.   END LOOP;
  45.   COMMIT;
  46. END;
  47. /
  48. BEGIN
  49.   FOR i IN 1..500000 LOOP
  50.     INSERT INTO emp VALUES(i, dbms_random.string(\'u\', 15), dbms_random.string(\'u\', 9), i, SYSDATE, i, i, MOD(i, 999));
  51.   END LOOP;
  52.   COMMIT;
  53. END;
  54. /
  55. EXEC dbms_stats.gather_schema_stats(\'SH\');

  56. --ic_query.sql
  57. --在emp表和dept表之间进行连接,优化程序使用索引来解析连接
  58. SET ECHO ON
  59. SET TIMING ON
  60. SET AUTOTRACE TRACEONLY
  61. SET PAGESIZE 1000

  62. @flush.sql

  63. SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno AND emp.deptno > 800;

  64. SET AUTOTRACE OFF
  65. SET TIMING OFF




点击(此处)折叠或打开

  1. --清理环境
  2. --ic_cleanup.sql
  3. SET ECHO ON
  4. DROP TABLE emp PURGE;
  5. DROP TABLE dept PURGE;
  6. DROP CLUSTER emp_dept INCLUDING TABLES;



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17013648/viewspace-1072937/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-02-06

  • 博文量
    1986
  • 访问量
    5642303