oracle sql 笔记



SQL> conn / as sysdba
Connected.
SQL> drop user puzzle cascade;

User dropped.

SQL> create user puzzle identified by puzzle;

User created.

SQL> grant create session,resource to puzzle;

Grant succeeded.

SQL> create table puzzle.employees as select * from hr.employees;

Table created.

SQL> create index puzzle.idx_emp_id on puzzle.employees(employee_id);

Index created.

SELECT DT.BLOCKS, DT.NUM_ROWS, DT.AVG_ROW_LEN, DI.CLUSTERING_FACTOR
  FROM DBA_TABLES DT, DBA_INDEXES DI
 WHERE DT.TABLE_NAME = 'EMPLOYEES'
   AND DT.TABLE_NAME = DI.TABLE_NAME
   AND DT.OWNER = 'PUZZLE'
  6     AND DI.index_name = 'IDX_EMP_ID';

    BLOCKS   NUM_ROWS AVG_ROW_LEN CLUSTERING_FACTOR
---------- ---------- ----------- -----------------
        3

SQL> l
  1  SELECT DT.BLOCKS, DT.NUM_ROWS, DT.AVG_ROW_LEN, DI.CLUSTERING_FACTOR
  2    FROM DBA_TABLES DT, DBA_INDEXES DI
  3   WHERE DT.TABLE_NAME = 'EMPLOYEES'
  4 AND DT.TABLE_NAME = DI.TABLE_NAME
  5 AND DT.OWNER = 'PUZZLE'
  6* AND DI.index_name = 'IDX_EMP_ID'
EXPLAIN PLAN FOR SELECT * FROM PUZZLE.EMPLOYEES WHERE EMPLOYEE_ID=205;

Explained.
--表新创建的，没有统计信息，oracle采用dynamic sampling
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
Plan hash value: 2126936620

------------------------------------------------------------------------------------------
| Id  | Operation      | Name  | Rows  | Bytes | Cost (%CPU)| Time  |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |   |     1 |   133 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES  |     1 |   133 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN     | IDX_EMP_ID |     1 |  |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPLOYEE_ID"=205)

Note
-----
   - dynamic sampling used for this statement (level=2)

18 rows selected.

--用analyze分析表，这个的替换工具为dbms_stats包
SQL> ANALYZE TABLE PUZZLE.EMPLOYEES ESTIMATE STATISTICS;

Table analyzed.
--已经有了统计信息，行数，平均行长度等等
SELECT DT.BLOCKS, DT.NUM_ROWS, DT.AVG_ROW_LEN, DI.CLUSTERING_FACTOR
  FROM DBA_TABLES DT, DBA_INDEXES DI
 WHERE DT.TABLE_NAME = 'EMPLOYEES'
   AND DT.TABLE_NAME = DI.TABLE_NAME
   AND DT.OWNER = 'PUZZLE'
  6     AND DI.index_name = 'IDX_EMP_ID';

    BLOCKS   NUM_ROWS AVG_ROW_LEN CLUSTERING_FACTOR
---------- ---------- ----------- -----------------
  5   107        71    3

SQL> EXPLAIN PLAN FOR SELECT * FROM PUZZLE.EMPLOYEES WHERE EMPLOYEE_ID=205;

Explained.
--使用了索引idx_emp_id
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
Plan hash value: 2126936620

------------------------------------------------------------------------------------------
| Id  | Operation      | Name  | Rows  | Bytes | Cost (%CPU)| Time  |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |   |     1 |    62 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES  |     1 |    62 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN     | IDX_EMP_ID |     1 |  |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPLOYEE_ID"=205)

14 rows selected.
--插入1000条employee_id为205的数据
SQL> BEGIN
  2  FOR I IN 1..1000 LOOP
  3  INSERT INTO PUZZLE.EMPLOYEES SELECT * FROM HR.EMPLOYEES WHERE EMPLOYEE_ID=205;
  4  END LOOP;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL> EXPLAIN PLAN FOR SELECT * FROM PUZZLE.EMPLOYEES WHERE EMPLOYEE_ID=205;

Explained.
--此时查询计划不变
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 2126936620

------------------------------------------------------------------------------------------
| Id  | Operation      | Name  | Rows  | Bytes | Cost (%CPU)| Time  |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |   |     1 |    62 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES  |     1 |    62 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN     | IDX_EMP_ID |     1 |  |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPLOYEE_ID"=205)

14 rows selected.
--再次收集统计信息
SQL> ANALYZE TABLE PUZZLE.EMPLOYEES ESTIMATE STATISTICS;

Table analyzed.

SELECT DT.BLOCKS, DT.NUM_ROWS, DT.AVG_ROW_LEN, DI.CLUSTERING_FACTOR
  FROM DBA_TABLES DT, DBA_INDEXES DI
 WHERE DT.TABLE_NAME = 'EMPLOYEES'
   AND DT.TABLE_NAME = DI.TABLE_NAME
   AND DT.OWNER = 'PUZZLE'
  6     AND DI.index_name = 'IDX_EMP_ID';

    BLOCKS   NUM_ROWS AVG_ROW_LEN CLUSTERING_FACTOR
---------- ---------- ----------- -----------------
 16  1107        74   15

SQL> EXPLAIN PLAN FOR SELECT * FROM PUZZLE.EMPLOYEES WHERE EMPLOYEE_ID=205;

Explained.
--查询计划仍然使用索引
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 2126936620

------------------------------------------------------------------------------------------
| Id  | Operation      | Name  | Rows  | Bytes | Cost (%CPU)| Time  |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |   |    10 |   640 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES  |    10 |   640 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN     | IDX_EMP_ID |    10 |  |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPLOYEE_ID"=205)

14 rows selected.
--对employee_id收集直方图信息
SQL> ANALYZE TABLE PUZZLE.EMPLOYEES ESTIMATE STATISTICS FOR COLUMNS EMPLOYEE_ID;

Table analyzed.

SELECT DT.BLOCKS, DT.NUM_ROWS, DT.AVG_ROW_LEN, DI.CLUSTERING_FACTOR
  FROM DBA_TABLES DT, DBA_INDEXES DI
 WHERE DT.TABLE_NAME = 'EMPLOYEES'
   AND DT.TABLE_NAME = DI.TABLE_NAME
   AND DT.OWNER = 'PUZZLE'
  6     AND DI.index_name = 'IDX_EMP_ID';

    BLOCKS   NUM_ROWS AVG_ROW_LEN CLUSTERING_FACTOR
---------- ---------- ----------- -----------------
 16  1107        74   15

SQL> EXPLAIN PLAN FOR SELECT * FROM PUZZLE.EMPLOYEES WHERE EMPLOYEE_ID=205;

Explained.
--计划采用全表扫描，因为虽然有索引但是205的数据占了很大一部分，所以全表
--扫描性能更好
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |   987 | 63168 |     6 (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |   987 | 63168 |     6 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMPLOYEE_ID"=205)

13 rows selected.

SQL>
--ANALYZE 替换工具为https://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_stats.htm#i1035428