分类目录归档：数据库

1.数据库版本

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

2.发现含有AND-EQUAL执行计划

SQL> SELECT 
  2  COUNT(TABXNPRESM1_.DETAIL_ID) AS X0_0_
  3    FROM QXTDEV1.TAB_XN_PRESMS TABXNPRESM0_, QXTDEV1.TAB_XN_PRESMS_DETAIL TABXNPRESM1_
  4  WHERE  (TABXNPRESM0_.COMPANY_ID = 346240)
  5     AND (TABXNPRESM0_.EMPLOYEE_ID = 0)
  6     AND (TABXNPRESM0_.PRE_TIME >= TO_DATE('2011/12/25', 'yyyy/mm/dd'))
  7     AND (TABXNPRESM0_.PRE_TIME < TO_DATE('2011/12/26', 'yyyy/mm/dd') + 1)
  8     AND ((TABXNPRESM0_.SEND_TYPE = 1) OR (TABXNPRESM0_.SEND_TYPE = 0))
  9     AND ((TABXNPRESM1_.RESULT = -1) OR (TABXNPRESM1_.RESULT = 0))
 10     AND (TABXNPRESM0_.PRE_ID = TABXNPRESM1_.PRE_ID);

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=1 Bytes=40)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TAB_XN_PRESMS_DETAIL'(Cost=3 Card=2 Bytes=30)
   3    2       NESTED LOOPS (Cost=11 Card=1 Bytes=40)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'TAB_XN_PRESMS' (Cost=8 Card=1 Bytes=25)
   5    4           AND-EQUAL
   6    5             INDEX (RANGE SCAN) OF 'IDX_XN_PRESMS_2' (NON-UNIQUE)
   7    5             INDEX (RANGE SCAN) OF 'IDX_XN_PRESMS_1' (NON-UNIQUE) (Cost=4 Card=638)
   8    3         INDEX (RANGE SCAN) OF 'IDX_XN_PRESMS_DETAIL' (NON-UNIQUE) (Cost=2 Card=2)



Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     272188  consistent gets
          0  physical reads
          0  redo size
        375  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

3.关于AND-EQUAL解释
If the WHERE clauses uses columns of many single-column indexes, then Oracle executes the statement by performing a range scan on each index to retrieve the rowids of the rows that satisfy each condition. Oracle then merges the sets of rowids to obtain a set of rowids of rows that satisfy all conditions. Oracle then accesses the table using these rowids.
Oracle can merge up to five indexes. If the WHERE clause uses columns of more than five single-column indexes, then Oracle merges five of them, accesses the table by rowid, and then tests the resulting rows to determine whether they satisfy the remaining conditions before returning them.
大概的意思是当where条件后面含有多个列的单列索引时(不超过5个)，会先得到每个条件的rowid，然后这些rowid进行merges，得到一个rowid的结果集，最后根据这些rowid取表中记录。

4.表/列/索引相关信息

--index和列信息
SQL> SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME
  2    FROM USER_IND_COLUMNS
  3   WHERE TABLE_NAME IN ('TAB_XN_PRESMS', 'TAB_XN_PRESMS_DETAIL');
 
INDEX_NAME                     TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ --------------------
IDX_XN_PRESMS_1                TAB_XN_PRESMS                  COMPANY_ID
IDX_XN_PRESMS_2                TAB_XN_PRESMS                  EMPLOYEE_ID
IDX_XN_PRESMS_3                TAB_XN_PRESMS                  PRE_TIME
PK_TAB_XN_PRESMS               TAB_XN_PRESMS                  PRE_ID
IDX_XN_PRESMS_4                TAB_XN_PRESMS                  SEND_TYPE
IDX_XN_PRESMS_DETAIL           TAB_XN_PRESMS_DETAIL           PRE_ID
IDX_XN_PRESMS_DETAIL_2         TAB_XN_PRESMS_DETAIL           SEND_TIME
PK_TAB_XN_PRESMS_DETAIL        TAB_XN_PRESMS_DETAIL           DETAIL_ID
 
8 rows selected

--index的统计信息
SQL> SELECT TABLE_NAME,
  2         INDEX_NAME,
  3         TO_CHAR(LAST_ANALYZED, 'yyyy-mm-dd hh24:mi:ss')
  4    FROM USER_INDEXES
  5   WHERE TABLE_NAME IN ('TAB_XN_PRESMS', 'TAB_XN_PRESMS_DETAIL');
 
