数据库是oracle9，
执行下面的语句：
update MAT_BUY_PLAN_B set supplier_cod='dd' where id='ddd' and supplier_cod is null;
其中，ID是关键字，执行速度慢，通过sqlmoniter跟踪发现分析表MAT_BUY_PLAN_B索引的时间超长接近1分钟，为什么会执行下面的内容，有什么办法不执行吗？！
Timestamp: 13:57:23.234
SELECT 1 AS scope, 'ROWID' AS column_name, -8 AS data_type,
'ROWID' AS type_name, 0 AS column_size, 0 AS buffer_length,
0 AS decimal_digits, 2 AS pseudo_column
FROM DUAL
WHERE :1 = 1
UNION
SELECT 2 AS scope,
t.column_name,
DECODE (t.data_type, 'CHAR', 1, 'VARCHAR2', 12, 'NUMBER', 3,
'LONG', -1, 'DATE', 93, 'RAW', -3, 'LONG RAW', -4, 1111)
AS data_type,
t.data_type AS type_name,
DECODE (t.data_precision, null, t.data_length, t.data_precision)
AS column_size,
0 AS buffer_length,
t.data_scale AS decimal_digits,
1 AS pseudo_column
FROM all_tab_columns t, all_ind_columns i
WHERE :2 = 1
AND t.table_name = :3
AND t.owner like :4 escape '/'
AND t.nullable != :5
AND t.owner = i.table_owner
AND t.table_name = i.table_name
AND t.column_name = i.column_name
:1 = 1
:2 = 1
:3 = 'MAT_BUY_PLAN_B'
:4 = '%'
:5 = 'X'
----------------------------------
Timestamp: 13:57:23.296
SELECT NULL AS table_cat,
c.owner AS table_schem,
c.table_name,
c.column_name,
c.position AS key_seq,
c.constraint_name AS pk_name
FROM all_cons_columns c, all_constraints k
WHERE k.constraint_type = 'P'
AND k.table_name = :1
AND k.owner like :2 escape '/'
AND k.constraint_name = c.constraint_name
AND k.table_name = c.table_name
AND k.owner = c.owner
ORDER BY column_name
:1 = 'MAT_BUY_PLAN_B'
:2 = '%'
----------------------------------
Timestamp: 13:57:29.796
analyze table MAT_BUY_PLAN_B estimate statistics
----------------------------------
Timestamp: 13:57:29.859
select null as table_cat,
owner as table_schem,
table_name,
0 as NON_UNIQUE,
null as index_qualifier,
null as index_name, 0 as type,
0 as ordinal_position, null as column_name,
null as asc_or_desc,
num_rows as cardinality,
blocks as pages,
null as filter_condition
from all_tables
where table_name = 'MAT_BUY_PLAN_B'
union
select null as table_cat,
i.owner as table_schem,
i.table_name,
decode (i.uniqueness, 'UNIQUE', 0, 1),
null as index_qualifier,
i.index_name,
1 as type,
c.column_position as ordinal_position,
c.column_name,
null as asc_or_desc,
i.distinct_keys as cardinality,
i.leaf_blocks as pages,
null as filter_condition
from all_indexes i, all_ind_columns c
where i.table_name = 'MAT_BUY_PLAN_B'
and i.uniqueness = 'UNIQUE'
and i.index_name = c.index_name
and i.table_owner = c.table_owner
and i.table_name = c.table_name
and i.owner = c.index_owner
order by non_unique, type, index_name, ordinal_position |
发表于 2012-7-5 15:04:12
楼主