subquery SQL

optimasi query

PENDAHULUAN
Data yang tersimpan dalam database semakin lama akan semakin besar ukuran atau
volumenya.    Kalau  tidak  didukung  dengan  kecepatan  akses  yang  memadai  maka  akan
semakin  menurun  unjuk  kerjanya.    Ukuran  unjuk  kerja  dalam  hal  ini  kecepatan  akses
data  dipengaruhi  oleh  banyak  faktor.    Pada  bab  ini  akan  membahas  tentang  optimasi
query  serta  faktor-faktor  lain  yang  berpengaruh  terhadap  optimalisasi  kecepatan  akses
data.
OPTIMASI PADA PERINTAH SQL
Desain  aplikasi  saja  tidak  cukup  untuk  meningkatkan  unjuk  kerja  harus  didukung
dengan  optimasi  dari  perintah  SQL  yang  digunakan  pada  aplikasi  tersebut.    Dalam
mendesain  database,  seringkali  lokasi  fisik  data  tidak  menjadi  perhatian  penting.
Karena  hanya  desain  logik  saja  yang  diperhatikan.    Padahal  untuk  menampilkan  hasil
query  dibutuhkan  pencarian  yang  melibatkan  struktur  fisik  penyimpanan  data.    Inti  dari
optimasi  query  adalah  meminimalkan  “jalur”  pencarian  untuk  menemukan  data  yang
disimpan dalam lokasi fisik.
Index pada database digunakan untuk  meningkatkan kecepatan akses data.   Pada
saat  query  dijalankan,  index  mencari  data  dan  menentukan  nilai  ROWID  yang
membantu  menemukan  lokasi  data  secara  fisik  di  disk.    Akan  tetapi  penggunaan  index
yang  tidak  tepat,  tidak  akan  meningkatkan  unjuk  kerja  dalam  hal  ini  kecepatan  akses
data.
Misal  digunakan  index  yang  melibatkan  tiga  buah  kolom  yang  mengurutkan
kolom menurut kota, propinsi dan kode pos dari tabel karyawan, sebagai berikut :
CREATE INDEX idx_kota_prop_kodepos
ON karyawan(kota, propinsi, kode_pos)
TABLESPACE INDX;
Kemudian user melakukan query sebagai berikut :
SELECT * FROM karyawan WHERE propinsi=’Jawa Barat’;
Pada  saat  melakukan  query  ini,  index  tidak  akan  digunakan  karena  kolom  pertama
(kota)  tidak  digunakan  dalam  klausa  WHERE.    Jika  user  sering  melakukan  query  ini,
maka  kolom  index  harus  diurutkan  menurut  propinsi.    Selain  itu,  proses  pencarian  data
akan  lebih  cepat  jika  data  terletak  pada  block  tabel  yang  berdekatan  daripada  harus
mencari di beberapa datafile yang terletak pada block yang berbeda.
Misal pada perintah SQL berikut ini :
SELECT * FROM karyawan
WHERE id BETWEEN 1010 AND 2010;
Query  ini  akan  melakukan  “scan”  terhadap  sedikit  data  block  jika  tabel  karyawan
diatas  diurutkan  berdasarkan  kolom  id.    Untuk  mengurutkan  berdasarkan  kolom  yang
berbeda-beda  maka  tabel  disimpan  dalam  flat  file,  kemudian  tabel  diekspor  dan
diurutkan sesuai kebutuhan.
Alternatif  yang  lain,  bisa  digunakan  perintah  untuk  membuat  tabel  lain  yang
memiliki urutan yang berbeda dari tabel asal, seperti perintah SQL berikut :
CREATE TABLE karyawan_urut
AS SELECT * FROM karyawan
ORDER BY id;
Pada  SQL  diatas,  tabel  karyawan_urut  berisi  data  yang  sama  dengan  tabel  karyawan
hanya datanya terurut berdasarkan kolom id.
PERENCANAAN EKSEKUSI
Bagaimana  cara  melihat  jalur  akses  yang  akan  digunakan  database  saat
melakukan  query  ?  Pada  Database  Oracle,  informasi  ini  dapat  dilihat  dengan
menggunakan  perintah  explain  plan,  yang  akan  memberi  informasi  tentang  rencana
eksekusi  dari  suatu  query.    Informasi  ini  disimpan  dalam  tabel  PLAN_TABLE  yang
terdapat di schema user  yang mengeksekusi perintah tersebut.
Sebelum  melakukan  perintah  explain  plan,  terlebih  dahulu  buat  table
PLAN_TABLE  dengan  menggunakan  script  utlxplan.sql  yang  diambil  dari
\%ORACLE_HOME%\RDBMS\ADMIN.
Setelah itu table PLAN_TABLE dapat digunakan seperti contoh berikut :
SQL> explain plan
Set statement_id=’test1’
Into plan_table for
Select * from karyawan where gaji=2000000;
Dalam  PLAN_TABLE  rencana  eksekusi  diatas  dikenal  dengan  nama  test1  yang
terdefinisi pada kolom statement_id.
Untuk melihat rencana eksekusi dari test1, digunakan perintah SELECT berikut :
SELECT LPAD(’ ’,2*Level)||Operation||’ ’||Options||’ ’||Object_Name Q_Plan
FROM plan_table
WHERE statement_id=’test1’
CONNECT BY PRIOR id=parent_id AND statement_id=’test1’
START WITH id=0 AND statement_id=’test1’;
Contoh hasil dari eksekusi query tersebut :
Q_PLAN
——————————————————————–SELECT STATEMENT
TABLE ACCESS FULL KARYAWAN
Output  tersebut  dibaca  mulai  dari    yang  indent-nya  paling  dalam  yaitu  :  TABLE
ACCESS  FULL  KARYAWAN.      Dikarenakan  klausa  WHERE  melibatkan  kolom  gaji
namun kolom gaji tidak ada index-nya, maka Oracle  melakukan full  table scan.  Setelah
seluruh  tabel karyawan selesai dibaca, selanjutnya adalah SELECT STATEMENT yang
berfungsi untuk menampilkan hasil query.
FAKTOR  LAIN  YANG  BERPENGARUH  TERHADAP  KECEPATAN
AKSES DATA
Faktor lain yang berpengaruh terhadap kecepatan akses data, tidak hanya terletak
pada  optimasi  perintah  SQL,  tapi  terhadap  hal-hal  lain  yang  berpengaruh.    Diantaranya
adalah  optimasi  aplikasi  dan  penggunaan  cluster  dan  index.    Hal  yang  akan  dibahas
dalam  optimasi  query  berikut  ini  tidak  melibatkan  penggunaan  komponen  yang  ada
dalam  Arsitektur  database  engine,  misal  pada  database  Oracle  kecepatan  akses  data
dipengaruhi oleh penyesuaian pada shared pool, buffer cache, redo log buffer dan sistem
operasi yang digunakan.
OPTIMASI APLIKASI
Dalam  pembuatan  aplikasi,  yang  perlu  mendapat  perhatian  adalah  apakah  akses
terhadap  data  sudah  efisien.    Efisien  dalam  hal  penggunaan  obyek  yang  mendukung
kecepatan akses, seperti index atau cluster.  Kemudian juga bagaimana cara database di-desain.  Apakah desain database sudah melakukan normalisasi data secara tepat.
Kadangkala  normalisasi  sampai  level  yang  kesekian,  tidak  menjamin  suatu
desain yang efisien.  Untuk membuat desain yang lebih tepat, kadang setelah melakukan
normalisasi  perlu  dilakukan  denormalisasi.    Misalnya  tabel  yang  hubungannya  one-to-one dan sering diakses bersama lebih baik disatukan dalam satu tabel.
CLUSTER DAN INDEX
Cluster  adalah  suatu  segment  yang  menyimpan  data  dari  tabel  yang  berbeda
dalam  suatu  struktur  fisik  disk  yang  berdekatan.    Konfigurasi  ini  bermanfaat  untuk
akses  data  dari  beberapa  tabel  yang  sering  di-query.    Penggunaan  cluster  secara  tepat
dilaksanakan  setelah  menganalisa  tabel-tabel  mana  saja  yang  sering  di-query  secara
bersamaan menggunaan perintah SQL join.
Jika  aplikasi  sering  melakukan  query  dengan  menggunakan  suatu  kolom  yang
berada  pada  klausa  WHERE,  maka  harus  digunakan  index  yang  melibatkan  kolom
tersebut.    Penggunaan  index yang  tepat  bergantung  pada  jenis  nilai yang  terdapat  dalam
kolom  yang  akan  diindex.    Dalam  RDBMS  Oracle,  index  B-Tree  digunakan  untuk
kolom  yang  mengandung  nilai  yang  cukup  bervariasi,  sedangkan  untuk  nilai  yang  tidak
memiliki variasi cukup banyak, lebih baik menggunakan index bitmap.

