Category: Oracle

  • Oracle Database 12CR1 monitoring with MRTG

    • OS: Oracle Enterprise Linux  7.2  (CentOS 7.2)
    • วิธีติดตั้ง MRTG สามารถติดตั้งได้โดยสามารถดูคู่มือที่ ติดตั้ง mrtg บน ubuntu อาจไม่เหมือนกันแต่สามารถทำได้ทำนองเดียวกัน
    • กราฟสำหรับ Idle CPU and Load average, CPU Time spent waiting for IO, Traffic Analysis for eth0, TCP Current Establish สามารถใช้ script เดียวกับลิงค์ในข้อ ๒ ได้เลย
    • สร้างแฟ้ม /etc/mrtg/get-memory.sh มีข้อความว่า
      #!/bin/bash
      FREE=$(free |grep "Mem:"|awk '{print $7}')
      SWAP=$(free |grep "Swap:"|awk '{print $3}')
      TIME=$(uptime)
      echo "${FREE}"
      echo "${SWAP}"
      echo "$TIME"
      hostname 

      สร้างแฟ้ม /etc/mrtg/myhost-memory.cfg มีข้อความว่า
      WorkDir: /var/www/mrtg/myhost
      Target[myhost-mem]:`/etc/mrtg/get-memory.sh`
      MaxBytes[myhost-mem]: 20000000000
      Title[myhost-mem]: Free Memory and Swap Used
      PageTop[myhost-mem]: <H1>Free Memory and Swap Used</H1>
      ShortLegend[myhost-mem]: bytes
      YLegend[myhost-mem]: bytes
      LegendI[myhost-mem]:  Free Memory:
      LegendO[myhost-mem]: Swap Used:
      Legend1[myhost-mem]: Free memory, in bytes
      Legend2[myhost-mem]: Swap Used, in bytes
      Options[myhost-mem]: gauge, nopercent, growrightทดสอบสร้างภาพต้นแบบด้วยคำสั่ง

      env LANG=C /usr/bin/mrtg/myhost-memory.cfgปรับปรุงแฟ้ม index.html ด้วยคำสั่ง
      indexmaker --column=2 --output=/var/www/mrtg/myhost/index.html /etc/mrtg/myhost-cpu.cfg /etc/mrtg/myhost-cpu-io.cfg /etc/mrtg/myhost-speed-eth0.cfg /etc/mrtg/myhost-tcpestab.cfg /etc/mrtg/myhost-memory.cfg

    • โฟลเดอร์ที่ต้องเฝ้าระวังได้แก่ /u02/app/oracle/adump, /u02/app/oracle/diag/rdbms/regist/regist/alert, /u02/app/oracle/rdbms_trace ซึ่งเป็นโฟลเดอร์สำหรับเก็บ Log ไฟล์ต่างๆ ซึ่งอาจมีขนาดเพิ่มขึ้นจนระบบไม่สามารถให้บริการได้ และโฟลเดอร์ /u03 เป็นโฟลเดอร์ที่ใช้เก็บ archive log (ในกรณีที่ฐานข้อมูลเปิด archive log mode)
      • สร้างแฟ้ม /etc/mrtg/get-diskfree-misc1.sh มีข้อความว่า
        #!/bin/bash
        adump=$(du -sm /u02/app/oracle/adump|awk '{ print $1 }')
        free=$(df -m /u02|grep u02|awk '{ print $4 }')
        TEMP=$(uptime|grep -o "load average.*"|awk '{print $3}'|cut -d',' -f 1)
        LOAD=$(echo "${TEMP:-0} * 100"|bc|cut -d'.' -f 1)
        TIME=$(uptime)
        echo "${adump}"
        echo "${free}"
        echo "$TIME"
        hostname
      • สร้างแฟ้ม /etc/mrtg/myhost-diskfree-misc1.cfg มีข้อความว่า
        WorkDir: /var/www/mrtg/myhost
        Target[myhost-misc1]:`/etc/mrtg/get-diskfree-misc1.sh`
        MaxBytes[myhost-misc1]: 20000000000
        Title[myhost-misc1]: Free disk space and disk Used of /u02/app/oracle/adump
        PageTop[myhost-misc1]: Free disk space and disk Used of /u02/app/oracle/adump
        ShortLegend[myhost-misc1]: bytes
        kMG[myhost-misc1]: M,G,T
        kilo[myhost-misc1]: 1024
        YLegend[myhost-misc1]: bytes
        LegendI[myhost-misc1]: Disk Used:
        LegendO[myhost-misc1]: Free Disk:
        Legend1[myhost-misc1]: Disk usage, in Bytes
        Legend2[myhost-misc1]: Free Disk Space, in Bytes
        Options[myhost-misc1]: gauge, nopercent, growright
        Timezone[myhost-misc1]: Bangkok

        ทดสอบสร้างภาพต้นแบบด้วยคำสั่ง
        env LANG=C /usr/bin/mrtg /etc/mrtg/myhost-diskfree-misc1.cfg
        ปรับปรุงแฟ้ม index.html ด้วยคำสั่ง
        indexmaker --column=2 --output=/var/www/mrtg/myhost/index.html /etc/mrtg/myhost-cpu.cfg /etc/mrtg/myhost-cpu-io.cfg /etc/mrtg/myhost-speed-eth0.cfg /etc/mrtg/myhost-tcpestab.cfg /etc/mrtg/myhost-memory.cfg /etc/mrtg/myhost-diskfree-misc1.cfg
      • สร้างแฟ้มเลียนแบบข้อ 1 และ 2 สำหรับโฟลเดอร์ที่เหลือ
      • แก้ไขแฟ้ม /etc/mrtg/mymrtg.sh เพิ่มข้อความ  env LANG=C /usr/bin/mrtg /etc/mrtg/myhost-diskfree-misc1.cfg ต่อท้ายไฟล์และเพิ่มทุกไฟล์ของทุกโฟลเดอร์
      • สำหรับโฟลเดอร์ /u03 ให้สร้างแฟ้ม /etc/mrtg/get-diskfree-u03.sh มีข้อความว่า
        #!/bin/bash
        used=$(df -m /u03|grep u03|awk '{ print $3 }')
        free=$(df -m /u03|grep u03|awk '{ print $4 }')
        TEMP=$(uptime|grep -o "load average.*"|awk '{print $3}'|cut -d',' -f 1)
        LOAD=$(echo "${TEMP:-0} * 100"|bc|cut -d'.' -f 1)
        TIME=$(uptime)
        echo "${used}"
        echo "${free}"
        echo "$TIME"
        hostname
        nof=$(ls -d1 /u03/app/oracle/fast_recovery_area/REGIST/archivelog/* |wc -l)
        max=3if [ "${nof}" == "${max}" ]
        then
                nod=$(expr ${max} - 1)
                f2d=$(ls -d1 /u03/app/oracle/fast_recovery_area/REGIST/archivelog/*|head -${nod})
                rm -rf ${f2d}
                su - oracle -c "/bin/sh /home/oracle/reclaim.sh"
        fi
        สร้างแฟ้ม /home/oracle/reclaim.sh มีข้อความว่า
        rman target / <<EOF
        crosscheck archivelog all;
        delete noprompt expired archivelog all;
        quit
        EOF
        สร้างแฟ้ม /etc/mrtg/myhost-diskfree-u03.cfg มีข้อความว่า
        WorkDir: /var/www/mrtg/myhost
        Target[myhost-u03]:`/etc/mrtg/get-diskfree-u03.sh`
        MaxBytes[myhost-u03]: 20000000000
        Title[myhost-u03]: Free disk space and disk Used of /u03
        PageTop[myhost-u03]: Free disk space and disk Used of /u03
        ShortLegend[myhost-u03]: bytes
        kMG[myhost-u03]: M,G
        kilo[myhost-u03]: 1024
        YLegend[myhost-u03]: bytes
        LegendI[myhost-u03]: Disk Used:
        LegendO[myhost-u03]: Free Disk:
        Legend1[myhost-u03]: Disk usage, in Bytes
        Legend2[myhost-u03]: Free Disk Space, in Bytes
        Options[myhost-u03]: gauge, nopercent, growrightทดสอบสร้างภาพต้นแบบด้วยคำสั่ง
        env LANG=C /usr/bin/mrtg /etc/mrtg/myhost-diskfree-u03.cfg
        อย่าลืมปรับปรุงแฟ้ม index.html ด้วย
    • เฝ้าระวังขนาดของ Tablespace SYSTEM และ USERS
      • สร้างแฟ้ม /etc/mrtg/get-tablespace-system.sh มีข้อความว่า
        #!/bin/bash
        used=$(su - oracle -c "sh /home/oracle/monitor/tablespacesize.sh"|grep SYSTEM|awk '{ print $2 }'|sed -e 's/,//g')
        used=$(expr ${used} \* 1024)
        free=$(su - oracle -c "sh /home/oracle/monitor/tablespacesize.sh"|grep SYSTEM|awk '{ print $3 }'|sed -e 's/,//g')
        free=$(expr ${free} \* 1024)
        TIME=$(uptime)
        echo "${used}"
        echo "${free}"
        echo "$TIME"
        hostname

        สร้างแฟ้ม /home/oracle/monitor/tablespacesize.sh มีข้อความว่า
        #!/bin/bash
        sqlplus / as sysdba << EOF
        col "Tablespace" for a22
        col "Used MB" for 99,999,999
        col "Free MB" for 99,999,999
        col "Total MB" for 99,999,999
        select df.tablespace_name "Tablespace",
        totalusedspace "Used MB",
        (df.totalspace - tu.totalusedspace) "Free MB",
        df.totalspace "Total MB",
        round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
        "Pct. Free"
        from
        (select tablespace_name,
        round(sum(bytes) / 1048576) TotalSpace
        from dba_data_files
        group by tablespace_name) df,
        (select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
        from dba_segments
        group by tablespace_name) tu
        where df.tablespace_name = tu.tablespace_name ;
        quit
        EOF

        สร้างแฟ้ม /etc/mrtg/myhost-tablespace-system.cfg
        WorkDir: /var/www/mrtg/myhost
        Target[myhost-system]:`/etc/mrtg/get-tablespace-system.sh`
        MaxBytes[myhost-system]: 20000000000
        Title[myhost-system]: Tablespace SYSTEM disk usage
        PageTop[myhost-system]: Tablespace SYSTEM Disk Usage
        ShortLegend[myhost-system]: bytes
        kMG[myhost-system]: k,M,G
        kilo[myhost-system]: 1024
        YLegend[myhost-system]: bytes
        LegendI[myhost-system]: Disk Used:
        LegendO[myhost-system]: Free Disk:
        Legend1[myhost-system]: Disk usage, in Bytes
        Legend2[myhost-system]: Free Disk Space, in Bytes
        Options[myhost-system]: gauge, nopercent, growrightทดสอบสร้างภาพต้นแบบด้วยคำสั่ง
        env LANG=C /usr/bin/mrtg /etc/mrtg/myhost-tablespace-system.cfgอย่าลืมปรับปรุง index.html ด้วย
      • ทำแบบเดียวกันกับ tablespace users
    • จบขอให้สนุก
  • ฟังก์ชัน WMSYS.WM_CONCAT และการเปลี่ยนแปลงเมื่ออัพเกรดไปใช้ Oracle 12c

    ฟังก์ชัน WMSYS.WM_CONCAT

    นักพัฒนาบางท่านที่พัฒนาระบบบนฐานข้อมูล Oracle 10g หรือ 11g อาจจะผ่านตาหรือเคยใช้งานฟังก์ชัน  WMSYS.WM_CONCAT โดยฟังก์ชันนี้เป็นฟังก์ชันที่ใช้ในการนำข้อมูลในฟีลด์เดียวกัน แต่อยู่ต่างเร็คคอร์ดมาเชื่อมต่อกันเป็นข้อมูลเร็คคอร์ดเดียว

    ในที่นี้จะยกตัวอย่างจากตารางข้อมูลทดสอบ ชื่อว่าตาราง STATIONERY ซึ่งเก็บข้อมูลเครื่องเขียน โดยแยกเป็นสี และระบุจำนวนของเครื่องเขียนแต่และชนิดไว้ ดังนี้

    stationery

     

    ทดลองใช้คำสั่ง SELECT แบบปกติ ด้วยคำสั่ง

    SELECT WMSYS.WM_CONCAT(COLOR) COLOR_LIST

    FROM STATIONERY;

    ผลลัพธ์ที่ได้

    list04

     

    แต่ในเง่การใช้งานส่วนใหญ่ มักจะต้องการแสดงข้อมูลสรุปเป็นกลุ่ม เช่น จากตัวอย่างนี้ ถ้าต้องการแยกข้อมูลสรุปเป็นกลุ่มตามชนิดเครื่องเขียน ว่าเครื่องเขียนแต่ละชนิดมีสีอะไรบ้าง ซึ่งก็สามารถทำได้โดยการเพิ่มการ GROUP BY ตามฟีลด์ STATIONERY เข้าไป ดังนี้

    SELECT STATIONERY, WMSYS.WM_CONCAT(COLOR) COLOR_LIST

    FROM STATIONERY

    GROUP BY STATIONERY;

    ผลลัพธ์ที่ได้

    list05

     

    หรือหากต้องการข้อมูลสรุปตามสีของเครื่องเขียน ว่าแต่ละสีมีเครื่องเขียนชนิดใดอยู่บ้าง คำสั่งที่ใช้งานก็จะเป็น

    SELECT COLOR, WMSYS.WM_CONCAT(STATIONERY) STATIONERY_LIST

    FROM STATIONERY

    GROUP BY COLOR;

    ผลลัพธ์ที่ได้

    list06

     

    ที่กล่าวไปข้างต้นคือการใช้งานฟังก์ชัน WMSYS.WM_CONCAT บนฐานข้อมูล Oracle 10g หรือ 11g แต่ถ้านักพัฒนาท่านใดวางแผนที่จะอัพเกรตฐานข้อมูลไปเป็น Oracle 12c  ท่านก็จะเจอกับปัญหาเมื่อมีการเรียกใช้งานฟังก์ชันนี้ โดยจะมีข้อความ error แจ้งกลับมาว่า 

    ORA-00904: "WMSYS"."WM_CONCAT": invalid identifier 

    นั่นเป็นเพราะใน Oracle 12c จะไม่มีฟังก์ชันนี้ให้เรียกใช้งานอีกต่อไปแล้ว

    ดังนั้นในบทความนี้จะขอแนะนำฟังก์ชันอีกฟังก์ชันหนึ่งซึ่งทำงานคล้ายคลึงกัน และสามารถให้ผลลัพธ์แบบเดียวกันกับฟังก์ชัน WMSYS.WM_CONCAT ซึ่งฟังก์ชันที่ว่านี้คือ LISTAGG

     

    ฟังก์ชัน LISTAGG

    ฟังก์ชัน LISTAGG เป็นฟังก์ชันที่เริ่มมีให้ใช้งานใน Oracle 11g R2 ใช้งานในลักษณะเดียวกันกับ ฟังก์ชัน WMSYS.WM_CONCAT แต่ในส่วนของรายละเอียดนั้นจะมีบางจุดที่แตกต่างกันออกไป

    จากตัวอย่างข้อมูลที่นำเสนอไปข้างต้น จากที่ใช้งานกับฟังก์ชัน WMSYS.WM_CONCAT ลองเปลี่ยนมาใช้ฟังก์ชัน LISTAGG ได้ดังนี้

     

    ตัวอย่างแรกเป็นการทดลอง SELECT แบบปกติ

    SELECT LISTAGG(COLOR,’,’)

    WITHIN GROUP (ORDER BY COLOR) COLOR_LIST

    FROM STATIONERY;

    ผลลัพธ์ที่ได้

    list07

     

    อธิบายการใช้งานคำสั่ง

    • LISTAGG(COLOR,’,’)  ภายในวงเล็บเป็นฟีลด์ข้อมูลจากต่างเร็คคอร์ดกันแต่ต้องการให้แสดงเรียงต่อกัน ซึ่งในตัวอย่างนี้ก็คือฟีลด์ COLOR ส่วน ‘,’ ก็คือการระบุตัวคั่นระหว่างข้อมูล ซึ่งในทีนี้ใช้เป็นจุลภาคนั่นเอง
    • WITHIN GROUP (ORDER BY COLOR) เป็นการระบุรูปแบบการเรียงข้อมูล ซึ่งในที่นี้จะเรียงตามฟีลด์ COLOR

    จากตัวอย่างจะเห็นว่าสิ่งที่ฟังก์ชัน LISTAGG ทำได้แตกต่างจาก WMSYS.WM_CONCAT คือ การระบุตัวคั่นระหว่างข้อมูล และการระบุการเรียงลำดับของข้อมูลที่มาต่อกันนั่นเอง

     

    ตัวอย่างต่อมา จะให้แสดงผลลัพธ์แยกสรุปเป็นกลุ่มตามชนิดเครื่องเขียน ว่าเครื่องเขียนแต่ละชนิดมีสีอะไรบ้าง คำสั่งที่ใช้ก็จะใช้การ GROUP BY ด้วยฟีลด์ STATIONERY เช่นเดิม คือ

    SELECT STATIONERY, LISTAGG(COLOR,’,’)

    WITHIN GROUP (ORDER BY STATIONERY) COLOR_LIST

    FROM STATIONERY GROUP BY STATIONERY;

    ผลลัพธ์ที่ได้

    list01

     

    ส่วนตัวอย่างสุดท้าย ก็จะเป็นการให้แสดงผลลัพธ์สรุปตามสีของเครื่องเขียน ว่าแต่ละสีมีเครื่องเขียนชนิดใดอยู่บ้าง คำสั่งที่ใช้งานก็จะเป็น

    SELECT COLOR, LISTAGG(STATIONERY,’,’)

    WITHIN GROUP (ORDER BY STATIONERY) LIST_STATIONERY

    FROM STATIONERY GROUP BY COLOR;

    ผลลัพธ์ที่ได้

    list02

     

    ข้อมูลอ้างอิง :

    https://oracle-base.com/articles/misc/string-aggregation-techniques

    http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030

  • ข้อจำกัดและข้อควรระวังในการใช้เงื่อนไข IN ในคำสั่ง SELECT บนฐานข้อมูล Oracle

    สำหรับบทความนี้ จะนำเสนอข้อจำกัดและข้อควรระวังในการใช้งานคำสั่ง SELECT บนฐานข้อมูล Oracle ซึ่งประสบมาจากการใช้งานจริงสองเรื่องด้วยกัน

     

    เรื่องแรกจะเป็นข้อจำกัดในการใช้เงื่อนไข IN (value1,value2,value3,…) ในคำสั่ง SELECT  ส่วนอีกเรื่องจะเป็นเรื่องของข้อควรระวังในการใช้ IN ร่วมกับเงื่อนไขที่เป็น subquery ในคำสั่ง SELECT  เช่นกัน

     

    การใช้คำสั่ง SELECT และเงื่อนไข IN นั้น เป็นรูปแบบคำสั่งพื้นฐานแบบหนึ่งที่นักพัฒนาที่ทำงานคลุกคลีกับฐานข้อมูลส่วนใหญ่จะคุ้นเคยกันเป็นอย่างดี  โดยรูปแบบที่เรามักจะใช้งานกันบ่อย คือ

    รูปแบบที่ 1 รูปแบบ SELECT * FROM TABLE1 WHERE FIELD1 IN  (value1,value2,value3,…)  โดยผลลัพธ์จะเป็นรายการข้อมูลในตาราง TABLE1 ที่ค่าของข้อมูลใน FIELD1 มีอยู่ใน value1,value2,value3 ,…

    รูปแบบที่ 2 คล้ายกับรูปแบบที่ 1  นั่นเอง แต่จะเป็นการใช้ subquery แทนที่ (value1,value2,value3,…)   โดยมีรูปแบบ SELECT * FROM TABLE1 WHERE FIELD1 IN  (SELECT FIELD2 FROM TABLE2) สำหรับผลลัพธ์จะเป็นรายการข้อมูลในตาราง TABLE1 ที่ค่าของข้อมูลใน FIELD1 มีใน FIELD2 ซึ่งเป็นผลลัพธ์จากการ SELECT ข้อมูลจากตาราง TABLE2

     

    ข้อจำกัดในการใช้เงื่อนไข IN (value1,value2,value3,…)

    สำหรับใน Oracle นั้น list รายการที่อยู่ภายในเครื่องหมายวงเล็บ สามารถมีได้มากสุดไม่เกิน 1000 ค่า  ซึ่งบางท่านอาจจะสงสัยว่าในการ SELECT ข้อมูลตามปกตินั้น มีโอกาสน้อยมากที่เราจะพิมพ์ค่าของข้อมูลใน list จนถึง 1000  ค่า แต่ก็มีโอกาสที่จะพบได้คือ เมื่อมีการใช้งานคำสั่งนี้ผ่านโปรแกรมที่เขียนขึ้นนั่นเอง ตัวอย่างเช่น หน้าจอการทำงานของโปรแกรมที่มีลักษณะเป็นชุดของรายการข้อมูลที่ให้ผู้ใช้สามารถเลือกเองได้ จากนั้นรายการที่ถูกเลือกจะถูกส่งไปแปลงเป็นเงื่อนไขในคำสั่ง SELECT  อีกครั้ง ทำให้มีโอกาสที่จะเกิดกรณีที่มี list เกิน 1000 ค่า ได้นั่นเอง

    ในภาพด้านล่างจะเป็นตัวอย่างของเว็บสำหรับสืบค้นหนังสือของห้องสมุด ซึ่งผู้ใช้สามารถเลือกรายการผลลัพธ์จากการสืบค้นและเก็บรวบรวมไว้เพื่อทำการ export ไปใช้งานต่อได้ ซึ่งก็มีโอกาสที่จะเลือกผลลัพธ์ได้เกิน 1000  รายการเกิดขึ้นได้

    Capture

    ถือว่าเป็นจุดหนึ่งที่ผู้พัฒนาควรระมัดระวังในการเขียนโปรแกรมที่มีการใช้เงื่อนไข IN ลักษณะนี้ในคำสั่ง SELECT

     

    ข้อควรระวังในการใช้ IN ร่วมกับเงื่อนไขที่เป็น subquery

    ในที่นี้ขอยกตัวอย่างข้อมูลเพื่อให้เห็นภาพชัดเจน  โดยมีข้อมูลจากตารางสองตาราง คือ TABLE01 และ TABLE02

    สำหรับ TABLE01 เป็นข้อมูลที่ต้องการ SELECT เพื่อให้ได้ผลลัพธ์ออกมา ส่วนตาราง TABLE02 จะเป็นเงื่อนไขที่จะใช้ใน subquery  ข้อมูลในตารางทั้งสองจะเป็นดังนี้

     

    ข้อมูลใน  TABLE01

    t01

    ข้อมูลใน  TABLE02

    t02

     

    จะยกตัวอย่างกรณีการ SELECT ออกเป็น 2 กรณีดังนี้

    1. ต้องการข้อมูลใน TABLE01 ที่ข้อมูลในฟีลด์ F02 ของตารางนี้มีในฟีลด์ F02 ของ TABLE02 ด้วย

    คำสั่งที่ใช้คือ SELECT * FROM TABLE01 WHERE F02 IN (SELECT F02 FROM TABLE02);

    ผลลัพธ์ที่ได้คือ

    result1

    ซึ่งถูกต้อง

     

    1. ต้องการข้อมูลใน TABLE01 ที่ข้อมูลในฟีลด์ F02 ของตารางนี้ไม่มีในฟีลด์ F02 ของ TABLE02 โดยปรับคำสั่งจาก IN เป็น NOT IN

    คำสั่งที่ใช้คือ SELECT * FROM TABLE01 WHERE F02 NOT IN (SELECT F02 FROM TABLE02);

    ผลลัพธ์ที่ได้คือ

    result2

    ซึ่งถูกต้อง

     

    จะเห็นว่าทั้งสองกรณีทั้งการใช้ IN หรือ NOT IN ผลลัพธ์ที่ได้ก็ออกมาถูกต้อง

     

    ทดลองต่อไปโดยการเพิ่มข้อมูลในตาราง TABLE02 ดังนี้

    t02_2

    โดยข้อมูลที่เพิ่มจะมี 1 รายการที่ข้อมูลใน F02 มีค่าเป็น null

     

    จากนั้นลองทำการ SELECT แบบเดิมดังนี้

    1. ต้องการข้อมูลใน TABLE01 ที่ข้อมูลในฟีลด์ F02 ของตารางนี้มีในฟีลด์ F02 ของ TABLE02 ด้วย

    คำสั่งที่ใช้คือ SELECT * FROM TABLE01 WHERE F02 IN (SELECT F02 FROM TABLE02);

    ผลลัพธ์ที่ได้คือ

    result1

    จะเห็นว่าได้ผลลัพธ์แบบเดิม ซึ่งถูกต้อง

     

    1. ต้องการข้อมูลใน TABLE01 ที่ข้อมูลในฟีลด์ F02 ของตารางนี้ไม่มีในฟีลด์ F02 ของ TABLE02 โดยปรับคำสั่งจาก IN เป็น NOT IN

    คำสั่งที่ใช้คือ SELECT * FROM TABLE01 WHERE F02 NOT IN (SELECT F02 FROM TABLE02);

    ผลลัพธ์ที่ได้คือ

    result3

    ซึ่งไม่มีผลลัพธ์ใด ๆ ออกมาเลย

    ลองเพิ่มเงื่อนไขใน subquery โดยเอารายการที่ F02 มีค่าเป็น null ออกไป

    คำสั่งที่ใช้คือ SELECT * FROM TABLE01 WHERE F02 NOT IN (SELECT F02 FROM TABLE02 WHERE F02 IS NOT NULL);

    ผลลัพธ์ที่ได้คือ

    result2

    ซึ่งเป็นไปตามที่เราต้องการ

     

    สรุปได้ว่า กรณีที่เราต้องการใช้งานคำสั่ง SELECT ที่ใช้ร่วมกับเงื่อนไข NOT IN ตามด้วย subquery  ต้องระมัดระวังในเรื่องของผลลัพธ์ที่ได้จาก subquery มีค่าข้อมูลที่เป็น null อยู่ด้วย ซึ่งจะทำให้ได้ผลลัพธ์ไม่ตรงตามที่เราต้องการ

     

    ข้อมูลอ้างอิง 

    https://docs.oracle.com/cd/B19306_01/server.102/b14200/conditions013.htm

    https://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions014.htm#i1033664