ProHoster > blog > administrasi > Mengikuti jejak Highload++ Siberia 2019 - 8 tugas di Oracle

Mengikuti jejak Highload++ Siberia 2019 - 8 tugas di Oracle

Hi!

Pada tanggal 24-25 Juni, konferensi Highload++ Siberia 2019 diadakan di Novosibirsk. Teman-teman kami juga hadir di sana laporan “Database container Oracle (CDB/PDB) dan penggunaan praktisnya untuk pengembangan perangkat lunak”, kami akan menerbitkan versi teksnya nanti. Itu keren, terima kasih olegbunin untuk organisasi, serta untuk semua orang yang datang.

Mengikuti jejak Highload++ Siberia 2019 - 8 tugas di Oracle
Dalam postingan ini, kami ingin berbagi dengan Anda masalah yang kami alami di booth kami sehingga Anda dapat menguji pengetahuan Oracle Anda. Di bawah potongan terdapat 8 soal, pilihan jawaban dan penjelasan.

Berapa nilai urutan maksimum yang akan kita lihat sebagai hasil dari menjalankan 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

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

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

Berapa banyak baris yang ada dalam tabel sebagai hasil dari menjalankan 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

MenjawabMenurut dokumentasi Oracle (dikutip dari 11.2):

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

Panggilan HP dari klien juga dianggap dan diproses sebagai satu pernyataan. Dengan demikian, panggilan HP pertama berhasil diselesaikan, setelah memasukkan tiga catatan; panggilan HP kedua berakhir dengan kesalahan dan mengembalikan catatan keempat yang berhasil dimasukkan; panggilan ketiga gagal, dan ada tiga catatan dalam tabel.

Berapa banyak baris yang ada dalam tabel sebagai hasil dari menjalankan 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

MenjawabMenurut dokumentasi Oracle (dikutip dari 11.2):

Batasan pemeriksaan memungkinkan Anda menentukan kondisi yang harus dipenuhi oleh setiap baris dalam tabel. Untuk memenuhi batasan tersebut, setiap baris dalam tabel harus membuat kondisinya menjadi TRUE atau tidak diketahui (karena nol). Saat Oracle mengevaluasi kondisi batasan pemeriksaan untuk baris tertentu, nama kolom apa pun dalam kondisi tersebut mengacu pada nilai kolom di baris tersebut.

Dengan demikian, nilai null akan lolos pemeriksaan, dan blok anonim akan berhasil dieksekusi hingga ada upaya untuk memasukkan nilai 3. Setelah ini, blok penanganan kesalahan akan menghapus pengecualian, tidak ada rollback yang akan terjadi, dan akan ada empat baris tersisa di tabel dengan nilai 1, null, 2 dan null lagi.

Pasangan nilai manakah yang akan menempati jumlah ruang yang sama dalam 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
  • aku dan J
  • J dan X
  • Semua terdaftar

MenjawabBerikut kutipan dokumentasi (12.1.0.2) tentang penyimpanan berbagai jenis data di Oracle.

Tipe Data CHAR
Tipe data CHAR menentukan string karakter dengan panjang tetap dalam kumpulan karakter database. Anda menentukan kumpulan karakter basis data saat Anda membuat basis data. 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, Oracle akan mengosongkan nilai tersebut ke panjang kolom.

Tipe Data VARCHAR2
Tipe data VARCHAR2 menentukan string karakter dengan panjang variabel dalam kumpulan karakter database. Anda menentukan kumpulan karakter basis data saat Anda membuat basis data. Oracle menyimpan nilai karakter dalam kolom VARCHAR2 persis seperti yang Anda tentukan, tanpa padding kosong apa pun, asalkan nilainya tidak melebihi panjang kolom.

Tipe Data NOMOR
Tipe data NUMBER menyimpan bilangan tetap nol serta 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 atau sama dengan 1.0 x 10126, maka Oracle mengembalikan kesalahan. Setiap nilai NUMBER memerlukan 1 hingga 22 byte. Dengan mempertimbangkan hal ini, ukuran kolom dalam byte untuk nilai data numerik tertentu NUMBER(p), dengan p adalah presisi dari nilai tertentu, dapat dihitung menggunakan rumus berikut: BULAT((panjang(p)+s)/2))+1 dimana s sama dengan nol jika bilangan tersebut positif, dan s sama dengan 1 jika bilangan tersebut negatif.