SQL Lanjutan

 Apa yang disebut dengan SubQuery ? Subquery adalah statement SELECT yang
dilampirkan sebagai klausa dalam SQL Statement yang lain.

Pada gambar diatas, subquery (inner query) dijalankan sekali sebelum main query. Kemudian hasil dari subquery digunakan oleh main query (outer query). Berikut posisi penulisan subquery dalam SQL command 

 PENGGUNAAN SUBQUERY

Subquery mengembalikan nilai ke main query. Subquery digunakan untuk menyelesaikan persoalan dimana terdapat suatu nilai yang tidak diketahui (unknown values). Berikut ini diberikan contoh penggunaan subquery.

Query diatas akan menampilkan nama pegawai yang gajinya lebih dari pegawai dengan nomer pegawai 149. Sebelumnya, gaji dari pegawai dengan nomer pegawai 149 tidak diketahui, untuk itu kita tempatkan sebagai subquery agar nilai yang tidak diketahui tersebut dapat diketahui dan pada ilustrasi gambar diatas nilai gaji dari pegawai 149 adalah 10500.

Contoh lain penggunaan SubQuery:

SELECT mhs.namaMHS,ambilMK.nilai FROM mhs,ambilMK WHERE mhs.nim=ambilMK.nim AND ambilMK.kodeMK='A02' AND ambilMK.nilai=(SELECT max(nilai) FROM ambilMK WHERE kodeMK='A02');

