Panduan Migrasi Oracle ke SQL Server dan SQL Server ke Oracle - Pt. 2

Bagian pertama dari seri ini membahas perbedaan antara Oracle Database dan Microsoft SQL Server dalam implementasi transaksi mereka, dengan fokus pada perangkap yang mungkin ditemui selama migrasi Oracle ke SQL Server dan sebaliknya. Angsuran berikutnya ini akan mencakup sejumlah elemen sintaks SQL yang umum digunakan yang tidak memiliki kecocokan atau memiliki arti atau penggunaan yang sama sekali berbeda di seluruh pembagian Oracle–SQL Server.

Urutan di Oracle dan Kolom Identitas di SQL Server

Ada kesenjangan lama dalam komunitas basis data antara dua kubu: patriot kunci alami dan pendukung kunci buatan (atau "pengganti").

Saya sendiri mempertahankan kunci alami tetapi sering menemukan diri saya membuat pengganti karena satu dan lain alasan. Tapi mengesampingkan substansi perdebatan ini, mari kita lihat mekanisme standar untuk menghasilkan kunci buatan: urutan Oracle dan kolom identitas SQL Server.

Urutan Oracle adalah objek tingkat database kelas satu. Sebaliknya, kolom identitas SQL Server adalah tipe kolom, bukan objek.

Ketika urutan Oracle digunakan untuk menghasilkan kunci tabel—biasanya kunci utama—itu dijamin akan bertambah, dan karenanya menjadi unik. Tapi itu tidak dijamin berturut-turut. Faktanya, bahkan dalam implementasi yang dirancang dengan baik, kemungkinan besar memiliki beberapa celah. Jadi tidak ada implementasi Oracle yang harus bergantung pada nilai yang dihasilkan urutan secara berurutan.

Juga, urutan dikelola melalui kamus data database Oracle, sehingga akan terlalu memakan sumber daya (dan rumit) untuk membuat urutan khusus untuk mendukung setiap kunci pengganti. Sebuah objek urutan tunggal dapat mendukung beberapa atau bahkan semua kunci pengganti.

Di sisi lain, ketika beberapa proses perlu mengakses NEXTVAL (nilai tambahan berikutnya) dari suatu urutan, urutan tersebut akan menjadi sumber daya akses tunggal yang kritis. Ini akan secara efektif membuat semua proses mengaksesnya secara berurutan, mengubah implementasi multi-utas (tunggal atau multi-server) menjadi proses berutas tunggal, dengan waktu tunggu yang lama dan penggunaan memori / CPU yang tinggi.

Implementasi seperti itu benar-benar terjadi. Solusi untuk masalah ini adalah mendefinisikan objek urutan yang dimaksud dengan nilai cache yang wajar—artinya rentang nilai yang ditentukan (baik itu 100 atau 100 ribu) dipilih ke dalam cache untuk proses panggilan, dicatat dalam kamus data seperti yang digunakan , dan tersedia untuk proses khusus ini tanpa perlu mengakses kamus data setiap kali NEXTVAL dipanggil.

Tapi inilah tepatnya mengapa celah akan dibuat karena tidak semua nilai yang di-cache kemungkinan akan digunakan. Ini juga berarti bahwa di beberapa proses dalam sesi paralel, beberapa nilai urutan yang direkam dapat dibalik secara kronologis. Pembalikan ini tidak dapat terjadi dalam satu proses kecuali nilai urutan disetel ulang atau digeser mundur. Tapi skenario terakhir ini sama dengan mencari masalah: Seharusnya tidak perlu, dan jika diterapkan secara tidak benar, itu dapat menghasilkan nilai duplikat.

Jadi, satu-satunya cara yang benar untuk menggunakan urutan Oracle adalah untuk menghasilkan kunci pengganti: kunci yang unik tetapi tidak dianggap menyimpan informasi lain yang dapat digunakan dengan andal.

Kolom Identitas di SQL Server

Bagaimana dengan SQL Server? Sementara urutan dengan fungsionalitas dan implementasi yang sangat mirip dengan rekan Oracle mereka telah diperkenalkan di SQL Server 2012, mereka bukan teknik tingkat pertama. Seperti penambahan fitur lainnya, mereka masuk akal untuk konversi dari Oracle, tetapi ketika menerapkan kunci pengganti dari awal di SQL Server, IDENTITY adalah pilihan yang jauh lebih baik.

IDENTITY adalah objek "anak" dari sebuah tabel. Itu tidak mengakses sumber daya di luar tabel dan dijamin berurutan kecuali sengaja dimanipulasi. Dan itu dirancang khusus untuk tugas ini, bukan untuk kompatibilitas semantik dengan Oracle.

Dengan Oracle yang telah mengimplementasikan fungsionalitas IDENTITY di versi 12.1, wajar untuk bertanya-tanya bagaimana hal itu terjadi sebelumnya, mengapa mengimplementasikannya sekarang, dan mengapa SQL Server membutuhkannya sejak awal (dari asal Sybase SQL Server).