Selain itu, mari kita ambil kutipan dari dokumentasi tentang penyimpanan nilai Null.

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

Berdasarkan data ini, kami membangun penalaran. Kami berasumsi bahwa database menggunakan pengkodean AL32UTF8. Dalam pengkodean ini, huruf Rusia akan menempati 2 byte.

1) A dan X, nilai field a 'Y' membutuhkan 1 byte, nilai field x 'D' membutuhkan 2 byte
2) B dan Y, 'Vasya' di b nilainya akan diisi dengan spasi hingga 10 karakter dan akan memakan waktu 14 byte, 'Vasya' di d akan memakan waktu 8 byte.
3) C dan K. Kedua field tersebut bernilai NULL, setelahnya ada field yang signifikan, sehingga menempati 1 byte.
4) C dan Z. Kedua field tersebut bernilai NULL, namun field Z adalah yang terakhir dalam tabel, sehingga tidak memakan tempat (0 byte). Bidang C menempati 1 byte.
5) K dan Z. Mirip dengan kasus sebelumnya. Nilai di bidang K menempati 1 byte, di Z – 0.
6) I dan J. Menurut dokumentasi, kedua nilai tersebut akan memakan waktu 2 byte. Kita menghitung panjangnya menggunakan rumus yang diambil dari dokumentasi: bulat( (1 + 0)/2) +1 = 1 + 1 = 2.
7) J dan X. Nilai pada kolom J akan memakan waktu 2 byte, nilai pada kolom X akan memakan waktu 2 byte.

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

Kira-kira berapa 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 puluhan
  • Sekitar ratusan
  • Sekitar ribuan
  • Sekitar puluhan ribu

MenjawabMenurut 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 lengkap lebih efisien untuk kueri tertentu). Faktor pengelompokan yang rendah menunjukkan pemindaian indeks yang efisien.

Faktor pengelompokan yang mendekati jumlah blok dalam tabel menunjukkan bahwa baris-baris tersebut diurutkan secara fisik dalam blok tabel berdasarkan kunci indeks. Jika database melakukan pemindaian tabel penuh, maka database cenderung mengambil baris saat disimpan di disk yang diurutkan berdasarkan kunci indeks. Faktor pengelompokan yang mendekati jumlah baris menunjukkan bahwa baris-baris tersebut tersebar secara acak di seluruh blok database sehubungan dengan kunci indeks. Jika database melakukan pemindaian tabel penuh, maka database tidak akan mengambil baris dalam urutan apa pun berdasarkan kunci indeks ini.

Dalam hal ini, data diurutkan secara ideal, sehingga faktor pengelompokannya akan sama atau mendekati jumlah blok yang ditempati dalam tabel. Untuk ukuran blok standar 8 kilobyte, Anda dapat mengharapkan sekitar seribu nilai angka sempit akan masuk ke dalam satu blok, sehingga jumlah blok, dan sebagai hasilnya, faktor pengelompokan akan menjadi sekitar puluhan.

Pada nilai N berapa skrip berikut akan berhasil dieksekusi dalam database biasa 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

MenjawabMenurut dokumentasi Oracle (dikutip dari 11.2):

Batas Basis Data Logis

Barang
Jenis Batasan
Nilai Batas

Indeks
Ukuran total kolom yang diindeks
75% dari ukuran blok database dikurangi beberapa overhead

Dengan demikian, ukuran total kolom yang diindeks tidak boleh melebihi 6Kb. Apa yang terjadi selanjutnya bergantung pada pengkodean dasar yang dipilih. Untuk pengkodean AL32UTF8, satu karakter dapat menempati maksimal 4 byte, jadi dalam skenario terburuk, sekitar 6 karakter akan muat dalam 1500 kilobyte. Oleh karena itu, Oracle akan melarang pembuatan indeks pada N = 400 (ketika panjang kunci kasus terburuk adalah 1600 karakter * 4 byte + panjang baris), sedangkan pada N = 200 (atau kurang) membuat indeks akan berfungsi tanpa masalah.

