exists、in、table join 实际案例

最新推荐文章于 2024-10-14 22:42:36 发布
最新推荐文章于 2024-10-14 22:42:36 发布
阅读量109 收藏
点赞数
文章标签： 数据库
 

SQL> select /*+ optimizer_features_enable('9.2.0') */

  2         pk_bdinfo,

  3         bdcode,

  4         bdname,

  5         bdtype,

  6         tablename,

  7         tablepkname,

  8         corpfieldname,

  9         codefieldname,

 10         namefieldname,

 11         refnodename

 12    from ufnc5610.bd_bdinfo a

 13   where exists (select 1

 14            from ufnc5610.bd_subjass b, ufnc5610.bd_accsubj acc

 15           where b.pk_accsubj = acc.pk_accsubj

 16             and a.pk_bdinfo = b.pk_bdinfo

 17             and (acc.pk_glorgbook = '0001AA1000000000S3MT'));

 

已选择16行。

 

已用时间:  00: 00: 00.98

 

执行计划

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

Plan hash value: 30633670

 

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

| Id  | Operation          | Name         | Rows  | Bytes | Cost  |

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

|   0 | SELECT STATEMENT   |              |    15 |  1590 |    11 |

|*  1 |  FILTER            |              |       |       |       |

|   2 |   TABLE ACCESS FULL| BD_BDINFO    |    15 |  1590 |     5 |

|   3 |   NESTED LOOPS     |              |   802 | 67368 |     6 |

|*  4 |    INDEX RANGE SCAN| I_BD_ACCSUBJ |   765 | 32130 |     4 |

|*  5 |    INDEX RANGE SCAN| I_BD_SUBJASS |     1 |    42 |     1 |

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

 

统计信息

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

          0  recursive calls

          0  db block gets

     412764  consistent gets

          0  physical reads

          0  redo size

       1861  bytes sent via SQL*Net to client

        250  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

         16  rows processed

        

换成in，效率的到提高

 

SQL> select /*+optimizer_features_enable('9.2.0')*/

  2         pk_bdinfo,

  3         bdcode,

  4         bdname,

  5         bdtype,

  6         tablename,

  7         tablepkname,

  8         corpfieldname,

  9         codefieldname,

 10         namefieldname,

 11         refnodename

 12    from ufnc5610.bd_bdinfo a

 13   where pk_bdinfo in (select b.pk_bdinfo

 14            from ufnc5610.bd_subjass b, ufnc5610.bd_accsubj acc

 15           where b.pk_accsubj = acc.pk_accsubj

 16             and (acc.pk_glorgbook = '0001AA1000000000S3MT'));

 

 

SQL> /

 

已选择16行。

 

已用时间:  00: 00: 00.01

 

执行计划

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

Plan hash value: 2360601918

 

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

| Id  | Operation           | Name         | Rows  | Bytes | Cost  |

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

|   0 | SELECT STATEMENT    |              |    22 |  2816 |    33 |

|*  1 |  HASH JOIN SEMI     |              |    22 |  2816 |    33 |

|   2 |   TABLE ACCESS FULL | BD_BDINFO    |   292 | 30952 |     5 |

|   3 |   VIEW              | VW_NSO_1     |  2346 | 51612 |    27 |

|   4 |    NESTED LOOPS     |              |  2346 |   192K|    27 |

|*  5 |     INDEX RANGE SCAN| I_BD_ACCSUBJ |   765 | 32130 |     4 |

|*  6 |     INDEX RANGE SCAN| I_BD_SUBJASS |     3 |   126 |     1 |

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

 

统计信息

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

          0  recursive calls

          0  db block gets

       1525  consistent gets

          0  physical reads

          0  redo size

       1946  bytes sent via SQL*Net to client

        250  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

         16  rows processed  

        

实际是CBO优化器没有对exists做unnest转换，提示其转换，效率得到提高              

 

SQL> ed

已写入 file afiedt.buf

 

  1  select /*+ optimizer_features_enable('9.2.0') */

  2         pk_bdinfo,

  3         bdcode,

  4         bdname,

  5         bdtype,

  6         tablename,

  7         tablepkname,

  8         corpfieldname,

  9         codefieldname,

 10         namefieldname,

 11         refnodename

 12    from ufnc5610.bd_bdinfo a

 13   where exists (select /*+unnest*/ 1

 14            from ufnc5610.bd_subjass b, ufnc5610.bd_accsubj acc

 15           where b.pk_accsubj = acc.pk_accsubj

 16             and a.pk_bdinfo = b.pk_bdinfo

 17*            and (acc.pk_glorgbook = '0001AA1000000000S3MT'))

