SQL学習したこと一覧

勉強用スキーマ

スキーマ 勉強本
squat 改訂新版 反復学習ソフト付き SQL書き方ドリル (WEB+DB PRESS plusシリーズ)
dondon どんどん身につく書き込み式SQLのドリル 改訂新版
sukkiri スッキリわかるSQL入門 ドリル215問付き!
pazzle SQLパズル 第2版~プログラミングが変わる書き方/考え方
tatujin 達人に学ぶ SQL徹底指南書

学習中

改訂新版 反復学習ソフト付き SQL書き方ドリル
C:\Users\kenta\Documents\W07_SQL\学習進捗.xlsx
SQUAT/chapXX
XX_DXX.sql →ドリルのSQL
XX_PXX.sql →練習のSQL

Oracleで他にもスキーマあるのを確認する方法

SELECT * FROM dba_users

スキーマの作成

CREATE USER SCHEMA
IDENTIFIED BY "SCHEMA"
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
/

SQL> GRANT DBA TO SCHEMA ;
 
権限付与が成功しました。
 
SQL> GRANT UNLIMITED TABLESPACE TO SCHEMA ;
 
権限付与が成功しました。

スキーマの削除

drop user schema
/

集合関数

よくやる間違い

集合関数はselect句でのみしか使用可能。
where句では、集合関数は使用できない。
having句では、集合関数は使用できる。

group by havingのイメージ

group byは指定されたキーでテーブルに区切りのラインを付けるイメージ。
havingはその中から消し込み線を入れるイメージ
図でイメージするOracle DatabaseのSQL全集 第4回 集約関数など

オラクル参考になったページ

SQLを学ぶにあたって
リレーショナル・データベースの世界
僕はSQLをこう学んだ | mah365
SQLアタマ養成講座:連載|gihyo.jp … 技術評論社


SQL*Plus コマンド一覧 - オラクル・Oracle SQL*Plus リファレンス
SQL*Plusコマンド・リファレンス
目次
SQL*Plus システム変数一覧 - オラクル・Oracle SQL*Plus リファレンス
データベースコンサルタントのノウハウちょい見せ
ORACLEで、TABLEやVIEWなどの定義情報を見る方法 - 闇忍日記

PL/SQL
・公式マニュアル
Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス 11g リリース1(11.1)
Oracle Database PL/SQL言語リファレンス, 11gリリース2(11.2)

書き込み式SQLのドリル 改訂新版 - WINGS
連載 PL/SQLを使ってみよう! | 集合研修 システム・テクノロジー・アイ銀座会場
■手を動かすためのツール
Dokoql


Oracleの外部結合演算子の覚え方を教えてください。 ANSI準拠の… - 人力検索はてな

datapump

データパンプのヘルプ

以下を実行することでデータパンプに必要な知識が見れます。

expdp -help
impdp -help

データパンプの準備

ディレクトリオブジェクトの作成

CREATE DIRECTORY TEST_DIR AS '/u01/app/oracle/admin/XE/test_dir';

ディレクトリオブジェクトへの権限付与

GRANT READ ON DIRECTORY TEST_DIR TO SCOTT ;

ディレクトリオブジェクトが作成されたことの確認

select * from all_directories;

コマンドの実行(一番簡単なコマンド)

expdp scott/tiger DIRECTORY=TEST_DIR TABLES=table_name

