Oracle中的带子句

大家好，

Oracle中的 “带有子句” 将选择查询的结果存储在缓存中，并将显示来自缓存的结果以下次显示同一语句的记录？

除了使用函数使用类型对象返回表之外，“带有子句” 的优点和替代方法是什么？

谢谢

使用子句存储缓存并不是强制性的。这是一个没有的例子，然后我暗示它会强迫它这样做

SQL> create table t
  2  as
  3  select *
  4  from dba_objects;

Table created.

SQL>
SQL> set feedback only
SQL> with xyz as
  2  (
  3    select owner, count(*) c
  4    from t
  5    group by owner
  6  )
  7  select * from xyz;

32 rows selected.

SQL>
SQL> set feedback on
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fzhrfc593c2hu, child number 0
-------------------------------------
with xyz as (   select owner, count(*) c   from t   group by owner )
select * from xyz

Plan hash value: 47235625

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   422 (100)|          |
|   1 |  HASH GROUP BY     |      |    32 |   192 |   422   (2)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    | 77225 |   452K|   418   (1)| 00:00:01 |
---------------------------------------------------------------------------


15 rows selected.

SQL>
SQL> set feedback only
SQL> with xyz as
  2  (
  3    select /*+ materialize */ owner, count(*) c
  4    from t
  5    group by owner
  6  )
  7  select * from xyz;

32 rows selected.

SQL>
SQL> set feedback on
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  35fqjpxwg3649, child number 0
-------------------------------------
with xyz as (   select /*+ materialize */ owner, count(*) c   from t
group by owner ) select * from xyz

Plan hash value: 1146019360

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                           |       |       |   424 (100)|          |
|   1 |  TEMP TABLE TRANSFORMATION               |                           |       |       |            |          |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6659_22ECE8 |       |       |            |          |
|   3 |    HASH GROUP BY                         |                           |    32 |   192 |   422   (2)| 00:00:01 |
|   4 |     TABLE ACCESS FULL                    | T                         | 77225 |   452K|   418   (1)| 00:00:01 |
|   5 |   VIEW                                   |                           |    32 |  2528 |     2   (0)| 00:00:01 |
|   6 |    TABLE ACCESS FULL                     | SYS_TEMP_0FD9D6659_22ECE8 |    32 |   192 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------


19 rows selected.

SQL>
复制

Waving子句就像其他任何SQL构造一样-工具箱中的另一个工具可以帮助您编写良好的SQL。它可以通过在部分中构建SQL来提供结构和维护方面的好处。它可以递归地用于以任何其他方式处理更难编码的查询。

那里有很多选择。