🥇Theo chân Highload++ Siberia 2019 - 8 nhiệm vụ trên Oracle

Hi!

Vào ngày 24-25 tháng 2019, hội nghị Highload++ Siberia XNUMX đã được tổ chức tại Novosibirsk. Các chàng trai của chúng tôi cũng có mặt ở đó bản báo cáo “Cơ sở dữ liệu bộ chứa Oracle (CDB/PDB) và cách sử dụng thực tế của chúng để phát triển phần mềm”, chúng tôi sẽ xuất bản phiên bản văn bản sau. Thật tuyệt vời, cảm ơn olebunin cho tổ chức cũng như cho tất cả những người đã đến.

Trong bài đăng này, chúng tôi muốn chia sẻ với bạn những vấn đề chúng tôi gặp phải tại gian hàng của mình để bạn có thể kiểm tra kiến thức về Oracle của mình. Bên dưới phần cắt là 8 bài toán, các phương án trả lời và giải thích.

Giá trị chuỗi tối đa mà chúng ta sẽ thấy khi thực thi tập lệnh sau là bao nhiêu?

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
Không, sẽ có lỗi

câu trả lờiTheo tài liệu của Oracle (trích dẫn từ 8.1.6):
Trong một câu lệnh SQL duy nhất, Oracle sẽ chỉ tăng chuỗi một lần trên mỗi hàng. Nếu một câu lệnh chứa nhiều hơn một tham chiếu đến NEXTVAL cho một chuỗi, Oracle sẽ tăng chuỗi đó một lần và trả về cùng một giá trị cho tất cả các lần xuất hiện của NEXTVAL. Nếu một câu lệnh chứa các tham chiếu đến cả CURRVAL và NEXTVAL, Oracle sẽ tăng chuỗi và trả về cùng một giá trị cho cả CURRVAL và NEXTVAL bất kể thứ tự của chúng trong câu lệnh.

Như vậy, giá trị tối đa sẽ tương ứng với số dòng, tức là 5.

Có bao nhiêu hàng trong bảng do chạy tập lệnh sau?

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

câu trả lờiTheo tài liệu của Oracle (trích dẫn từ 11.2):

Trước khi thực thi bất kỳ câu lệnh SQL nào, Oracle đánh dấu một điểm lưu trữ ẩn (không có sẵn cho bạn). Sau đó, nếu câu lệnh không thành công, Oracle sẽ tự động khôi phục câu lệnh đó và trả về mã lỗi áp dụng cho SQLCODE trong SQLCA. Ví dụ: nếu câu lệnh INSERT gây ra lỗi khi cố gắng chèn một giá trị trùng lặp vào một chỉ mục duy nhất thì câu lệnh sẽ được khôi phục.

Việc gọi HP từ khách hàng cũng được xem xét và xử lý dưới dạng một câu lệnh duy nhất. Do đó, lệnh gọi HP đầu tiên hoàn tất thành công sau khi chèn ba bản ghi; cuộc gọi HP thứ hai kết thúc với lỗi và khôi phục bản ghi thứ tư mà nó đã chèn vào; cuộc gọi thứ ba thất bại, và có ba bản ghi trong bảng.

Có bao nhiêu hàng trong bảng do chạy tập lệnh sau?

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

câu trả lờiTheo tài liệu của Oracle (trích dẫn từ 11.2):

Ràng buộc kiểm tra cho phép bạn chỉ định một điều kiện mà mỗi hàng trong bảng phải đáp ứng. Để thỏa mãn ràng buộc, mỗi hàng trong bảng phải đặt điều kiện là TRUE hoặc không xác định (do giá trị rỗng). Khi Oracle đánh giá một điều kiện ràng buộc kiểm tra cho một hàng cụ thể, bất kỳ tên cột nào trong điều kiện đều tham chiếu đến các giá trị cột trong hàng đó.

Do đó, giá trị null sẽ vượt qua quá trình kiểm tra và khối ẩn danh sẽ được thực thi thành công cho đến khi cố gắng chèn giá trị 3. Sau đó, khối xử lý lỗi sẽ xóa ngoại lệ, sẽ không xảy ra khôi phục và sẽ còn lại bốn hàng trong bảng với các giá trị 1, null, 2 và null nữa.

Cặp giá trị nào sẽ chiếm cùng một dung lượng trong khối?

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 và X
B và Y
C và K
C và Z
K và Z
Tôi và J
J và X
Tất cả được liệt kê

câu trả lờiDưới đây là các đoạn trích từ tài liệu (12.1.0.2) về việc lưu trữ các loại dữ liệu khác nhau trong Oracle.