TABLE_NAME                     INDEX_NAME                     TO_CHAR(LAST_ANALYZED,'YYYY-MM
------------------------------ ------------------------------ ------------------------------
TAB_XN_PRESMS                  IDX_XN_PRESMS_1                2011-12-29 09:25:32
TAB_XN_PRESMS                  IDX_XN_PRESMS_2                2011-12-29 09:25:35
TAB_XN_PRESMS                  IDX_XN_PRESMS_3                2011-12-29 09:25:39
TAB_XN_PRESMS                  IDX_XN_PRESMS_4                2011-12-29 09:25:21
TAB_XN_PRESMS_DETAIL           IDX_XN_PRESMS_DETAIL           2011-12-29 09:20:03
TAB_XN_PRESMS_DETAIL           IDX_XN_PRESMS_DETAIL_2         2011-12-29 09:20:01
TAB_XN_PRESMS                  PK_TAB_XN_PRESMS               2011-12-29 09:25:46
TAB_XN_PRESMS_DETAIL           PK_TAB_XN_PRESMS_DETAIL        2011-12-29 09:20:02
 
8 rows selected

--列的唯一度情况
SQL>  SELECT TABLE_NAME, COLUMN_NAME, NUM_DISTINCT
  2     FROM USER_TAB_COLS
  3    WHERE (TABLE_NAME, COLUMN_NAME) IN
  4          (SELECT TABLE_NAME, COLUMN_NAME
  5             FROM USER_IND_COLUMNS
  6            WHERE TABLE_NAME IN ('TAB_XN_PRESMS', 'TAB_XN_PRESMS_DETAIL'))
  7            ORDER BY table_name,NUM_DISTINCT DESC;
 
TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT
------------------------------ ------------------------------ ------------
TAB_XN_PRESMS                  PRE_ID                              1999270
TAB_XN_PRESMS                  PRE_TIME                            1342594
TAB_XN_PRESMS                  EMPLOYEE_ID                           10676
TAB_XN_PRESMS                  COMPANY_ID                             3136
TAB_XN_PRESMS                  SEND_TYPE                                10
TAB_XN_PRESMS_DETAIL           DETAIL_ID                           3863184
TAB_XN_PRESMS_DETAIL           PRE_ID                              1996872
TAB_XN_PRESMS_DETAIL           SEND_TIME                            437526
 
8 rows selected

通过这些信息可以得出：
1)统计信息是最新收集过的
2)因为有多个单列index，数据库为了使得cost最小，可能选择了不合适的index(IDX_XN_PRESMS_2[EMPLOYEE_ID]/IDX_XN_PRESMS_1[COMPANY_ID]),使得出现AND-EQUAL，从而逻辑读偏高。对于这个sql，应该使用唯一度比较高的IDX_XN_PRESMS_3[PRE_TIME]
3)也可以通过修改index，实现程序高效，但是考虑到会影响启动程序，在没有十足的把握之前遵守hint优先原则

5.增加hint提示

SQL> SELECT /*+ index(TABXNPRESM0_ IDX_XN_PRESMS_3) */
  2  COUNT(TABXNPRESM1_.DETAIL_ID) AS X0_0_
  3    FROM QXTDEV1.TAB_XN_PRESMS TABXNPRESM0_, QXTDEV1.TAB_XN_PRESMS_DETAIL TABXNPRESM1_
  4  WHERE  (TABXNPRESM0_.COMPANY_ID = 346240)
  5     AND (TABXNPRESM0_.EMPLOYEE_ID = 0)
  6     AND (TABXNPRESM0_.PRE_TIME >= TO_DATE('2011/12/25', 'yyyy/mm/dd'))
  7     AND (TABXNPRESM0_.PRE_TIME < TO_DATE('2011/12/26', 'yyyy/mm/dd') + 1)
  8     AND ((TABXNPRESM0_.SEND_TYPE = 1) OR (TABXNPRESM0_.SEND_TYPE = 0))
  9     AND ((TABXNPRESM1_.RESULT = -1) OR (TABXNPRESM1_.RESULT = 0))
 10     AND (TABXNPRESM0_.PRE_ID = TABXNPRESM1_.PRE_ID);

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=641 Card=1 Bytes=40)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TAB_XN_PRESMS_DETAIL'(Cost=3 Card=2 Bytes=30)
   3    2       NESTED LOOPS (Cost=641 Card=1 Bytes=40)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'TAB_XN_PRESMS' (Cost=638 Card=1 Bytes=25)
   5    4           INDEX (RANGE SCAN) OF 'IDX_XN_PRESMS_3' (NON-UNIQUE) (Cost=63 Card=22286)
   6    3         INDEX (RANGE SCAN) OF 'IDX_XN_PRESMS_DETAIL' (NON-UNIQUE) (Cost=2 Card=2)



Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1422  consistent gets
          0  physical reads
          0  redo size
        375  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