Dependency dan Normalisasi

Functional Dependency
Menggambarkan hubungan, batasan, keterkaitan antara atribut-atribut dalam relasi.
Simbolny: -> dibaca secara fungsional menentukan.

contoh:
A->B = A secara fungsional menentukan B
A dan B merupakan atribut tabel.

• NIM->Nama 

           Karena untuk setiap NIM yang sama maka Nama juga sama.

• {Matakuliah,NIM}->NilaiHuruf

          Karena untuk menentukan NilaiHuruf tidak hanya membutuhkan NIM saja.

Macam Dependency:

• Full Functional
• Partial
• Transitive

• Full Functional

          Menunjukkan jika terdapat atribut A dan B dimana

• B memiliki ketergantungan secara penuh pada A
• B bukan full dependency jika ada subset A

     contoh:

     {NIM,Nama}->id_ruang

• Partially Dependency

          Merupakan ketergantungan fungsional dimana beberapa atribut dapat dihilangkan dari A dengan ketergantungan tetap dipertahankan.

> B memiliki dependency terhadap subset A.

contoh:

NIM,Nama-> id_ruang

Jika nama dihilangkan tetap terjadi ketergantungan atau id_ruang tetap bisa dipertahankan.

• Transitive Dependency

          Merupakan tipe functional dependency yaitu kondisi dimana A,B,C adalah atribut sebuah relasi dimana A-> B dan B->C

Maka C diakatakan sebagai transitive dependency terhadap A melalui B.

Normalisasi

Merupakan suatu proses untuk mendapatkan struktur tabel atau relasi yang efisien dan bebas sari anomali dan mengacu pada cara data item dikelompokkan ke dalam struktur record.

Normalisasi adalah proses pembentukan struktur basis data sehingga sebagian besar ambiguitas bisa dihilangkan.

Mengapa dilakukan Optimalisasi?