Kiểu dữ liệu CHAR
Kiểu dữ liệu CHAR chỉ định chuỗi ký tự có độ dài cố định trong bộ ký tự cơ sở dữ liệu. Bạn chỉ định bộ ký tự cơ sở dữ liệu khi tạo cơ sở dữ liệu của mình. Oracle đảm bảo rằng tất cả các giá trị được lưu trữ trong cột CHAR có độ dài được chỉ định theo kích thước trong ngữ nghĩa độ dài đã chọn. Nếu bạn chèn một giá trị ngắn hơn độ dài cột thì Oracle sẽ đệm giá trị đó vào độ dài cột.

Kiểu dữ liệu VARCHAR2
Kiểu dữ liệu VARCHAR2 chỉ định chuỗi ký tự có độ dài thay đổi trong bộ ký tự cơ sở dữ liệu. Bạn chỉ định bộ ký tự cơ sở dữ liệu khi tạo cơ sở dữ liệu của mình. Oracle lưu trữ một giá trị ký tự trong cột VARCHAR2 chính xác như bạn chỉ định, không có bất kỳ phần đệm trống nào, miễn là giá trị không vượt quá độ dài của cột.

NUMBER Kiểu dữ liệu
Kiểu dữ liệu SỐ lưu trữ số 1.0 cũng như các số cố định dương và âm có giá trị tuyệt đối từ 10 x 130-1.0 đến nhưng không bao gồm 10126 x 1.0. Nếu bạn chỉ định một biểu thức số học có giá trị có giá trị tuyệt đối lớn hơn hoặc bằng 10126 x 1 thì Oracle sẽ trả về lỗi. Mỗi giá trị SỐ yêu cầu từ 22 đến XNUMX byte. Khi tính đến điều này, kích thước cột tính bằng byte cho một giá trị dữ liệu số cụ thể NUMBER(p), trong đó p là độ chính xác của một giá trị nhất định, có thể được tính bằng công thức sau: VÒNG((độ dài(p)+s)/2))+1 trong đó s bằng 1 nếu số dương và s bằng XNUMX nếu số âm.

Ngoài ra, hãy lấy một đoạn trích từ tài liệu về việc lưu trữ giá trị Null.

Giá trị rỗng là sự vắng mặt của một giá trị trong một cột. Giá trị rỗng biểu thị dữ liệu bị thiếu, không xác định hoặc không thể áp dụng. Các giá trị rỗng được lưu trữ trong cơ sở dữ liệu nếu chúng nằm giữa các cột có giá trị dữ liệu. Trong những trường hợp này, chúng yêu cầu 1 byte để lưu trữ độ dài của cột (không). Các giá trị rỗng ở cuối hàng không cần lưu trữ vì tiêu đề hàng mới báo hiệu rằng các cột còn lại ở hàng trước là rỗng. Ví dụ: nếu ba cột cuối cùng của bảng là null thì không có dữ liệu nào được lưu trữ cho các cột này.

Dựa trên những dữ liệu này, chúng tôi xây dựng lý luận. Chúng tôi giả định rằng cơ sở dữ liệu sử dụng mã hóa AL32UTF8. Trong bảng mã này, các chữ cái tiếng Nga sẽ chiếm 2 byte.

1) A và X, giá trị của trường a 'Y' mất 1 byte, giá trị của trường x 'D' mất 2 byte
2) B và Y, 'Vasya' trong b giá trị sẽ được đệm bằng khoảng trắng tối đa 10 ký tự và sẽ lấy 14 byte, 'Vasya' trong d sẽ lấy 8 byte.
3) C và K. Cả hai trường đều có giá trị NULL, sau chúng là các trường quan trọng nên chiếm 1 byte.
4) C và Z. Cả hai trường đều có giá trị NULL, nhưng trường Z là trường cuối cùng trong bảng nên không chiếm dung lượng (0 byte). Trường C chiếm 1 byte.
5) K và Z. Tương tự như trường hợp trước. Giá trị trong trường K chiếm 1 byte, thuộc Z – 0.
6) I và J. Theo tài liệu, cả hai giá trị sẽ chiếm 2 byte. Chúng tôi tính toán độ dài bằng công thức lấy từ tài liệu: round((1 + 0)/2) +1 = 1 + 1 = 2.
7) J và X. Giá trị trong trường J sẽ lấy 2 byte, giá trị trong trường X sẽ lấy 2 byte.

Tổng cộng, các phương án đúng là: C và K, I và J, J và X.

Hệ số phân cụm của chỉ số T_I sẽ xấp xỉ bằng bao nhiêu?

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

Khoảng hàng chục
Khoảng hàng trăm
Khoảng hàng ngàn
Khoảng chục ngàn

câu trả lờiTheo tài liệu của Oracle (trích dẫn từ 12.1):

Đối với chỉ mục cây B, hệ số phân cụm chỉ mục đo lường việc nhóm các hàng vật lý liên quan đến giá trị chỉ mục.

