表r_register的索引
PK_R_REGISTER Unique CARD_NO, REG_DATE
R_REGISTERPAYKIND Normal REG_DATE, REGLEVL_CODE, OPER_CODE, PAYKIND_CODE
R_REGISTER_INDEX1 Normal REG_DATE, DEPT_CODE表income_expenses的索引
INCOME_EXPENSES_INDEX3 Normal CARD_NO, OPER_CODE, ACC_DTIME
INCOME_EXPENSES_INDEX2 Normal BILL_DATE N
select /*+ index(a,PK_R_REGISTER)*/
distinct b.invoice_no invoice_no,
b.own_cost own_cost,
a.dept_name dept_name,
a.empl_code doct_code,
a.mcard_no mcard_no,
a.card_no card_no,
b.cost cost,
a.name name,
a.pact_name pact_name,
a.pact_code pact_code,
b.fee_date fee_Date,
b.oper_code fee_person_code,
a.reglevl_code reglevl_code
From (select sum(cost) cost,
sum(own_cost) own_cost,
card_no,
invoice_no,
acc_dtime fee_date,
bill_date,
oper_code
from income_expenses
where income_expenses.card_no > '-1'
group by invoice_no, card_no, acc_dtime, bill_date, oper_code) b,
r_register a
where a.card_no = b.card_no
and a.reg_date = b.bill_date
SELECT STATEMENT, GOAL = CHOOSE 耗费=10236 基数=163609 字节=25195786
SORT UNIQUE 耗费=10236 基数=163609 字节=25195786
HASH JOIN 耗费=6434 基数=163609 字节=25195786
VIEW 对象所有者=TLHIS 耗费=910 基数=163609 字节=10798194
SORT GROUP BY 耗费=910 基数=163609 字节=10470976
TABLE ACCESS BY GLOBAL INDEX ROWID 对象名称=INCOME_EXPENSES 耗费=10 基数=163609 字节=10470976
INDEX RANGE SCAN 对象名称=INCOME_EXPENSES_INDEX3 耗费=2 基数=29450
TABLE ACCESS BY GLOBAL INDEX ROWID 对象名称=R_REGISTER 耗费=826 基数=2964088 字节=260839744
INDEX FULL SCAN 对象名称=PK_R_REGISTER 耗费=26 基数=1
PK_R_REGISTER Unique CARD_NO, REG_DATE
R_REGISTERPAYKIND Normal REG_DATE, REGLEVL_CODE, OPER_CODE, PAYKIND_CODE
R_REGISTER_INDEX1 Normal REG_DATE, DEPT_CODE表income_expenses的索引
INCOME_EXPENSES_INDEX3 Normal CARD_NO, OPER_CODE, ACC_DTIME
INCOME_EXPENSES_INDEX2 Normal BILL_DATE N
select /*+ index(a,PK_R_REGISTER)*/
distinct b.invoice_no invoice_no,
b.own_cost own_cost,
a.dept_name dept_name,
a.empl_code doct_code,
a.mcard_no mcard_no,
a.card_no card_no,
b.cost cost,
a.name name,
a.pact_name pact_name,
a.pact_code pact_code,
b.fee_date fee_Date,
b.oper_code fee_person_code,
a.reglevl_code reglevl_code
From (select sum(cost) cost,
sum(own_cost) own_cost,
card_no,
invoice_no,
acc_dtime fee_date,
bill_date,
oper_code
from income_expenses
where income_expenses.card_no > '-1'
group by invoice_no, card_no, acc_dtime, bill_date, oper_code) b,
r_register a
where a.card_no = b.card_no
and a.reg_date = b.bill_date
SELECT STATEMENT, GOAL = CHOOSE 耗费=10236 基数=163609 字节=25195786
SORT UNIQUE 耗费=10236 基数=163609 字节=25195786
HASH JOIN 耗费=6434 基数=163609 字节=25195786
VIEW 对象所有者=TLHIS 耗费=910 基数=163609 字节=10798194
SORT GROUP BY 耗费=910 基数=163609 字节=10470976
TABLE ACCESS BY GLOBAL INDEX ROWID 对象名称=INCOME_EXPENSES 耗费=10 基数=163609 字节=10470976
INDEX RANGE SCAN 对象名称=INCOME_EXPENSES_INDEX3 耗费=2 基数=29450
TABLE ACCESS BY GLOBAL INDEX ROWID 对象名称=R_REGISTER 耗费=826 基数=2964088 字节=260839744
INDEX FULL SCAN 对象名称=PK_R_REGISTER 耗费=26 基数=1
这开始比较耗时
你强制用NRSTED LOOP来试试看
如何强制用NRSTED LOOP