SQL> /

 

已选择16行。

 

已用时间:  00: 00: 00.03

 

执行计划

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

Plan hash value: 2542036995

 

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

| Id  | Operation           | Name         | Rows  | Bytes | Cost  |

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

|   0 | SELECT STATEMENT    |              |    22 |  2816 |    33 |

|*  1 |  HASH JOIN SEMI     |              |    22 |  2816 |    33 |

|   2 |   TABLE ACCESS FULL | BD_BDINFO    |   292 | 30952 |     5 |

|   3 |   VIEW              | VW_SQ_1      |  2346 | 51612 |    27 |

|   4 |    NESTED LOOPS     |              |  2346 |   192K|    27 |

|*  5 |     INDEX RANGE SCAN| I_BD_ACCSUBJ |   765 | 32130 |     4 |

|*  6 |     INDEX RANGE SCAN| I_BD_SUBJASS |     3 |   126 |     1 |

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

 

统计信息

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

          1  recursive calls

          0  db block gets

       1525  consistent gets

          0  physical reads

          0  redo size

       1946  bytes sent via SQL*Net to client

        250  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

         16  rows processed

 

oracle 10G正常情况下会做unnest，如下

 

  1  select /*+ optimizer_features_enable('10.2.0.1') */

  2         pk_bdinfo,

  3         bdcode,

  4         bdname,

  5         bdtype,

  6         tablename,

  7         tablepkname,

  8         corpfieldname,

  9         codefieldname,

 10         namefieldname,

 11         refnodename

 12    from ufnc5610.bd_bdinfo a

 13   where exists (select  1

 14            from ufnc5610.bd_subjass b, ufnc5610.bd_accsubj acc

 15           where b.pk_accsubj = acc.pk_accsubj

 16             and a.pk_bdinfo = b.pk_bdinfo

 17*            and (acc.pk_glorgbook = '0001AA1000000000S3MT'))

SQL> /

 

已选择16行。

 

已用时间:  00: 00: 00.01

 

执行计划

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

Plan hash value: 2542036995

 

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

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

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

|   0 | SELECT STATEMENT    |              |    22 |  2816 |    36   (3)| 00:00:01 |

|*  1 |  HASH JOIN SEMI     |              |    22 |  2816 |    36   (3)| 00:00:01 |

|   2 |   TABLE ACCESS FULL | BD_BDINFO    |   292 | 30952 |     8   (0)| 00:00:01 |

|   3 |   VIEW              | VW_SQ_1      |  2346 | 51612 |    27   (0)| 00:00:01 |

|   4 |    NESTED LOOPS     |              |  2346 |   192K|    27   (0)| 00:00:01 |

|*  5 |     INDEX RANGE SCAN| I_BD_ACCSUBJ |   765 | 32130 |     4   (0)| 00:00:01 |

|*  6 |     INDEX RANGE SCAN| I_BD_SUBJASS |     3 |   126 |     1   (0)| 00:00:01 |

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

 

统计信息

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

          1  recursive calls

          0  db block gets

       1525  consistent gets

          0  physical reads

          0  redo size

       1946  bytes sent via SQL*Net to client

        250  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

         16  rows processed

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27378/viewspace-670132/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27378/viewspace-670132/