Hệ số phân cụm chỉ mục giúp trình tối ưu hóa quyết định xem quét chỉ mục hay quét toàn bộ bảng hiệu quả hơn đối với một số truy vấn nhất định). Hệ số phân cụm thấp cho thấy quá trình quét chỉ mục hiệu quả.

Hệ số phân cụm gần với số khối trong bảng cho biết rằng các hàng được sắp xếp vật lý trong các khối bảng bằng khóa chỉ mục. Nếu cơ sở dữ liệu thực hiện quét toàn bộ bảng thì cơ sở dữ liệu có xu hướng truy xuất các hàng khi chúng được lưu trữ trên đĩa được sắp xếp theo khóa chỉ mục. Hệ số phân cụm gần với số hàng cho biết các hàng nằm rải rác ngẫu nhiên trên các khối cơ sở dữ liệu liên quan đến khóa chỉ mục. Nếu cơ sở dữ liệu thực hiện quét toàn bộ bảng thì cơ sở dữ liệu sẽ không truy xuất các hàng theo bất kỳ thứ tự sắp xếp nào theo khóa chỉ mục này.

Trong trường hợp này, dữ liệu được sắp xếp lý tưởng nên hệ số phân cụm sẽ bằng hoặc gần với số khối được sử dụng trong bảng. Đối với kích thước khối tiêu chuẩn là 8 kilobyte, bạn có thể mong đợi rằng khoảng một nghìn giá trị số hẹp sẽ vừa với một khối, do đó, số lượng khối và kết quả là hệ số phân cụm sẽ là khoảng hàng chục.

Với giá trị nào của N thì tập lệnh sau sẽ được thực thi thành công trong cơ sở dữ liệu thông thường với cài đặt tiêu chuẩn?

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

câu trả lờiTheo tài liệu của Oracle (trích dẫn từ 11.2):

Giới hạn cơ sở dữ liệu logic

Mục
Loại giới hạn
Giá trị giới hạn

Chỉ số
Tổng kích thước của cột được lập chỉ mục
75% kích thước khối cơ sở dữ liệu trừ đi một số chi phí

Do đó, tổng kích thước của các cột được lập chỉ mục không được vượt quá 6Kb. Điều gì xảy ra tiếp theo tùy thuộc vào mã hóa cơ sở đã chọn. Đối với mã hóa AL32UTF8, một ký tự có thể chiếm tối đa 4 byte, vì vậy trong trường hợp xấu nhất, khoảng 6 ký tự sẽ vừa với 1500 kilobyte. Do đó, Oracle sẽ không cho phép tạo chỉ mục ở N=400 (khi độ dài khóa trong trường hợp xấu nhất là 1600 ký tự * 4 byte + độ dài rowid), trong khi tại N = 200 (hoặc ít hơn) việc tạo chỉ mục sẽ hoạt động mà không gặp vấn đề gì.

Toán tử INSERT với gợi ý APPEND được thiết kế để tải dữ liệu ở chế độ trực tiếp. Điều gì xảy ra nếu nó được áp dụng cho bảng mà trình kích hoạt bị treo trên đó?

Dữ liệu sẽ được tải ở chế độ trực tiếp, trình kích hoạt sẽ hoạt động như mong đợi
Dữ liệu sẽ được tải ở chế độ trực tiếp, nhưng trình kích hoạt sẽ không được thực thi
Dữ liệu sẽ được tải ở chế độ thông thường, trình kích hoạt sẽ hoạt động bình thường
Dữ liệu sẽ được tải ở chế độ thông thường, nhưng trình kích hoạt sẽ không được thực thi
Dữ liệu sẽ không được tải, sẽ có lỗi được ghi lại

câu trả lờiVề cơ bản, đây là một câu hỏi logic hơn. Để tìm ra câu trả lời đúng, tôi xin đề xuất mô hình lý luận sau:

Việc chèn ở chế độ trực tiếp được thực hiện bằng cách hình thành trực tiếp khối dữ liệu, bỏ qua công cụ SQL, đảm bảo tốc độ cao. Do đó, việc đảm bảo thực hiện trình kích hoạt là rất khó, nếu không muốn nói là không thể, và điều này chẳng có ý nghĩa gì, vì nó vẫn sẽ làm chậm quá trình chèn một cách triệt để.
Việc không thực thi trình kích hoạt sẽ dẫn đến thực tế là, nếu dữ liệu trong bảng giống nhau, thì trạng thái của toàn bộ cơ sở dữ liệu (các bảng khác) sẽ phụ thuộc vào chế độ mà dữ liệu này được chèn vào. Điều này rõ ràng sẽ phá hủy tính toàn vẹn của dữ liệu và không thể áp dụng như một giải pháp trong sản xuất.
Việc không thể thực hiện thao tác được yêu cầu thường được coi là một lỗi. Nhưng ở đây chúng ta nên nhớ rằng APPEND là một gợi ý và logic chung của các gợi ý là chúng sẽ được tính đến nếu có thể, còn nếu không, toán tử sẽ được thực thi mà không tính đến gợi ý.

