Geekly articles weekly

Mengikuti Highload ++ Siberia 2019 - 8 Tugas Oracle

Hai

Pada 24-25 Juni, konferensi Highload ++ Siberia 2019 diadakan di Novosibirsk. Orang-orang kami juga ada di laporan "Oracle Container Bases (CDB / PDB) dan Penggunaan Praktisnya untuk Pengembangan Perangkat Lunak," kami akan memposting versi teks sedikit kemudian. Itu keren, terima kasih olegbunin untuk pengorganisasian, serta semua orang yang datang.


Dalam posting ini, kami ingin berbagi dengan Anda tugas-tugas yang ada di stan kami sehingga Anda dapat menguji pengetahuan Anda di Oracle. Di bawah tugas cut-8, jawab opsi dan penjelasan.

Berapa nilai maksimum dari urutan yang akan kita lihat sebagai hasil dari skrip berikut?


create sequence s start with 1; select s.currval, s.nextval, s.currval, s.nextval, s.currval from dual connect by level <= 5;

  • 1
  • 5
  • 10
  • 25
  • Tidak, akan ada kesalahan

Jawabannya
Menurut dokumentasi Oracle (dikutip dari 8.1.6):
Dalam pernyataan SQL tunggal, Oracle akan menambah urutan hanya sekali per baris. Jika pernyataan berisi lebih dari satu referensi ke NEXTVAL untuk suatu urutan, Oracle menambah urutan satu kali dan mengembalikan nilai yang sama untuk semua kemunculan NEXTVAL. Jika pernyataan berisi referensi ke CURRVAL dan NEXTVAL, Oracle menambah urutan dan mengembalikan nilai yang sama untuk CURRVAL dan NEXTVAL terlepas dari urutan mereka dalam pernyataan.

Dengan demikian, nilai maksimum akan sesuai dengan jumlah baris, yaitu 5 .

Berapa banyak baris yang akan ada dalam tabel sebagai hasil dari skrip berikut?


 create table t(i integer check (i < 5)); create procedure p(p_from integer, p_to integer) as begin for i in p_from .. p_to loop insert into t values (i); end loop; end; / exec p(1, 3); exec p(4, 6); exec p(7, 9);

  • 0
  • 3
  • 4
  • 5
  • 6
  • 9

Jawabannya
Menurut dokumentasi Oracle (dikutip dari 11.2):

Sebelum menjalankan pernyataan SQL apa pun, Oracle menandai savepoint implisit (tidak tersedia untuk Anda). Kemudian, jika pernyataan gagal, Oracle mengembalikannya secara otomatis dan mengembalikan kode kesalahan yang berlaku ke SQLCODE di SQLCA. Misalnya, jika pernyataan INSERT menyebabkan kesalahan dengan mencoba memasukkan nilai duplikat dalam indeks unik, pernyataan tersebut dibatalkan.

Panggilan dari klien juga dianggap dan diproses sebagai satu pernyataan. Dengan demikian, panggilan pertama ke HP berhasil diselesaikan dengan memasukkan tiga catatan; panggilan kedua ke HP berakhir dengan kesalahan dan mengembalikan catatan keempat, yang berhasil saya masukkan; panggilan ketiga gagal, dan tiga entri muncul di tabel .

Berapa banyak baris yang akan ada dalam tabel sebagai hasil dari skrip berikut?


 create table t(i integer, constraint i_ch check (i < 3)); begin insert into t values (1); insert into t values (null); insert into t values (2); insert into t values (null); insert into t values (3); insert into t values (null); insert into t values (4); insert into t values (null); insert into t values (5); exception when others then dbms_output.put_line('Oops!'); end; /

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

Jawabannya
Menurut dokumentasi Oracle (dikutip dari 11.2):

Batasan pemeriksaan memungkinkan Anda menentukan kondisi yang harus dipenuhi setiap baris dalam tabel. Untuk memenuhi batasan, setiap baris dalam tabel harus membuat kondisi BENAR atau tidak diketahui (karena nol). Ketika Oracle mengevaluasi kondisi kendala pemeriksaan untuk baris tertentu, nama kolom apa pun dalam kondisi tersebut merujuk ke nilai kolom di baris itu.

