SQL> select * from V$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise EditionRelease 10.2.0.4.0 - 64bi PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for 64-bit Windows: Version 10.2.0.4.0- Production NLSRTL Version 10.2.0.4.0 - Production
开发中的库，在做测试时，发现有1SQL比较消耗资源，我想把他优化一下。
SQL如下： SELECT r.OUT_VER_BEGIN_ID dataID FROM DMS_DATA_RELA r, DMS_OBJ o WHERE r.DELETE_FLAG = "0" AND r.RELA_TYPE_CODE ="parent" AND r.OUT_OBJ_CODE =o.OBJ_CODE AND o.DELETE_FLAG = "0" AND o.OPEN_STATE = "1" AND r.IN_OBJ_CODE ！= "o_in" START WITH r.IN_DATA_ID in （SELECT d.OUT_DATA_ID FROM DMS_DATA_RELA d where d.OUT_VER_BEGIN_ID = :1 and d.last_curent_flag = "1"） CONNECT BY r.IN_VER_BEGIN_ID = PRIORr.OUT_VER_BEGIN_ID
SQL> selectindex_name,column_name,table_name from dba_ind_columns where table_name="DMS_OBJ" order by index_name; INDEX_NAME COLUMN_NAME TABLE_NAME -------------------------------------------------- ------------------------------ PK_DMS_OBJ OBJ_ID DMS_OBJ
SQL> desc DMS_DATA_RELA Name Type Nullable Default Comments ------------------------------ ------------- -------- ------- -------- RELA_ID CHAR（32） IN_DATA_ID VARCHAR2（200） Y IN_DATA_NAME VARCHAR2（200） Y IN_DATA_SOURCE_ID VARCHAR2（200） Y IN_DATA_SOURCE_CODE VARCHAR2（200） Y IN_OBJ_CODE VARCHAR2（200） Y IN_VER_BEGIN_ID VARCHAR2（200） Y IN_VER_BEGIN_DATA_SOURCE_ID VARCHAR2（200） Y IN_VER_BEGIN_DATA_SOURCE_CODE VARCHAR2（64） Y IN_VER_BEGIN_DATA_OBJ_CODE VARCHAR2（64） Y OUT_DATA_ID VARCHAR2（200） Y OUT_DATA_NAME VARCHAR2（200） Y OUT_DATA_SOURCE_ID VARCHAR2（200） Y OUT_DATA_SOURCE_CODE VARCHAR2（200） Y OUT_OBJ_CODE VARCHAR2（200） Y OUT_VER_BEGIN_ID VARCHAR2（200） Y OUT_VER_BEGIN_DATA_SOURCE_ID VARCHAR2（200） Y OUT_VER_BEGIN_DATA_SOURCE_CODE VARCHAR2（64） Y OUT_VER_BEGIN_DATA_OBJ_CODE VARCHAR2（64） Y RELA_TYPE_CODE VARCHAR2（200） Y MIDDLE_OBJ_CODE VARCHAR2（64） Y LAST_CURENT_FLAG CHAR（1） Y CREATE_USER_NAME VARCHAR2（200） Y CREATE_USER_REAL_NAME VARCHAR2（200） Y CREATE_TIME TIMESTAMP（6） Y UPDATE_USER_NAME VARCHAR2（200） Y UPDATE_USER_REAL_NAME VARCHAR2（200） Y UPDATE_TIME TIMESTAMP（6） Y DELETE_FLAG CHAR（1） Y ORDER_NUM NUMBER（10） Y
有人问索引状态，也一并附上： SQL> select table_name, index_name,index_type,statusfrom user_indexes where table_name="DMS_DATA_RELA"; TABLE_NAME INDEX_NAME INDEX_TYPE STATUS ------------------------------------------------------------ --------------------------- -------- DMS_DATA_RELA OUT_VER_BEGIN_ID_INDEX NORMAL VALID DMS_DATA_RELA IN_DATA_ID_INDEX NORMAL VALID DMS_DATA_RELA IN_DATA_NAME_INDEX NORMAL VALID DMS_DATA_RELA IN_DATA_SOURCE_CODE_INDEX NORMAL VALID DMS_DATA_RELA IN_DATA_SOURCE_ID_INDEX NORMAL VALID DMS_DATA_RELA IN_OBJ_CODE_INDEX NORMAL VALID DMS_DATA_RELA IN_VER_BEGIN_ID_INDEX NORMAL VALID DMS_DATA_RELA OUT_DATA_NAME_INDEX NORMAL VALID DMS_DATA_RELA OUT_DATA_SOURCE_ID_INDEX NORMAL VALID DMS_DATA_RELA OUT_OBJ_CODE_INDEX NORMAL VALID DMS_DATA_RELA RELA_TYPE_CODE_INDEX NORMAL VALID DMS_DATA_RELA DELETE_FLAG_INDEX NORMAL VALID DMS_DATA_RELA LAST_CURENT_FLAG_INDEX NORMAL VALID DMS_DATA_RELA CREATE_TIME_INDEX FUNCTION-BASED NORMAL VALID DMS_DATA_RELA OUT_DATA_ID_INDEX NORMAL VALID DMS_DATA_RELA PK_DMS_DATA_RELA NORMAL VALID 16 rows selected
我试过物化视图
CREATE MATERIALIZED VIEW mv_dms_ddr REFRESH force ON demand WITH ROWID enable query rewrite AS SELECT r.OUT_VER_BEGIN_ID, r.IN_DATA_ID,r.IN_VER_BEGIN_ID FROM DMS_DATA_RELA r, DMS_OBJ o WHERE r.DELETE_FLAG = "0" AND r.RELA_TYPE_CODE = "parent" AND r.IN_OBJ_CODE ！= "o_in" AND r.OUT_OBJ_CODE =o.OBJ_CODE AND o.DELETE_FLAG = "0" AND o.OPEN_STATE = "1" 改写SQL SELECT OUT_VER_BEGIN_ID dataID FROM mv_dms_ddr START WITH IN_DATA_ID in （SELECT d.OUT_DATA_ID FROM DMS_DATA_RELA d where d.OUT_VER_BEGIN_ID = :1 and d.last_curent_flag = "1"） CONNECT BY PRIOR OUT_VER_BEGIN_ID =IN_VER_BEGIN_ID （没用fast更新），带入变量试了，第1次慢，后面快，执行时间大概是3秒多了。有提升，但不是很理想，而且表更新也频繁，刷新方式没做on commit。（我猜测在数据库变动频繁的情况下，是不是这个查询还是会慢，这个想法还没验证）
易网时代-编程资源站