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

Hello!

Pada 24-25 Jun, persidangan Highload++ Siberia 2019 telah diadakan di Novosibirsk. Lelaki kami juga berada di sana laporan "Pangkalan data kontena Oracle (CDB/PDB) dan kegunaan praktikalnya untuk pembangunan perisian", kami akan menerbitkan versi teks sedikit kemudian. Ia adalah sejuk, terima kasih olegbunin untuk organisasi, dan juga kepada semua yang datang.

Dalam siaran ini, kami ingin berkongsi dengan anda masalah yang kami hadapi di gerai kami supaya anda boleh menguji pengetahuan Oracle anda. Di bawah potongan terdapat 8 masalah, pilihan jawapan dan penjelasan.

Apakah nilai jujukan maksimum yang akan kita lihat hasil daripada melaksanakan 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 ralat

JawabMenurut dokumentasi Oracle (dipetik daripada 8.1.6):
Dalam satu pernyataan SQL, Oracle akan menambah jujukan sekali sahaja setiap baris. Jika pernyataan mengandungi lebih daripada satu rujukan kepada NEXTVAL untuk jujukan, Oracle menambah jujukan sekali dan mengembalikan nilai yang sama untuk semua kejadian NEXTVAL. Jika pernyataan mengandungi rujukan kepada kedua-dua CURRVAL dan NEXTVAL, Oracle menambah jujukan dan mengembalikan nilai yang sama untuk kedua-dua CURRVAL dan NEXTVAL tanpa mengira susunannya dalam penyata.

Oleh itu, nilai maksimum akan sepadan dengan bilangan baris, iaitu 5.

Berapakah bilangan baris dalam jadual hasil daripada 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

JawabMenurut dokumentasi Oracle (dipetik daripada 11.2):

Sebelum melaksanakan sebarang pernyataan SQL, Oracle menandakan titik simpanan tersirat (tidak tersedia untuk anda). Kemudian, jika kenyataan itu gagal, Oracle melancarkannya kembali secara automatik dan mengembalikan kod ralat yang berkenaan kepada SQLCODE dalam SQLCA. Sebagai contoh, jika penyataan INSERT menyebabkan ralat dengan cuba memasukkan nilai pendua dalam indeks unik, penyataan itu digulung semula.

Memanggil HP daripada pelanggan juga dianggap dan diproses sebagai satu kenyataan. Oleh itu, panggilan HP pertama berjaya diselesaikan, setelah memasukkan tiga rekod; panggilan HP kedua berakhir dengan ralat dan melancarkan semula rekod keempat yang berjaya dimasukkannya; panggilan ketiga gagal, dan terdapat tiga rekod dalam jadual.

Berapakah bilangan baris dalam jadual hasil daripada 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

JawabMenurut dokumentasi Oracle (dipetik daripada 11.2):

Kekangan semakan membolehkan anda menentukan syarat yang mesti dipenuhi oleh setiap baris dalam jadual. Untuk memenuhi kekangan, setiap baris dalam jadual mesti menjadikan syarat sama ada BENAR atau tidak diketahui (kerana batal). Apabila Oracle menilai keadaan kekangan semak untuk baris tertentu, sebarang nama lajur dalam keadaan merujuk kepada nilai lajur dalam baris itu.

Oleh itu, nilai null akan lulus semakan, dan blok tanpa nama akan dilaksanakan dengan jayanya sehingga percubaan untuk memasukkan nilai 3. Selepas ini, blok pengendalian ralat akan mengosongkan pengecualian, tiada rollback akan berlaku, dan akan ada empat baris lagi dalam jadual dengan nilai 1, null, 2 dan null lagi.

Pasangan nilai yang manakah akan menggunakan 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
• Saya dan J
• J dan X
• Semua disenaraikan

JawabBerikut adalah petikan daripada dokumentasi (12.1.0.2) tentang menyimpan pelbagai jenis data dalam Oracle.