Dengan demikian, nilai nol akan lulus ujian, dan blok anonim akan berhasil dieksekusi sampai upaya untuk memasukkan nilai 3. Setelah ini, blok pemrosesan kesalahan akan membuang pengecualian, kemunduran tidak akan terjadi dan tabel akan memiliki empat baris dengan nilai 1, null, 2 dan nol lagi.

Pasangan nilai apa yang akan menempati jumlah ruang yang sama di blok?


 create table t ( a char(1 char), b char(10 char), c char(100 char), i number(4), j number(14), k number(24), x varchar2(1 char), y varchar2(10 char), z varchar2(100 char)); insert into t (a, b, i, j, x, y) values ('Y', '', 10, 10, '', '');

  • A dan X
  • B dan Y
  • C dan K
  • C dan Z
  • K dan Z
  • I dan J
  • J dan X
  • Semua terdaftar

Jawabannya
Berikut adalah kutipan dari dokumentasi (12.1.0.2) untuk menyimpan berbagai tipe data di Oracle.

Tipe data char
Tipe data CHAR menentukan string karakter tetap-panjang dalam set karakter basis data. Anda menentukan karakter database yang ditetapkan ketika Anda membuat database Anda. Oracle memastikan bahwa semua nilai yang disimpan dalam kolom CHAR memiliki panjang yang ditentukan oleh ukuran dalam semantik panjang yang dipilih. Jika Anda memasukkan nilai yang lebih pendek dari panjang kolom, maka Oracle mengosongkan nilainya dengan panjang kolom.

Tipe Data VARCHAR2
Tipe data VARCHAR2 menentukan string karakter-panjang variabel dalam set karakter basis data. Anda menentukan karakter database yang ditetapkan ketika Anda membuat database Anda. Oracle menyimpan nilai karakter dalam kolom VARCHAR2 persis seperti yang Anda tentukan, tanpa bantalan kosong, asalkan nilainya tidak melebihi panjang kolom.

NUMBER Jenis Data
Tipe data NUMBER menyimpan nol serta bilangan tetap positif dan negatif dengan nilai absolut dari 1,0 x 10-130 hingga tetapi tidak termasuk 1,0 x 10126. Jika Anda menentukan ekspresi aritmatika yang nilainya memiliki nilai absolut lebih besar dari atau sama dengan 1,0 x 10126, lalu Oracle mengembalikan kesalahan. Setiap nilai NUMBER membutuhkan 1 hingga 22 byte. Dengan mempertimbangkan hal ini, ukuran kolom dalam byte untuk nilai data numerik tertentu NUMBER (p), di mana p adalah presisi dari nilai yang diberikan, dapat dihitung menggunakan rumus berikut: ROUND ((panjang (p) + s) / 2)) +1 di mana s sama dengan nol jika angka positif, dan s sama dengan 1 jika angka negatif.

Selain itu, kami mengambil kutipan dari dokumentasi tentang menyimpan nilai Null.

Nol adalah tidak adanya nilai dalam kolom. Nulls mengindikasikan data yang hilang, tidak diketahui, atau tidak dapat diterapkan. Nulls disimpan dalam database jika berada di antara kolom dengan nilai data. Dalam kasus ini, mereka membutuhkan 1 byte untuk menyimpan panjang kolom (nol). Mengejar nol dalam satu baris tidak memerlukan penyimpanan karena header baris baru memberi sinyal bahwa kolom yang tersisa di baris sebelumnya adalah nol. Misalnya, jika tiga kolom terakhir dari sebuah tabel adalah nol, maka tidak ada data yang disimpan untuk kolom ini.

Berdasarkan data ini, kami membangun alasan. Kami percaya bahwa basis data menggunakan pengodean AL32UTF8. Dalam penyandian ini, huruf Rusia akan menempati 2 byte.