• Optimalisasi struktur-struktur tabel
• Meningkatkan kecepatan
• Menghilangkan pemasukan data yang sama
• Lebih efisien dalam penggunaan media penyimpanan
• Mengurangi redundansi
• Menghindari anomali (onsertion anomalies, deletion anomalies, update anomalies)
• Integritas data yang ditingkatkan

Bentuk Normalisasi

• 1NF
• 2NF
• 3NF
• BCNF
• 4NF
• 5NF

Pengenalan SQL

SQL adalah suatu bentuk script yang digunakan untuk berkomunikasi dengan basisdata.

 Elemen SQL
Elemen dasar SQL mencakup pernyataan, nama, tipe data, konstanta, ekspresi, dan fungsi bawaan.
1. Pernyataan
Pernyataan adalah perintah SQL yang meminta sesuatu tindakan kepada DBMS. SQL memiliki kira-kira 30 pernyataan. Beberapa pernyataan dasar SQL berikut ini :
ALTER
Mengubah struktur tabel
COMMIT
Mengakhiri sebuah eksekusi transaksi
CREATE
Menciptakan tabel, indeks atau pandangan
DELETE
Menghapus baris pada tabel
DROP
Menghapus tabel, indeks atau pandangan
GRANT
Menugaskan hak terhadap basis data kepada pengguna atau grup pengguna
INSERT
Menambahkan sebuah baris pada tabel
REVOKE
Membatalkan hak terhadap basis data
ROLLBACK
Mengembalikan ke keadaan semula sekiranya suatu transaksi gagal dilaksanakan
SELECT
Memilih baris dan kolom pada tabel
UPDATE
Mengubah nilai pada sebuah baris


2. Nama
Nama digunakan sebagai identitas bagi objek-objek pada DBMS. Contoh objek pada DBMS adalah tabel, kolom dan pengguna.


3. Tipe Data
Setiap data memiliki tipe data.
a. Tipe data untuk numerik
b. Tipe data string
c. Tipe data tanggal dan jam


4. Konstanta
Konstanta menyatakan nilai yang tetap.
5. Ekspresi
Ekspresi adalah segala sesuatu yang menghasilkan nilai. Ekspresi digunakan untuk menghitung nilai.

6. Aggregate Functions (Fungsi Agregat)
Fungsi adalah sebuah subprogram yang menghasilkan suatu nilai jika dipanggil. Fungsi agregat adalah fungsi standar di dalam SQL, suatu fungsi yang digunakan untuk melakukan summary, fungsi statistik standar yang dikenakan pada suatu tabel atau query.
a. AVG(ekspresi)
Fungsi ini digunakan untuk mencari rata-rata nilai dalam suatu kolom dari suatu tabel atau ekspresi. Ekspresi dalam fungsi AVG umumnya adalah nama kolom. Kolom yang dicari nilai rata-ratanya adalah kolom dengan tipe data numerik.
b. COUNT(x)
Fungsi ini digunakan untuk menghitung jumlah record (baris) dari suatu kolom dari suatu tabel. X adalah nama kolom yang ingin dicari jumlah barisnya.
c. MAX(ekspresi)
Fungsi ini digunakan untuk mencari nilai terbesar dari suatu kolom dari suatu tabel. Kolom yang dicari nilai terbesarnya memiliki tipe data numerik.
d. MIN(ekspresi)
Fungsi ini digunakan untuk mencari nilai terkecil dari suatu kolom dari suatu tabel. Kolom yang dicari nilai terkecilnya memiliki tipe data numerik.
e. SUM(ekspresi)
Fungsi ini digunakan untuk mendapatkan nilai total dari suatu kolom pada suatu tabel.

Transformasi Model Data

Model data lanjut
> Varian entitas
    - Entitas kuat yaitu himpunan entitas yang dilibatkan dalam ERD
    - Entitas Lemah yaitu bahwa keberadaannya tergantung dari keberadaan entitas lain
>Agregasi yaitu himpunan relasi yang langsung menghubungkan himpunan entitas dengan himpunan relasi
  dalam ERD.

   TRANSFORMASI MODEL DATA KE BASIS DATA FISIK