Operator INSERT dengan petunjuk APPEND dirancang untuk memuat data dalam mode langsung. Apa yang terjadi jika diterapkan pada tabel tempat pemicunya digantung?

  • Data akan dimuat dalam mode langsung, pemicunya akan berfungsi seperti yang diharapkan
  • Data akan dimuat dalam mode langsung, tetapi pemicunya tidak akan dijalankan
  • Data akan dimuat dalam mode normal, pemicunya akan berfungsi sebagaimana mestinya
  • Data akan dimuat dalam mode konvensional, tetapi pemicunya tidak akan dijalankan
  • Data tidak akan dimuat, kesalahan akan dicatat

MenjawabPada dasarnya, ini lebih merupakan pertanyaan logika. Untuk menemukan jawaban yang benar, saya menyarankan model penalaran berikut:

  1. Penyisipan dalam mode langsung dilakukan dengan pembentukan blok data secara langsung, melewati mesin SQL, yang menjamin kecepatan tinggi. Oleh karena itu, memastikan eksekusi pemicu sangatlah sulit, bahkan tidak mungkin, dan tidak ada gunanya melakukan hal ini, karena hal ini masih akan memperlambat penyisipan secara radikal.
  2. Kegagalan menjalankan pemicu akan menyebabkan fakta bahwa, jika data dalam tabel sama, keadaan database secara keseluruhan (tabel lain) akan bergantung pada mode di mana data ini dimasukkan. Hal ini jelas akan merusak integritas data dan tidak dapat diterapkan sebagai solusi dalam produksi.
  3. Ketidakmampuan untuk melakukan operasi yang diminta umumnya dianggap sebagai kesalahan. Namun di sini kita harus ingat bahwa APPEND adalah sebuah petunjuk, dan logika umum dari petunjuk tersebut adalah bahwa petunjuk tersebut diperhitungkan jika memungkinkan, tetapi jika tidak, operator dieksekusi tanpa memperhitungkan petunjuk tersebut.

Jadi jawaban yang diharapkan adalah data akan dimuat dalam mode normal (SQL), pemicu akan diaktifkan.

Menurut dokumentasi Oracle (dikutip dari 8.04):

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

Apa yang akan terjadi jika skrip berikut dijalankan?

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 berhasil
  • Kegagalan karena kesalahan sintaksis
  • Kesalahan: Transaksi Otonom Tidak Sah
  • Kesalahan terkait dengan melebihi kumpulan panggilan maksimum
  • Kesalahan Pelanggaran Kunci Asing
  • Kesalahan terkait dengan kunci

MenjawabTabel dan pemicu dibuat dengan cukup benar dan operasi ini tidak akan menimbulkan masalah. Transaksi otonom dalam pemicu juga diperbolehkan, jika tidak, pencatatan log tidak akan dapat dilakukan, misalnya.

Setelah menyisipkan baris pertama, pemicu yang berhasil diaktifkan akan menyebabkan baris kedua disisipkan, menyebabkan pemicu diaktifkan lagi, menyisipkan baris ketiga, dan seterusnya hingga pernyataan gagal karena melebihi jumlah panggilan maksimum yang bersarang. Namun, ada hal halus lainnya yang ikut berperan. Pada saat pemicu dijalankan, penerapan untuk rekaman pertama yang disisipkan belum selesai. Oleh karena itu, pemicu yang berjalan dalam transaksi otonom mencoba memasukkan ke dalam tabel baris yang mereferensikan kunci asing ke catatan yang belum dikomit. Hal ini mengakibatkan menunggu (transaksi otonom menunggu transaksi utama berkomitmen untuk melihat apakah dapat memasukkan data) dan pada saat yang sama transaksi utama menunggu transaksi otonom terus bekerja setelah pemicu. Terjadi kebuntuan dan akibatnya transaksi otonom dibatalkan karena alasan yang berkaitan dengan kunci.

Hanya pengguna terdaftar yang dapat berpartisipasi dalam survei. Masuk, silakan.

Sulit untuk melakukannya?

  • Bagaikan dua jari, saya langsung memutuskan semuanya dengan benar.

  • Tidak juga, saya salah dalam beberapa pertanyaan.

  • Saya menyelesaikan setengahnya dengan benar.

  • Saya menebak jawabannya dua kali!

  • Saya akan menulis di komentar

14 pengguna memilih. 10 pengguna abstain.

Sumber: www.habr.com

Tambah komentar