Jenis Data CHAR
Jenis data CHAR menentukan rentetan aksara panjang tetap dalam set aksara pangkalan data. Anda menentukan set aksara pangkalan data apabila anda mencipta pangkalan data anda. Oracle memastikan bahawa semua nilai yang disimpan dalam lajur CHAR mempunyai panjang yang ditentukan mengikut saiz dalam semantik panjang yang dipilih. Jika anda memasukkan nilai yang lebih pendek daripada panjang lajur, maka Oracle blank-pad nilai ke panjang lajur.

Jenis Data VARCHAR2
Jenis data VARCHAR2 menentukan rentetan aksara panjang berubah dalam set aksara pangkalan data. Anda menentukan set aksara pangkalan data apabila anda mencipta pangkalan data anda. Oracle menyimpan nilai aksara dalam lajur VARCHAR2 tepat seperti yang anda nyatakan, tanpa sebarang padding kosong, dengan syarat nilai itu tidak melebihi panjang lajur.

NUMBER Jenis Data
Jenis data NUMBER menyimpan sifar serta nombor tetap positif dan negatif dengan nilai mutlak dari 1.0 x 10-130 hingga tetapi tidak termasuk 1.0 x 10126. Jika anda menentukan ungkapan aritmetik yang nilainya mempunyai nilai mutlak lebih besar daripada atau sama dengan 1.0 x 10126, kemudian Oracle mengembalikan ralat. Setiap nilai NUMBER memerlukan dari 1 hingga 22 bait. Dengan mengambil kira perkara ini, saiz lajur dalam bait untuk nilai data berangka tertentu NUMBER(p), dengan p ialah ketepatan nilai yang diberikan, boleh dikira menggunakan formula berikut: BULAT((panjang(p)+s)/2))+1 di mana s sama dengan sifar jika nombor itu positif, dan s sama dengan 1 jika nombor itu negatif.

Di samping itu, mari kita ambil petikan daripada dokumentasi tentang menyimpan nilai Null.

Nol ialah ketiadaan nilai dalam lajur. Null menunjukkan data yang hilang, tidak diketahui atau tidak boleh digunakan. Nulls disimpan dalam pangkalan data jika ia berada di antara lajur dengan nilai data. Dalam kes ini, mereka memerlukan 1 bait untuk menyimpan panjang lajur (sifar). Nol mengekori berturut-turut tidak memerlukan storan kerana pengepala baris baharu menandakan bahawa lajur yang tinggal dalam baris sebelumnya adalah batal. Sebagai contoh, jika tiga lajur terakhir jadual adalah batal, maka tiada data disimpan untuk lajur ini.

Berdasarkan data ini, kami membina penaakulan. Kami menganggap bahawa pangkalan data menggunakan pengekodan AL32UTF8. Dalam pengekodan ini, huruf Rusia akan menduduki 2 bait.

1) A dan X, nilai medan 'Y' mengambil 1 bait, nilai medan x 'D' mengambil 2 bait
2) B dan Y, 'Vasya' dalam b nilai akan dipadatkan dengan ruang sehingga 10 aksara dan akan mengambil 14 bait, 'Vasya' dalam d akan mengambil 8 bait.
3) C dan K. Kedua-dua medan mempunyai nilai NULL, selepasnya terdapat medan penting, jadi mereka menduduki 1 bait.
4) C dan Z. Kedua-dua medan mempunyai nilai NULL, tetapi medan Z adalah yang terakhir dalam jadual, jadi ia tidak mengambil ruang (0 bait). Medan C menduduki 1 bait.
5) K dan Z. Sama seperti kes sebelum ini. Nilai dalam medan K menduduki 1 bait, dalam Z – 0.
6) I dan J. Menurut dokumentasi, kedua-dua nilai akan mengambil masa 2 bait. Kami mengira panjang menggunakan formula yang diambil dari dokumentasi: round( (1 + 0)/2) +1 = 1 + 1 = 2.
7) J dan X. Nilai dalam medan J akan mengambil masa 2 bait, nilai dalam medan X akan mengambil 2 bait.

