精华内容
下载资源
问答
  • 结论with as 固化的结果集再添加条件再次使用的时候是可以使用原的索引的。 测试步骤如下: 1.构建测试 create table dbmgr.TEST_APPEND as select * from dba_objects ;  insert into dbmgr.TEST_APPEND ...

    结论with as 固化的结果集再添加条件再次使用的时候是可以使用原表的索引的。


    测试步骤如下:

    1.构建测试表

    create table  dbmgr.TEST_APPEND  as select * from dba_objects ; 
    insert into dbmgr.TEST_APPEND   select * from dbmgr.TEST_APPEND   ; 
    select count(*) from dbmgr.TEST_APPEND ;
     ---8125900


    2.创建索引

    create index dbmgr.test_withas on dbmgr.test_append(object_name) ; 


    3.收集统计信息

    SQL> exec dbms_stats.gather_table_stats(ownname => 'dbmgr',tabname => 'TEST_APPEND') ; 
    PL/SQL procedure successfully completed


    4.验证正常情况下索引可以使用。

    select /* without with as aaaaaaaaaa*/  * from  dbmgr.TEST_APPEND aa  where object_name='I_USER1' ;

    select sql_id,sql_text from v$sql m where m.SQL_TEXT like '%aaaaa%' ; 


    QL> select  * from table(dbms_xplan.display_cursor('45wh03j2uhb86')) ; 
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    SQL_ID  45wh03j2uhb86, child number 0
    -------------------------------------
    select /*+ without with as aaaaaaaaaa*/  * from  dbmgr.TEST_APPEND
    where object_name='I_USER1'
    Plan hash value: 1601588939
    --------------------------------------------------------------------------------
    | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|
    --------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |             |       |       |   623 (100)|
    |   1 |  TABLE ACCESS BY INDEX ROWID| TEST_APPEND |   743 | 66127 |   623   (0)|
    |*  2 |   INDEX RANGE SCAN          | TEST_WITHAS |   743 |       |     6   (0)|       <<<<<<<<<<<<<<<<<<<<可以看到正常可以使用索引
    --------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - access("OBJECT_NAME"='I_USER1')
    20 rows selected


    5.刷新shared_pool 

    SQL> alter system flush shared_pool ;
    System altered


    6.使用with as 后验证是否可以使用到索引


    with test_wias as
     (select owner, object_name, object_id, object_type from dbmgr.TEST_APPEND)
    select /* without with as bbbbbbbbbbbb*/ *
      from test_wias
     where object_name = 'I_USER1';


    select sql_id,sql_text from v$sql m where m.SQL_TEXT like '%bbbbbbbbbbbb%' ; 


    SQL> select  * from table(dbms_xplan.display_cursor('c7dq7c6ddnhz6')) ; 
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    SQL_ID  c7dq7c6ddnhz6, child number 0
    -------------------------------------
    with test_wias as (select owner,object_name,object_id,object_type from
    dbmgr.TEST_APPEND )  select  /* without with as bbbbbbbbbbbb*/ * from
    test_wias where object_name='I_USER1'
    Plan hash value: 1601588939
    --------------------------------------------------------------------------------
    | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|
    --------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |             |       |       |   623 (100)|
    |   1 |  TABLE ACCESS BY INDEX ROWID| TEST_APPEND |   743 | 26748 |   623   (0)|
    |*  2 |   INDEX RANGE SCAN          | TEST_WITHAS |   743 |       |     6   (0)|  <<<<<<<<<<<<<<<<<<<<<<<<<<<可以看到仍然可以使用索引
    --------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - access("OBJECT_NAME"='I_USER1')
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    21 rows selected

    展开全文
  • sql with 使用-临时

    千次阅读 2018-10-27 10:14:45
     有些需求需要对多个分别统计出结果,然后使用每个统计的结果进行汇总,这个时候临时就发挥作用了。 2、简单使用: with t1 as(select empId,yearMonth,sum(salary) as'a' from tb_salary where year...

    1、业务场景:

     有些需求需要对多个表分别统计出结果,然后使用每个表统计的结果进行汇总,这个时候临时表就发挥作用了。

    2、简单使用:

    with 
    t1 as(select empId,yearMonth,sum(salary) as'a'
    from tb_salary where yearMonth = '2017-01'
    group by empId,yearMonth
    
    ),
    
    t2 as (select empId,yearMonth,sum(salary) as'a'
    from tb_salary where yearMonth = '2017-02'
    group by empId,yearMonth ),
    
    t as (select t1.empId e1, t2.empId e2, t1.empId e3, isnull(t1.yearMonth,'2017-01') m1,
     isnull(t2.yearMonth,'2017-02') m2, '与上月差额
    ' m3 ,isnull(t1.a,'0') a1, isnull(t2.a,'0') a2,emp.staffNo,emp.staffName,emp.orgPath
    from t1 right join t2 on t1.empId = t2.empId RIGHT JOIN tb_employees emp on t2.empId = emp.empId) 
    
    -- 行转列
    select 1 n,tt.e2,tt.staffNo,tt.m1,tt.a1 from (select * from from t) tt union all
    select 2 n,tt.e2,tt.staffNo,tt.m2,tt.a2 from (select * from from t) tt union all
    select 3 n,tt.e2,tt.staffNo,tt.m3,(tt.a2-tt.a1) a3 from (select * from from t  where t.staffNo = '4289') tt
    ORDER BY t.e2 desc,n asc;

     

    展开全文
  • with as 和临时的使用

    千次阅读 2020-01-12 16:53:31
    with as 和临时的使用with as 和建临时的区别with as (公共表达式CTE)...with as 是子查询部分,并不是真正的临时,查询结果保存在内存中。定义一个sql片段,该片段在整个sql都可以被利用,可以提高代码的...

    with as 和建临时表的区别

    with as (公共表达式CTE)

    当我们书写一些比较结构复杂,用的表也很多的sql时,可以用with as。
    with as 是子查询部分,并不是真正的临时表,查询结果保存在内存中。定义一个sql片段,该片段在整个sql都可以被利用,可以提高代码的可读性以及减少重复读,减少性能成本。

    with table1 as
    select one,tow,thire
    from A
    
    table2 as
    select one,five
    from B
    
    insert overwrite table AB  partition(dt={'hiveconf:dt'})
    select one,tow,thire,four,five
    from A left join B on A.one = B.one
    
    

    注意:后面必须直接紧跟使用CTE的SQL语句,否则失效

    临时表

    数据库临时表和永久表类似,储存在磁盘的临时区中,只有数据库连接断开,或者drop掉,才会消失。

    drop table if exists tmp.table1;
    create table tmp.table1 stored as orc as
    select one,tow,thire
    from A
    
    drop table if exists tmp.table2;
    create table tmp.table2 stored as orc as
    select one,five
    from B
    
    insert overwrite table AB  partition(dt={'hiveconf:dt'})
    select one,tow,thire,four,five
    from A left join B on A.one = B.one
    

    使用技巧

    1.来源于一张表的不同维度的数据,也倾向于在一次with as中收集齐全,命更高层次上抽象主题。避免同一张底表在多个with as中存在。
    2.在数仓开发之前,先理清楚需要用到的表以及字段,思考清楚以什么结构去开发,思考性能瓶颈是哪里。最好用白纸写清楚。磨刀不误砍柴工。
    3.表的连接:
    3.1优先汇总之后再连接
    3.2汇总维度相同,巧用union,不用join。取各个临时表所需的字段,收集齐全后,一次group by。

    展开全文
  • oracle with as 别名

    千次阅读 2011-10-14 15:18:39
    with 别名 as (select * from *) select * from 别名;  相当于建个临时 语法就是 with tempname as (select ....) select ... 例子: with t as (select *
    
    

    with  别名 as (select * from *)

    select * from 别名; 
    相当于建个临时表
    语法就是
    with tempname as (select ....)
    select ...

    例子:
    with t as (select * from emp where depno=10)
    select * from t where empno=xxx

    再举个简单的例子

    with a as (select * from test)

    select * from a;

    其实就是把一大堆重复用到的SQL语句放在with as 里面,取一个别名,后面的查询就可以用它

    这样对于大批量的SQL语句起到一个优化的作用,而且清楚明了

     

    多表就是: 

         with a as (select '1','2' from dual),    --逗号

    b as (select '1','2' from dual)     --第二个表不需要with关键字

        select * from a,b;


    是个临时存储,一般是在存储过程里使用的

    可以做多个表的连接,结果集的连接查询

    比如查询某个emp、dept、还有work表 work表是用来存储工作信息的,eid,did,salary等

    要求查询工资比起所在部门工资高的员工的信息,此时需要很多的连接,就可以使用with as 比较清晰的连接
    几个表

    Assembly code

    with wd as (select did,arg(salary) 平均工资 from work), em as (select emp.*,w.salary from emp left join work w on emp.eid = w.eid) select * from wd,em where wd.did =em.did and wd.平均工资>em.salary;
    展开全文
  • SQL--如何将Sql语句查询出来的结果作为一个表名 再次进行查询 ...创建临时,把第一个查询内容作为一个临时,(Oracle10G 以后的语法) with A as (select a from Awhere a=1) select * from A == ...
  • oracle中with table as临时

    千次阅读 2015-09-09 14:17:45
    with table as 是用于创建临时,它是用来创建临时,主要是在进行查询的时候,做一个测试用 个人感觉好处有如下几点: 1.起到一个测试作用,不需要创建实体表; 2.当你需要进行一个复杂的查询时,可以从中提取...
  • mysql8 WITH AS(公用表达式)

    千次阅读 2020-01-07 10:29:50
    公用表达式(CTE)是一个命名的临时结果集,它存在于单个语句的范围内,以后可以在该语句中引用,可能多次。以下讨论描述了如何编写使用CTE的语句。 常用表达式 要指定公用表达式,请使用 WITH具有一个或多个...
  • 1、with table as 相当于建个临时(用于一个语句中某些中间结果放在临时空间的SQL语句),Oracle 9i 新增WITH语法,可以将查询中的子查询命名,放到SELECT语句的最前面。 语法就是 with tempname as (select .....
  • oracle 临时 with as 写法

    万次阅读 2017-03-08 11:41:54
    优化时,发现查询关系逻辑混乱又复杂(虽然数据查询结果并没有错),多个类似的子查询嵌套,导致查询性能变的很低。  寻求资料知道ORACLE有个WITH as 用法及其好用   写法大致如下:
  • hive中使用with as查询作为临时中间

    千次阅读 2018-12-13 22:15:00
    oracle、hive都存在with as的用法。用于将重复的查询结果复用。 今天做统计分析时用到,使用例子如下: 1. 直接查询 with tmp_a as ( ...2. 多计算结果join with tmp_a as ( select f1,f2,f3 ...
  • sql之with as 公用表达式

    千次阅读 2016-07-05 23:14:17
    sql 中的with as 具有强大的功能,特别是在有递归需要的情况下,一般来说结合union all一起用。 示例如下: with w_Users(UserId) as ( select UserId from Users where UserId={0}
  • WITH AS 提升性能代替临时

    千次阅读 2011-03-01 09:35:00
    WITH AS 提升性能代替临时
  • DB2公共表达式(with)

    千次阅读 2008-03-17 14:09:00
    2、公共表达式 公共表达式是在全查询的开头使用 WITH 关键字定义的命名结果表。公共表达式是您创建以在复杂查询之中使用的表达式。在查询的开头使用 WITH 子句定义并命名公共表达式。
  • 如果第一个SQL能查出结果,则返回结果,否则,执行第二条SQL,返回第二条SQL的结果。 SQL Server中使用变量的方式: 如果查出有“wangwang”用户则返回,否则查询“zhangzhang”用户的id返回。有优先级 declare @...
  • T-SQL with关键字 with as 递归循环

    千次阅读 2013-06-09 10:04:08
    1.把从customer查询出来的结果,动态的组装进新CustomersDups里: IF OBJECT_ID ( 'dbo.CustomersDups' ) IS NOT NULL DROP TABLE dbo . CustomersDups ; GO WITH CrossCustomers AS ( SELECT...
  • ORACLE WITH AS 用法,创建临时

    千次阅读 2018-06-29 09:05:00
    语法: withtempNameas(select....)select... –针对一个别名with tmp as (select * from tb_...–针对多个别名with tmp as (select * from tb_name), tmp2 as (select * from tb_name2), tmp3 as (select * f...
  • SQL数据库中数据处理时,有时候需要建立临时,将查询后的结果集放到临时中,然后在针对这个数据进行操作。  创建“临时”(逻辑上的临时,可能不一定是数据库的)的方法有一下几种:  1.with ...
  • 首先看代码: $userCoupons = UserCoupons::with(['coupon' => function($query) use($groupId){ return $query->select('id', 'group_id', 'cover', 'group_number', 'group_cov...
  • 使用的: CREATE TABLE `b` (  `eid` INT(11) DEFAULT NULL,  `month` DATETIME DEFAULT NULL,  `sals` DECIMAL(10,2) DEFAULT NULL ) ENGINE=INNODB DEFAULT CHARSET=latin1 当只有一个...
  • 结构 现在有客户、订单、图书、作者, 客户Customer (id customer_name)订单Order (id order_name customer_id book_id)图书 (id book_name author_id)作者
  • ORACLE-WITH 子句详解

    千次阅读 2016-11-20 19:03:51
    概述with 子句是在oracle ...该查询块的检索结果会被oracle 保存在用户的临时空间中,该命名块就像虚或者内联视图一样。语法WITH subquery_name AS (the aggregation SQL statement) SELECT (query naming subquer
  • Yii2中使用join、joinwith关联查询

    千次阅读 2018-03-30 09:05:33
    我们用实例来说明这一部分结构现在有客户、订单、图书、作者,客户Customer (id customer_name)订单Order (id order_name customer_id book_id)图书 (id book_name author_id)作者 ...
  • Hive_Hive WITH table_name AS 生成临时

    千次阅读 2020-04-08 23:06:22
    然后我们根据这个临时,进行之后的操作,但是创建临时有一定的开销。 如果这个临时并不需要保存,并且下文只需要用有限的几次,我们可以采用下面的方法。 背景: 当我们书写一些结构相对复杂的S...
  • 最近优化sql,发现这个临时真是个好东西。分享出来(1)with基础使用WITH AS 语句可以为一个子查询语句块定义一个名称,使用这个子查询名称可以在查询语句的很多地方引用这个子查询。Oracle 数据库像对待内联视图...
  • CTE(Common Table Expression) 公用表达式,它是在单个语句的执行范围内定义的临时结果集,只在查询期间有效。它可以自引用,也可在同一查询中多次引用,实现了代码段的重复利用。 CTE最大的好处是提升T-Sql代码...
  • DB2 公共表达式(WITH语句的使用)

    千次阅读 热门讨论 2009-09-27 14:44:00
    其实那只是它的一个用途而已,它的本名正如我们标题写的那样,叫做:公共表达式(Common Table Expression),从字面理解,大家觉得它是用来干嘛的呢?其实,它是用来定义临时集合的。啊?VALUES语句不是用来定义...
  • with as 将查询出来的临时联合查询

    千次阅读 2011-06-08 09:43:00
    -------------将查询出来的临时联合查询with 临时表名 as(select * from emp where emp_name in(select emp_name from emp group by emp_name having count(*)>1))select * from 临时表名 left join empon 临时...
  • 结构 现在有客户、订单、图书、作者, 客户Customer (id customer_name) 订单Order (id order_name customer_id book_id) 图书 (id book_name author_id) 作者 ...
  • ----根据第一大段,我得出个结论,谓词里,靠后的条件,只会遍历靠前的条件筛选出的结果集,而不会遍历全了。(根据下面报错与否得出的结论,哪句没报错说明它没遍历全),注:谓词顺序不是where语句顺序,需要...
  • 关于with cube ,with rollup 和 grouping 通过查看sql 2005的帮助文档找到了CUBE 和 ROLLUP 之间的具体区别: CUBE 生成的结果集显示了所选列中值的所有组合的聚合。ROLLUP 生成的结果集显示了所选列中值的某一...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 436,929
精华内容 174,771
关键字:

with表结果