Alasannya adalah Oracle selalu memiliki fitur kunci identitas: pseudocolumn ROWID , memiliki tipe data ROWID atau UROWID . Nilai ini non-numerik ( ROWID dan UROWID adalah tipe data Oracle berpemilik) dan secara unik mengidentifikasi catatan data.

Tidak seperti IDENTITY SQL Server, ROWID Oracle tidak dapat dengan mudah dimanipulasi (dapat ditanyakan, tetapi tidak dimasukkan atau dimodifikasi), dan dibuat di latar belakang untuk setiap baris di setiap tabel Oracle. Selain itu, cara paling efisien untuk mengakses baris data apa pun dalam database Oracle adalah dengan ROWID -nya, jadi ini digunakan sebagai teknik pengoptimalan kinerja. Terakhir, ini mendefinisikan urutan pengurutan output kueri default, karena ini secara efektif mengindeks penyimpanan data baris tingkat rendah.

Jika ROWID Oracle sangat penting, bagaimana SQL Server bertahan selama bertahun-tahun dan dirilis tanpanya? Dengan menggunakan kolom IDENTITY sebagai kunci utama (pengganti).

Penting untuk dicatat perbedaan dalam implementasi struktur indeks antara Oracle dan SQL Server.

Di SQL Server, indeks pertama—kunci utama, lebih sering daripada tidak—dikelompokkan; ini berarti bahwa paling umum, data dalam file data primer diurutkan oleh kunci ini. Di sisi Oracle, setara dengan indeks berkerumun adalah tabel indeks-terorganisir. Ini adalah konstruksi opsional di Oracle yang digunakan secara sporadis, hanya jika diperlukan—untuk tabel pencarian hanya-baca, misalnya.

Semua pola desain di Oracle yang didasarkan pada penggunaan ROWID (seperti deduplikasi data) harus diimplementasikan berdasarkan kolom IDENTITY saat bermigrasi ke SQL Server.

Meskipun migrasi dari menggunakan IDENTITY di SQL Server ke menggunakan IDENTITY di Oracle dapat menghasilkan kode yang benar secara fungsional, itu tidak optimal, karena di sisi Oracle, ROWID akan bekerja jauh lebih efisien.

Hal yang sama berlaku ketika melakukan konversi sintaks SQL sederhana untuk memindahkan urutan Oracle ke SQL Server: Kode akan berjalan, tetapi menggunakan IDENTITY adalah opsi yang paling disukai dalam hal kesederhanaan dan kinerja kode.

Indeks yang Difilter di Microsoft SQL Server

Bertahun-tahun yang lalu, Microsoft SQL Server 2008 memperkenalkan sejumlah fitur penting yang mengubahnya menjadi database perusahaan kelas satu. Salah satu yang telah menyelamatkan hari saya lebih dari sekali telah disaring indeks.

Indeks yang difilter adalah indeks non-cluster (yaitu, yang ada sebagai file datanya sendiri) yang memiliki klausa WHERE . Ini berarti bahwa file indeks hanya berisi catatan data yang relevan dengan klausa. Untuk memanfaatkan sepenuhnya indeks yang difilter, indeks tersebut juga harus memiliki klausa INCLUDE yang mencantumkan semua kolom yang diperlukan saat mengembalikan kumpulan data. Saat kueri Anda dioptimalkan untuk menggunakan indeks terfilter khusus yang mencakup semua titik data yang diperlukan, mesin database hanya perlu mengakses file indeks (kecil) bahkan tanpa melihat file data tabel utama.

Ini sangat berharga bagi saya beberapa tahun yang lalu ketika bekerja dengan tabel berukuran terabyte. Klien yang bersangkutan sering kali hanya perlu mengakses sebagian kecil dari persen catatan yang aktif pada waktu tertentu. Implementasi awal dari akses ini (dipicu oleh tindakan UI pengguna akhir) tidak hanya sangat lambat—tetapi juga tidak dapat digunakan. Ketika saya menambahkan indeks yang difilter dengan INCLUDE s yang diperlukan, itu menjadi pencarian sub-milidetik. Waktu yang saya habiskan untuk tugas pengoptimalan ini hanya satu jam.

Tentu, indeks yang difilter memiliki beberapa batasan. Mereka tidak dapat menyertakan kolom LOB, ada batasan pada kondisi apa klausa WHERE yang dapat disertakan oleh indeks itu sendiri, dan mereka menambah jejak penyimpanan database. Tetapi jika kasus penggunaan sesuai dengan parameter ini, pengorbanan penyimpanan biasanya cukup kecil dibandingkan dengan peningkatan kinerja signifikan yang dapat diberikan oleh indeks yang difilter.

Bagaimana Dengan Indeks yang Difilter di Oracle Database?

Kemudian saya menemukan diri saya dalam tim besar di perusahaan Fortune 500 sebagai pengembang/DBA pada proyek migrasi SQL Server-ke-Oracle. Kode di sekitar basis data sumber—SQL Server 2008—diimplementasikan dengan buruk, dengan kinerja yang sedikit yang membuat konversi menjadi keharusan: Pekerjaan sinkronisasi back-end harian berjalan lebih dari 23 jam. Itu tidak memiliki indeks yang difilter, tetapi dalam sistem baru—Oracle 11g—, saya melihat banyak kasus di mana indeks yang difilter akan sangat bermanfaat. Tetapi Oracle 11g tidak memiliki indeks yang difilter!

