Home » RDBMS Server » Server Administration » Why dont bind variable use MV.
Why dont bind variable use MV. [message #285602] Wed, 05 December 2007 02:25
alantany
Messages: 115
Registered: July 2007
Senior Member

Hello,all:
I build a MV like :
(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)

When I issued a sql like :

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 >= 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'))

Then plan will choose MV:
  SELECT STATEMENT  ALL_ROWSCost: 4,916  Bytes: 9,483,334  Cardinality: 152,957                                 
        5 HASH JOIN  Cost: 4,916  Bytes: 9,483,334  Cardinality: 152,957                          
                3 PARTITION RANGE SINGLE  Cost: 1,513  Bytes: 7,665,600  Cardinality: 159,700  Partition #: 2  Partitions accessed #6               
                        2 PARTITION HASH SINGLE  Cost: 1,513  Bytes: 7,665,600  Cardinality: 159,700  Partition #: 3  Partitions accessed #1        
                                1 MAT_VIEW REWRITE ACCESS FULL MAT_VIEW REWRITE POWERERP.T_BILL_HEADER_DETAIL Cost: 1,513  Bytes: 7,665,600  Cardinality: 159,700  Partition #: 4  Partitions accessed #6
                4 TABLE ACCESS FULL TABLE POWERERP.T_BILL_PAGE Cost: 1,196  Bytes: 19,655,342  Cardinality: 1,403,953                  

When I isued a binded variable SQL:
  
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 <= :ADT_END);

Then plan will not choose MV:
SELECT STATEMENT  ALL_ROWSCost: 39,433  Bytes: 177,384  Cardinality: 4,668                                          
        8 FILTER                                 
                7 NESTED LOOPS  Cost: 39,433  Bytes: 177,384  Cardinality: 4,668                          
                        4 NESTED LOOPS  Cost: 31,937  Bytes: 116,976  Cardinality: 4,874                  
                                1 TABLE ACCESS FULL TABLE POWERERP.T_BILL_HEADER Cost: 2,442  Bytes: 12,087  Cardinality: 711         
                                3 TABLE ACCESS BY INDEX ROWID TABLE POWERERP.T_BILL_DETAIL Cost: 45  Bytes: 49  Cardinality: 7         
                                        2 INDEX RANGE SCAN INDEX POWERERP.XIF74T_BILL_DETAIL Cost: 3  Cardinality: 18  
                        6 TABLE ACCESS BY INDEX ROWID TABLE POWERERP.T_BILL_PAGE Cost: 2  Bytes: 14  Cardinality: 1                  
                                5 INDEX UNIQUE SCAN INDEX (UNIQUE) POWERERP.XPKT_BILL_PAGE Cost: 1  Cardinality: 1  

Could somebody tell me why?
Regards
Alan
Previous Topic: Character Set
Next Topic: corrupt block header error
Goto Forum:
  


Current Time: Fri Dec 09 08:04:12 CST 2016

Total time taken to generate the page: 0.11608 seconds