8月5日 ，雲和恩墨高階技術顧問[範計傑老師](https://www.modb.pro/u/3770?1009)，在墨天輪上分享了[《經典知識庫：Oracle DBA的SQL編寫技能提升寶典》](https://www.modb.pro/event/356?1009)的直播。老師圍繞實際運維場景下的案例，向大家介紹了提升SQL編寫技能的乾貨和小技巧。

當晚，朋友們對這場直播的反響十分熱烈，考慮到有些朋友沒有來得及參與，這裡小編為大家整理了文字版乾貨重點、PPT資源以及直播回放，有需要的朋友們可以收藏、檢視。（文末還有部分SQL的[資源下載包](https://www.modb.pro/db/121698?1009 "資源下載包")。）

## 直播乾貨

### 一、運維場景SQL例項

#### 案例一：建立表空間

背景：要遷移資料庫，需要建立與源庫相同的表空間，大小與源庫相同。由於個別表空間較大，手工新增可能需要寫很多的指令碼，於是同事透過PL/SQL解決了問題。

但實際上透過一條SQL就可以搞定，步驟?

建立表空間——新增資料檔案，直到與源庫大小相同

1.  查詢源表空間的大小；
    
2.  生成檔案列表；
    
3.  進行關聯
    
SQL如下：

```sql
col sqltext for a999
with t as
(select tablespace_name tsname, round(sum(bytes) / 1024 / 1024 / 1024) gb
   from dba_data_files
  group by tablespace_name),
t2 as
(select rownum n from dual
       connect by rownum < 1024)
select decode(b.n, 1,'create tablespace','alter tablespace')||
       a.tsname || ' datafile''+DATADG''size 30G;'sqltxt
  from t a, t2 b
 where 30 * b.n < a.gb
 order by a.tsname, b.n;

```
  
![1.jpg](https://oss-emcsprod-public.modb.pro/image/editor/20210929-08d0ef73-91d3-4427-9ccb-910a2c4e1ebd.jpg)

#### 案例二：巡檢-異常檢測-週期資料

資料庫主機CPU一般每天隨著上下班時間進行規律性波動。在這種資料中，怎麼找出CPU使用率異常的主機、時間點？

**1、從一堆資料庫中找出異常的資料庫節點**
    
![2.png](https://oss-emcsprod-public.modb.pro/image/editor/20210929-887e6169-68cc-4e92-ad95-e9c2a2350477.png)![3.png](https://oss-emcsprod-public.modb.pro/image/editor/20210929-6575efb2-05d9-4c67-b22a-7f912d30cb28.png)

**2、找出波動區域後，針對異常節點Excel畫圖，以觀察其與正常負載的區別**
![4.png](https://oss-emcsprod-public.modb.pro/image/editor/20210929-c7cf4955-936c-4722-abb0-b830c74c9fb4.png)

**3、異常主機查詢-主要SQL介紹**

a. 以天、小時為單位對資料分組
    
b. 用分析函式取平均值分析標準方差，以更加精準定位問題主機
    
```sql
with t as
 (select to_char(exectime, 'yyyy-mm-dd') d,
         to_char(exectime, 'hh24') h,
         hostname,
         round(avg(100 - id)) cpu
    from sys_vmstat
   where exectime between trunc(sysdate - 7, 'dd') and trunc(sysdate, 'dd')
     and (hostname like 'yyyy%' or hostname like 'zzzz%')
   group by to_char(exectime, 'yyyy-mm-dd'),
            to_char(exectime, 'hh24'),
            hostname),
t2 as
 (select d,
         h,
         hostname,
         cpu,
         round(avg(cpu) over(partition by hostname, h)) avg_cpu,
         round(STDDEV(cpu) over(partition by hostname, h)) cpu_stddev
    from t)
select *
  from t2
 where cpu_stddev < 15
   and cpu - avg_cpu > 20
 order by cpu - avg_cpu desc;
```

c. 生成7天對比圖，使用pivot函式將每天每小時的sql使用率作圖
    
```sql
with t as (select to_char(exectime,'yyyy-mm-dd')day,to_char(exectime,'hh24') hour,hostname,round(avg(100-id)) cpu from gm.sys_vmstat  
where exectime between trunc(sysdate-7,'dd') and  trunc(sysdate,'dd') and hostname='testb2'
group by to_char(exectime,'yyyy-mm-dd'),to_char(exectime,'hh24') ,hostname)
SELECT day,
       "00-01_ ",
       "01-02_ ",
       "02-03_ ",
       "03-04_ ",
       "04-05_ ",
       "05-06_ ",
       "06-07_ ",
       "07-08_ ",
       "08-09_ ",
       "09-10_ ",
       "10-11_ ",
       "11-12_ ",
       "12-13_ ",
       "13-14_ ",
       "14-15_ ",
       "15-16_ ",
       "16-17_ ",
       "17-18_ ",
       "18-19_ ",
       "19-20_ ",
       "20-21_ ",
       "21-22_ ",
       "22-23_ ",
       "23-24_ "
 From  t pivot(sum(CPU) as " " for hour in('00' AS "00-01",
                                         '01' AS "01-02",
                                         '02' AS "02-03",
                                         '03' AS "03-04",
                                         '04' AS "04-05",
                                         '05' AS "05-06",
                                         '06' AS "06-07",
                                         '07' AS "07-08",
                                         '08' AS "08-09",
                                         '09' AS "09-10",
                                         '10' AS "10-11",
                                         '11' AS "11-12",
                                         '12' AS "12-13",
                                         '13' AS "13-14",
                                         '14' AS "14-15",
                                         '15' AS "15-16",
                                         '16' AS "16-17",
                                         '17' AS "17-18",
                                         '18' AS "18-19",
                                         '19' AS "19-20",
                                         '20' AS "20-21",
                                         '21' AS "21-22",
                                         '22' AS "22-23",
                                         '23' AS "23-24"))

```

#### 案例三：巡檢-異常檢測-異常波動

平時維護資料庫較多時，若想透過人工找出某一資料庫在何時間發生過較大的負載波動，會比較麻煩，透過以下 SQL篩查的方式會相對高效。

具體查詢SQL可從後文“==SQL資源下載==”中的資源包下載。

**1、透過函式將前後幾分鐘的負載變化選出**
    
**2、透過發生問題的時間點、例項採用LISTAGG生成負載變化圖，透過excel生成直觀曲線圖**
    
![8.png](https://oss-emcsprod-public.modb.pro/image/editor/20210929-59432a16-0a36-49e6-b29d-23dc31448955.png)![7.png](https://oss-emcsprod-public.modb.pro/image/editor/20210929-e82fd532-290e-43f5-916a-a39a24fbacad.png)

最後製作成的excle表格如下

![9.png](https://oss-emcsprod-public.modb.pro/image/editor/20210929-1bb75318-6b98-4039-8c29-9d5a4782b21e.png)

#### 案例四：巡檢-表空間分析

透過分析表空間的變化，可以瞭解表空間有無異常增長或變化明顯的情況，以合理規劃表空間。

![10.png](https://oss-emcsprod-public.modb.pro/image/editor/20210929-66422e54-43d0-46e8-97a5-04b52d1f502b.png)

**1、儲存空間規劃**

❓如以下表空間每天增長、清理，有少量淨增長，表空間預留多大才能使用90天？

![11.png](https://oss-emcsprod-public.modb.pro/image/editor/20210929-9d27bafb-8bf5-49e5-8281-43bb9a8e1480.png)

<font color="FireBrick" size="3">**計算公式：每天淨增長\*預留天數+每天最大使用量+最小保留**</font>

![12.png](https://oss-emcsprod-public.modb.pro/image/editor/20210929-02b08cf6-60c3-4369-b458-503be03c3111.png)
淨增長的計算方式

![13.png](https://oss-emcsprod-public.modb.pro/image/editor/20210929-8dd4e737-cb11-4827-bc9c-53fd2a4b7504.png)

**2、等待鏈**

在分析效能問題或堵塞時需要對等待鏈進行分析。

```sql
set lines 1000
col wait_chain format a160
with t as
 (select * from gv$session),
t2 as
 (select level lv,
         sid,
         serial#,
         program,
         event,
         connect_by_iscycle iscycle,
         connect_by_isleaf leaf,
         LEVEL,
         SYS_CONNECT_BY_PATH('['||program||']'||'('||                
           to_char(inst_id)||'-'||nvl(event,state)||')',
           '->') wait_chain
   from t
 connect by NOCYCLE prior blocking_session= sid
          and  prior blocking_instance= inst_id
   start with state='WAITING')
select wait_chain,count(*),max(iscycle)iscycle 
  from t2 
 where leaf=1
   AND LV>1 
 group by wait_chain
 order by count(*) desc;

```

不同的場景則需對SQL進行調整。

（1）使用gv%session函式
    
![14.jpg](https://oss-emcsprod-public.modb.pro/image/editor/20210929-0d177fc9-2dab-479e-a9e5-c2c8a088a4d9.jpg)
（2）使用ISCYCLE

![15.jpg](https://oss-emcsprod-public.modb.pro/image/editor/20210929-b74e1787-38f1-4417-a1d7-2c70fe03cbd8.jpg)

![16.jpg](https://oss-emcsprod-public.modb.pro/image/editor/20210929-006db369-41bf-4fed-a95e-a19744805294.jpg)
右側可看到堵塞者為SQL\*Net message from client，為空閒等待事件，即活堵塞處為非活動狀態，在執行SQL後未及時提交事務，導致堵塞。

**3、ASH分析**

進行ASH分析，分析等待事件的變化，可以使用event或sql\_id執行。

（1）**event**
```sql
----by event
break on etime
with t as (select to_char(sample_time,'hh24:mi')etime,nvl(event,'ONCPU')event,round(count(*)/60,2) cnt,row_number()over(partition by to_char(sample_time,'hh24:mi')order by count(*) desc) rn from v$active_session_history
where sample_time>sysdate-30/1440
group by to_char(sample_time,'hh24:mi') ,nvl(event,'ONCPU'))
select * from t where rn<=10 order by etime,cnt desc;

```

![17.jpg](https://oss-emcsprod-public.modb.pro/image/editor/20210929-a19d7611-23f5-469a-aaa3-6704117b4676.jpg)

（2）**sql_id**

可以透過函式，看到百分比以及某一時間點的使用次數

```sql
---by sqlid
break on etime
with t as (select to_char(sample_time,'hh24:mi')etime,nvl(sql_id,TOP_LEVEL_CALL_NAME) sql_id,round(count(*)/60,2) cnt,round(ratio_to_report(count(*))over(partition by to_char(sample_time,'hh24:mi'))*100,2) pct, max(SQL_EXEC_ID)-min(SQL_EXEC_ID)+1 execs,row_number()over(partition by to_char(sample_time,'hh24:mi')order by count(*) desc) rn from v$active_session_history
where sample_time>sysdate-30/1440
group by to_char(sample_time,'hh24:mi') ,nvl(sql_id,TOP_LEVEL_CALL_NAME))
select * from t where rn<=10 order by etime,cnt desc;
```

![18.jpg](https://oss-emcsprod-public.modb.pro/image/editor/20210929-9d2cbd27-103b-48bc-9263-d335fd7be4e5.jpg)

**4、SQLPLUS 中的效能監控**

可以使用 PL/SQL PIPE ROW 特性進行實時監控某些指標，函式中指標可配置，用逗號隔開即可

主要SQL如下：

（具體查詢SQL可從後文“==SQL資源下載==”中的資源包下載。）

```sql
dbms_lock.sleep(interval_sec);
PIPE ROW ('------------------------------------');
for r in (select /*+use_hash(s) leading(l,s)*/s.name,s.value,sysdate etime from table(dbmt.split(stat_str))l, v$sysstat s where l.column_value=s.name)
loop
v_interval_sec:=(r.etime-v_date)*24*3600;
ret_str:=to_char(r.etime,'hh24:mi:ss')||' '||rpad(r.name||'/s ',30,'-')||' '|| round((r.value-stat1(r.name))/v_interval_sec,2);
PIPE ROW (ret_str);
stat1(r.name):=r.value;
v_date_new:=r.etime;
end loop;
/
```

![19.jpg](https://oss-emcsprod-public.modb.pro/image/editor/20210929-b64e1785-d589-4e84-b129-b617bd277f92.jpg)

### 二、常用函式介紹、示例

#### 1、分析函式

用來支援進行OLAP（聯機分析處理），提供強大的分析功能

分析函式語法及釋義：

```sql
FUNCTION\_NAME（<argument>，<argument>..…）

OVER（<partition-clause><order-by-clause><windowing clause="">）
```

**（1）FUNCTION子句**

ORACLE提供了很多分析函式，按功能分5類：等級（ranking）函式，開窗（windowing）函式，製表（reporting）函式，LAG/LEAD函式，線性的衰減函式。

**（2）PARTITION子句**

按照表示式分割槽（就是分組），如果省略了分割槽子句，則全部的結果集被看作是一個單一的組

可根據實際情況進行調整，是否需要依不同條件、值進行分組。

**（3）ORDER BY子句**

分析函式中ORDER BY的存在將新增一個預設的開窗子句，這意味著計算中所使用的行的集合是當前分割槽中當前行和前面所有行，沒有ORDER BY時，預設的視窗是全部的分割槽。根據實際情況選擇是否使用。

**（4）WINDOWING子句**

用於定義分析函式將在其上操作的行的集合

Windowing子句給出了一個定義變化或固定的資料視窗的方法，分析函式將對這些資料進行操作，預設的視窗是一個固定的視窗，僅僅在一組的第一行開始，一直繼續到當前行，要使用視窗，必須使用ORDER BY子句，根據2個標準可以建立視窗：資料值的範圍（RANGE）或與當前行的行偏移量（ROWS）。

示例：
![20.png](https://oss-emcsprod-public.modb.pro/image/editor/20210929-5162c8f9-b4da-47ea-9a55-55f1db6b5f82.png)

- RANGE邏輯視窗

針對圖中ID列的值作運算，RANGE\_SUM列為邏輯視窗，意為當前行的值-1到當前行+2的視窗中所包含的值求和

- ROWS物理視窗

針對圖中ID列的值作運算，ROWS\_SUM列為物理視窗，意為當前行的前一行+當前行+後兩行的值求和

#### 2、常用分析函式

**（1）彙總類**

- **Sum**

- **Avg**
- **Count**
-  **Max/min**
- **Ratio\_to\_report**

在透過ASH分析效能時，在進行分組後會計算活動會話的百分比，即可用此函式

```sql
with t as
 (select to_char(sample_time,' hh24: mi') etime,
         nv1(sql_id, TOP_LEVEL_CALL NAME) sql_id,
         round(count(*)/60,2) cnt,
         round(ratio_to report(count(*)).
               over(partition by to_char(sample_time, 'hh24:mi'))*100,
               2) pct,
         max(SQL_EXEC_ID)-min(SQL_EXEC_ID)+1 execs,
         row_number() over(partition by to char(sample_time,' hh24: mi') order by count(*) desc) rn
    from vSactive_session history
   where sample_time>sysdate-30/1440
   group by to_char(sample_time,' hh24: mi'),
            nvl(sql id, TOP LEVELCALLNAME))
select * from t where rn<=10 order by ctime, cnt desc;
```

- **LISTAGG**（多個值平均到一起）

將一個分組中的多個值合成一行
![23.png](https://oss-emcsprod-public.modb.pro/image/editor/20210929-22ad909a-3f73-49d6-8e9f-94ac04e9c0c6.png)

示例：
![21.png](https://oss-emcsprod-public.modb.pro/image/editor/20210929-597b1236-59a3-4e26-b1b6-969efa1c2a20.png)

使用listagg函式後，結果如下：

![22.png](https://oss-emcsprod-public.modb.pro/image/editor/20210929-c27e3da2-b805-469c-ac43-267eef9922cb.png)

**注意**：超長溢位處理方式（最長4000位元組）

a. ON OVERFLOW ERROR
    
b. ON OVERFLOW TRUNCATE
    
 
  
**（2）排行類**

- **Row\_number**

- **Rank**

取每個使用者下最多的兩類物件

```sql
select*
 from（select owner，
        object_type，
        cnt，
        rank（）over（partition by owner order by cnt desc）rank
      from（select owner，object_type，count（*）cnt
          from dbmt.db_objects
         group by owner，object_type）t）
where rank<5
```

- **Dense\_rank**
    
 
**注意**：

a. Row number分配一個唯一的行編號
    
b. Rank排名可能不是連續的數字
    
c. Dense\_rank排名是連續的數字

**（3）其他**

- **LAG , LEAD**

取當前行的前一/幾行（LAG）或後一/幾行（LEAD）中指定值
![24.png](https://oss-emcsprod-public.modb.pro/image/editor/20210929-39bbbfcf-e814-4a05-bb9b-789e3dff26ab.png)

SQL與示例：

```sql
with t as
 (select rownum r from dual connect by level<10)
select r,
1ag(r)over(order by r)lagr,
lead(r)over(order by r)lead_r
  from t;
```

![25.png](https://oss-emcsprod-public.modb.pro/image/editor/20210929-0164dac1-8bd9-40d4-85b0-0fca41df17a9.png)

常用：分析AWR資料，用當前的值-上一行值即可計算增量，示例SQL如下

```sql
select st. instance_number,
    st. snap_id,
    to_char(SN. BEGIN_INTERVAL_TIME,' yyyy-mm-dd hh24: mi") BEGIN_INTERVAL_TIME,
    (value-(lag(value)
    over(partition by st. instance_number order by st. snap_id))) value
 from dba_hist_sysstat st, DBA_HIST_SNAPSHOT sn
where st. INSTANCE_NUMBER=SN. INSTANCE_NUMBER
 and st. SNAP_ID=SN. SNAP_ID
 and sn. begin_interval_time> to_date("2021-03-01', yyyy-mm-dd")
 and sn. instance_number=1
 and stat_name=' gc cr blocks received'
order by st. instance_number, st. snap_id;
```

- **GREATEST , LEAST（取最大值、最小值）**

常用於分析SQL歷史效能

![26.png](https://oss-emcsprod-public.modb.pro/image/editor/20210929-74523682-fab9-4b1c-93d9-44bc76b60fc8.png)

```sql
select ss.plan hash value phv，
    to_char（s.begin_interval_time，'mm-dd HH24：MI"）snap_time，
    ss.instance_number，
    ss.executions delta execs，
    round（ss.rows processed delta/greatest（ss.executions delta，1），2）rows per exec，
    round（ss.buffer gets delta/greatest（ss.executions delta，1））lio_per_exec，
    round（ss.disk_reads delta/greatest（ss.executions delta，1））pio_per_exec，
    round（（ss.cpu time delta/1e3）/greatest（ss.executions delta，1），2）cpu_per_exec，
    round（（ss.elapsed_time_delta/1e3）/greatest（ss.executions_delta，1），2）ela_per_exec
from dba_hist_snapshot s，
   dba hist_sqlstat ss
where ss.dbid=s.dbid
and ss.instance_number=s.instance_number
and ss.snap_id=s.snap_idand ss.sql_id='&v_sqlid'
and ss.executions_delta>0
and s.begin_interval_time>=sysdate-&v_days
order by ss.plan_hash_value，s.snap_id；
```

示例：找出7列相同或不同的記錄

![27.png](https://oss-emcsprod-public.modb.pro/image/editor/20210929-73373a1d-327b-4ce7-893b-1ead1c663a90.png)![28.png](https://oss-emcsprod-public.modb.pro/image/editor/20210929-1d5a5ccd-fe2a-4db5-9361-f33791fd1cb0.png)

SQL如下：

```sql
SELECT * FROM dbmt. ogg tables count2 WHERE GREATEST(ACNT, BCNT, CCNT, DCNT, ECNT, FCNT, GCNT)<>LEAST(ACNT, BCNT, CCNT, DCNT, ECNT, FCNT, GCNT)
```

- **FIRST\_VALUE , LAST\_VALUE**

取分組中的第一個（FIRST\_VALUE）、最後一個值（LAST\_VALUE）

![29.png](https://oss-emcsprod-public.modb.pro/image/editor/20210929-0ff7bd52-ea9a-4af8-9aa6-097624665816.png)

```sql
with t as(select substr(time,1,5)d,
       time
       first_value(mb) gver(partition by substr(time,1,5) order by time)
begin_mb,
      mb,
      min(mb) over(partition by substr(time,1,5)) min_mb,
      max(mb) over(partition by substr(time,1,5)) max_mb
  from dbmt. tmp tbs used)
select distinct d, begin mb, min_mb, max_mb from t order by d
```

![30.png](https://oss-emcsprod-public.modb.pro/image/editor/20210929-3aa07b7c-6703-43a9-8646-05df79426699.png)

- **NVL ，NVL2**
    
    *   NVL（EXP，返回值1）當EXP值為null時返回值1，不為null時返回本身
        
    *   NVL2（EXP，返回值1，返回值2）當exp的值為null時返回值1，不為null時返回值2
        
```sql
nvl(NULL,'N')==N
nvl'A','')==A
nv12(NULL,'A','B')==B
nv12('c','A','B')==A
```

- **LNNVL**

LNNVL當條件的一個或兩個運算元可能為空時，LNNVL提供了一種簡明的方法來計算條件。它接受一個條件作為引數，如果條件為假或未知則返回TRUE，如果條件為真則返回FALSE。LNNVL可以在任何標量表示式可能出現的地方使用。

*end\_date is null or end\_date>sysdate*

可改為

*Innvl（end\_date<=sysdate）；*

- **DECODE**（IF ELSE 分支判斷）

DECODE（EXP，條件1，返回值1，條件2，返回值2，..，預設值）
![32.jpg](https://oss-emcsprod-public.modb.pro/image/editor/20210929-28a3e0e5-008a-459e-8405-eb3e79df2883.jpg)

一般用於行轉列。示例：

```sql
select owner,
sum(decode(object type,' TABLE',1,0)) table cnt,
sum(decode(ob. ject type,' INDEX',1,0)) index cnt
from dbmt. db objects where object type in (' TABLE',' INDEX')
group by owner
```

![31.png](https://oss-emcsprod-public.modb.pro/image/editor/20210929-207c3189-6734-4c0c-8564-b588aa77ebf6.png)

- **LPAD , RPAD**

LPAD（COLUMN，引數1，引數2）

LPAD指在column列的左邊填充指定長度的指定字串，RPAD指在右邊填充。一般用於格式化資料。

![26.png](https://oss-emcsprod-public.modb.pro/image/editor/20210929-a0315d07-51de-428a-a85b-abe172972431.png)

示例：

```sql
SQL> select
rownum,LPAD(to_char(rownum),10,'0'),RPAD(to_char(rownum),10,'0') from dual
connect by rownum<4;
    ROWNUM LPAD(TO_CHAR(ROWNUM) RPAD(TO_CHAR(ROWNUM)
---------- -------------------- --------------------
         1 0000000001           1000000000
         2 0000000002           2000000000
         3 0000000003           3000000000
```

- **LTRIM , RTRIM**

去掉一個字串中左邊（LTRIM）或右邊（RTRIM）的字元

***LTRIM(char [,set ])***

***RTRIM(char [,set ])***

char引數為字串，set為需要去掉的字元，若不指定具體字元預設為去掉空字元。

- **一些正規表示式：REGEXP\_LIKE、REPLACE、SUBSTR**
    
![34.jpg](https://oss-emcsprod-public.modb.pro/image/editor/20210929-30e8ead8-04d3-4e5b-a5b6-d6a5985fd4ea.jpg)

可以指定引數

*   match\_param
    
    *   i 指定不區分大小寫的匹配。
        
    *   c 指定區分大小寫的匹配。
        
    *   n 允許句點.匹配換行符，省略則與換行符不匹配。
        
    *   'm’ 將源字串視為多行。
        
    *   Oracle將 ^ 和 $ 分別解釋為源字串中任何位置的任何行的開始和結束，而不僅僅是整個源字串的開始或結束。如果省略此引數，則Oracle將源字串視為一行

- **ORA\_HASH計算HASH值**
![35.jpg](https://oss-emcsprod-public.modb.pro/image/editor/20210929-dba46029-c865-4684-b98e-9fb1ea011f23.jpg)

***select object_id,***

***ora_hash(ownerllobject_name)hashval from dba obａjects***

- **Connect by**

***select * from table [start with condition1]***

***connect by [nocycle]***

***[prior]id=parentid==***

1）[start with condition1]遞迴開始的條件，第一層
    
2）connect by [prior] id=parented 遞迴條件
    
3）[prior] id為當前層，parented為遞迴查詢列，下次遞迴SQL類似select*from table where parented=id（當前層）

示例：

```sql
set lines 400 col txt for a100 set tab off
with t as
(select*from vSsql_plan
        where sql_id="7fybj6y7ug6q2'
         AND CHILD_NUMBER=0).
select id,
    parent_id,
    LEVEL,
    lpad("", level*2-1,") || operation ||'' || options ||'' ||
    object_owner || decode(object_name, null,",.") || object_name txt
 from t
start with id=0
Connect by parent_id= prior id;
Select * from t where parent_id=0
Selct * from t where parent_id=1

```

![36.jpg](https://oss-emcsprod-public.modb.pro/image/editor/20210929-3643a926-a72d-46e3-ad1f-989c227a68bb.jpg)

**Connect by 可用的函式、偽列**

1）SYS\_CONNECT\_BY\_PATH（column，char）層級路徑
    
2）CONNECT\_BY\_ISLEAF是否為頁子節點
    
3）LEVEL當前層級，始於1
    
4）CONNECT\_BY\_ISCYCLE是否產生死迴圈，只有制定NOCYCLE時才能使用該偽列

![36.jpg](https://oss-emcsprod-public.modb.pro/image/editor/20210929-ab9e6a58-eb21-491f-96f9-31264a1f67b6.jpg)
### 三、常用SQL技巧

#### 1、生成資料

**（1）遞迴生成數字列表**

```sql
select rownum rn from dual
       connect by rownum<=10;
```
![38.png](https://oss-emcsprod-public.modb.pro/image/editor/20210929-fd2bc3ca-6073-4090-9ac2-d1a314f3f8b4.png)

rownum<=10；

改變這個條件，可生成不同數量的數字列表。

如下想生成偶數列表呢？

**（2）生成日期列表，透過生成數字列表的方式擴充套件**

```sql
select to_date('2017-01-01','yyyy-mm-dd')+rownum-1
date_day,
add_months(to_date('2017-01-01','yyyy-mm-dd'),rownum-1)
date_month from dual
       connect by rownum<=10;
```

根據不同需要，可以天列表，月列表

![39.png](https://oss-emcsprod-public.modb.pro/image/editor/20210929-a0f0e709-9808-40a6-8a25-03f45de2f090.png)

**（3）生成隨機資料（使用包DBMS\_RANDOM）**

```sql
select rownum rn,dbms_random.string('a',10)
random_str,abs(mod(dbms_random.random(),100)) random_num
from dual
       connect by rownum<=10;
```

- dbms\_random.string('a',10)

改變引數可限制生成的字串長度

- mod(dbms\_random.random(),100)

改變引數可限制生成的數字範圍

![40.png](https://oss-emcsprod-public.modb.pro/image/editor/20210929-81ea6642-c3f2-41da-9fad-8f6dc503ac10.png)

**引數注意**：

*   小數（0 ~ 1）

select dbms\_random.value from dual ;

*   指定範圍內的小數 ( 0 ~ 100 )

select dbms\_random.value(0,100) from dual ;

*   指定範圍內的整數 ( 0 ~ 100 ）

trunc(dbms\_random.value(0,100)) from dual ;

*   隨機字串

select dbms\_random.string('x', 3) from dual ;

![41.png](https://oss-emcsprod-public.modb.pro/image/editor/20210929-b652f8ad-d26b-4cd3-81fe-ed493565d84e.png)

#### 2、統計分析

**使用case when 行轉列**

（與DECODE類似，但DECODE只能是等值查選）

如下面統計每個使用者下，2017年以前建立的物件有多少，2017年及以後建立的物件有多

少？

```sql
select owner,
sum(case when created'2017','yyyy') then 1 else 0end) "2017年以前",
sum(case when created>=to_date('2017','yyyy') then 1 else 0end) "2017年及以後"
from dbmt.db_objects where object_type in ('TABLE','INDEX')
group by owner
```

![42.png](https://oss-emcsprod-public.modb.pro/image/editor/20210929-153aca1c-dce6-4cad-9fe1-d74955274367.png)

#### 3、SQL技巧

**查詢連續值**

可以查詢出序列中的連續值或中斷位置

![43.png](https://oss-emcsprod-public.modb.pro/image/editor/20210929-65d45284-b0ad-4fb6-88f8-208dc3c4888f.png)

```sql
select min(id), max(id)
  from (select id,id - rownum rn
          from (select id from dbmt.list1
order by id))
 group by rn
having count(rn) > 1
 order by min(id);
 
 
MIN(ID) MAX(ID)
---------- ----------
         1          4  //1-4連續
         6          9  //6-9連續
        11         21  //11-21連續
        24         49  //24-49連續
```

#### 4、XML TABLE

可透過函式提取分支資料

![44.png](https://oss-emcsprod-public.modb.pro/image/editor/20210929-1e7711fc-9036-4846-8e25-49d5cc1b9693.png)

```sql
with t as (select xmltype('value1value2') xmlvalfrom dual)
select i from t a,
            xmltable('/a/i' passing xmlval columns i path '/i')
```

老師在直播中還講解了一些關於null的注意事項，感興趣的朋友點選檢視[回放影片](https://www.modb.pro/course/play/97?lsId=4811)、[PPT資源](https://www.modb.pro/doc/42329?1009)繼續學習。

## SQL資源下載

文章中包含的部分SQL（ASH、check cpu、stats\_diff、 wait chain以及時序資料異常波動）大家可以點選?下載合集。

[Oracle DBA的SQL編寫技能提升寶典-sql資源](https://www.modb.pro/download/138549?1009)

---

歡迎大家在[**原文評論區**](https://www.modb.pro/db/121698?1009)留言或前往[**範計傑老師個人主頁**](https://www.modb.pro/u/3770?1009)留言互動。

[墨天輪](https://www.modb.pro/?1009 "墨天輪")，圍繞資料人的學習成長提供一站式的全面服務，打造集新聞資訊、線上問答、活動直播、線上課程、文件閱覽、資源下載、知識分享及線上運維為一體的統一平臺，持續促進資料領域的知識傳播和技術創新。

關注官方公眾號：
墨天輪、 墨天輪平臺、墨天輪成長營、資料庫國產化 、資料庫資訊</windowing></order-by-clause></partition-clause></argument></argument>

直播回顧 | 雲和恩墨範計傑：Oracle DBA的SQL編寫技能提升寶典（含SQL資源）

相關文章