Secara keseluruhan, pilihan yang betul ialah: C dan K, I dan J, J dan X.

Berapakah 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);

• Kira-kira berpuluh-puluh
• Kira-kira ratusan
• Kira-kira beribu-ribu
• Kira-kira berpuluh ribu

JawabMenurut dokumentasi Oracle (dipetik daripada 12.1):

Untuk indeks pokok B, faktor pengelompokan indeks mengukur pengelompokan fizikal baris berhubung dengan nilai indeks.

Faktor pengelompokan indeks membantu pengoptimum memutuskan sama ada imbasan indeks atau imbasan jadual penuh lebih cekap untuk pertanyaan tertentu). Faktor pengelompokan yang rendah menunjukkan imbasan indeks yang cekap.

Faktor pengelompokan yang hampir dengan bilangan blok dalam jadual menunjukkan bahawa baris disusun secara fizikal dalam blok jadual oleh kunci indeks. Jika pangkalan data melakukan imbasan jadual penuh, maka pangkalan data cenderung untuk mendapatkan semula baris kerana ia disimpan pada cakera yang diisih mengikut kunci indeks. Faktor pengelompokan yang hampir dengan bilangan baris menunjukkan bahawa baris bertaburan secara rawak merentasi blok pangkalan data berhubung dengan kunci indeks. Jika pangkalan data melakukan imbasan jadual penuh, maka pangkalan data tidak akan mendapatkan semula baris dalam sebarang susunan disusun mengikut kunci indeks ini.

Dalam kes ini, data disusun secara ideal, jadi faktor pengelompokan akan sama dengan atau hampir dengan bilangan blok yang diduduki dalam jadual. Untuk saiz blok standard 8 kilobait, anda boleh menjangkakan bahawa kira-kira seribu nilai nombor sempit akan dimuatkan ke dalam satu blok, jadi bilangan blok, dan sebagai hasilnya, faktor pengelompokan akan kira-kira berpuluh-puluh.

Pada nilai N apakah skrip berikut akan berjaya dilaksanakan dalam pangkalan data biasa dengan tetapan standard?

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

JawabMenurut dokumentasi Oracle (dipetik daripada 11.2):

Had Pangkalan Data Logik

Perkara
Jenis Had
Had Nilai

Indeks
Jumlah saiz lajur diindeks
75% daripada saiz blok pangkalan data tolak beberapa overhed

Oleh itu, jumlah saiz lajur diindeks tidak boleh melebihi 6Kb. Perkara yang berlaku seterusnya bergantung pada pengekodan asas yang dipilih. Untuk pengekodan AL32UTF8, satu aksara boleh menduduki maksimum 4 bait, jadi dalam senario terburuk, kira-kira 6 aksara akan dimuatkan ke dalam 1500 kilobait. Oleh itu, Oracle akan tidak membenarkan penciptaan indeks pada N = 400 (apabila panjang kunci kes terburuk ialah 1600 aksara * 4 bait + panjang rowid), manakala pada N = 200 (atau kurang) mencipta indeks akan berfungsi tanpa masalah.

Operator INSERT dengan pembayang APPEND direka untuk memuatkan data dalam mod langsung. Apakah yang berlaku jika ia digunakan pada jadual di mana picu digantung?

• Data akan dimuatkan dalam mod langsung, pencetus akan berfungsi seperti yang diharapkan
• Data akan dimuatkan dalam mod langsung, tetapi pencetus tidak akan dilaksanakan
• Data akan dimuatkan dalam mod konvensional, pencetus akan berfungsi sebagaimana mestinya
• Data akan dimuatkan dalam mod konvensional, tetapi pencetus tidak akan dilaksanakan
• Data tidak akan dimuatkan, ralat akan direkodkan

JawabPada asasnya, ini lebih kepada persoalan logik. Untuk mencari jawapan yang betul, saya akan mencadangkan model penaakulan berikut:

1. Penyisipan dalam mod langsung dilakukan dengan pembentukan terus blok data, memintas enjin SQL, yang memastikan kelajuan tinggi. Oleh itu, memastikan pelaksanaan pencetus adalah sangat sukar, jika tidak mustahil, dan tidak ada gunanya dalam hal ini, kerana ia masih akan memperlahankan sisipan secara radikal.
2. Kegagalan untuk melaksanakan pencetus akan membawa kepada fakta bahawa, jika data dalam jadual adalah sama, keadaan pangkalan data secara keseluruhan (jadual lain) akan bergantung pada mod di mana data ini dimasukkan. Ini jelas akan memusnahkan integriti data dan tidak boleh digunakan sebagai penyelesaian dalam pengeluaran.
3. Ketidakupayaan untuk melaksanakan operasi yang diminta biasanya dianggap sebagai ralat. Tetapi di sini kita harus ingat bahawa APPEND ialah pembayang, dan logik umum pembayang ialah ia diambil kira jika boleh, tetapi jika tidak, pengendali dilaksanakan tanpa mengambil kira pembayang.

Jadi jawapan yang diharapkan ialah data akan dimuatkan dalam mod biasa (SQL), pencetus akan menyala.

Menurut dokumentasi Oracle (dipetik daripada 8.04):

Pelanggaran sekatan akan menyebabkan pernyataan dilaksanakan secara bersiri, menggunakan laluan sisipan konvensional, tanpa amaran atau mesej ralat. Pengecualian ialah sekatan ke atas penyata yang mengakses jadual yang sama lebih daripada sekali dalam transaksi, yang boleh menyebabkan mesej ralat.
Contohnya, jika pencetus atau integriti rujukan terdapat pada jadual, maka pembayang APPEND akan diabaikan apabila anda cuba menggunakan INSERT beban terus (siri atau selari), serta pembayang atau klausa SELARI, jika ada.

Apakah yang akan berlaku apabila skrip berikut dilaksanakan?

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);

• Selesai dengan jayanya
• Kegagalan kerana ralat sintaks
• Ralat: Transaksi Autonomi Tidak Sah
• Ralat berkaitan dengan melebihi sarang panggilan maksimum
• Ralat Pelanggaran Kunci Asing
• Ralat yang berkaitan dengan kunci

JawabJadual dan pencetus dibuat dengan betul dan operasi ini tidak sepatutnya membawa kepada masalah. Transaksi autonomi dalam pencetus juga dibenarkan, jika tidak, pengelogan tidak akan dapat dilakukan, sebagai contoh.

Selepas memasukkan baris pertama, penembakan pencetus yang berjaya akan menyebabkan baris kedua dimasukkan, menyebabkan pencetus menyala semula, memasukkan baris ketiga, dan seterusnya sehingga pernyataan gagal kerana melebihi sarang maksimum panggilan. Walau bagaimanapun, satu lagi perkara halus berlaku. Pada masa pencetus dilaksanakan, komit masih belum selesai untuk rekod pertama yang dimasukkan. Oleh itu, pencetus yang berjalan dalam transaksi autonomi cuba memasukkan baris ke dalam jadual yang merujuk kunci asing kepada rekod yang belum dilakukan. Ini mengakibatkan penantian (urus niaga autonomi menunggu transaksi utama untuk melakukan untuk melihat sama ada ia boleh memasukkan data) dan pada masa yang sama transaksi utama menunggu transaksi autonomi untuk terus berfungsi selepas pencetus. Kebuntuan berlaku dan, akibatnya, transaksi autonomi dibatalkan atas sebab yang berkaitan dengan kunci.

Hanya pengguna berdaftar boleh mengambil bahagian dalam tinjauan. Log masuk, Sama-sama.

Sukar ke?

• Seperti dua jari, saya segera memutuskan semuanya dengan betul.

• Tidak juga, saya silap dalam beberapa soalan.

• Saya menyelesaikan separuh daripadanya dengan betul.

• Saya meneka jawapannya dua kali!

• Saya akan menulis dalam komen

14 pengguna mengundi. 10 pengguna berpantang.

Sumber: www.habr.com