1) A dan X, nilai bidang a 'Y' adalah 1 byte, nilai bidang x 'D' adalah 2 byte
2) B dan Y, 'Vasya' dalam nilai b akan ditambah dengan spasi hingga 10 karakter dan akan menempati 14 byte, 'Vasya' dalam d - akan mengambil 8 byte.
3) C dan K. Kedua bidang tersebut NULL, setelah itu ada bidang yang signifikan, sehingga menempati 1 byte.
4) C dan Z. Kedua bidang NULL, tetapi bidang Z adalah yang terakhir dalam tabel, sehingga tidak memakan ruang (0 byte). Field C membutuhkan 1 byte.
5) K dan Z. Mirip dengan kasus sebelumnya. Nilai dalam bidang K adalah 1 byte, dalam Z - 0.
6) I dan J. Menurut dokumentasi, kedua nilai masing-masing akan mengambil 2 byte. Kami mempertimbangkan panjang sesuai dengan rumus yang diambil dari dokumentasi: babak ((1 + 0) / 2) +1 = 1 + 1 = 2.
7) J dan X. Nilai di bidang J akan mengambil 2 byte, nilai di bidang X akan mengambil 2 byte.

Secara total, opsi yang benar adalah: C dan K, I dan J, J dan X.


Apa kira-kira faktor pengelompokan indeks T_I?


 create table t (i integer); insert into t select rownum from dual connect by level <= 10000; create index t_i on t(i);

  • Sekitar lusinan
  • Sekitar ratusan
  • Dari urutan ribuan
  • Dari urutan puluhan ribu

Jawabannya
Menurut dokumentasi Oracle (dikutip dari 12.1):

Untuk indeks B-tree, faktor pengelompokan indeks mengukur pengelompokan fisik baris dalam kaitannya dengan nilai indeks.

Faktor pengelompokan indeks membantu pengoptimal memutuskan apakah pemindaian indeks atau pemindaian tabel penuh lebih efisien untuk kueri tertentu). Faktor pengelompokan rendah menunjukkan pemindaian indeks yang efisien.

Faktor pengelompokan yang dekat dengan jumlah blok dalam tabel menunjukkan bahwa baris secara fisik dipesan di blok tabel oleh kunci indeks. Jika database melakukan pemindaian tabel penuh, maka database cenderung untuk mengambil baris ketika disimpan pada disk yang diurutkan berdasarkan kunci indeks. Faktor pengelompokan yang dekat dengan jumlah baris menunjukkan bahwa baris tersebar secara acak di seluruh blok basis data terkait dengan kunci indeks. Jika database melakukan pemindaian tabel penuh, maka database tidak akan mengambil baris dalam urutan apa pun dengan kunci indeks ini.

Dalam kasus ini, data diurutkan secara ideal, sehingga faktor pengelompokan akan sama dengan atau dekat dengan jumlah blok yang ditempati dalam tabel. Untuk ukuran blok standar 8 kilobyte, Anda dapat berharap bahwa sekitar seribu nilai angka sempit akan cocok dalam satu blok, sehingga jumlah blok, dan sebagai hasilnya faktor pengelompokan, akan berada di urutan puluhan .

Pada nilai N apa skrip berikut akan berhasil dieksekusi dalam database reguler dengan pengaturan standar?


 create table t ( a varchar2(N char), b varchar2(N char), c varchar2(N char), d varchar2(N char)); create index t_i on t (a, b, c, d);

  • 100
  • 200
  • 400
  • 800
  • 1600
  • 3200
  • 6400

Jawabannya
Menurut dokumentasi Oracle (dikutip dari 11.2):

Batas basis data logis

BarangJenis batasNilai batas
IndeksUkuran total kolom yang diindeks75% dari ukuran blok basis data dikurangi beberapa overhead

Dengan demikian, ukuran total kolom yang diindeks tidak boleh melebihi 6Kb. Lebih lanjut tergantung pada basis penyandian yang dipilih. Untuk pengodean AL32UTF8, satu karakter dapat menempati maksimum 4 byte, jadi dalam skenario terburuk, 6 kilobyte akan muat sekitar 1.500 karakter. Oleh karena itu, Oracle akan melarang pembuatan indeks pada N = 400 (ketika panjang kunci dalam kasus terburuk adalah 1600 karakter * 4 byte + panjang baris), sedangkan pada N = 200 (atau kurang) membuat indeks akan bekerja tanpa masalah.

Pernyataan INSERT dengan petunjuk LAMPIRAN dirancang untuk memuat data dalam mode langsung. Apa yang terjadi jika diterapkan pada tabel tempat pemicunya hang?


  • Data akan dimuat dalam mode langsung, pemicu akan berfungsi sebagaimana mestinya
  • Data akan dimuat dalam mode langsung, tetapi pemicunya tidak akan dieksekusi
  • Data akan dimuat dalam mode konvensional, pemicunya akan berfungsi sebagaimana mestinya
  • Data akan dimuat dalam mode konvensional, tetapi pemicunya tidak akan dieksekusi
  • Data tidak akan diunggah, kesalahan akan diperbaiki