- ERD direpresentasikan menjadi sebuah basis data secara physic
- Komponen ERD (himpunan relasi dan entitas) ditransformasikan ke dalam bentuk tabel

TRANSFORMASI DASAR
1. Relasi 1-1

2. Relasi 1-N

3. Relasi N-N

IMPLEMENTASI HIMPUNAN ENTITAS LEMAH DAN SUB ENTITAS

Penggunaan himpunan entitas lemah dan sub entitas langsung diimplementasikan dalm bentuk tabel seperti pada entitas kuat. Perbedaannya entitas lemah hanya dapat ditransformasikan dalam sebuah tabel dengan menyertakan pula atribut key sebagai hasil implementasi entitas lemah.

HUBUNGAN HIMPUNAN ENTITAS LEMAH DAN SUB ENTITAS

MODEL DATA RELASIONAL

            ya kembali lagi bersama saya disini :D. ya kemarin kan udah ada pos tentang ERD. la sekarang kita bakal belajar tentang model data relasional. apa itu model data relasional. mau tau? mau tau aja pa mau tau banget? ya weslah, cekidot aja :D

model data relasional adalah Kumpulan tabel berdimensi dua dengan masing relasi tersusun atas tuple dan atribut pada suatu basis data..model data meletakkan data dalam bentuk relasi.

Dalam model relasi data terdapat beberapa istilah, yaitu :

·         Relasi                    : sebuah tabel yang terdiri dari baris dan kolom.

·         Attribute             :  kolom pada relasi (field)

·         Tuple                     :baris pada sebuah relasi (record)

·         Domain                                :kumpulan nilai yang valid untuk satu atau lebih atribut

·         Derajat                                 :jumlah atribut

·         Cardinality           :jumlah tupel

Relasi

                Relasi menunjukkan adanya hubungan diantra sejumlah entitas dari himpunan entitas yang sama. Merupakn setruktur data dasar untuk menyimpan informasi. Setiap relasi mempunyai skema yang menunjukkan nama relasi dan atribut serta tipenya.  Contoh : murid = (nomer_induk : string, nama_murid : string). Keterangan contoh : murid adalah nama relasi, dan nama_murid adalah nama atribute, string yaitu tipe dari suatu atribute.

Pendefinisian Domain

·         Memberi nama domain sesuai dengan nilai yang akan dimiliki domain tersebut.

·         Menentukan tipe data dari nilai yang akan membentuk domain

·         Menentukan format dari domain.

Kunci atribut dari relasi

·         Candidate key   :atribut yang digunakan untuk memebrdakan antar satu baris dengan yang lainya

·         Primary key        :candidate key yang digunakan untuk mengidentifikasi tupel secara unik dalam relasi

·         Alternate key    : candidate key yang tidak di[ilih sebagai primary key

·         Foreign key        :atribut dengan doamain yang sama yang menjadi kunci utama pada sebuah relasi tetapi pada relasi lain atribut tersebut hanya sebagai atribut biasa.

Integrity Constraints

            Adalah suatu batasan yang diberikan kepada suatu relasi yang ditetapkan ketika skema didefinisikan.

Relational integrity rules

1.      Null

Nilai suatu atribut yang tidak diketahui dan tidak cocok untuk baris (tuple) tersebut

2.    Entity Integrity

Tidak ada satu komponen primary key yang bernilai null.

3.    Referential Integrity

Suatu domain dapat dipakai sebagai kunci primer bila merupakan atribut tunggal pada domain yang bersangkutan.

tugas 1


Entitas Account
Candidat Key : Account_number, balance
Primary Key : account_number
Alternate Key : balance
Foreign Key : -
Entitas Branch
Candidat Key : branch-name, branch-city, assets
Primary Key : branch-name
Alternate Key : branch-city, assets
Entitas Customer
Candidat Key : customer-name, customer-street, customer-city
Primary Key : customer-name
Alternate Key : customer-street, customer-city
Entitas Loan
Candidat Key : loan-number, amount
Primary Key : loan-number
Alternate Key : amount



tugas 2

tabel keterhubungan