色々つけると最終的にこうなる(例えば↓
expdp scott/tiger DIRECTORY=TEST_DIR DUMPFILE=xxx.dmp LOGFILE=xxx.log TABLES=table_name

ヒント句一覧

SQL> desc V$SQL_HINT
 名前            NULL?    型
 ----------------- -------- ------------
 NAME                       VARCHAR2(64)
 SQL_FEATURE                VARCHAR2(64)
 CLASS                      VARCHAR2(64)
 INVERSE                    VARCHAR2(64)
 TARGET_LEVEL               NUMBER
 PROPERTY                   NUMBER
 VERSION                    VARCHAR2(25)
 VERSION_OUTLINE            VARCHAR2(25)

SQL> select * V$SQL_HINT^CV$SQ

ヒント句 オプション

CHOOSE 統計情報があれば、コストベース。なければ、ルールベースを選択。
RULE ルールベース
ALL_ROWS コストベース、スループット(全行出力)重視。
FIRST_ROWS コストベース、レスポンス(最初の行出力)重視。
FIRST_ROWS(N) コストベース、レスポンス(最初のN行出力)重視。
FULL(テーブル名) テーブルフルスキャン(全表走査)による検索。
ROWID(テーブル名) ROWID(作成順列番号)による検索。
INDEX(テーブル名 インデックス名) 使用インデックスを指定。複数書くとオプティマイザが自動選択。
INDEX_FFS インデックスフルスキャン(全索引走査)による検索。
HASH(テーブル名) ハッシュ検索による検索。
HASH_AJ(テーブル名) NOT IN副問い合わせを、ハッシュアンチジョイン(逆結合)に変換して検索。
MERGE_AJ(テーブル名) NOT IN副問い合わせを、マージアンチジョイン(逆結合)に変換して検索。
INDEX(テーブル名) テーブル内のインデックスの中からオプティマイザが自動選択。
AND_EQUALS(テーブル名 インデックス名 インデックス名) 複数のインデックスを結合して使用。
INDEX_DESC(テーブル名 インデックス名) 複合インデックスを降順に使用。ORDERD from句の記述順で、テーブルを結合。
USE_MERGE(テーブル名) ソートマージジョインで、テーブルを結合。
USE_NL(テーブル名) ネステッドループスジョインで、テーブルを結合。
USE_HASH(テーブル名) ハッシュジョインで、テーブルを結合。
PUSH_SUBQ 結合対象とならない副問い合わせを最初に解析。

sqlplus上でsqlの編集から実行まで

sqlplus上でsqlファイルの編集から実行までを行う方法

0.バッファーのSQLを保存する。

save a.sql

1.保存したSQLを編集

# デフォルトはedが設定されている。
SQL> def _editor
DEFINE _EDITOR         = "ed" (CHAR)

# エディタをviに変更
SQL> DEFINE _EDITOR         ="vi"
SQL> DEFINE _EDITOR
DEFINE _EDITOR         = "vi" (CHAR)

SQL>edit a.sql

2.保存したSQLを実行(ファイルから)

SQL> @test.sql

3.保存したSQLを実行(バッファーから)

SQL> get test.sql;
SQL> l
SQL> r

外部結合

外部結合とは

索引について

  • 索引とは

索引とは、DB上のデータを効率的に取得するために使用するDBオブジェクトのこと
ルートブロック、ブランチブロック、リーフブロック
ルートブロックには、ブランチブロックへのキーの範囲と下位ブロックのポインタが入っている、
リーフブロックには、ROWIDとキー値が入っている。

  • 索引の種類は
  • 索引の中身を見てみよう
ROWID
------------------
AAAZBiAAEAAAAOEADt
AAAZBiAAEAAAAOEADu
AAAZBiAAEAAAAOEADv

SQL> select * from sales where rowid = 'AAAZBiAAEAAAAOEADv';
    SALEID   QUANTITY CUSTOMERID  PRODUCTID EMPLOYEEID SALEDATE
---------- ---------- ---------- ---------- ---------- --------
       998          1         14          8         23 07-08-28

SQL> analyze index sales_index compute statistics;
索引が分析されました。

SQL> select HEIGHT,BLOCKS,NAME,LF_ROWS,LF_BLKS,BR_ROWS,BR_BLKS from index_stats;
    HEIGHT     BLOCKS NAME                              LF_ROWS    LF_BLKS    BR_ROWS    BR_BLKS
---------- ---------- ------------------------------ ---------- ---------- ---------- ----------
         2          8 SALES_INDEX                          1001          2          1          1

Oracleパフォーマンス障害の克服(3):Bツリーインデックスに最高のパフォーマンスを (2/4) - @IT

ANALYZE
■マージジョイン

SQL> r
  1* select * from sales s,products p  where s.productid = p.productid

1000行が選択されました。


実行計画
----------------------------------------------------------
Plan hash value: 3026272181

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |  1000 | 44000 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |              |  1000 | 44000 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| PRODUCTS     |    50 |  1000 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | SYS_C0015209 |    50 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |              |  1000 | 24000 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | SALES        |  1000 | 24000 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   4 - access("S"."PRODUCTID"="P"."PRODUCTID")
       filter("S"."PRODUCTID"="P"."PRODUCTID")


統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         84  consistent gets
          0  physical reads
          0  redo size
      42393  bytes sent via SQL*Net to client
       1246  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       1000  rows processed

SQL>