検索
ホームページデータベースmysql チュートリアルoracle标量子查询简介和表连接改写

oracle标量子查询简介和表连接改写

Jun 07, 2016 pm 04:36 PM
oracleリライトお問い合わせ導入接続する量子

之前小鱼就听过了标量子查询,不过对于其中的细节理解还是远远不够,借助一部分资料和自己测试对标量子查询做一点简单的分析和介绍。 Oracle允许在select子句中包含单行子查询,这个也就是oracle的标量子查询,标量子查询有点类似于外连接,当使用到外连接时

之前小鱼就听过了标量子查询,不过对于其中的细节理解还是远远不够,借助一部分资料和自己测试对标量子查询做一点简单的分析和介绍。

Oracle允许在select子句中包含单行子查询,这个也就是oracle的标量子查询,标量子查询有点类似于外连接,当使用到外连接时我们可以灵活的将其转化为标量子查询。

SQL> create table t1 as select * from all_users;

Table created.

SQL> create table t2 as select * from all_objects;

Table created.

SQL> select a.object_id,(select b.username from t1 b where a.owner=b.username) f
rom t2 a;

49812 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1364172329

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 53276 | 1560K| 152 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 17 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T2 | 53276 | 1560K| 152 (1)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("B"."USERNAME"=:B1)

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
4282 consistent gets
0 physical reads
0 redo size
1176699 bytes sent via SQL*Net to client
37012 bytes received via SQL*Net from client
3322 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
49812 rows processed

标量子查询其实还是一个子查询,那么它究竟是如何查询的:首先走的是外部的查询,比如上一个sql语句执行计划,先全表扫描的T2 a,然后取T2 a表的每一行数据就去和T1 b去过滤,过滤条件是a.owner=b.username,如果符合则返回子查询的值,如果不符合则用null补充。当然这个时候还有个类似的filter去重的运算,对于t2 a中重复的数据行不用再去和t1 b去过滤。

而上面这个标量子查询的sql语句其实是等价于下面外连接sql语句的:
SQL> select a.object_id,b.username from t2 a,t1 b
2 where a.owner=b.username(+) ;

49812 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 535089106

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 53276 | 2445K| 155 (2)| 00:00:02 |
|* 1 | HASH JOIN RIGHT OUTER| | 53276 | 2445K| 155 (2)| 00:00:02 |
| 2 | TABLE ACCESS FULL | T1 | 23 | 391 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T2 | 53276 | 1560K| 152 (1)| 00:00:02 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("A"."OWNER"="B"."USERNAME"(+))

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
4043 consistent gets
0 physical reads
0 redo size
1176659 bytes sent via SQL*Net to client
37012 bytes received via SQL*Net from client
3322 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
49812 rows processed

而如果标量子查询中如果主查询的一行对应子查询返回有多个值,这个是不允许的
SQL> select a.username,b.object_id from t1 a,t2 b
2 where a.username=b.owner(+);

29742 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1823443478

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 53276 | 2445K| 155 (2)| 00:00:02 |
|* 1 | HASH JOIN OUTER | | 53276 | 2445K| 155 (2)| 00:00:02 |
| 2 | TABLE ACCESS FULL| T1 | 23 | 391 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 53276 | 1560K| 152 (1)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("A"."USERNAME"="B"."OWNER"(+))

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
2726 consistent gets
0 physical reads
0 redo size
654542 bytes sent via SQL*Net to client
22294 bytes received via SQL*Net from client
1984 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
29742 rows processed

SQL> select a.username,(select b.object_id from t2 b where a.username=b.owner) f
rom t1 a;
select a.username,(select b.object_id from t2 b where a.username=b.owner) from t
1 a
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row

这里由于a.username=b.owner,其中b.owner有多个相同的值,所以这里返回的b.object_id可能有多个值,这里就出现上述的ora-01427错误。

