各位大侠,这里我出现了一个关于物化视图方面的问题,还请大侠们多多指教!
现在我需要查询使用物化视图,而不是基表.
如果我不使用绑定变量的话,计划会走物化视图
但是如果使用绑定变量的话,计划就走基表,不走物化视图了,这不是我想要的结果
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bi
PL/SQL Release 10.1.0.3.0 - Production
CORE10.1.0.3.0Production
TNS for Linux: Version 10.1.0.3.0 - Production
NLSRTL Version 10.1.0.3.0 - Production
SQL> show parameter REWRITE
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled
stringTRUE
query_rewrite_integrity
stringTRUSTED
SQL> SELECT X.KSPPINM NAME, Y.KSPPSTVL VALUE, Y.KSPPSTDF ISDEFAULT,
2 DECODE (BITAND (Y.KSPPSTVF, 7),
3
1, 'MODIFIED',
4
4, 'SYSTEM_MOD',
5
'FALSE'
6
) ISMOD,
7 DECODE (BITAND (Y.KSPPSTVF, 2), 2, 'TRUE', 'FALSE') ISADJ
8FROM SYS.X$KSPPI X, SYS.X$KSPPCV Y
9 WHERE X.INST_ID = USERENV ('Instance')
10 AND Y.INST_ID = USERENV ('Instance')
11 AND X.INDX = Y.INDX
12 AND X.KSPPINM LIKE '%_&PARA%'
13ORDER BY TRANSLATE (X.KSPPINM, '_', '')
14/
Enter value for para: optim_peek
old12:AND X.KSPPINM LIKE '%_&PARA%'
new12:AND X.KSPPINM LIKE '%_optim_peek%'
NAME
VALUE
ISDEFAULT ISMODISADJ
---------------------------------------- ------------------------- --------- ---------- -----
_optim_peek_user_binds
TRUE
TRUEFALSEFALSE
基表:T_BILL_HEADER,T_BILL_DETAIL
物化视图:T_BILL_HEADER_DETAIL 定义如下
(BUILD IMMEDIATE REFRESH FAST ON COMMIT WITH PRIMARY KEY ENABLE QUERY REWRITE AS SELECT .... FROM T_BILL_HEADER, T_BILL_DETAIL
WHERE T_BILL_HEADER.BSEQID = T_BILL_DETAIL.BSEQID)
使用绑定变量
SELECT USERNAME, CHECKERNAME COUNTNUM
FROM T_BILL_HEADER, T_BILL_DETAIL, T_BILL_PAGE
WHERE (T_BILL_HEADER.BSEQID = T_BILL_DETAIL.BSEQID)
AND (T_BILL_PAGE.BSEQID = T_BILL_DETAIL.BSEQID)
AND (T_BILL_PAGE.PAGENO = T_BILL_DETAIL.PAGENO)
AND (T_BILL_HEADER.DEPTID = 2)
AND (T_BILL_HEADER.TIMEVALIDATED >= :ADT_START)
AND (T_BILL_HEADER.TIMEVALIDATED = TO_DATE('2006-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
AND (T_BILL_HEADER.TIMEVALIDATED <= TO_DATE('2006-05-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS'))
计划是:Plan
SELECT STATEMENTALL_ROWSCost: 4,916Bytes: 9,483,334Cardinality: 152,957
5 HASH JOINCost: 4,916Bytes: 9,483,334Cardinality: 152,957
3 PARTITION RANGE SINGLECost: 1,513Bytes: 7,665,600Cardinality: 159,700Partition #: 2Partitions accessed #6
2 PARTITION HASH SINGLECost: 1,513Bytes: 7,665,600Cardinality: 159,700Partition #: 3Partitions accessed #1
1 MAT_VIEW REWRITE ACCESS FULL MAT_VIEW REWRITE POWERERP.T_BILL_HEADER_DETAIL Cost: 1,513Bytes: 7,665,600Cardinality: 159,700Partition #: 4Partitions accessed #6
4 TABLE ACCESS FULL TABLE POWERERP.T_BILL_PAGE Cost: 1,196Bytes: 19,655,342Cardinality: 1,403,953
请问如何解决
|