从这里可以看出，程序的逻辑读下降了很多(从272188下降到1422)，得到了优化效果，提高了程序执行效率
结合上篇:BITMAP CONVERSION FROM ROWIDS,总结一个经验，如果同时使用到了一个表的多个index，效率一般情况下不会太高。同时也给各位提个醒，index并非越多越好，有时候会适得其反(建立index时需要考虑情况)

最近两天，开发要求，清理掉某些表的部分数据，因为不能停业务，不能采用cast+rman方式实现.只能自己写脚本删除，在这些删除数据中，经过总结，共有三种类型:
1.删除单个表数据
删除a表以dealdate为范围的部分数据

DECLARE 
  P_SQL   VARCHAR2(300) := 
  'DELETE FROM a WHERE dealdate<to_date('||''''||
'2010-11-01 00:00:00'||''''||','||''''||'yyyy-mm-dd hh24:mi:ss'||''''||')';
  P_COUNT NUMBER := 1000;
BEGIN
  WHILE 1 = 1 LOOP
    EXECUTE IMMEDIATE P_SQL || ' and rownum <= :nu'
      USING P_COUNT;
    IF SQL%NOTFOUND THEN
      EXIT;
    END IF;
    COMMIT;
  END LOOP;
  COMMIT;
END;

2.删除两个关联表数据
tab_a,tab_b两个表通过共有的PRE_ID列关联，然后按照tab_a.ACCEPT_TIME列为条件删除两个表中数据,tab_b表中数据比tab_a多很多

DECLARE
  CURSOR CUR IS
    SELECT B.ROWID BID, A.ROWID AID
      FROM tab_a A, tab_b B
     WHERE A.PRE_ID = B.PRE_ID
       AND A.ACCEPT_TIME <
           TO_DATE('2010-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
     ORDER BY B.ROWID;
  V_COUNTER NUMBER;
BEGIN
  V_COUNTER := 0;
  FOR ROW IN CUR LOOP
    DELETE FROM tab_a WHERE ROWID = ROW.AID;
    DELETE FROM tab_b WHERE ROWID = ROW.BID;
    V_COUNTER := V_COUNTER + 1;
    IF (V_COUNTER >= 1000) THEN
      COMMIT;
      V_COUNTER := 0;
    END IF;
  END LOOP;
  COMMIT;
END;

3.删除某个表中重复列
删除tab_a表中的COMPANY_ID/PY_DES/PY_DES_Q/PY_TYPE/RELATE_ID列重复数据

DECLARE
  CURSOR CUR IS
    SELECT A.ROWID AID
      FROM tab_a A
     WHERE ROWID NOT IN (SELECT MAX(B.ROWID)
                           FROM tab_a B
                          WHERE A.COMPANY_ID = B.COMPANY_ID
                            AND A.PY_DES = B.PY_DES
                            AND A.PY_DES_Q = B.PY_DES_Q
                            AND A.PY_TYPE = B.PY_TYPE
                            AND A.RELATE_ID = B.RELATE_ID)
     ORDER BY A.ROWID;
  V_COUNTER NUMBER;
BEGIN
  V_COUNTER := 0;
  FOR ROW IN CUR LOOP
    DELETE FROM tab_a WHERE ROWID = ROW.AID;
    V_COUNTER := V_COUNTER + 1;
    IF (V_COUNTER >= 1000) THEN
      COMMIT;
      V_COUNTER := 0;
    END IF;
  END LOOP;
  COMMIT;
END;