隐式转换可能会禁用物化视图的PCT特性

一生只为爱 · 发表于 2010-6-3 10:45

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jun 2 12:51:37 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options

SQL> CONN YSP/YSP
Connected.
SQL> SHOW PARAMETER QUERY_REWRITE

NAME                               TYPE                VALUE
------------------------------------ ---------------------- ------------------------------
query_rewrite_enabled             string                TRUE
query_rewrite_integrity             string                enforced
SQL> CREATE TABLE T(ID INT,TIME DATE)
  2  PARTITION BY RANGE(TIME)
  3  (
  4  PARTITION P1 VALUES LESS THAN(TO_DATE('2008-01-01','YYYY-MM-DD')),
  5  PARTITION P2 VALUES LESS THAN(TO_DATE('2009-01-01','YYYY-MM-DD')),
  6  PARTITION P3 VALUES LESS THAN(TO_DATE('2010-01-01','YYYY-MM-DD')),
  7  PARTITION P4 VALUES LESS THAN(TO_DATE('2011-01-01','YYYY-MM-DD'))
  8  )
  9  /

Table created.

SQL> INSERT INTO T SELECT ROWNUM,SYSDATE-ROWNUM FROM ALL_OBJECTS;

50505 rows created.

SQL> COMMIT;

Commit complete.

SQL> CREATE MATERIALIZED VIEW LOG ON T WITH ROWID,SEQUENCE(ID,TIME) INCLUDING NEW VALUES;

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW MV_T
  2  BUILD IMMEDIATE
  3  REFRESH FAST
  4  ENABLE QUERY REWRITE
  5  AS
  6  SELECT TIME,COUNT(1) FROM T GROUP BY TIME;

Materialized view created.

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(USER);

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(1) FROM T PARTITION (P1);

  COUNT(1)
----------
   49622

SQL> SELECT COUNT(1) FROM T PARTITION (P2);

  COUNT(1)
----------
   366

SQL> SELECT COUNT(1) FROM T PARTITION (P3);

  COUNT(1)
----------
   365

SQL> SELECT COUNT(1) FROM T PARTITION (P4);

  COUNT(1)
----------
   152

SQL> SET AUTOT TRACEONLY EXPLAIN
SQL> SELECT TIME,COUNT(1) FROM T WHERE TIME >'2007-01-01' AND TIME<'2007-01-10';
SELECT TIME,COUNT(1) FROM T WHERE TIME >'2007-01-01' AND TIME<'2007-01-10'
   *
ERROR at line 1:
ORA-00937: not a single-group group function

SQL> SELECT TIME,COUNT(1) FROM T WHERE TIME >'2007-01-01' AND TIME<'2007-01-10' GROUP BY TIME;

Execution Plan
----------------------------------------------------------
Plan hash value: 2513640124

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |    |    9 | 99 | 30 (7)| 00:00:01 |
|*  1 |  FILTER                      |    |    |    |          |       |
|*  2 | MAT_VIEW REWRITE ACCESS FULL| MV_T |    9 | 99 | 30 (7)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(TO_DATE('2007-01-01')<TO_DATE('2007-01-10'))
2 - filter("MV_T"."TIME">'2007-01-01' AND "MV_T"."TIME"<'2007-01-10')

这里使用了查询重写。

SQL> SET AUTOT OFF
SQL> DELETE FROM T PARTITION(P4) WHERE ROWNUM<=10;

10 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> COL MVIEW_NAME FORMAT A10
SQL> SELECT MVIEW_NAME,STALENESS FROM USER_MVIEWS;

MVIEW_NAME STALENESS
---------- --------------------------------------
MV_T    NEEDS_COMPILE

SQL> SET AUTOT TRACEONLY EXPLAIN
SQL> SELECT TIME,COUNT(1) FROM T WHERE TIME >'2007-01-01' AND TIME<'2007-01-10' GROUP BY TIME;

Execution Plan
----------------------------------------------------------
Plan hash value: 1232665453

