ORACLE视图加上别名查询非常慢,去掉之后正常

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的查询计划雷同

Plan hash value: 984768051

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

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

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

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

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

| 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://cqcxhl.cn/article/pdodec.html

重庆分公司，新征程启航

ORACLE视图加上别名查询非常慢,去掉之后正常

其他资讯