cujiaowu9180
关注
SQL中EXISTS的用法
weixin_30702413的博客
12-25 6254
比如在Northwind数据库中有一个查询为 SELECT c.CustomerId,CompanyName FROM Customers c WHERE EXISTS( SELECT OrderID FROM Orders o WHERE o.CustomerID=c.CustomerID) 这里面的EXISTS是如何运作呢？子查询返回的是OrderId字段，可是外面的查询要找的是Cu...
> 1072 - Key column 'ID' doesn't exist in table问题
Attacked_code的博客
06-03 2万+
> 1072 - Key column 'ID' doesn't exist in table问题 起因：使用SQL语句创建表出现提示错误 解决办法:看下你创建的自增长的主键名名称和PRIMARY KEY (`主键编号名称`) USING BTREE是否对应 ...
Java SQLSyntaxErrorException: Key column ‘xxx‘ doesn‘t exist in table问题解决
旭东怪的博客
09-10 8515
问题描述： Cause: java.sql.SQLSyntaxErrorException: Key column 'user_name' doesn't exist in table 问题分析： 1、设置主键的字段名称在表格中不存在，导致报错。 CREATE TABLE users( `username` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名
ERROR 1176 (42000): Key 'XXX' doesn't exist in table 'XXX'报错处理
daxikuo7780的博客
02-27 2736
MySQL5.7对sql语句强制使用索引查询时报错如下： 解决：这里的id字段是表的主键，查看别人的经验贴得知是语法错误，参考链接https://stackoverflow.com/questions/19205004/key-emp-no-doesnt-exist-in-table-employees ，当强制索引是主键时sql语句应这样写 mysql> SELECT C...
1072 - Key column 'xxx_id' doesn't exist in table
haohan_lt的博客
06-06 2万+
MySQL创建表报没有xxx_id列,查看一下自己的sql中是不是没有此列
【SQL开发实战技巧】系列（五）：从执行计划看IN、EXISTS 和 INNER JOIN效率，我们要分场景不要死记网上结论
赵延东的一亩三分地
02-08 9万+
从执行计划角度分析IN、EXISTS 和 INNER JOIN效率而不是死记网上结论、表的5种关联：INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL JOIN 解析【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。
【SQL开发实战技巧】系列（六）：从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率，记住内外关联条件不要乱放
热门推荐
赵延东的一亩三分地
02-08 9万+
从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率，还是那就话，别死记网上结论、在使用内外关联时，特别是简写方式时记住关联条件不要乱放！【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。
mysql关于in、exists、join查询性能真实案例分析
猴戏浅滩的博客
03-06 610
【代码】mysql关于in、exists、join的explain语句执行分析。
MySQL- In 和 Exists的优化案例讲解
小工匠
08-08 1万+
文章目录Demo Tablein的逻辑优化原则exists的逻辑 Demo Table CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_a` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; create table
SQL LEFT JOIN与IN使用案例说明
数据科学汇集
12-14 1669
SQL LEFT JOIN与IN使用案例说明
Flink：Lookup Join 实现与示例代码
Laurence的技术博客
03-21 929
本文要演示的是：在流上关联一张外部表（例如 MySQL 数据库中的一张维表），用于丰富流上的数据，实际上，这正是最普遍的 ”维表 Join“ 的实现方式。通过这种方式和外部维表关联时，依然能关联到最新变化的维度数据，所以才说这是 ”维表 Join“。Lookup Join 与 《Flink Temporal Join 示例演示 (2)：Temporal Table DDL + 处理时间》一文演示情形是很相似的，但不同之处在于：这里的维表是通过 JDBC 方式连接的，而文章中的 Temporal Join 方
Spark性能调优案例-多表join优化，减少shuffle
u014034497的专栏
11-12 2148
A任务在凌晨1点到3点，平均耗时1h,且是核心公共任务，急需优化。
oracle in 空值替换,【学习笔记】测试not in或not exists对连接列有空值的处理方法...
weixin_35639750的博客
04-03 487
【学习笔记】测试not in或not exists对连接列有空值的处理方法时间:2016-11-02 20:13来源:Oracle研究中心作者:HTZ点击:次天萃荷净Oracle研究中心学习笔记：分享一篇关于SQL语句在连接查询时遇到列值有空值的处理方法，测试not in或not exists对连接列有空值的案例。1，环境介绍oracleplus.net> select *...
【MySQL】MySQL STRAIGHT JOIN 使用案例以及简介
九师兄
07-26 1217
只不过左表始终在右表之前读取。这可用于联接优化器以次优顺序处理表的那些（少数）情况。注意：总的来说，因为left join、right join已经知道了哪个表作为驱动表，哪个表作为被驱动表，比如left join就是以左表为驱动表，right join反之，而，所以这个特性可以用于一些调优，强制改变mysql的优化器选择的执行计划。
SQL优化之子查询-IN和EXISTS哪个快点
南山南的Python和大数据学习笔记
03-27 612
关注我的微信公众号：pythonislover,领取python,大数据，SQL优化相关视频资料！~ Python大数据与SQL优化笔 QQ群：771686295 对SQL优化，有一定理解的人都会知道，SQL优化的核心是减少物理IO的次数，说的通俗点，我们要尽量减少表的扫描次数，这里的表主要是大表。 今天说的子查询，我们可以理解为SQL包含IN, NOT IN, ...
MySQL数据库的详细学习步骤
2401_87352036的博客
10-11 1199
MySQL数据库的详细学习步骤可以归纳为以下几个阶段，每个阶段都包含了特定的学习内容和目标。
Java分布式锁
juanmiao的专栏
10-12 655
每种方法都有其优点和适应的场景，数据库通常简单，但可能存在性能问题，redis非常快，但需要第三方库，并且可能引入网诺问题，Zookeeper提供了更复杂的同步原语，但需要额外学习曲线。分布式锁的实现比线程锁和进程锁要复杂得多，因为它需要在网诺中的多个节点之间进行协调，以保证锁的唯一性和一致性。在实现分布式锁时，通常会有一个中心节点（或者称为锁服务），所有需要获取锁的节点都需要向这个中心节点申请。这个中心节点负责协调和管理所有节点的锁请求，确保锁的唯一性和一致性。创建临时节点，执行业务逻辑，释放锁。
mysql锁之乐观锁、悲观锁、表锁、行锁、共享锁、排他锁
最新发布
qq_68883928的博客
10-14 547
锁是计算机协调多个进程或线程并发访问某一个资源的机制，在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所在有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。
SQLite数据库在Android中的应用及操作方式
Good_tea_h的博客
10-14 555
SQLite数据库在Android中的应用非常广泛，它作为一个轻量级的关系型数据库，以其高效、可靠和易于使用的特点，成为了Android平台上存储和管理数据的首选方案。以下是对SQLite数据库在Android中的应用及操作方式的详细介绍。
python 数据库封装完整 案例
03-28
以下是一个使用Python封装数据库的完整案例，该案例使用了SQLite3数据库。 1. 创建数据库连接 ```python import sqlite3 class Database: def __init__(self, db_name): self.db_name = db_name self.conn = None self.cursor = None def connect(self): self.conn = sqlite3.connect(self.db_name) self.cursor = self.conn.cursor() def disconnect(self): self.conn.close() db = Database('MyDatabase.db') db.connect() ``` 2. 创建表格 ```python class Table: def __init__(self, db, table_name, columns): self.db = db self.table_name = table_name self.columns = columns def create(self): self.db.cursor.execute(f"CREATE TABLE IF NOT EXISTS {self.table_name} ({self.columns})") self.db.conn.commit() users_table = Table(db, 'users', 'id INTEGER PRIMARY KEY, name TEXT, email TEXT') users_table.create() ``` 3. 插入数据 ```python class User: def __init__(self, db, table_name): self.db = db self.table_name = table_name def insert(self, data): columns = ', '.join(data.keys()) values = ', '.join(['?' for _ in range(len(data))]) sql = f"INSERT INTO {self.table_name} ({columns}) VALUES ({values})" self.db.cursor.execute(sql, tuple(data.values())) self.db.conn.commit() user = User(db, 'users') user.insert({'name': 'John Doe', 'email': 'johndoe@example.com'}) ``` 4. 查询数据 ```python class Query: def __init__(self, db, table_name): self.db = db self.table_name = table_name def select_all(self): sql = f"SELECT * FROM {self.table_name}" self.db.cursor.execute(sql) return self.db.cursor.fetchall() query = Query(db, 'users') users = query.select_all() print(users) ``` 5. 更新数据 ```python class Update: def __init__(self, db, table_name): self.db = db self.table_name = table_name def update(self, data, where=None): set_clause = ', '.join([f"{k} = ?" for k in data.keys()]) where_clause = '' if where: where_clause = f" WHERE {where}" sql = f"UPDATE {self.table_name} SET {set_clause}{where_clause}" self.db.cursor.execute(sql, tuple(data.values())) self.db.conn.commit() update = Update(db, 'users') update.update({'email': 'janedoe@example.com'}, where="name='Jane Doe'") ``` 6. 删除数据 ```python class Delete: def __init__(self, db, table_name): self.db = db self.table_name = table_name def delete(self, where=None): where_clause = '' if where: where_clause = f" WHERE {where}" sql = f"DELETE FROM {self.table_name}{where_clause}" self.db.cursor.execute(sql) self.db.conn.commit() delete = Delete(db, 'users') delete.delete(where="name='John Doe'") ``` 7. 关闭数据库连接 ```python db.disconnect() ```
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值