---------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |    |    9 | 72 | 58 (6)| 00:00:01 |    |    |
| 1 |  HASH GROUP BY          |    |    9 | 72 | 58 (6)| 00:00:01 |    |    |
|*  2 | FILTER                |    |    |    |          |       |    |    |
| 3 | PARTITION RANGE ITERATOR|    |    9 | 72 | 57 (4)| 00:00:01 | KEY | KEY |
|*  4 |    TABLE ACCESS FULL    | T |    9 | 72 | 57 (4)| 00:00:01 | KEY | KEY |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(TO_DATE('2007-01-01')<TO_DATE('2007-01-10'))
4 - filter("TIME">'2007-01-01' AND "TIME"<'2007-01-10')

删除数据后，没有利用到PCT特性，使用了全表扫描。

SQL> EXEC DBMS_MVIEW.REFRESH('MV_T','F');

PL/SQL procedure successfully completed.

SQL> SELECT TIME,COUNT(1) FROM T WHERE TIME >'2007-01-01' AND TIME<'2007-01-10' GROUP BY TIME;

Execution Plan
----------------------------------------------------------
Plan hash value: 2513640124

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |    |    9 | 99 | 30 (7)| 00:00:01 |
|*  1 |  FILTER                      |    |    |    |          |       |
|*  2 | MAT_VIEW REWRITE ACCESS FULL| MV_T |    9 | 99 | 30 (7)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(TO_DATE('2007-01-01')<TO_DATE('2007-01-10'))
2 - filter("MV_T"."TIME">'2007-01-01' AND "MV_T"."TIME"<'2007-01-10')

刷新后，ORACLE又用到了查询重写。

SQL> SET AUTOT OFF
SQL> SELECT MVIEW_NAME,STALENESS FROM USER_MVIEWS;

MVIEW_NAME STALENESS
---------- --------------------------------------
MV_T    FRESH

SQL> ALTER TABLE T DROP PARTITION P4;

Table altered.

SQL> SELECT MVIEW_NAME,STALENESS FROM USER_MVIEWS;

MVIEW_NAME STALENESS
---------- --------------------------------------
MV_T    NEEDS_COMPILE

SQL> SET AUTOT TRACEONLY EXPLAIN

SQL> SELECT TIME,COUNT(1) FROM T WHERE TIME >'2007-01-01' AND TIME<'2007-01-10' GROUP BY TIME;

