这边执行计划如下：
问题sql语句：
SELECT
t.row_id,
T.MATERIAL_CODE, --鐗╂枡缂栧彿
T.MATERIAL_DESCRITION, --鐗╂枡鎻忚堪
T.MATERIAL_TYPE, --鐗╂枡绫诲瀷
t.LAB_STUDIO,--瀹為獙瀹
t.created,--鍒涘缓鏃堕棿
TT.PRO_BAND, --鍝佺墝
'' PRO_BAND_name,
t.DEPARTMENT,
'' department_name,
t.BASIC_MTL,
'' basic_mtl_name,
'' product_line_code_name,
tt.PRODUCT_LIST,
--d.value_meaning product_line_code_name,
tt.IS_CRUST_FLAG,
TT.MARK_FLAG,
TT.MARK_DATE,
TT.PRODUCTION_START_DATE,
TT.PRODUCTION_END_DATE,
t.DELETE_FLAG,
T.PROD_LIFE_STATE,
T.PA_DATE_PLAN,
T.PA_DATE_ACTUAL,
T.EM_DATE_PLAN,
T.EM_DATE_ACTUAL,
tt.pro_use,
tt.kind_one,
t.created_by,
GREATEST(DECODE(T.LAST_UPD,
NULL,
TO_DATE('1900/01/01', 'YYYY/MM/DD'),
T.LAST_UPD),
DECODE(TT.LAST_UPD,
NULL,
TO_DATE('1900/01/01', 'YYYY/MM/DD'),
TT.LAST_UPD)) LAST_UPD --鏇存柊鏃ユ湡
FROM HM_MTL_GENERAL T,
HM_MTL_ADDITIONAL_DATA TT
WHERE (t.created_by is null or t.created_by!='HOPE') and t.row_id = tt.
material_id
;
HM_MTL_GENERAL row_id列的统计信息，唯一值较高适合创建索引，但是该列上已经创建了一个唯一索引PK_HM_MTL_GENERAL
COLUMN_NAME DATA_TYPE N NUM_DISTINCT DENSITY NUM_BUCKETS NUM_NULLS GLO USE SAMPLE_SIZE TO_CHAR(T.LAST_ANAL
-------------------- ------------------------------ - ------------ ---------- ----------- ---------- --- --- ----------- -------------------
ROW_ID VARCHAR2 N 2316682 4.3005E-07 254 0 YES NO 17685 2016-02-17 22:09:40
Where条件执行时间和结果
SYS@haiermdm3>Select count(material_id) from HAIERMDM.HM_MTL_ADDITIONAL_DATA;
COUNT(MATERIAL_ID)
------------------
1100968
Elapsed: 00:00:00.06
SYS@haiermdm3>set timing on
SYS@haiermdm3>Select count(row_id) from HAIERMDM.HM_MTL_GENERAL where created_by is null or created_by!='HOPE';
COUNT(ROW_ID)
-------------
2316922
Elapsed: 00:00:02.08
HM_MTL_GENERAL的表2319320行，
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN GLO USE SAMPLE_SIZE TO_CHAR(A.LAST_ANAL
---------------------------------------- ---------- ---------- ------------ ---------- ---------- ----------- --- --- ----------- -------------------
HM_MTL_GENERAL 2319320 142256 0 0 0 366 YES NO 2319320 2016-02-23 15:44:08
需要的行数已经占表HM_MTL_GENERAL 40%以上，数据库认为全表扫描更快，其为正确的执行计划。
SYS@haier3>select round(1100968/2316922,2)*100||'%' from dual;
ROU
---
47%
