v_gasmonthsum是个视图,select * from v_gasmonthsum t;带上t查询100s都出不来,去掉t 1秒以内就出来,执行计划一样,有哪位遇到过,这个问题反复验证过,不是偶然的

新晃网站建设公司创新互联,新晃网站设计制作，有大型网站制作公司丰富经验。已为新晃数千家提供企业网站建设服务。企业网站搭建\成都外贸网站建设公司要多少钱，请找那个售后服务好的新晃做网站的公司定做！

但是通过查询select * from v_gasmonthsum t where rownum<1000速度却又恢复正常,总感觉select * from v_gasmonthsum t;这个查询方法是不是少了自动分页提取数据的功能

以下是select * from v_gasmonthsum t执行计划:不带别名t的查询计划雷同

1

Plan hash   value: 984768051

2

3

------------------------------------------------------------------------------------------------------------

4

| Id  | Operation                         | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |

5

------------------------------------------------------------------------------------------------------------

6

|     0 | SELECT STATEMENT                    |                        |     1 |     224 |  5308   (1)| 00:01:04 |

7

|     1 |  NESTED LOOPS                     |                        |     1 |     224 |  5308   (1)| 00:01:04 |

8

|     2 |   NESTED LOOPS                    |                        |     1 |     211 |  5304   (1)| 00:01:04 |

9

|     3 |    NESTED LOOPS                   |                        |     1 |     117 |  5298   (1)| 00:01:04 |

#

|     4 |     VIEW                          | VW_SQ_1                |   213 | 11076 |  4870     (1)| 00:00:59 |

#

|     5 |      HASH GROUP BY                |                        |   213 | 11076 |  4870     (1)| 00:00:59 |

#

|*    6 |       FILTER                      |                        |       |         |            |          |

#

|     7 |        TABLE ACCESS BY INDEX   ROWID| GASOUTPUTMONTHDATA     |   213 | 11076 |  4870     (1)| 00:00:59 |

#

|*    8 |         INDEX FULL SCAN           | IDX_GASOUTPUTMONTHDATA |  8726 |         |  4766   (1)| 00:00:58 |

#

|*    9 |     TABLE ACCESS BY INDEX   ROWID   | GASOUTPUTMONTHDATA     |       1 |    65 |     3     (0)| 00:00:01 |

#

|* 10 |      INDEX RANGE SCAN             | IDX_GASOUTPUTMONTHDATA |     1 |         |     2   (0)| 00:00:01 |

#

|    11 |    INLIST ITERATOR                |                        |       |         |            |          |

#

|    12 |     TABLE ACCESS BY INDEX   ROWID   | GASFACMONTHDATA        |       1 |    94 |     6     (0)| 00:00:01 |

#

|* 13 |      INDEX RANGE SCAN             | IDX_GASFACMONTHDATA    |       1 |       |     5     (0)| 00:00:01 |

#

|* 14 |   VIEW PUSHED PREDICATE           | VW_SQ_2                |     1 |      13 |     4   (0)| 00:00:01 |

#

|* 15 |    FILTER                         |                        |       |         |            |          |

#

|    16 |     SORT AGGREGATE                |                        |     1 |      55 |            |          |

#

|* 17 |      FILTER                       |                        |       |         |            |          |

#

|* 18 |       INDEX RANGE SCAN            | IDX_GASFACMONTHDATA    |       1 |    55 |     4     (0)| 00:00:01 |

#

------------------------------------------------------------------------------------------------------------

#

#

Predicate Information (identified by   operation id):

#

---------------------------------------------------

#

#

6 - filter(201801

#

8 - access("G"."YEARANDMONTH">=201801 AND   "G"."YEARANDMONTH"

#

)))

#

filter("G"."YEARANDMONTH">=201801 AND   "G"."YEARANDMONTH"

#

)))

#

9 - filter("T"."SHAREID"="MAX(SHAREID)")

#

10 - access("T"."PSCODE"="ITEM_1" AND   "T"."OUTPUTCODE"="ITEM_3" AND   "T"."YEARANDMONTH"="ITEM_2")

#

filter("T"."YEARANDMONTH">=201801 AND   "T"."YEARANDMONTH"

#

)))

#

13 -   access("T"."PSCODE"="T"."PSCODE" AND   "T"."OUTPUTCODE"="T"."OUTPUTCODE"   AND

#

("T"."POLLUTANTCODE"='001' OR   "T"."POLLUTANTCODE"='002' OR   "T"."POLLUTANTCODE"='003') AND

#

"T"."YEARANDMONTH"="T"."YEARANDMONTH")

#

filter("T"."YEARANDMONTH">=201801 AND   "T"."YEARANDMONTH"

#

)))

#

14 -   filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("SHAREID"))="MAX(SHAREID)")

#

15 - filter(COUNT(*)>0 AND   201801

#

17 -   filter(TO_NUMBER(TO_CHAR(SYSDATE@!,'YYYYMM'))>"T"."YEARANDMONTH"   AND

#

201801<="T"."YEARANDMONTH" AND   201801

#

18 -   access("G"."PSCODE"="T"."PSCODE" AND   "G"."OUTPUTCODE"="T"."OUTPUTCODE"   AND

#

"G"."POLLUTANTCODE"="T"."POLLUTANTCODE"   AND   "G"."YEARANDMONTH"="T"."YEARANDMONTH")

#

filter("G"."YEARANDMONTH">=201801 AND   "G"."YEARANDMONTH"

#

)))

#

#

Note

#

-----

#

- dynamic sampling used for this statement (level=2)

当前名称：ORACLE视图加上别名查询非常慢,去掉之后正常
分享地址：http://myzitong.com/article/pdodec.html