Pindah Rata Rata Pl Sql

Daftar Isi williamrobertson Menyiapkan Pengembang PLSQL, bagian 2 Artikel ini ditulis untuk Pengembang PLSQL 8.0.4 menggunakan Oracle 11.2 dan Windows XP, dalam mesin virtual Parallels di Mac saya, oleh karena itu screenshot menunjukkan campuran jendela XP Silver and Aqua. PLSQL Developer adalah salah satu dari beberapa lingkungan pengembangan terintegrasi (IDE) yang tersedia untuk Oracle. Salah satu hal yang saya sukai adalah bagaimana dikonfigurasi - Anda dapat mengubah hampir semua hal, dan dengan plug-in yang dapat didownload seperti Browser Extender Anda dapat menambahkan fungsionalitas Anda sendiri. Setelah memindahkan PC beberapa kali dan harus segera menginstal ulang Pengembang PLSQL setiap saat, saya telah menemukan bahwa ada beberapa penyesuaian yang tidak dapat saya jalani, dan menurut saya, Id mendokumentasikannya. Bagian Pertama mencakup preferensi seperti font dan tata letak layar, dan Bagian 2 mencakup browser Sesi. Saya akan menyertakan tindakan mouseclick yang benar yang telah saya tambahkan menggunakan Browser Extender. Tapi ada banyak hal yang dapat Anda lakukan dengan browser Session yang harus saya tinggalkan untuk Bagian 3. Memperluas Browser Sesi 1. Buat Sesi Browser lebih mudah ditemukan Dalam tata letak default, cara terkuburnya di bawah daftar di bawah Alat menu, tapi sebagai sesuatu yang Anda akan menggunakan semua waktu yang jauh lebih baik untuk memiliki tombol untuk itu. Jika Anda melewatkannya di bagian 1. Anda dapat menyesuaikan toolbar dengan menambahkan ikon untuk Session Browser. Heres jenis hal yang dapat Anda lakukan: Bilah alat default toolbar yang disesuaikan Perhatikan ikon lintas tombol kedua dari kiri di toolbar yang disesuaikan. 2. Tampak pada pengaturan default Sekarang buka Session Browser dan lihat set-up defaultnya. (Sebenarnya tidak cukup default - saya telah mengubah font ke Corbel 8pt, yang sesuai dengan lebih banyak informasi di layar, dan juga lebih menarik dari pada bawaan menurut saya Tahoma juga bekerja dengan baik. Anda akan sering melihat layar ini, Setelah semua.) Layar adalah laporan master-detail, dengan kedua master dan detail kueri dikonfigurasi menggunakan ikon kunci inggris. Permintaan utama di bagian atas jendela didefinisikan di bawah Filter, dan beberapa variasi pada pilih dari vsession disediakan. Di bawah Rincian, ada empat kueri yang sangat mendasar untuk kursor terbuka, pernyataan SQL saat ini, statistik sesi dan kunci: Perhatikan variabel terikat: sid di kueri Cursors. Hal yang keren tentang query detail browser sesi adalah Anda dapat merujuk pada nilai kolom saat ini dari bagian atas sebagai variabel terikat dalam query detail. Jadi, selama query utama berisi kolom bernama sid, kita bisa menggunakan ungkapan seperti di mana sessionid: sid in any detail query. (Ini berarti, bagaimanapun, bahwa Anda mungkin perlu memasukkan beberapa kolom dalam query master semata-mata untuk digunakan sebagai kunci dalam pertanyaan rinci.) Satu hal lagi yang perlu diperhatikan tentang kotak permintaan detail adalah menambahkan teks yang digabungkan setelah kueri membuat Pengembang PLSQL menggabungkan semua baris output menjadi satu blok besar. Sementara fitur yang apik, ini juga mencegah pengguliran jadi saya merasakannya berkat campuran. 3. Tuliskan pertanyaan VSESSION Anda sendiri Active Sessions Query default semua dipilih dari vsession dimana. . Yang tentu saja adalah pengaturan default yang masuk akal yang akan bekerja di semua versi Oracle. Atribut baru dan berguna ditambahkan ke vsession di setiap rilis, dan tentu saja mengkodekannya secara eksplisit dalam join dan look-up berarti query mungkin tidak bekerja pada versi sebelumnya.1 Jika Anda bekerja dengan beberapa versi Oracle, Anda mungkin perlu menyimpan Lebih dari satu query di bagian Filter, dan pilih yang sesuai sesuai kebutuhan (sayangnya pengembang PLSQL tidak dapat memeriksa versinya dan memilihnya untuk Anda). Heres query sesi yang lebih aktif yang lebih baik untuk Oracle 10.2.0.2 dan seterusnya (perhatikan kolom plsqlentryobjectid dan plsqlentrysubprogramid antara lain, ditambahkan dalam versi ini sehingga tidak berfungsi di Oracle 10g XE). Semua sesi yang sedang aktif (kecuali proses latar belakang Oracle seperti Log Writer), atau yang menghalangi sesi lain, atau yang dimiliki oleh saya. Orang tua mereka, jika bagian dari kueri paralel Objek yang saat ini ditunggu (biasanya berupa tabel atau indeks) - mendongak dari dbaobjects menggunakan rowwaitobj. The PLSQL entri dan prosedur saat ini - mendongak dari dbaprocedures menggunakan kolom plsql ditambahkan di Oracle 10.2.0.2. Beberapa statistik tentang CPU, baca, penggunaan memori dan query parsing, dari vsessmetric. Saat hasilnya ditampilkan, Anda dapat mengeklik kolom ini untuk mengurutkan sesi dengan penggunaan CPU. Sesi mana pun yang menghalangi yang lain, terlepas dari statusnya, selain sesi latar depan yang sedang aktif. Contoh RAC, untuk cluster multi-node. Jika Anda hanya memiliki satu contoh, itu akan menjadi 1 (Anda mungkin ingin memindahkannya ke akhir daftar untuk memberi ruang bagi kolom lainnya). Tampilan GV untuk RAC Tampilan v (sebenarnya sinonim untuk tampilan sys. v) semuanya memiliki versi awalan - misalnya, gvsession - yang menyertakan nomor contoh, untuk digunakan dalam sistem RAC. Untuk sistem single-instance ini akan selalu menjadi 1. Daftar dokumentasi hanya versi v, jadi jika Anda ingin tahu tentang gvsession. Sebagai contoh, lihat saja vsession dan anggap akan ada satu kolom tambahan bernama instid. Saya telah menggunakan nama vv reguler dan RAC-ready secara bergantian. Salin kueri di bawah ini ke kotak Kueri (setelah mengujinya di jendela SQL untuk memastikannya bekerja sesuai dengan versi dan izin Oracle Anda - untuk mengakses tampilan V yang Anda butuhkan SELECTCATALOGROLE). Perhatikan bahwa tidak ada titik koma di akhir. Anda mungkin juga ingin mengulasnya terhadap vsession jika ada kolom berguna yang berguna bagi Anda. Sesi Saya Pada sistem yang sibuk, terkadang Anda hanya ingin melihat sesi Anda sendiri dan mengecualikan hal lainnya. Untuk ini saya menggunakan kueri sesi saya, yang sama dengan yang di atas kecuali klausa WHERE, yaitu: Semua Sesi Ini juga terkadang berguna untuk memiliki satu versi yang menunjukkan semua sesi, termasuk penulis log Oracle, monitor proses dll. Buatlah salinan lain dari query di atas, dan tinggalkan klausa WHERE. 4. Sekarang tambahkan tab detail Anda sendiri Ini menggunakan vsqlstats untuk menampilkan statistik eksekusi rinci tentang sesi pernyataan SQL saat ini (diidentifikasi oleh sqlid). Perhatikan bahwa itu mengacu pada semua contoh kursor, bukan hanya sesi ini saat ini panggilan. (Juga, karena v pandangan hanya merefleksikan apa yang ada dalam ingatan saat ini, mungkin berbeda dari apa yang Anda lihat dalam pandangan dbahist jika Anda memiliki Paket Diagnostik) Gagasan tentang persentase adalah untuk menunjukkan bagaimana waktu berlalu total Ke CPU, IO, concurrency menunggu dll. Perkiraannya hanya dan mereka tidak selalu menambahkan hingga 100, karena mungkin ada faktor lain yang tidak diketahui seperti waktu transfer jaringan dan pemrosesan aplikasi, namun mereka memberi gambaran bagaimana pernyataan tersebut sedang Diproses Sekarang Anda harus mendapatkan tab Statistik SQL seperti tangkapan layar di bawah untuk sesi yang mengeksekusi SQL. (Binding, Prev SQL dll adalah tab lain Ill define in a moment.) Perf history this cursor Jika sebuah pernyataan SQL memakan waktu lama, Anda mungkin ingin memeriksa riwayat kinerjanya (dari dbahistsqlstats) untuk melihat apakah ini normal untuk Kursor atau apakah sesuatu telah berubah Query pertama di bawah ini memberikan rencana eksekusi yang berbeda dan statistik runtime yang sesuai, digabungkan untuk keseluruhan sejarah kursor, sehingga Anda dapat melihat waktu eksekusi rata-rata dan apakah ada banyak rencana. (Perhatikan join ke gvsqlplan - g yang menunjukkan versi RAC-enabled - yang seeems menjadi cara yang paling andal untuk menemukan rencana eksekusi yang saat ini digunakan karena mencakup nomor anak. Seperti laporan vsqlstats hanya satu baris per sqlid yang berbeda ) Versi yang kedua - yang saya beri label sebagai Perf history kursor ini menurut tanggal - membagi informasi yang sama setiap hari, sehingga Anda dapat melihat apakah berjalan cepat pada hari Selasa yang lalu, atau apakah itu Rencana berubah pagi ini: Permintaan berikut akan menampilkan variabel terikat yang ada di vsqlbindcapture untuk pernyataan SQL saat ini. Saya telah memfilter hasilnya untuk mengecualikan duplikat. Perhatikan bahwa Oracle tidak menangkap setiap nilai bind tunggal, dan hanya memegang nilai terakhir yang diambil pada interval cursorbindcaptureinterval dan tergantung pada jumlah ruang yang tersedia hingga cursorbindcaptureareasize. Alternatifnya adalah untuk mendapatkan data bind yang digunakan pada waktu parsing dari vsqlplan. Meskipun ini membutuhkan beberapa decoding seperti yang dipegang dalam format RAW dalam kolom XML - lihat entri blog Jonathan Lewis Bind Capture. Yang terhubung ke Membuat Skrip Uji Dengan Variabel Bind dari Kerry Osborne dan Melacak Nilai Bind dari Dion Cho. Hal ini membawa saya pada pertanyaan berikut menggunakan sebuah gagasan dari Kyle Hailey dalam komentar di posting Jonathan Lewis: Dalam pengujian saya yang menggunakan Oracle 11.2.0.2, ini menghilangkan nama pengikat. Pokoknya menangkap nilai-nilai mengikat adalah subjek yang besar, maka saya akan meninggalkan Anda dengan pertanyaan di atas untuk bereksperimen dengan, dan melanjutkan. Sebelumnya SQL, statemen SQL sebelumnya Terkadang berguna untuk melihat seperti apa pernyataan sebelumnya. Vsession berisi beberapa kolom prev, jadi duplikat tab detail untuk SQL Text dan SQL Stats tapi ganti prevsqlid dan prevchildnumber. Statistik objek Saat menyelidiki masalah kinerja, Anda sering ingin memeriksa status statistik saat ini pada tabel yang terlibat dalam kueri. Query di bawah ini bergabung dengan vsqlplanstatisticsall dengan dbatabstatistics untuk mencantumkan informasi ini - tabel partisi yang tidak sempurna jika dipartisi, karena masalahnya mungkin terletak pada statistik partisi individual atau subpartisi, namun merupakan awal. Ganti query Cursors default (pilih dari vopencursor dimana sid: sid) dengan yang berikut untuk menambahkan beberapa statistik aktivitas. (Perhatikan bahwa statistik eksekusi mengacu pada semua sesi, bukan hanya sesi saat ini.) Rencana saat ini PLSQL Developers built-in Explain Plan tool (F5) semuanya baik dan bagus, namun hanya bisa sebagus menjelaskan rencana. Artinya, alat tersebut menggunakan menjelaskan rencana untuk memprediksi rencana eksekusi, dan kemudian menampilkan hasilnya dalam bentuk grafis. Terkadang hal ini tidak sama dengan runtime plan yang sebenarnya. Saat melihat sesi pelaksana saat ini, saya ingin menggunakan dbmsxplan. displaycursor () untuk melihat apa sebenarnya yang sedang dilakukan database. Tentukan tab Current plan dengan menggunakan berikut ini: Komentar gabungan akan membuat Developer PLSQL membungkus semua baris output dari query menjadi satu blok besar. Hal ini membuat lebih mudah dibaca, meski memang juga mencegah scrolling jadi saya tidak yakin semua itu berguna disini. (Sayangnya Anda tidak dapat menentukan font monospace untuk item menu individual, jadi tampilan defaultnya tidak terlalu bagus.) Cara terbaik untuk membacanya adalah dengan menyalin dan menempelkan ke jendela SQL baru. Ini lebih mudah jika Anda mendefinisikan hotkey seperti Alt-S untuk File gt New gt SQL Window seperti yang saya sarankan di bagian 1. (Saya juga memiliki ekstensi Browser Extender untuk melakukan ini dalam satu klik kanan, yang akan datang nanti. ) Saya juga menggunakan variasi lain dari query ini, yang telah saya beri label Current plan GPS (Mengumpulkan Rencana Statistik - walaupun mungkin rencana Extended akan menjadi nama yang lebih baik sekarang saya pikirkan). Ini menggunakan ALLSTATS LAST dalam format argumen untuk dbmsxplan. displaycursor untuk mendapatkan jumlah baris yang diperkirakan dan aktual (kardinalitas) jika kueri menggunakan petunjuk gatherplanstatistics, atau jika statistik parameter ditetapkan ke SEMUA untuk sesi tersebut. Bagian yang sedikit rumit dengan ini adalah Anda tidak dapat menggunakannya sampai permintaan selesai (karena jumlah baris aktual belum diketahui), namun bila selesai, sudah tidak ada lagi kueri yang dijalankan saat ini dan karena itu lenyap dari vsession, dan saat Anda melakukannya Refresh browser sesi Anda yang hilang. Sebagai gantinya Anda perlu menyegarkan layar saat kueri dijalankan, tapi tunggu sampai selesai sebelum membuka tab GPS Rencana Saat Ini. Menunggu saat ini Meskipun sesi menunggu saat ini sudah ditunjukkan pada query master, di atas, saya juga ingin menyimpan informasi di tabnya sendiri. Saya telah memberi label pada objek tunggu yang mendongak dari rowwaitobj karena mungkin tidak terkait sebagai pengingat bahwa meskipun ini adalah objek terbaru yang menunggu sesi ini, pemrosesan mungkin sekarang telah beralih ke hal lain (memilah output misalnya, atau menunggu aplikasi ke Memproses output query) dan sesi sebenarnya tidak mengakses objek ini saat ini. 10 terakhir menunggu Berikut ini adalah sekilas aktivitas sesi dengan menggunakan vsessionwaithistory (waktu tunggu dalam seperseratus detik): vsessionlongops menampilkan status berbagai operasi yang berjalan lebih lama dari 6 detik. Operasi ini saat ini mencakup banyak fungsi backup dan pemulihan, pengumpulan statistik, dan eksekusi kueri, dan lebih banyak operasi ditambahkan untuk setiap rilis Oracle. Jika query menggunakan operasi hash atau sort, pemindaian tabel, operasi partisi dll yang memakan waktu lebih dari 6 detik, operasi ini akan muncul dalam vsessionlongops dan Anda dapat melacak kemajuannya. (Perhatikan bahwa satu-satunya operasi individual yang dilacak, bukan keseluruhan query.) Banyak proses Oracle yang telah berjalan lama diinstruksikan juga, seperti yang manual sebutkan. Lain-lain yang tidak tercantum di atas mencakup Replay Database dan SQL Performance Analyzer berjalan (11g), dan pekerjaan impor impor datapump - dan tentu saja setiap proses Anda sendiri yang Anda sertakan dbmsapplicationinfo. setsessionlongops panggilan untuk mencatat total pekerjaan dan jumlah yang diproses sejauh ini. Saya juga mendefinisikan sebuah ops Long tab query ini, yang merupakan salinan dari yang di atas tapi dengan filter tambahan untuk membatasinya pada operasi yang sedang dijalankan: ringkasan ASH - session vactivesessionhistory adalah sebuah snapshot dari vsession yang diambil sekali setiap detik, ditahan untuk sebuah Periode terbatas (biasanya 30 sampai 60 menit) dan kemudian disimpan dalam dbahistactivesesshistory. (Untuk menggunakan ini, Anda memerlukan Paket Diagnostik, jadi pastikan Anda berlisensi meskipun bekerja - Anda tidak ingin atasan Anda mendapatkan tagihan tak terduga mengikuti audit Oracle.) Ada banyak cara kreatif untuk menambang informasi ini, dan saya menggunakan tiga kueri Untuk melacak sesi yang sedang berjalan saat ini. Karena sampel ASH setiap detik, dapat berguna untuk meringkasnya dengan pernyataan SQL dan mencantumkan hasilnya berdasarkan waktu yang dibutuhkan. Jika Anda menonton prosedur atau batch yang memanggil beberapa pernyataan, ini memberi gambaran umum tentang sesi yang menghabiskan waktunya (sedikit seperti menelusuri sesi). Permintaan berikut memberikan satu baris per sqlid. Dalam urutan total waktu, dengan total di bagian bawah. Ringkasan ASH - eksekusi Saya juga memiliki versi yang lebih rinci yang dimungkinkan di 11g oleh kolom sqlexecstart dalam vactivesessionhistory. Yang memungkinkan saya melihat eksekusi individual dari pernyataan SQL daripada satu baris gabungan. ASH ringkasan - waktu kursor objek Laporan menunjukkan objek menunggu oleh semua pernyataan SQL untuk sesi tertentu. Ini dimaksudkan sebagai cara cepat untuk melihat seperti apa sesi yang telah menghabiskan waktunya, dalam hal objek dan bukan kueri. ASH ringkasan query ini dengan pemanggil Selanjutnya saya memiliki query GROUP-BY untuk sqlid saat ini. Dalam urutan hitungan sampel. Idenya adalah untuk melihat di mana waktu yang dihabiskan dalam pernyataan yang sedang dijalankan (bukan pernyataan yang telah mengambil waktu dalam sesi saat ini). Sejak Active Session History menggunakan interval polling 1 detik, sesuatu yang terjadi pada 10 sampel mungkin telah memakan waktu sekitar 10 detik. Perhatikan bahwa itu hanya filter pada sqlid. Jadi beberapa eksekusi dari kueri yang sama oleh sesi akan digabungkan bersama-sama. (Di 11g Anda bisa menggunakan kolom sqlexecid baru untuk membedakan antara eksekusi.) Ingat juga bahwa ASH dapat melakukan aktivitas sampel seperti On CPU bersama dengan objek database - ini hanya berarti objek terakhir diakses pada saat sampel diambil, bukan Bahwa CPU tentu saja terkait dengan objek itu. Saya memiliki dua rasa yang satu ini, dengan dan tanpa rincian prosedur pemanggilan PLSQL. ASH ringkasan query SQL saja Ini sama dengan query sebelumnya, tapi tanpa pemanggilan rincian PLSQL untuk memberi tampilan akses database yang lebih jelas. ASH detail sesi ini Akhirnya, saya memiliki daftar riwayat vactivesessionession yang lurus untuk sesi saat ini sehingga Anda bisa mendapatkan ide tentang apa yang sedang dilakukannya: Penyiapan default tidak disertakan dengan tab Kunci. Katakanlah sesi melakukan tindakan berikut: Tab Kunci default menampilkan ini: Mengubahnya menjadi berikut ini memberikan beberapa rincian lebih lanjut: Pengoptimal tidak standar Saya merasa berguna untuk memeriksa setelan pengoptimalan yang digunakan oleh sesi tertentu (yang mungkin bukan Pengaturan yang sama seperti sesi Anda atau default contohnya). Ini mengikuti vsysoptimizerenv (parameter sistem yang terkait dengan pengoptimasi) dengan vsesoptimizerenv (parameter sesi yang terkait dengan pengoptimasi, yang pada awalnya diwariskan dari pengaturan tingkat sistem namun mencerminkan perubahan yang dibuat oleh perintah alter session) dan melaporkan perbedaan. Temp space Berapa banyak ruang temp adalah sesi ini yang digunakan untuk hash bergabung, macam salinan dll William Robertson 2011 Berlangganan artikel Berlangganan kode dan scripts22 SQL untuk Analisis dan Pelaporan Oracle telah meningkatkan kemampuan pemrosesan analisis SQL dengan memperkenalkan keluarga baru fungsi SQL analitik. Fungsi analitik ini memungkinkan Anda untuk menghitung: Tingkatan dan persentil Perhitungan jendela bergerak Regresi linier Statistik Fungsi peringkat mencakup distribusi kumulatif, peringkat persen, dan ubin N. Perhitungan jendela bergerak memungkinkan Anda menemukan agregasi bergerak dan kumulatif, seperti jumlah dan rata-rata. Analisis Laglead memungkinkan referensi antar baris langsung sehingga Anda dapat menghitung perubahan periode-ke-periode. Analisis firstlast memungkinkan Anda menemukan nilai pertama atau terakhir dalam grup yang dipesan. Perangkat tambahan lainnya ke SQL mencakup ekspresi CASE dan partisi outer join. Pernyataan CASE menyediakan jika-maka logika berguna dalam banyak situasi. Partisi outer join adalah perpanjangan sintaks join ANSI outer yang memungkinkan pengguna untuk secara selektif mengelompokkan dimensi tertentu sambil menjaga agar orang lain jarang. Ini memungkinkan alat pelaporan untuk memodifikasikan dimensi secara selektif, misalnya yang muncul dalam laporan lintas-tabular sambil membuat orang lain jarang. Untuk meningkatkan kinerja, fungsi analitik dapat diparalelkan: beberapa proses secara bersamaan dapat mengeksekusi semua pernyataan ini. Kemampuan ini membuat perhitungan menjadi lebih mudah dan efisien, sehingga meningkatkan kinerja database, skalabilitas, dan kesederhanaan. Fungsi analitik diklasifikasikan seperti yang dijelaskan pada Tabel 22-1. Tabel 22-1 Fungsi Analitik dan Kegunaannya Untuk melakukan operasi ini, fungsi analitik menambahkan beberapa elemen baru ke pemrosesan SQL. Unsur-unsur ini dibangun di atas SQL yang ada untuk memungkinkan ekspresi perhitungan yang fleksibel dan kuat. Dengan hanya beberapa pengecualian, fungsi analitik memiliki elemen baru ini. Aliran pemrosesan ditunjukkan pada Gambar 22-1. Gambar 22-1 Pesanan Pengolahan Konsep penting yang digunakan dalam fungsi analitik adalah: Pengolahan kueri menggunakan fungsi analitik berlangsung dalam tiga tahap. Pertama, semua bergabung, WHERE. Klausa GROUP BY dan HAVING dilakukan. Kedua, himpunan hasil dibuat tersedia untuk fungsi analitik, dan semua perhitungannya terjadi. Ketiga, jika kueri memiliki klausa ORDER BY pada akhirnya, ORDER BY diproses untuk memungkinkan pesanan pesanan yang tepat. Urutan pemrosesan ditunjukkan pada Gambar 22-1. Hasil mengatur partisi Fungsi analitik memungkinkan pengguna untuk membagi hasil query set ke dalam kelompok baris yang disebut partisi. Perhatikan bahwa istilah partisi yang digunakan dengan fungsi analitik tidak terkait dengan fitur partisi tabel. Sepanjang bab ini, istilah partisi hanya mengacu pada makna yang terkait dengan fungsi analitik. Partisi dibuat setelah kelompok didefinisikan dengan klausa GROUP BY, sehingga tersedia untuk hasil agregat seperti jumlah dan rata-rata. Pembagian partisi mungkin didasarkan pada kolom atau ungkapan yang diinginkan. Hasil query set dapat dipartisi menjadi satu partisi yang memegang semua baris, beberapa partisi besar, atau banyak partisi kecil yang masing-masing hanya memiliki beberapa baris. Untuk setiap baris di partisi, Anda dapat menentukan jendela geser data. Jendela ini menentukan rentang baris yang digunakan untuk melakukan perhitungan untuk baris saat ini. Ukuran jendela dapat didasarkan pada sejumlah fisik baris atau interval logis seperti waktu. Jendela memiliki baris awal dan baris akhir. Bergantung pada definisinya, jendela bisa bergerak pada satu atau kedua ujungnya. Misalnya, jendela yang didefinisikan untuk fungsi jumlah kumulatif akan memiliki baris mulai yang ditetapkan pada baris pertama partisi, dan baris akhir akan meluncur dari titik awal sampai ke baris terakhir partisi. Sebaliknya, jendela yang didefinisikan untuk rata-rata bergerak akan memiliki titik awal dan akhir slide sehingga mempertahankan rentang fisik atau logis konstan. Sebuah jendela dapat diatur sebesar semua baris dalam sebuah partisi atau hanya sebuah jendela geser satu baris dalam sebuah partisi. Ketika sebuah jendela dekat perbatasan, fungsi mengembalikan hasil hanya untuk baris yang tersedia, daripada memberi peringatan bahwa hasilnya bukan yang Anda inginkan. Saat menggunakan fungsi jendela, baris saat ini disertakan selama penghitungan, jadi sebaiknya tentukan (n -1) saat Anda menangani n item. Setiap perhitungan yang dilakukan dengan fungsi analitik didasarkan pada baris saat ini dalam sebuah partisi. Baris saat ini berfungsi sebagai titik acuan menentukan awal dan akhir jendela. Misalnya, perhitungan rata-rata bergerak terpusat dapat didefinisikan dengan jendela yang menampung baris saat ini, enam baris sebelumnya, dan enam baris berikut. Ini akan membuat sebuah jendela geser dari 13 baris, seperti yang ditunjukkan pada Gambar 22-2. Gambar 22-2 Contoh Jendela Sliding Ranking, Windowing, dan Fungsi Pelaporan Bagian ini mengilustrasikan fungsi analitik dasar untuk rangking, windowing, dan pelaporan. Perhitungan Regresi Linier Linier Dalam contoh ini, kita menghitung garis regresi kuadrat biasa-kuadrat terkecil yang mengekspresikan kuantitas yang terjual suatu produk sebagai fungsi linear dari daftar harga produk. Perhitungannya dikelompokkan menurut saluran penjualan. Nilai SLOPE. INTCPT. RSQR adalah kemiringan, intersep, dan koefisien determinasi garis regresi. Nilai (integer) COUNT adalah jumlah produk di setiap saluran yang tersedia untuk kuantitas dan harga jual. Agregat Statistik Oracle menyediakan satu set fungsi statistik SQL dan paket statistik, DBMSSTATFUNCS. Bagian ini mencantumkan beberapa fungsi baru beserta sintaks dasar. Statistik Deskriptif Anda dapat menghitung statistik deskriptif berikut ini: Median dari Mode Kumpulan Data Kumpulan Data Anda dapat menghitung statistik parametrik berikut ini: Spearmans rho Koefisien Kendalls tau-b Koefisien Selain fungsi, rilis ini memiliki paket PLSQL, DBMSSTATFUNCS . Ini berisi fungsi statistik deskriptif RINGKASAN beserta fungsinya untuk mendukung pemasangan distribusi. Fungsi RINGKASAN merangkum kolom numerik tabel dengan berbagai statistik deskriptif. Fungsi distribusi lima distribusi mendukung distribusi normal, seragam, Weibull, Poisson, dan eksponensial. Agregat yang Ditetapkan Pengguna Oracle menawarkan fasilitas untuk membuat fungsi Anda sendiri, yang disebut fungsi agregat yang ditentukan pengguna. Fungsi-fungsi ini ditulis dalam bahasa pemrograman seperti PLSQL, Java, dan C, dan dapat digunakan sebagai fungsi analitik atau agregat dalam tampilan terwujud. Lihat Panduan Pengembang Cartridge Data Database Oracle untuk informasi lebih lanjut mengenai sintaks dan batasan. Kelebihan dari fungsi ini adalah: Fungsi yang sangat kompleks dapat diprogram menggunakan bahasa prosedural secara lengkap. Skalabilitas yang lebih tinggi daripada teknik lainnya saat fungsi yang ditentukan pengguna diprogram untuk pemrosesan paralel. Tipe data objek bisa diolah. Sebagai contoh sederhana dari fungsi agregat yang ditentukan pengguna, perhatikan statistik miring. Perhitungan ini mengukur jika kumpulan data memiliki distribusi miring tentang mean-nya. Ini akan memberitahu Anda jika satu ekor distribusi secara signifikan lebih besar dari yang lain. Jika Anda membuat agregat yang ditentukan pengguna yang disebut udskew dan menerapkannya pada data batas kredit pada contoh sebelumnya, pernyataan dan hasil SQL mungkin terlihat seperti ini: Sebelum membangun fungsi agregat yang ditentukan pengguna, Anda harus mempertimbangkan apakah kebutuhan Anda dapat dipenuhi. Di SQL biasa Banyak perhitungan yang kompleks dapat dilakukan secara langsung di SQL, terutama dengan menggunakan ekspresi CASE. Tinggal dengan SQL biasa akan memungkinkan pengembangan yang lebih sederhana, dan banyak operasi query sudah paralel dengan baik di SQL. Bahkan contoh sebelumnya, statistik miring, dapat dibuat dengan menggunakan standar, meskipun panjang, SQL. Operasi Pivoting D ata yang dikembalikan oleh query intelijen bisnis seringkali paling dapat digunakan jika disajikan dalam format crosstabular. The pivotclause dari pernyataan SELECT memungkinkan Anda menulis query crosstabulation yang memutar baris ke kolom, menggabungkan data dalam proses rotasi. Pivoting adalah teknik kunci dalam gudang data. Di dalamnya, Anda mengubah beberapa baris input ke baris yang lebih sedikit dan umumnya lebih lebar di gudang data. Saat berputar, operator agregasi diterapkan untuk setiap item dalam daftar nilai kolom pivot. Kolom pivot tidak boleh mengandung ekspresi yang sewenang-wenang. Jika Anda perlu berporos pada ekspresi, maka Anda harus alias berekspresi dalam pandangan sebelum operasi PIVOT. Sintaks dasarnya adalah sebagai berikut: Untuk menggambarkan penggunaan pivoting, buatlah tampilan berikut sebagai dasar untuk contoh selanjutnya: Contoh: Pivoting Pernyataan berikut ini menggambarkan pivot khas pada kolom saluran: Perhatikan bahwa output telah menciptakan empat kolom alias baru. , PENJUALAN LANGSUNG. INTERNETSALES. KATALOGSAL Dan TELESALES. Satu untuk masing-masing nilai pivot. Outputnya adalah jumlah. Jika tidak ada alias yang diberikan, judul kolomnya adalah nilai IN - list. Pivoting on Multiple Columns Anda dapat berporos pada lebih dari satu kolom. Pernyataan berikut menggambarkan pivot kolom beberapa tipikal: Perhatikan bahwa contoh ini menentukan IN - list multi kolom dengan judul kolom yang dirancang agar sesuai dengan anggota IN - list. Pivoting: Multiple Agregat Anda dapat berporos dengan beberapa agregat, seperti ditunjukkan pada contoh berikut: Perhatikan bahwa kueri membuat judul kolom dengan menggabungkan nilai pivot (atau alias) dengan alias fungsi agregat, ditambah garis bawah. Membedakan Nulls Generasi PIVOT dari Nulls di Sumber Data Anda dapat membedakan antara nilai null yang dihasilkan dari penggunaan PIVOT dan yang ada di data sumber. Contoh berikut menggambarkan null yang dihasilkan oleh PIVOT. Query berikut mengembalikan baris dengan 5 kolom, kolom prodid. Dan pivot menghasilkan kolom Q1. Q1COUNTTOTAL. Q2. Q2COUNTTOTAL. Untuk setiap nilai unik prodid. Q1COUNTTOTAL mengembalikan jumlah baris yang nilai qtrnya adalah Q1. Yaitu, dan Q2COUNTTOTAL mengembalikan jumlah baris yang nilai qtrnya adalah Q2. Asumsikan kita memiliki tabel penjualan2 dari struktur berikut: Dari hasilnya, kita tahu bahwa untuk prodid 100, ada 2 baris penjualan untuk kuartal Q1. Dan 1 baris penjualan untuk kuartal Q2 untuk produk 200, ada 1 baris penjualan untuk kuartal Q1. Dan tidak ada baris penjualan untuk kuartal Q2. Jadi, di Q2COUNTTOTAL. Anda dapat mengidentifikasi bahwa NULLlt1gt berasal dari sebuah baris di tabel asli yang ukurannya bernilai null, sementara NULLlt2gt adalah karena tidak ada baris yang hadir dalam tabel asli untuk produk 200 pada kuartal Q2. Operasi yang Tidak Menghasilkan Sebuah unpivot tidak membalikkan operasi PIVOT. Sebagai gantinya, ia memutar data dari kolom menjadi beberapa baris. Jika Anda bekerja dengan data berporos, operasi UNPIVOT tidak dapat membalikkan agregasi yang telah dilakukan oleh PIVOT atau cara lainnya. Untuk mengilustrasikan unpivoting, pertama buat tabel yang diputar yang mencakup empat kolom, untuk perempat tahun: Isi tabel menyerupai berikut ini: Operasi UNPIVOT berikut memutar kolom kuartal ke dalam baris. Untuk setiap produk, akan ada empat baris, satu untuk setiap kuartal. Perhatikan penggunaan INCLUDE NULLS dalam contoh ini. Anda juga bisa menggunakan EXCLUDE NULLS. Yang merupakan setting default. Selain itu, Anda juga dapat melakukan unpivot dengan menggunakan dua kolom, seperti berikut ini: Wildcard dan Subquery Pivoting with XML Operations Jika Anda ingin menggunakan wildcard argument atau subquery di kolom pivoting Anda, Anda dapat melakukannya dengan sintaks XML PIVOT. Dengan PIVOT XML, output dari operasi tersebut diformat dengan benar XML. Contoh berikut menggambarkan penggunaan kata kunci wildcard, APAPUN. Ini menghasilkan XML yang mencakup semua nilai saluran di salesview: Perhatikan bahwa kata kunci ANY tersedia dalam operasi PIVOT hanya sebagai bagian dari operasi XML. Output ini mencakup data untuk kasus dimana saluran berada di kumpulan data. Perhatikan juga bahwa fungsi agregasi harus menentukan klausa GROUP BY untuk mengembalikan beberapa nilai, namun pivotclause tidak mengandung klausa GROUP BY eksplisit. Sebagai gantinya, pivotclause melakukan GROUP BY implisit. Contoh berikut menggambarkan menggunakan subkueri. Ini menghasilkan XML yang mencakup semua nilai saluran dan data penjualan yang sesuai dengan masing-masing saluran: Output densifies data untuk memasukkan semua saluran yang mungkin untuk setiap produk. Data Densification untuk Pelaporan Data biasanya disimpan dalam bentuk yang jarang. Artinya, jika tidak ada nilai untuk kombinasi nilai dimensi yang diberikan, tidak ada baris dalam tabel fakta. Namun, Anda mungkin ingin melihat data dalam bentuk padat, dengan baris untuk semua kombinasi nilai dimensi ditampilkan meskipun tidak ada data fakta untuk mereka. Misalnya, jika produk tidak terjual selama jangka waktu tertentu, Anda mungkin masih ingin melihat produk untuk jangka waktu tersebut dengan nilai penjualan nol di sampingnya. Selain itu, perhitungan deret waktu dapat dilakukan dengan sangat mudah bila data padat sepanjang dimensi waktu. Ini karena data padat akan mengisi jumlah baris yang konsisten untuk setiap periode, yang pada gilirannya mempermudah penggunaan fungsi windowing analitik dengan offset fisik. Keragaman data adalah proses pengubahan data yang jarang menjadi bentuk yang padat. Untuk mengatasi masalah sparsity, Anda bisa menggunakan outer yang dipartisi untuk mengisi celah dalam deret waktu atau dimensi lainnya. Gabung semacam itu memperluas sintaks join luar konvensional dengan menerapkan outer join ke setiap partisi logical yang didefinisikan dalam query. Oracle secara logis mempartisi baris dalam query Anda berdasarkan ekspresi yang Anda tentukan di dalam PARTITION BY clause. Hasil dari outer join yang dipartisi adalah UNION dari outer join dari masing-masing partisi di tabel yang dipartisi secara logis dengan tabel di sisi lain join. Perhatikan bahwa Anda dapat menggunakan jenis ini untuk mengisi celah dalam dimensi apa pun, bukan hanya dimensi waktu saja. Sebagian besar contoh di sini berfokus pada dimensi waktu karena dimensi inilah yang paling sering digunakan sebagai dasar perbandingan. Partition Join Syntax Sintaks untuk partisi outer join memperluas klausa ANSI SQL JOIN dengan frasa PARTITION BY diikuti oleh daftar ekspresi. Ekspresi dalam daftar menentukan kelompok yang mana outer join diterapkan. Berikut ini adalah dua bentuk sintaks yang biasanya digunakan untuk partisi outer join: Perhatikan bahwa FULL OUTER JOIN tidak didukung dengan outer join yang dipartisi. Sample of Sparse Data A typi cal situation with a sparse dimension is shown in the following example, which computes the weekly sales and year-to-date sales for the product Bounce for weeks 20-30 in 2000 and 2001: In this example, we would expect 22 rows of data (11 weeks each from 2 years) if the data were dense. However, we get only 18 rows because weeks 25 and 26 are missing in 2000, and weeks 26 and 28 in 2001. Filling Gaps in Data We can take the sparse data of the preceding query and do a partitioned outer join with a dense set of time data. In the following query, we alias our original query as v and we select data from the times table, which we alias as t. Here we retrieve 22 rows because there are no gaps in the series. The four added rows each have 0 as their Sales value set to 0 by using the NVL function. Note that in this query, a WHERE condition was placed for weeks between 20 and 30 in the inline view for the time dimension. This was introduced to keep the result set small. Filling Gaps in Two Dimensions N-dimensional data is typically displayed as a dense 2-dimensional cross tab of (n - 2) page dimensions. This requires that all dimension values for the two dimensions appearing in the cross tab be filled in. The following is another example where the partitioned outer join capability can be used for filling the gaps on two dimensions: In this query, the WITH subquery factoring clause v1 summarizes sales data at the product, country, and year level. This result is sparse but users may want to see all the country, year combinations for each product. To achieve this, we take each partition of v1 based on product values and outer join it on the country dimension first. This will give us all values of country for each product. We then take that result and partition it on product and country values and then outer join it on time dimension. This will give us all time values for each product and country combination. Filling Gaps in an Inventory Table An inventory table typically tracks quantity of units available for various products. This table is sparse: it only stores a row for a product when there is an event. For a sales table, the event is a sale, and for the inventory table, the event is a change in quantity available for a product. For example, consider the following inventory table: The inventory table now has the following rows: For reporting purposes, users may want to see this inventory data differently. For example, they may want to see all values of time for each product. This can be accomplished using partitioned outer join. In addition, for the newly inserted rows of missing time periods, users may want to see the values for quantity of units column to be carried over from the most recent existing time period. The latter can be accomplished using analytic window function LASTVALUE value. Here is the query and the desired output: The inner query computes a partitioned outer join on time within each product. The inner query densifies the data on the time dimension (meaning the time dimension will now have a row for each day of the week). However, the measure column quantity will have nulls for the newly added rows (see the output in the column quantity in the following results. The outer query uses the analytic function LASTVALUE. Applying this function partitions the data by product and orders the data on the time dimension column ( timeid ). For each row, the function finds the last non-null value in the window due to the option IGNORE NULLS. which you can use with both LASTVALUE and FIRSTVALUE. We see the desired output in the column repeatedquantity in the following output: Computing Data Values to Fill Gaps Examples in previous section illustrate how to use partitioned outer join to fill gaps in one or more dimensions. However, the result sets produced by partitioned outer join have null values for columns that are not included in the PARTITION BY list. Typically, these are measure columns. Users can make use of analytic SQL functions to replace those null values with a non-null value. For example, the following q uery computes monthly totals for products 64MB Memory card and DVD-R Discs (product IDs 122 and 136) for the year 2000. It uses partitioned outer join to densify data for all months. For the missing months, it then uses the analytic SQL function AVG to compute the sales and units to be the average of the months when the product was sold. If working in SQLPlus, the following two commands wraps the column headings for greater readability of results: Time Series Calculations on Densified Data Densificatio n is not just for reporting purpose. It also enables certain types of calculations, especially, time series calculations. Time series calculations are easier when data is dense along the time dimension. Dense data has a consistent number of rows for each time periods which in turn make it simple to use analytic window functions with physical offsets. To illustrate, let us first take the example on Filling Gaps in Data. and lets add an analytic function to that query. In the following enhanced version, we calculate weekly year-to-date sales alongside the weekly sales. The NULL values that the partitioned outer join inserts in making the time series dense are handled in the usual way: the SUM function treats them as 0s. Period-to-Period Comparison for One Time Level: Example How do we use this feature to compare values across time periods Specifically, how do we calculate a year-over-year sales comparison at the week level The following query returns on the same row, for each product, the year-to-date sales for each week of 2001 with that of 2000. Note that in this example we start with a WITH clause. This improves readability of the query and lets us focus on the partitioned outer join. If working in SQLPlus, the following command wraps the column headings for greater readability of results: In the FROM clause of the inline view densesales. we use a partitioned outer join of aggregate view v and time view t to fill gaps in the sales data along the time dimension. The output of the partitioned outer join is then processed by the analytic function SUM. OVER to compute the weekly year-to-date sales (the weeklyytdsales column). Thus, the view densesales computes the year-to-date sales data for each week, including those missing in the aggregate view s. The inline view yearoveryearsales then computes the year ago weekly year-to-date sales using the LAG function. The LAG function labeled weeklyytdsalesprioryear specifies a PARTITION BY clause that pairs rows for the same week of years 2000 and 2001 into a single partition. We then pass an offset of 1 to the LAG function to get the weekly year to date sales for the prior year. The outermost query block selects data from yearoveryearsales with the condition yr 2001. and thus the query returns, for each product, its weekly year-to-date sales in the specified weeks of years 2001 and 2000. Period-to-Period Comparison for Multiple Time Levels: Example While the prior example shows us a way to create comparisons for a single time level, it would be even more useful to handle multiple time levels in a single query. For example, we could compare sales versus the prior period at the year, quarter, month and day levels. How can we create a query which performs a year-over-year comparison of year-to-date sales for all levels of our time hierarchy We will take several steps to perform this task. The goal is a single query with comparisons at the day, week, month, quarter, and year level. The steps are as follows: We will create a view called cubeprodtime. which holds a hierarchical cube of sales aggregated across times and products . Then we will create a view of the time dimension to use as an edge of the cube. The time edge, which holds a complete set of dates, will be partitioned outer joined to the sparse data in the view cubeprodtime . Finally, for maximum performance, we will create a materialized view, mvprodtime. built using the same definition as cubeprodtime . For more information regarding hierarchical cubes, see Chapter 21, SQL for Aggregation in Data Warehouses. The materialized view is defined in Step 1 in the following section. Step 1 Create the hierarchical cube view The materialized view shown in the following may already exist in your system if not, create it now. If you must generate it, note that we limit the query to just two products to keep processing time short: Because this view is limited to two products, it returns just over 2200 rows. Note that the column HierarchicalTime contains string representations of time from all levels of the time hierarchy. The CASE expression used for the HierarchicalTime column appends a marker (0, 1. ) to each date string to denote the time level of the value. A 0 represents the year level, 1 is quarters, 2 is months, and 3 is day. Note that the GROUP BY clause is a concatenated ROLLUP which specifies the rollup hierarchy for the time and product dimensions. The GROUP BY clause is what determines the hierarchical cube contents. Step 2 Create the view edgetime, which is a complete set of date values edgetime is the source for filling time gaps in the hierarchical cube using a partitioned outer join. The column HierarchicalTime in edgetime will be used in a partitioned join with the HierarchicalTime column in the view cubeprodtime. The following statement defines edgetime : Step 3 Create the materialized view mvprodtime to support faster performance The materialized view definition is a duplicate of the view cubeprodtime defined earlier. Because it is a duplicate query, references to cubeprodtime will be rewritten to use the mvprodtime materialized view. The following materialized may already exist in your system if not, create it now. If you must generate it, note that we limit the query to just two products to keep processing time short. Step 4 Create the comparison query We have now set the stage for our comparison query. We can obtain period-to-period comparison calculations at all time levels. It requires applying analytic functions to a hierarchical cube with dense data along the time dimension. Some of the calculations we can achieve for each time level are: Sum of sales for prior period at all levels of time. Variance in sales over prior period. Sum of sales in the same period a year ago at all levels of time. Variance in sales over the same period last year. The following example performs all four of these calculations. It uses a partitioned outer join of the views cubeprodtime and edgetime to create an inline view of dense data called densecubeprodtime. The query then uses the LAG function in the same way as the prior single-level example. The outer WHERE clause specifies time at three levels: the days of August 2001, the entire month, and the entire third quarter of 2001. Note that the last two rows of the results contain the month level and quarter level aggregations. Note that to make the results easier to read if you are using SQLPlus, the column headings should be adjusted with the following commands. The commands will fold the column headings to reduce line length: Here is the query comparing current sales to prior and year ago sales: The first LAG function ( salespriorperiod ) partitions the data on gidp. cat. subcat. prod. gidt and orders the rows on all the time dimension columns. It gets the sales value of the prior period by passing an offset of 1. The second LAG function ( salessameperiodprioryear ) partitions the data on additional columns qtrnum. monnum. and daynum and orders it on yr so that, with an offset of 1, it can compute the year ago sales for the same period. The outermost SELECT clause computes the variances. Creating a Custom Member in a Dimension: Example In many analytical SQL tasks, it is helpful to define custom members in a dimension. For instance, you might define a specialized time period for analyses. You can use a partitioned outer join to temporarily add a member to a dimension. Note that the new SQL MODEL clause is suitable for creating more complex scenarios involving new members in dimensions. See Chapter 23, SQL for Modeling for more information on this topic. As an example of a task, what if we want to define a new member for our time dimension We want to create a 13th member of the Month level in our time dimension. This 13th month is defined as the summation of the sales for each product in the first month of each quarter of year 2001. The solution has two steps. Note that we will build this solution using the views and tables created in the prior example. Two steps are required. First, create a view with the new member added to the appropriate dimension. The view uses a UNION ALL operation to add the new member. To query using the custom member, use a CASE expression and a partitioned outer join. Our new member for the time dimension is created with the following view: In this statement, the view timec is defined by performing a UNION ALL of the edgetime view (defined in the prior example) and the user-defined 13th month. The gidt value of 8 was chosen to differentiate the custom member from the standard members. The UNION ALL specifies the attributes for a 13th month member by doing a SELECT from the DUAL table. Note that the grouping id, column gidt. is set to 8, and the quarter number is set to 5. Then, the second step is to use an inline view of the query to perform a partitioned outer join of cubeprodtime with timec. This step creates sales data for the 13th month at each level of product aggregation. In the main query, the analytic function SUM is used with a CASE expression to compute the 13th month, which is defined as the summation of the first months sales of each quarter. The SUM function uses a CASE to limit the data to months 1, 4, 7, and 10 within each year. Due to the tiny data set, with just 2 products, the rollup values of the results are necessarily repetitions of lower level aggregations. For more realistic set of rollup values, you can include more products from the Game Console and Y Box Games subcategories in the underlying materialized view. Miscellaneous Analysis and Reporting Capabilities This section illustrates the following additional analytic capabilities: WIDTHBUCKET Function For a given expression, the WIDTHBUCKET function returns the bucket number that the result of this expression will be assigned after it is evaluated. You can generate equiwidth histograms with this function. Equiwidth histograms divide data sets into buckets whose interval size (highest value to lowest value) is equal. The number of rows held by each bucket will vary. A related function, NTILE. creates equiheight buckets. Equiwidth histograms can be generated only for numeric, date or datetime types. So the first three parameters should be all numeric expressions or all date expressions. Other types of expressions are not allowed. If the first parameter is NULL. the result is NULL. If the second or the third parameter is NULL. an error message is returned, as a NULL value cannot denote any end point (or any point) for a range in a date or numeric value dimension. The last parameter (number of buckets) should be a numeric expression that evaluates to a positive integer value 0, NULL. or a negative value will result in an error. Buckets are numbered from 0 to ( n 1). Bucket 0 holds the count of values less than the minimum. Bucket( n 1) holds the count of values greater than or equal to the maximum specified value. WIDTHBUCKET Syntax The WIDTHBUCKET takes four expressions as parameters. The first parameter is the expression that the equiwidth histogram is for. The second and third parameters are expressions that denote the end points of the acceptable range for the first parameter. The fourth parameter denotes the number of buckets. Consider the following data from table customers. that shows the credit limits of 17 customers. This data is gathered in the query shown in Example 22-24 . In the table customers. the column custcreditlimit contains values between 1500 and 15000, and we can assign the values to four equiwidth buckets, numbered from 1 to 4, by using WIDTHBUCKET (custcreditlimit, 0, 20000, 4). Ideally each bucket is a closed-open interval of the real number line, for example, bucket number 2 is assigned to scores between 5000.0000 and 9999.9999. sometimes denoted 5000, 10000) to indicate that 5,000 is included in the interval and 10,000 is excluded. To accommodate values outside the range 0, 20,000), values less than 0 are assigned to a designated underflow bucket which is numbered 0, and values greater than or equal to 20,000 are assigned to a designated overflow bucket which is numbered 5 (num buckets 1 in general). See Figure 22-3 for a graphical illustration of how the buckets are assigned. Figure 22-3 Bucket Assignments You can specify the bounds in the reverse order, for example, WIDTHBUCKET ( custcreditlimit. 20000. 0. 4 ). When the bounds are reversed, the buckets will be open-closed intervals. In this example, bucket number 1 is ( 15000,20000 , bucket number 2 is ( 10000,15000 , and bucket number 4, is ( 0 ,5000 . The overflow bucket will be numbered 0 ( 20000. infinity ), and the underflow bucket will be numbered 5 (- infinity. 0 . It is an error if the bucket count parameter is 0 or negative. Example 22-24 WIDTHBUCKET The followin g query shows the bucket numbers for the credit limits in the customers table for both cases where the boundaries are specified in regular or reverse order. We use a range of 0 to 20,000. Linear Algebra Linear algebra is a branch of mathematics with a wide range of practical applications. Many areas have tasks that can be expressed using linear algebra, and here are some examples from several fields: statistics (multiple linear regression and principle components analysis), data mining (clustering and classification), bioinformatics (analysis of microarray data), operations research (supply chain and other optimization problems), econometrics (a nalysis of consumer demand data), and finance (asset allocation problems). Various libraries for linear algebra are freely available for anyone to use. Oracles UTLNLA package exposes matrix PLSQL data types and wrapper PLSQL subprograms for two of the most popular and robust of these libraries, BLAS and LAPACK. Linear algebra depends on matrix manipulation. Performing matrix manipulation in PLSQL in the past required inventing a matrix representation based on PLSQLs native data types and then writing matrix manipulation routines from scratch. This required substantial programming effort and the performance of the resulting implementation was limited. If developers chose to send data to external packages for processing rather than create their own routines, data transfer back and forth could be time consuming. Using the UTLNLA package lets data stay within Oracle, removes the programming effort, and delivers a fast implementation. Example 22-25 Linear Algebra Here is an example of how Oracles linear algebra support could be used for business analysis. It invokes a multiple linear regression application built using the UTLNLA package. The multiple regression application is implemented in an object called OLSRegression. Note that sample files for the OLS Regression object can be found in ORACLEHOMEplsqldemo . Consider the scenario of a retailer analyzing the effectiveness of its marketing program. Each of its stores allocates its marketing budget over the following possible programs: media advertisements ( media ), promotions ( promo ), discount coupons ( disct ), and direct mailers ( dmail ). The regression analysis builds a linear relationship between the amount of sales that an average store has in a given year ( sales ) and the spending on the four components of the marketing program. Suppose that the marketing data is stored in the following table: Then you can build the following sales-marketing linear model using coefficients: This model can be implemented as the following view, which refers to the OLS regression object: Using this view, a marketing program manager can perform an analysis such as Is this sales-marketing model reasonable for year 2004 data That is, is the multiple-correlation greater than some acceptable value, say, 0.9 The SQL for such a query might be as follows: You could also solve questions such as What is the expected base-line sales revenue of a store without any marketing programs in 2003 or Which component of the marketing program was the most effective in 2004 That is, a dollar increase in which program produced the greatest expected increase in sales See Oracle Database PLSQL Packages and Types Reference for further information regarding the use of the UTLNLA package and linear algebra. CASE Expressions Oracle now supports simple and searched CASE statements. CASE statements are similar in purpose to the DECODE statement, but they offer more flexibility and logical power. They are also easier to read than traditional DECODE statements, and offer better performance as well. They are commonly used when breaking categories into buckets like age (for example, 20-29, 30-39, and so on). The syntax for simple CASE statements is: Simple CASE expressions test if the expr value equals the comparisonexpr . The syntax for searched CASE statements is: You can use any kind of condition in a searched CASE expression, not just an equality test. You can specify only 65,535 arguments and each WHEN. THEN pair counts as two arguments. To avoid exceeding this limit, you can nest CASE expressions so that the returnexpr itself is a CASE expression. Example 22-26 CASE Suppose you wanted to find the average salary of all employees in the company. If an employees salary is less than 2000, you want the query to use 2000 instead. Without a CASE statement, you might choose to write this query as follows: Note that this runs against the hr sample schema. In this, foo is a function that returns its input if the input is greater than 2000, and returns 2000 otherwise. The query has performance implications because it needs to invoke a function for each row. Writing custom functions can also add to the development load. Using CASE expressions in the database without PLSQL, this query can be rewritten as: Using a CASE expression lets you avoid developing custom functions and can also perform faster. Example 22-27 CASE for Aggregating Independent Subsets Using CASE inside aggregate functions is a convenient way to perform aggregates on multiple subsets of data when a plain GROUP BY will not suffice. For instance, the preceding example could have included multiple AVG columns in its SELECT list, each with its own CASE expression. We might have had a query find the average salary for all employees in the salary ranges 0-2000 and 2000-5000. It would look like: Although this query places the aggregates of independent subsets data into separate columns, by adding a CASE expression to the GROUP BY clause we can display the aggregates as the rows of a single column. The next section shows the flexibility of this approach with two approaches to creating histograms with CASE . Creating Histograms You can use the CASE statement when you want to obtain histograms with user-defined buckets (both in number of buckets and width of each bucket). The following are two examples of histograms created with CASE statements. In the first example, the histogram totals are shown in multiple columns and a single row is returned. In the second example, the histogram is shown with a label column and a single column for totals, and multiple rows are returned. Example 22-28 Histogram Example 1 Example 22-29 Histogram Example 2 Frequent Itemsets Instead of counting how often a given event occurs (for example, how often someone has purchased milk at the grocery), you may find it useful to count how often multiple events occur together (for example, how often someone has purchased both milk and cereal together at the grocery store). You can count these multiple events using what is called a frequent itemset, which is, as the name implies, a set of items. Some examples of itemsets could be all of the products that a given customer purchased in a single trip to the grocery store (commonly called a market basket), the web pages that a user accessed in a single session, or the financial services that a given customer utilizes. The practical motivation for using a frequent itemset is to find those itemsets that occur most often. If you analyze a grocery stores point-of-sale data, you might, for example, discover that milk and bananas are the most commonly bought pair of items. Frequent itemsets have thus been used in business intelligence environments for many years, with the most common one being for market basket analysis in the retail industry. Frequent itemset calculations are integrated with the database, operating on top of relational tables and accessed through SQL. This integration provides the following key benefits: Applications that previously relied on frequent itemset operations now benefit from significantly improved performance as well as simpler implementation. SQL-based applications that did not previously use frequent itemsets can now be easily extended to take advantage of this functionality. Frequent itemsets analysis is performed with the PLSQL package DBMSFREQUENTITEMSETS. See Oracle Database PLSQL Packages and Types Reference for more information. In addition, there is an example of frequent itemset usage in Frequent itemsets . Scripting on this page enhances content navigation, but does not change the content in any way. SQL Correlated Subquery Summary . in this tutorial, you will learn about the SQL correlated subquery . which is a subquery that depends on the outer query. This tutorial requires a good knowledge of subquery. If you don8217t know anything about the subquery. check it out the subquery tutorial before moving forward with this tutorial. Introduction to SQL correlated subquery A correlated subquery is a subquery that depends on the outer query. It means that the WHERE clause of the correlated subquery uses the data of the outer query. The main difference between a correlated subquery and a non-correlated subquery is that you cannot execute a correlated subquery alone like a non-correlated subquery. In addition, a correlated subquery executes once for each selected row from the outer query. A correlated subquery is also known as repeating subquery or synchronized subquery. SQL correlated subquery examples Lets take a look at some examples to understand the idea of the correlated subquery. SQL correlated subquery in the SELECT clause example The following query selects top five customers by sales: