DB:10GR2
OS:AIX5.3
insert 后面的内容(select部分仅需要25秒)，但是整个语句却需要几个小时，为什么会这样呢？
alter table sta_rb_acct_yldnologging;后做的插入操作，插入过程中并没有发现锁等异常情况
另，该表所在表空间也正常，free空间很多，请大虾赐教为何，谢谢！
sta_rb_acct_yld共有40多个字段，特抽出几个字段插入数据
insert /*+append*/into sta_rb_acct_yld (INTERNAL_KEY,
BRANCH,
CCY,
CLIENT_NO,
CERTIFICATE_NO,
CLIENT_SHORT,
ACCT_OPEN_DATE,
ACCT_STATUS,
ACCT_NO ,
ACCT_TYPE ,
DEPOSIT_TYPE,
OD_FACILITY ,
ACTUAL_BAL,
ACCT_CLOSE_DATE,
BASE_ACCT_NO,
ACCT_NAME,
SYM_RUN_DATE)
selectINTERNAL_KEY,
BRANCH,
CCY,
CLIENT_NO,
CERTIFICATE_NO,
CLIENT_SHORT,
ACCT_OPEN_DATE,
ACCT_STATUS,
ACCT_NO ,
ACCT_TYPE ,
DEPOSIT_TYPE,
OD_FACILITY ,
sum(BAL),
ACCT_CLOSE_DATE,
BASE_ACCT_NO,
ACCT_NAME,
SYM_RUN_DATE from (
select INTERNAL_KEY,
BRANCH,
CCY,
CLIENT_NO,
CERTIFICATE_NO,
CLIENT_SHORT,
ACCT_OPEN_DATE,
ACCT_STATUS,
ACCT_NO ,
ACCT_TYPE ,
DEPOSIT_TYPE,
OD_FACILITY ,
LEDGER_BAL,
-ACTUAL_BAL bal,
ACCT_CLOSE_DATE,
BASE_ACCT_NO,
ACCT_NAME,
SYM_RUN_DATE from sta_rb_acct where sym_run_date=to_date('20081024','yyyymmdd')
union all
SELECT /*+use_hash(tmp acct)*/acct.internal_key, acct.branch, acct.ccy,
acct.client_no, acct.certificate_no, acct.client_short,
acct.acct_open_date, acct.acct_status, acct.acct_no,
acct.acct_type, acct.deposit_type, acct.od_facility,
acct.ledger_bal, b.tran_amount bal,
acct.acct_close_date, acct.base_acct_no,
acct.acct_name, to_date('20081024','yyyymmdd') sym_run_date
FROM sta_bi_yld_nds b,
sta_rb_acct acct,
(SELECT base_acct_no, ccy,
MIN (a.internal_key) internal_key
FROM sta_rb_acct a
WHERE a.sym_run_date = to_date('20081024','yyyymmdd')
GROUP BY base_acct_no, ccy) tmp
WHERE acct.internal_key = tmp.internal_key
AND tmp.base_acct_no = b.nds_account_no
AND tmp.ccy = b.tran_ccy
AND TO_DATE (b.tran_day, 'yyyy-mm-dd') = to_date('20081024','yyyymmdd')
AND b.deposit_start_dateto_date('20081024','yyyymmdd')
AND acct.sym_run_date = to_date('20081024','yyyymmdd'))
group by INTERNAL_KEY,
BRANCH,
CCY,
CLIENT_NO,
CERTIFICATE_NO,
CLIENT_SHORT,
ACCT_OPEN_DATE,
ACCT_STATUS,
ACCT_NO ,
ACCT_TYPE ,
DEPOSIT_TYPE,
OD_FACILITY ,
ACCT_CLOSE_DATE,
BASE_ACCT_NO,
ACCT_NAME,
SYM_RUN_DATE;
|