标量子查询中也可以有聚合函数的出现:
SQL> set autotrace traceonly;
SQL> select a.username,max(b.object_id) from t1 a,t2 b
2 where a.username=b.owner(+)
3 group by a.username;

23 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 577572187

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 53276 | 2445K| 159 (4)| 00:00:02 |
| 1 | HASH GROUP BY | | 53276 | 2445K| 159 (4)| 00:00:02 |
|* 2 | HASH JOIN OUTER | | 53276 | 2445K| 155 (2)| 00:00:02 |
| 3 | TABLE ACCESS FULL| T1 | 23 | 391 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T2 | 53276 | 1560K| 152 (1)| 00:00:02 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("A"."USERNAME"="B"."OWNER"(+))

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
92 recursive calls
0 db block gets
777 consistent gets
685 physical reads
0 redo size
1169 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
23 rows processed

SQL> select a.username,(select max(b.object_id) from t2 b where b.owner=a.userna
me) from t1 a;

23 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 367820

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23 | 391 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| T2 | 533 | 15990 | 152 (1)| 00:00:02 |
| 3 | TABLE ACCESS FULL | T1 | 23 | 391 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("B"."OWNER"=:B1)

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
15929 consistent gets
0 physical reads
0 redo size
1206 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
23 rows processed

但是我们注意到上述标量子查询却存在一个问题,就是无法将子查询展开为表连接,换句话说无法采用灵活的hash join outer的关联方式。

关于标量子查询和表关联的性能简介:
如果主查询返回的数据较多,而子查询中又没有高效的索引,关联列对应的主查询表又没有较多的重复值,那么这个标量子查询的执行成本是很大的,如上面的标量子查询和外连接的sql语句中可以看出外连接IO成本要明显小于标量子查询。

但是标量子查询oracle内部确是有优化的,优化器cache了中间的结果,如果结果集不大,子查询中又有高效的索引,那么这个标量子查询可能会比常规的表关联更加高效。

小鱼列出几种常会涉及到的标量子查询和表连接的sql改写:
1 最简单的标量子查询
table :a(a1,a2),b(a1,b2)
select a2,(select b2 from b where b.a1=a.a1) from a

表连接:
select a2,b2 from a,b where a.a1=b.a1(+);

2 子查询中限制返回一行数据
table :a(a1,a2),b(a1,b2)
select a2,(select b2 from b where b.a1=a.a1 and rownum=1) from a

表连接:
SELECT a2, c.b2
FROM a,
(SELECT b2, ROW_NUMBER () OVER (PARTITION BY a1 ORDER BY a1) cn
FROM b
WHERE cn = 1) c
WHERE a.a1 = c.b2(+);

3 子查询带有聚合函数
table :a(a1,a2),b(a1,b2)
select a2,(select sum(b2) from b where b.a1 = a.a1) from a

表连接1:
SELECT a2, bb.sum_value
FROM a,
( SELECT SUM (b2) sum_value, a1
FROM b
GROUP BY a1) bb
WHERE a.a1 = b.a1(+);
表连接2:
SELECT a2, SUM (b2)
FROM a, b
WHERE a.a1 = b.a1(+)
GROUP BY a2;

4 查询中包括好几个标量子查询
table :a(a1,a2),b(a1,b2),c(a1,b2,c2)
SELECT a.a2,
(SELECT c2
FROM b, c
WHERE b.a1 = a.a1 AND b.b2 = c.b2 AND ROWNUM = 1),
(SELECT b2
FROM b
WHERE b.a1 = a.a1 AND ROWNUM = 1),
(SELECT c2
FROM c
WHERE c.a1 = a.a1 AND ROWNUM = 1)
FROM a