Execution Plan
----------------------------------------------------------
Plan hash value: 2513640124

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |    |    9 | 99 | 30 (7)| 00:00:01 |
|*  1 |  FILTER                      |    |    |    |          |       |
|*  2 | MAT_VIEW REWRITE ACCESS FULL| MV_T |    9 | 99 | 30 (7)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(TO_DATE('2007-01-01')<TO_DATE('2007-01-10'))
2 - filter("MV_T"."TIME">'2007-01-01' AND "MV_T"."TIME"<'2007-01-10' AND
            ("MV_T"."TIME"<TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR
            "MV_T"."TIME">=TO_DATE(' 2011-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

DDL语句可以用到PCT特性。

不知道为什么DML没有用到。

[ 本帖最后由一生只为爱于 2010-6-3 23:58 编辑 ]

yangtingkun · 发表于 2010-6-3 11:19

试试将DELETE中的PARTITION(partition_name)改为指定查询条件

huanhuanlove · 发表于 2010-6-3 11:33

学习学习。。。。

一生只为爱 · 发表于 2010-6-3 12:50

SQL> DROP TABLE T PURGE;

Table dropped.

SQL> DROP MATERIALIZED VIEW MV_T;

Materialized view dropped.

SQL> SELECT TNAME FROM TAB;

TNAME
------------------------------------------------------------
T_EXP
TEST
TEMP_STATS
MV_CAPABILITIES_TABLE
TEST3
YSP
SYS_TEMP_FBT

7 rows selected.

SQL>  CREATE TABLE T(ID INT,TIME DATE)
  2 PARTITION BY RANGE(TIME)
  3 (
  4    PARTITION P1 VALUES LESS THAN(TO_DATE('2008-01-01','YYYY-MM-DD')),
  5    PARTITION P2 VALUES LESS THAN(TO_DATE('2009-01-01','YYYY-MM-DD')),
  6    PARTITION P3 VALUES LESS THAN(TO_DATE('2010-01-01','YYYY-MM-DD')),
  7    PARTITION P4 VALUES LESS THAN(TO_DATE('2011-01-01','YYYY-MM-DD'))
  8 )
  9  /

Table created.

SQL> INSERT INTO T SELECT ROWNUM,SYSDATE-ROWNUM FROM ALL_OBJECTS;

50474 rows created.

SQL> COMMIT;

Commit complete.

SQL> CREATE MATERIALIZED VIEW LOG ON T WITH ROWID,SEQUENCE(ID,TIME) INCLUDING NEW VALUES;

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW MV_T
  2  BUILD IMMEDIATE
  3  REFRESH FAST
  4  ENABLE QUERY REWRITE
  5  AS
  6  SELECT TIME,COUNT(1) FROM T GROUP BY TIME;

Materialized view created.

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(USER);

PL/SQL procedure successfully completed.

SQL> SET AUTOT TRACEONLY EXPLAIN
SQL> SELECT TIME,COUNT(1) FROM T WHERE TIME >'2007-01-01' AND TIME<'2007-01-10' GROUP BY TIME;

Execution Plan
----------------------------------------------------------
Plan hash value: 2513640124

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |    |    9 | 99 | 30 (7)| 00:00:01 |
|*  1 |  FILTER                      |    |    |    |          |       |
|*  2 | MAT_VIEW REWRITE ACCESS FULL| MV_T |    9 | 99 | 30 (7)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(TO_DATE('2007-01-01')<TO_DATE('2007-01-10'))
2 - filter("MV_T"."TIME">'2007-01-01' AND "MV_T"."TIME"<'2007-01-10')

SQL> SET AUTOT OFF
SQL> DELETE FROM T WHERE TIME>'2010-02-04' AND TIME<'2010-05-01';

86 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> COL MVIEW_NAME FORMAT A10
SQL> SELECT MVIEW_NAME,STALENESS FROM USER_MVIEWS;

MVIEW_NAME STALENESS
---------- --------------------------------------
MV_T    NEEDS_COMPILE

SQL> SET AUTOT TRACEONLY EXPLAIN
SQL>  SELECT TIME,COUNT(1) FROM T WHERE TIME >'2007-01-01' AND TIME<'2007-01-10' GROUP BY TIME;

Execution Plan
----------------------------------------------------------
Plan hash value: 1232665453

---------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |    |    9 | 72 | 58 (6)| 00:00:01 |    |    |
| 1 |  HASH GROUP BY          |    |    9 | 72 | 58 (6)| 00:00:01 |    |    |
|*  2 | FILTER                |    |    |    |          |       |    |    |
| 3 | PARTITION RANGE ITERATOR|    |    9 | 72 | 57 (4)| 00:00:01 | KEY | KEY |
|*  4 |    TABLE ACCESS FULL    | T |    9 | 72 | 57 (4)| 00:00:01 | KEY | KEY |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(TO_DATE('2007-01-01')<TO_DATE('2007-01-10'))
4 - filter("TIME">'2007-01-01' AND "TIME"<'2007-01-10')

SQL> SET AUTOT OFF
SQL> TRUNCATE TABLE MV_CAPABILITIES_TABLE;

Table truncated.

SQL> EXEC DBMS_MVIEW.EXPLAIN_MVIEW('MV_T');

PL/SQL procedure successfully completed.

SQL> COL CAPABILITY_NAME FORMAT A30
SQL> COL RELATED_TEXT  FORMAT A20
SQL> COL MSGTXT FORMAT A20
SQL> SELECT CAPABILITY_NAME,RELATED_TEXT,POSSIBLE,MSGTXT FROM MV_CAPABILITIES_TABLE WHERE CAPABILITY_NAME LIKE '%PCT%';

CAPABILITY_NAME             RELATED_TEXT       PO MSGTXT
------------------------------ -------------------- -- --------------------
PCT                                              Y
PCT_TABLE                   T                   Y
REFRESH_FAST_PCT                                  Y
REWRITE_PCT                                        Y
PCT_TABLE_REWRITE             T                   Y

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0    Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production

SQL>

刚试了一下，还是不行会不是是版本的问题

yangtingkun · 发表于 2010-6-3 13:53

Oracle在10g中认为HASH GROUP BY的代价较低，因此没有选择使用查询重新
试试通过加上/*+ rewrite */，看看是否利用物化视图进行查询重新

一生只为爱 · 发表于 2010-6-3 14:50

还是不行啊

SQL> SELECT /*+ REWRITE */ TIME,COUNT(1) FROM T WHERE TIME >'2007-01-01' AND TIME<'2007-01-10' GROUP BY TIME;

Execution Plan
----------------------------------------------------------
Plan hash value: 1232665453

---------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |    |    9 | 72 | 58 (6)| 00:00:01 |    |    |
| 1 |  HASH GROUP BY          |    |    9 | 72 | 58 (6)| 00:00:01 |    |    |
|*  2 | FILTER                |    |    |    |          |       |    |    |
| 3 | PARTITION RANGE ITERATOR|    |    9 | 72 | 57 (4)| 00:00:01 | KEY | KEY |
|*  4 |    TABLE ACCESS FULL    | T |    9 | 72 | 57 (4)| 00:00:01 | KEY | KEY |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(TO_DATE('2007-01-01')<TO_DATE('2007-01-10'))
4 - filter("TIME">'2007-01-01' AND "TIME"<'2007-01-10')

SQL> SELECT /*+ REWRITE(MV_T) */ TIME,COUNT(1) FROM T WHERE TIME >'2007-01-01' AND TIME<'2007-01-10' GROUP BY TIME;

Execution Plan
----------------------------------------------------------
Plan hash value: 1232665453

---------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |    |    9 | 72 | 58 (6)| 00:00:01 |    |    |
| 1 |  HASH GROUP BY          |    |    9 | 72 | 58 (6)| 00:00:01 |    |    |
|*  2 | FILTER                |    |    |    |          |       |    |    |
| 3 | PARTITION RANGE ITERATOR|    |    9 | 72 | 57 (4)| 00:00:01 | KEY | KEY |
|*  4 |    TABLE ACCESS FULL    | T |    9 | 72 | 57 (4)| 00:00:01 | KEY | KEY |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(TO_DATE('2007-01-01')<TO_DATE('2007-01-10'))
4 - filter("TIME">'2007-01-01' AND "TIME"<'2007-01-10')

SQL> SET AUTOT OFF
SQL> SHOW PARAMETER QUERY_REWRITE

NAME                               TYPE                VALUE
------------------------------------ ---------------------- ------------------------------
query_rewrite_enabled             string                TRUE
query_rewrite_integrity             string                enforced
SQL> ALTER SESSION SET query_rewrite_integrity='trusted';

Session altered.

SQL> SET AUTOT TRACEONLY EXPLAIN
SQL> SELECT TIME,COUNT(1) FROM T WHERE TIME >'2007-01-01' AND TIME<'2007-01-10' GROUP BY TIME;

Execution Plan
----------------------------------------------------------
Plan hash value: 1232665453

---------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |    |    9 | 72 | 58 (6)| 00:00:01 |    |    |
| 1 |  HASH GROUP BY          |    |    9 | 72 | 58 (6)| 00:00:01 |    |    |
|*  2 | FILTER                |    |    |    |          |       |    |    |
| 3 | PARTITION RANGE ITERATOR|    |    9 | 72 | 57 (4)| 00:00:01 | KEY | KEY |
|*  4 |    TABLE ACCESS FULL    | T |    9 | 72 | 57 (4)| 00:00:01 | KEY | KEY |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(TO_DATE('2007-01-01')<TO_DATE('2007-01-10'))
4 - filter("TIME">'2007-01-01' AND "TIME"<'2007-01-10')

SQL> SELECT /*+ REWRITE(MV_T) */ TIME,COUNT(1) FROM T WHERE TIME >'2007-01-01' AND TIME<'2007-01-10' GROUP BY TIME;

Execution Plan
----------------------------------------------------------
Plan hash value: 1232665453

---------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |    |    9 | 72 | 58 (6)| 00:00:01 |    |    |
| 1 |  HASH GROUP BY          |    |    9 | 72 | 58 (6)| 00:00:01 |    |    |
|*  2 | FILTER                |    |    |    |          |       |    |    |
| 3 | PARTITION RANGE ITERATOR|    |    9 | 72 | 57 (4)| 00:00:01 | KEY | KEY |
|*  4 |    TABLE ACCESS FULL    | T |    9 | 72 | 57 (4)| 00:00:01 | KEY | KEY |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(TO_DATE('2007-01-01')<TO_DATE('2007-01-10'))
4 - filter("TIME">'2007-01-01' AND "TIME"<'2007-01-10')

SQL> ALTER SESSION SET query_rewrite_integrity='stale_tolerated';

Session altered.

SQL> SELECT TIME,COUNT(1) FROM T WHERE TIME >'2007-01-01' AND TIME<'2007-01-10' GROUP BY TIME;

Execution Plan
----------------------------------------------------------
Plan hash value: 2513640124

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |    |    9 | 99 | 30 (7)| 00:00:01 |
|*  1 |  FILTER                      |    |    |    |          |       |
|*  2 | MAT_VIEW REWRITE ACCESS FULL| MV_T |    9 | 99 | 30 (7)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(TO_DATE('2007-01-01')<TO_DATE('2007-01-10'))
2 - filter("MV_T"."TIME">'2007-01-01' AND "MV_T"."TIME"<'2007-01-10')

还是不行啊除非设置ALTER SESSION SET query_rewrite_integrity='stale_tolerated'  或者做一次刷新才能启用查询重写

yangtingkun · 发表于 2010-6-3 15:47

难道真是10204的问题

[php]

SQL> create table t (id number, time date)
  2  partition by range (time)
  3  (partition p1 values less than (to_date('2008-1-1', 'yyyy-mm-dd')),
  4  partition p2 values less than (to_date('2009-1-1', 'yyyy-mm-dd')),
  5  partition p3 values less than (to_date('2010-1-1', 'yyyy-mm-dd')),
  6  partition p4 values less than (to_date('2011-1-1', 'yyyy-mm-dd')))
  7  ;

Table created.

SQL> insert into t select rownum, sysdate - rownum from dba_objects;

76162 rows created.

SQL> commit;

Commit complete.

SQL> create materialized view log on t with rowid, sequence (id, time)
  2  including new values;

Materialized view log created.

SQL> create materialized view mv_t refresh fast enable query rewrite as
  2  select time, count(*) from t group by time;

Materialized view created.

SQL> delete t where time < to_date('2008-1-1', 'yyyy-mm-dd');

75278 rows deleted.

SQL> commit;

Commit complete.

SQL> set autot on exp
SQL> select /*+ rewrite */ time, count(*) from t where time > to_date('2009-1-1', 'yyyy-mm-dd')
  2  and time < to_date('2009-1-10', 'yyyy-mm-dd') group by time;

TIME          COUNT(*)
-------------- ----------
08-1月 -09             1
04-1月 -09             1
07-1月 -09             1
01-1月 -09             1
03-1月 -09             1
09-1月 -09             1
06-1月 -09             1
05-1月 -09             1
02-1月 -09             1

9 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1712400360

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |    | 13 | 286 | 33 (4)| 00:00:01 |
|*  1 |  MAT_VIEW REWRITE ACCESS FULL| MV_T | 13 | 286 | 33 (4)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("MV_T"."TIME">TO_DATE('2009-01-01 00:00:00', 'yyyy-mm-dd
            hh24:mi:ss') AND "MV_T"."TIME"<TO_DATE('2009-01-10 00:00:00', 'yyyy-mm-dd
            hh24:mi:ss'))

Note
-----
- dynamic sampling used for this statement

SQL> delete t where time > to_date('2009-1-11', 'yyyy-mm-dd');

508 rows deleted.

Execution Plan
----------------------------------------------------------
Plan hash value: 1771284949

--------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT       |    |    1 | 22 |    5 (0)| 00:00:01 |    |    |
| 1 |  DELETE                | T |    |    |          |       |    |    |
| 2 | PARTITION RANGE ITERATOR|    |    1 | 22 |    5 (0)| 00:00:01 |    3 |    4 |
|*  3 | TABLE ACCESS FULL    | T |    1 | 22 |    5 (0)| 00:00:01 |    3 |    4 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("TIME">TO_DATE('2009-01-11 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))

Note
-----
- dynamic sampling used for this statement

SQL> commit;

Commit complete.

SQL> select /*+ rewrite */ time, count(*) from t where time > to_date('2009-1-1', 'yyyy-mm-dd')
  2  and time < to_date('2009-1-10', 'yyyy-mm-dd') group by time;

TIME          COUNT(*)
-------------- ----------
08-1月 -09             1
04-1月 -09             1
07-1月 -09             1
01-1月 -09             1
03-1月 -09             1
09-1月 -09             1
06-1月 -09             1
05-1月 -09             1
02-1月 -09             1

9 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 603163729

-----------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT    |    |    9 | 81 |    4  (25)| 00:00:01 |    |    |
| 1 |  PARTITION RANGE SINGLE|    |    9 | 81 |    4  (25)| 00:00:01 |    3 |    3 |
| 2 | HASH GROUP BY       |    |    9 | 81 |    4  (25)| 00:00:01 |    |    |
|*  3 | TABLE ACCESS FULL | T |    9 | 81 |    3 (0)| 00:00:01 |    3 |    3 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("TIME">TO_DATE('2009-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
            "TIME"<TO_DATE('2009-01-10 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))

Note
-----
- dynamic sampling used for this statement

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0    Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

.
[/php]

一生只为爱 · 发表于 2010-6-3 16:01

还真是怪了，唯一的区别，我收集了一下统计信息。按照你的方法我在试一下。
现在没有10.2.0.3的环境。

一生只为爱 · 发表于 2010-6-3 16:15

SQL> CONN YSP/YSP
Connected.
SQL> SELECT TNAME FROM  TAB;

TNAME
------------------------------------------------------------
T
MLOG$_T
MV_T
T_EXP
TEST
TEMP_STATS
MV_CAPABILITIES_TABLE
TEST3
YSP
SYS_TEMP_FBT

10 rows selected.

SQL> DROP MATERIALIZED VIEW MV_T;

Materialized view dropped.

SQL> DROP TABLE T PURGE;

Table dropped.

SQL> create table t (id number, time date)
  2  partition by range (time)
  3  (partition p1 values less than (to_date('2008-1-1', 'yyyy-mm-dd')),
  4  partition p2 values less than (to_date('2009-1-1', 'yyyy-mm-dd')),
  5  partition p3 values less than (to_date('2010-1-1', 'yyyy-mm-dd')),
  6  partition p4 values less than (to_date('2011-1-1', 'yyyy-mm-dd')))
  7  ;

Table created.

SQL> insert into t select rownum, sysdate - rownum from dba_objects;

51099 rows created.

SQL> COMMIT;

Commit complete.

SQL> create materialized view log on t with rowid, sequence (id, time)  including new values;

Materialized view log created.

SQL> create materialized view mv_t refresh fast enable query rewrite as
  2  select time, count(*) from t group by time;

Materialized view created.

SQL> set autot traceonly explain
SQL> select time, count(*) from t where time > to_date('2009-1-1', 'yyyy-mm-dd')  and time < to_date('2009-1-10', 'yyyy-mm-dd') group by time;

Execution Plan
----------------------------------------------------------
Plan hash value: 3024588181

-----------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT    |    |    9 | 81 |    4  (25)| 00:00:01 |    |    |
| 1 |  PARTITION RANGE SINGLE|    |    9 | 81 |    4  (25)| 00:00:01 |    3 |    3 |
| 2 | HASH GROUP BY       |    |    9 | 81 |    4  (25)| 00:00:01 |    |    |
|*  3 | TABLE ACCESS FULL | T |    9 | 81 |    3 (0)| 00:00:01 |    3 |    3 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("TIME">TO_DATE(' 2009-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
            "TIME"<TO_DATE(' 2009-01-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Note
-----
- dynamic sampling used for this statement

SQL> select /*+ rewrite */ time, count(*) from t where time > to_date('2009-1-1', 'yyyy-mm-dd')  and time < to_date('2009-1-10', 'yyyy-mm-dd') group by time;

Execution Plan
----------------------------------------------------------
Plan hash value: 2061866561

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |    | 18 | 396 | 30 (4)| 00:00:01 |
|*  1 |  MAT_VIEW REWRITE ACCESS FULL| MV_T | 18 | 396 | 30 (4)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("MV_T"."TIME">TO_DATE(' 2009-01-01 00:00:00', 'syyyy-mm-dd
            hh24:mi:ss') AND "MV_T"."TIME"<TO_DATE(' 2009-01-10 00:00:00', 'syyyy-mm-dd
            hh24:mi:ss'))

Note
-----
- dynamic sampling used for this statement

SQL> set autot off
SQL> delete t where time < to_date('2008-1-1', 'yyyy-mm-dd');

50215 rows deleted.

SQL> commit;

Commit complete.

SQL> set autot traceonly explain
SQL> select /*+ rewrite */ time, count(*) from t where time > to_date('2009-1-1', 'yyyy-mm-dd')  and time < to_date('2009-1-10', 'yyyy-mm-dd') group by time;

Execution Plan
----------------------------------------------------------
Plan hash value: 2061866561

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |    | 18 | 396 | 30 (4)| 00:00:01 |
|*  1 |  MAT_VIEW REWRITE ACCESS FULL| MV_T | 18 | 396 | 30 (4)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("MV_T"."TIME">TO_DATE(' 2009-01-01 00:00:00', 'syyyy-mm-dd
            hh24:mi:ss') AND "MV_T"."TIME"<TO_DATE(' 2009-01-10 00:00:00', 'syyyy-mm-dd
            hh24:mi:ss'))

Note
-----
- dynamic sampling used for this statement

SQL> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

SQL> select /*+ rewrite */ time, count(*) from t where time > to_date('2009-1-1', 'yyyy-mm-dd')  and time < to_date('2009-1-10', 'yyyy-mm-dd') group by time;

Execution Plan
----------------------------------------------------------
Plan hash value: 2061866561

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |    | 18 | 396 | 30 (4)| 00:00:01 |
|*  1 |  MAT_VIEW REWRITE ACCESS FULL| MV_T | 18 | 396 | 30 (4)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("MV_T"."TIME">TO_DATE(' 2009-01-01 00:00:00', 'syyyy-mm-dd
            hh24:mi:ss') AND "MV_T"."TIME"<TO_DATE(' 2009-01-10 00:00:00', 'syyyy-mm-dd
            hh24:mi:ss'))

Note
-----
- dynamic sampling used for this statement

SQL> exec dbms_stats.gather_table_stats(user,'MV_T');

PL/SQL procedure successfully completed.

SQL> select /*+ rewrite */ time, count(*) from t where time > to_date('2009-1-1', 'yyyy-mm-dd')  and time < to_date('2009-1-10', 'yyyy-mm-dd') group by time;

Execution Plan
----------------------------------------------------------
Plan hash value: 2061866561

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |    |    9 | 99 | 30 (4)| 00:00:01 |
|*  1 |  MAT_VIEW REWRITE ACCESS FULL| MV_T |    9 | 99 | 30 (4)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("MV_T"."TIME">TO_DATE(' 2009-01-01 00:00:00', 'syyyy-mm-dd
            hh24:mi:ss') AND "MV_T"."TIME"<TO_DATE(' 2009-01-10 00:00:00', 'syyyy-mm-dd
            hh24:mi:ss'))

SQL> select time, count(*) from t where time > to_date('2009-1-1', 'yyyy-mm-dd')  and time < to_date('2009-1-10', 'yyyy-mm-dd') group by time;

Execution Plan
----------------------------------------------------------
Plan hash value: 3024588181

-----------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT    |    |    8 | 64 |    4  (25)| 00:00:01 |    |    |
| 1 |  PARTITION RANGE SINGLE|    |    8 | 64 |    4  (25)| 00:00:01 |    3 |    3 |
| 2 | HASH GROUP BY       |    |    8 | 64 |    4  (25)| 00:00:01 |    |    |
|*  3 | TABLE ACCESS FULL | T |    8 | 64 |    3 (0)| 00:00:01 |    3 |    3 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("TIME"<TO_DATE(' 2009-01-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
            "TIME">TO_DATE(' 2009-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

yangtingkun · 发表于 2010-6-3 16:24

把你最开始的语句里面的隐式转换，改为显示的TO_DATE试试