Vì vậy, câu trả lời mong đợi là dữ liệu sẽ được tải ở chế độ bình thường (SQL), trình kích hoạt sẽ kích hoạt.

Theo tài liệu của Oracle (trích dẫn từ 8.04):

Việc vi phạm các hạn chế sẽ khiến câu lệnh được thực thi tuần tự, sử dụng đường dẫn chèn thông thường mà không có cảnh báo hoặc thông báo lỗi. Một ngoại lệ là hạn chế các câu lệnh truy cập vào cùng một bảng nhiều lần trong một giao dịch, điều này có thể gây ra thông báo lỗi.
Ví dụ: nếu trình kích hoạt hoặc tính toàn vẹn tham chiếu có trên bảng thì gợi ý APPEND sẽ bị bỏ qua khi bạn cố gắng sử dụng INSERT tải trực tiếp (nối tiếp hoặc song song), cũng như gợi ý hoặc mệnh đề PARALLEL, nếu có.

Điều gì sẽ xảy ra khi đoạn script sau được thực thi?

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

Hoàn thành thành công
Lỗi do lỗi cú pháp
Lỗi: Giao dịch tự động không hợp lệ
Lỗi liên quan đến việc vượt quá giới hạn tối đa của cuộc gọi lồng nhau
Lỗi vi phạm khóa ngoại
Lỗi liên quan đến ổ khóa

câu trả lờiBảng và trình kích hoạt được tạo khá chính xác và thao tác này sẽ không gây ra sự cố. Ví dụ: các giao dịch tự động trong trình kích hoạt cũng được cho phép, nếu không thì việc ghi nhật ký sẽ không thể thực hiện được.

Sau khi chèn hàng đầu tiên, việc kích hoạt trình kích hoạt thành công sẽ khiến hàng thứ hai được chèn, khiến trình kích hoạt kích hoạt lại, chèn hàng thứ ba, v.v. cho đến khi câu lệnh không thành công do vượt quá mức lồng cuộc gọi tối đa. Tuy nhiên, một điểm tinh tế khác phát huy tác dụng. Tại thời điểm kích hoạt được thực thi, cam kết vẫn chưa được hoàn thành đối với bản ghi được chèn đầu tiên. Do đó, trình kích hoạt chạy trong giao dịch tự động sẽ cố gắng chèn vào bảng một hàng tham chiếu khóa ngoại đến bản ghi chưa được cam kết. Điều này dẫn đến việc chờ đợi (giao dịch tự trị chờ giao dịch chính xác nhận xem liệu nó có thể chèn dữ liệu hay không) và đồng thời giao dịch chính chờ giao dịch tự trị tiếp tục hoạt động sau khi kích hoạt. Bế tắc xảy ra và kết quả là giao dịch tự động bị hủy do các lý do liên quan đến khóa.

Chỉ những người dùng đã đăng ký mới có thể tham gia khảo sát. Đăng nhập, xin vui lòng.

Thật khó để?

Giống như hai ngón tay, tôi ngay lập tức quyết định mọi việc một cách chính xác.
Không hẳn, tôi đã sai ở một số câu hỏi.
Tôi đã giải quyết đúng một nửa.
Tôi đã đoán câu trả lời hai lần!
Tôi sẽ viết trong phần bình luận

14 người dùng bình chọn. 10 người dùng bỏ phiếu trắng.

Nguồn: www.habr.com

Theo chân Highload++ Siberia 2019 - 8 nhiệm vụ trên Oracle

Giá trị chuỗi tối đa mà chúng ta sẽ thấy khi thực thi tập lệnh sau là bao nhiêu?

Có bao nhiêu hàng trong bảng do chạy tập lệnh sau?

Có bao nhiêu hàng trong bảng do chạy tập lệnh sau?

Cặp giá trị nào sẽ chiếm cùng một dung lượng trong khối?

Hệ số phân cụm của chỉ số T_I sẽ xấp xỉ bằng bao nhiêu?

Với giá trị nào của N thì tập lệnh sau sẽ được thực thi thành công trong cơ sở dữ liệu thông thường với cài đặt tiêu chuẩn?

Toán tử INSERT với gợi ý APPEND được thiết kế để tải dữ liệu ở chế độ trực tiếp. Điều gì xảy ra nếu nó được áp dụng cho bảng mà trình kích hoạt bị treo trên đó?

Điều gì sẽ xảy ra khi đoạn script sau được thực thi?

Thật khó để?

Thêm một lời nhận xét Hủy bỏ trả lời