表连接:
SELECT a.a2,
bb.c2,
cc.b2,
dd.c2
FROM (SELECT c2,
b.a1,
ROW_NUMBER () OVER (PARTITION BY b.a1 ORDER BY b.a1) cnt
FROM b, c
WHERE b.b2 = c.b2 AND cnt = 1) bb,
(SELECT b2, ROW_NUMBER () OVER (PARTITION BY a1 ORDER BY a1) cnt
FROM b
WHERE cnt = 1) cc,
(SELECT c2, ROW_NUMBER () OVER (PARTITION BY a1 ORDER BY a1) cnt
FROM c
WHERE cnt = 1) dd,
a
WHERE a.a1 = bb.a1(+) AND a.a1 = cc.a1(+) AND a.a1 = dd.a1(+);

SELECT a2,
(SELECT SUM (c2)
FROM b, c
WHERE b.a1 = a.a1 AND b.b2 = c.b2),
(SELECT SUM (b2)
FROM b
WHERE b.a1 = a.a1),
(SELECT SUM (c2)
FROM c
WHERE c.a1 = a.a1)
FROM a
表连接:
SELECT a2,
bb.sum1,
cc.sum2,
dd.sum3
FROM ( SELECT SUM (c2) sum1, b.a1
FROM b, c
WHERE b.b2 = c.b2
GROUP BY b.a1) bb,
( SELECT SUM (b2) sum2, a1
FROM b
GROUP BY a1) cc,
( SELECT SUM (c2) sum3, a1
FROM c
GROUP BY a1) dd,
a
WHERE a.a1 = bb.a1(+) AND a.a1 = cc.a1(+) AND a.a1 = dd.a1(+);

声明
この記事の内容はネチズンが自主的に寄稿したものであり、著作権は原著者に帰属します。このサイトは、それに相当する法的責任を負いません。盗作または侵害の疑いのあるコンテンツを見つけた場合は、admin@php.cn までご連絡ください。
MySQL:初心者が習得するための必須スキルMySQL:初心者が習得するための必須スキルApr 18, 2025 am 12:24 AM

MySQLは、初心者がデータベーススキルを学ぶのに適しています。 1.MySQLサーバーとクライアントツールをインストールします。 2。selectなどの基本的なSQLクエリを理解します。 3。マスターデータ操作:テーブルを作成し、データを挿入、更新、削除します。 4.高度なスキルを学ぶ:サブクエリとウィンドウの関数。 5。デバッグと最適化:構文を確認し、インデックスを使用し、選択*を避け、制限を使用します。

MySQL:構造化データとリレーショナルデータベースMySQL:構造化データとリレーショナルデータベースApr 18, 2025 am 12:22 AM

MySQLは、テーブル構造とSQLクエリを介して構造化されたデータを効率的に管理し、外部キーを介してテーブル間関係を実装します。 1.テーブルを作成するときにデータ形式と入力を定義します。 2。外部キーを使用して、テーブル間の関係を確立します。 3。インデックス作成とクエリの最適化により、パフォーマンスを改善します。 4.データベースを定期的にバックアップおよび監視して、データのセキュリティとパフォーマンスの最適化を確保します。

MySQL:説明されている主要な機能と機能MySQL:説明されている主要な機能と機能Apr 18, 2025 am 12:17 AM

MySQLは、Web開発で広く使用されているオープンソースリレーショナルデータベース管理システムです。その重要な機能には、次のものが含まれます。1。さまざまなシナリオに適したInnodbやMyisamなどの複数のストレージエンジンをサポートします。 2。ロードバランスとデータバックアップを容易にするために、マスタースレーブレプリケーション機能を提供します。 3.クエリの最適化とインデックスの使用により、クエリ効率を改善します。

SQLの目的:MySQLデータベースとの対話SQLの目的:MySQLデータベースとの対話Apr 18, 2025 am 12:12 AM

SQLは、MySQLデータベースと対話して、データの追加、削除、変更、検査、データベース設計を実現するために使用されます。 1)SQLは、ステートメントの選択、挿入、更新、削除を介してデータ操作を実行します。 2)データベースの設計と管理に作成、変更、ドロップステートメントを使用します。 3)複雑なクエリとデータ分析は、ビジネス上の意思決定効率を改善するためにSQLを通じて実装されます。