Jawabannya
Pada prinsipnya, ini lebih merupakan masalah logika. Untuk menemukan jawaban yang tepat, saya akan menyarankan model penalaran berikut:

  1. Penyisipan dalam mode langsung dilakukan dengan pembentukan langsung blok data, melewati mesin SQL, yang memastikan kecepatan tinggi. Jadi, untuk memastikan eksekusi pelatuk sangat sulit, jika memungkinkan, dan tidak ada gunanya, karena hal itu akan secara dramatis memperlambat penyisipan.
  2. Kegagalan untuk memicu akan mengarah pada fakta bahwa, dengan data yang sama dalam tabel, keadaan basis data secara keseluruhan (dari tabel lain) akan tergantung pada mode mana data dimasukkan. Ini jelas akan merusak integritas data dan tidak dapat diterapkan sebagai solusi dalam produksi.
  3. Ketidakmampuan untuk melakukan operasi yang diminta, secara umum, diperlakukan sebagai kesalahan. Tetapi di sini harus diingat bahwa APPEND adalah petunjuk, dan logika umum petunjuk adalah bahwa mereka diperhitungkan jika mungkin, tetapi jika tidak, operator dieksekusi tanpa mempertimbangkan petunjuk tersebut.

Dengan demikian, jawaban yang diharapkan adalah bahwa data akan dimuat dalam mode normal (SQL), pemicunya akan diaktifkan.

Menurut dokumentasi Oracle (dikutip dari 8.04):

Pelanggaran pembatasan akan menyebabkan pernyataan dieksekusi secara serial, menggunakan jalur insert konvensional, tanpa peringatan atau pesan kesalahan. Pengecualian adalah pembatasan pada pernyataan yang mengakses tabel yang sama lebih dari sekali dalam suatu transaksi, yang dapat menyebabkan pesan kesalahan.
Misalnya, jika pemicu atau integritas referensial ada di atas meja, maka petunjuk APPEND akan diabaikan ketika Anda mencoba menggunakan INSERT muatan langsung (serial atau paralel), serta petunjuk atau klausa PARALLEL, jika ada.

Apa yang terjadi ketika menjalankan skrip berikut?


 create table t(i integer not null primary key, j integer references t); create trigger t_a_i after insert on t for each row declare pragma autonomous_transaction; begin insert into t values (:new.i + 1, :new.i); commit; end; / insert into t values (1, null);

  • Eksekusi yang sukses
  • Kesalahan sintaksis gagal
  • Kesalahan transaksi offline tidak valid
  • Galat terkait dengan melebihi nesting panggilan maksimum
  • Kesalahan Pelanggaran Kunci Asing
  • Kunci Kesalahan

Jawabannya
Tabel dan pemicu dibuat dengan benar dan operasi ini seharusnya tidak menimbulkan masalah. Transaksi otonom dalam pelatuk juga diperbolehkan, jika tidak, tidak mungkin, misalnya, logging.

Setelah memasukkan baris pertama, pemicu yang berhasil akan mengarah pada penyisipan dari baris kedua, sehubungan dengan mana pemicu akan bekerja lagi, masukkan baris ketiga, dan seterusnya sampai pernyataan jatuh karena melebihi bersarang maksimum panggilan. Namun, titik halus lain dipicu. Pada saat pemicu dieksekusi, komit belum dieksekusi untuk catatan yang dimasukkan pertama. Oleh karena itu, pemicu yang bekerja dalam transaksi otonom mencoba menyisipkan baris dalam tabel yang merujuk oleh kunci asing ke catatan yang belum dilakukan. Ini mengarah ke menunggu (transaksi otonom sedang menunggu komit utama untuk memahami apakah mungkin untuk memasukkan data) dan pada saat yang sama transaksi utama sedang menunggu komit otonom untuk terus bekerja setelah pemicu. Kebuntuan terjadi dan, sebagai akibatnya, transaksi otonom dipecat karena alasan yang terkait dengan kunci .

Source: https://habr.com/ru/post/id459680/

More articles:


All Articles