Indeks yang difilter juga tidak diterapkan di Oracle 18c terbaru.

Tetapi tugas kita sebagai profesional teknis adalah memanfaatkan sebaik-baiknya apa yang kita miliki. Jadi saya menerapkan indeks yang setara dengan indeks yang difilter di sistem Oracle 11g saya (dan teknik yang sama yang saya gunakan nanti di 12c). Idenya didasarkan pada bagaimana Oracle menangani NULL s, dan dapat digunakan dalam versi Oracle apa pun.

Oracle tidak memperlakukan nilai NULL dengan cara yang sama seperti data biasa. NULL di Oracle bukanlah apa-apa—itu tidak ada. Akibatnya, jika Anda mendefinisikan kolom terindeks sebagai NULLABLE dan Anda mencari berdasarkan nilai non- NULL , file data indeks Anda hanya akan berisi catatan yang diinginkan. Karena definisi indeks Oracle tidak memiliki klausa INCLUDE , Anda harus membuat indeks komposit dengan semua kolom yang perlu disertakan dalam hasil. (Teknik ini memiliki beberapa overhead dibandingkan dengan klausa INCLUDE SQL Server, tetapi cukup tidak signifikan.)

Implementasi solusi seperti itu memang menambah batasan: Kolom indeks utama harus mengizinkan NULL s dan, oleh karena itu, tidak bisa menjadi kunci utama tabel. Namun, itu bisa berupa kolom turunan atau kalkulasi yang dibuat khusus untuk mendukung metode pengoptimalan kinerja ini. Dalam beberapa hal, kolom utama indeks adalah biner logis: nilai non- NULL untuk data yang disertakan dalam pencarian Anda, dan NULL untuk data apa pun yang seharusnya "tidak terlihat".

Opsi lain yang mungkin dalam memigrasi logika indeks terfilter SQL Server ke Oracle adalah dengan mengimplementasikan indeks (atau tabel secara penuh) sebagai dipartisi. Dalam kasus ini, hanya partisi indeks yang relevan yang akan diakses oleh mesin database—kueri yang disediakan diimplementasikan dengan benar dengan menggunakan kondisi partisi yang tepat dalam klausa WHERE mereka.

Ini akan bekerja dengan baik, bahkan dalam skala besar, pada data yang relatif statis, tetapi dapat menempatkan beban pemeliharaan yang tinggi pada tim DBA jika diterapkan pada data yang sering berubah. Contohnya adalah ketika mengoptimalkan akses ke data hari ini dalam aplikasi yang berpusat pada waktu: Tim DBA perlu mendefinisikan ulang partisi setiap hari. Meskipun redefinisi ini dapat ditulis dalam pekerjaan pemeliharaan malam, hal itu membuat sistem Anda lebih kompleks dan memperkenalkan titik kegagalan sistemik potensial baru.

Jadi, seseorang harus sangat spesifik dan berhati-hati setiap kali logika indeks yang difilter SQL Server perlu dimigrasikan ke Oracle.

Cara Menangani Konversi

Dengan migrasi Oracle ke SQL Server, cari peluang untuk pengoptimalan menggunakan indeks yang difilter. Anda tidak akan melihat indeks yang difilter di Oracle, tetapi Anda mungkin melihat indeks yang menyertakan nilai NULL . Jangan menyalinnya apa adanya: Ini mungkin tempat terbaik di mana Anda bisa mendapatkan peningkatan kinerja dan peningkatan desain dalam konversi Anda.

Untuk migrasi SQL Server ke Oracle, jika Anda melihat indeks yang difilter, cari cara menghindari kemacetan kinerja dalam kode Oracle yang sesuai. Lihat bagaimana Anda dapat mendesain ulang aliran data untuk mengimbangi peningkatan performa yang hilang yang diberikan oleh indeks yang difilter dalam implementasi sumber.

SQL Server ke Oracle / Oracle ke SQL Server Migrasi Tantangan Demystified

Untuk proyek migrasi antara Oracle dan SQL Server di kedua arah, penting untuk memiliki pemahaman yang lebih dalam tentang mekanisme yang terlibat. Saat rilis terkini dari masing-masing database (Oracle 18c dan Microsoft SQL Server 2017*) berisi ekuivalen leksikal dari fungsi masing-masing—misalnya, dalam urutan dan identitas—mungkin tampak seperti kemenangan yang mudah. Tetapi menyalin desain yang baik pada satu RDBMS langsung ke yang lain dapat menghasilkan kode yang tidak perlu rumit dan berkinerja buruk.

Di bagian berikutnya dan terakhir dari seri ini, saya membahas konsistensi membaca dan penggunaan alat migrasi. Pantau terus!

* SQL Server 2019 (atau "15.x") belum keluar cukup lama untuk adopsi perusahaan secara luas.