初心者向けのMySQL:データベース管理を開始します初心者向けのMySQL:データベース管理を開始しますApr 18, 2025 am 12:10 AM

MySQLの基本操作には、データベース、テーブルの作成、およびSQLを使用してデータのCRUD操作を実行することが含まれます。 1.データベースの作成:createdatabasemy_first_db; 2。テーブルの作成:createTableBooks(idintauto_incrementprimarykey、titlevarchary(100)notnull、authorvarchar(100)notnull、published_yearint); 3.データの挿入:InsertIntoBooks(タイトル、著者、公開_year)VA

MySQLの役割:WebアプリケーションのデータベースMySQLの役割:WebアプリケーションのデータベースApr 17, 2025 am 12:23 AM

WebアプリケーションにおけるMySQLの主な役割は、データを保存および管理することです。 1.MYSQLは、ユーザー情報、製品カタログ、トランザクションレコード、その他のデータを効率的に処理します。 2。SQLクエリを介して、開発者はデータベースから情報を抽出して動的なコンテンツを生成できます。 3.MYSQLは、クライアントサーバーモデルに基づいて機能し、許容可能なクエリ速度を確保します。

MySQL:最初のデータベースを構築しますMySQL:最初のデータベースを構築しますApr 17, 2025 am 12:22 AM

MySQLデータベースを構築する手順には次のものがあります。1。データベースとテーブルの作成、2。データの挿入、および3。クエリを実行します。まず、createdAtabaseおよびcreateTableステートメントを使用してデータベースとテーブルを作成し、InsertINTOステートメントを使用してデータを挿入し、最後にSelectステートメントを使用してデータを照会します。

MySQL:データストレージに対する初心者向けのアプローチMySQL:データストレージに対する初心者向けのアプローチApr 17, 2025 am 12:21 AM

MySQLは、使いやすく強力であるため、初心者に適しています。 1.MYSQLはリレーショナルデータベースであり、CRUD操作にSQLを使用します。 2。インストールは簡単で、ルートユーザーのパスワードを構成する必要があります。 3.挿入、更新、削除、および選択してデータ操作を実行します。 4. Orderby、Where and Joinは複雑なクエリに使用できます。 5.デバッグでは、構文をチェックし、説明を使用してクエリを分析する必要があります。 6.最適化の提案には、インデックスの使用、適切なデータ型の選択、優れたプログラミング習慣が含まれます。

See all articles

ホットAIツール

Undresser.AI Undress

Undresser.AI Undress

リアルなヌード写真を作成する AI 搭載アプリ

AI Clothes Remover

AI Clothes Remover

写真から衣服を削除するオンライン AI ツール。

Undress AI Tool

Undress AI Tool

脱衣画像を無料で

Clothoff.io

Clothoff.io

AI衣類リムーバー

AI Hentai Generator

AI Hentai Generator

AIヘンタイを無料で生成します。

ホットツール

ドリームウィーバー CS6

ドリームウィーバー CS6

ビジュアル Web 開発ツール

AtomエディタMac版ダウンロード

AtomエディタMac版ダウンロード

最も人気のあるオープンソースエディター

ゼンドスタジオ 13.0.1

ゼンドスタジオ 13.0.1

強力な PHP 統合開発環境

SublimeText3 Mac版

SublimeText3 Mac版

神レベルのコード編集ソフト(SublimeText3)

DVWA

DVWA

Damn Vulnerable Web App (DVWA) は、非常に脆弱な PHP/MySQL Web アプリケーションです。その主な目的は、セキュリティ専門家が法的環境でスキルとツールをテストするのに役立ち、Web 開発者が Web アプリケーションを保護するプロセスをより深く理解できるようにし、教師/生徒が教室環境で Web アプリケーションを教え/学習できるようにすることです。安全。 DVWA の目標は、シンプルでわかりやすいインターフェイスを通じて、さまざまな難易度で最も一般的な Web 脆弱性のいくつかを実践することです。このソフトウェアは、