🥇跟隨 Highload++ Siberia 2019 的腳步 - Oracle 上的 8 個任務

Привет！

24月25-2019日，Highload++ Siberia XNUMX會議在新西伯利亞舉行，我們的人員也參加了報告 “Oracle容器資料庫（CDB/PDB）及其在軟體開發中的實際用途”，我們稍後將發布文字版本。太酷了，謝謝油酸蛋白為了這個組織，也為了每一個來的人。

在這篇文章中，我們想與您分享我們在展位上遇到的問題，以便您可以測試您的 Oracle 知識。剪輯下方有 8 個問題、答案選項和解釋。

執行以下腳本後我們將看到的最大序列值是多少？

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
不，會有錯誤

回答根據Oracle文件（引自8.1.6）：
在單一 SQL 語句中，Oracle 每行只會遞增序列一次。如果一語句包含多個對某個序列的 NEXTVAL 的引用，則 Oracle 會將該序列遞增一次，並為所有出現的 NEXTVAL 傳回相同的值。如果語句包含 CURRVAL 和 NEXTVAL 的引用，則 Oracle 會遞增序列並為 CURRVAL 和 NEXTVAL 傳回相同的值，無論它們在語句中的順序為何。

因此， 最大值將對應行數，即 5.

運行以下腳本後，表中將有多少行？

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

回答根據Oracle文件（引自11.2）：

在執行任何 SQL 語句之前，Oracle 會標記一個隱式保存點（您無法使用）。然後，如果該語句失敗，Oracle 將自動回滾該語句，並將適用的錯誤代碼傳回 SQLCA 中的 SQLCODE。例如，如果 INSERT 語句嘗試在唯一索引中插入重複值而導致錯誤，則該語句會回溯。

從客戶端呼叫 HP 也被視為單一語句並進行處理。因此，第一個 HP 呼叫成功完成，插入了三個記錄；第二個 HP 呼叫以錯誤結束，並回滾它設法插入的第四筆記錄；第三次呼叫失敗， 且表格中有XNUMX筆記錄.

運行以下腳本後，表中將有多少行？

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

回答根據Oracle文件（引自11.2）：

檢查約束允許您指定表格中每一行必須滿足的條件。為了滿足約束，表中的每一行必須使條件為 TRUE 或未知（由於為空）。當Oracle評估特定行的檢查約束條件時，條件中的任何列名都會引用該行中的列值。

因此，值 null 將通過檢查，匿名區塊將成功執行，直到嘗試插入值 3。此後，錯誤處理區塊將清除異常，不會發生回滾，且 表中將剩下四行 值為 1、null、2 和 null。

哪對值會在區塊中佔用相同的空間？

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和X
B和Y
C和K
C和Z
K 和 Z
我和J
J和X
全部列出

回答以下是有關在 Oracle 中儲存各種類型資料的文件 (12.1.0.2) 的摘錄。

CHAR 資料型別
CHAR 資料型別指定資料庫字元集中的固定長度字串。您在建立資料庫時指定資料庫字元集。 Oracle 確保儲存在 CHAR 欄位中的所有值都具有選定長度語意中 size 指定的長度。如果插入的值短於列長度，則 Oracle 會將該值空白填入列長度。

VARCHAR2 資料型別
VARCHAR2 資料類型指定資料庫字元集中的可變長度字串。您在建立資料庫時指定資料庫字元集。 Oracle 完全按照您指定的方式將字元值儲存在 VARCHAR2 欄位中，沒有任何空白填充，前提是該值不超過列的長度。

數字資料型
NUMBER 資料類型儲存零以及絕對值從 1.0 x 10-130 到但不包括 1.0 x 10126 的正負固定數。如果指定算術表達式，其值的絕對值大於或等於1.0 x 10126，則Oracle 傳回錯誤。每個 NUMBER 值需要 1 到 22 個位元組。考慮到這一點，可以使用以下公式計算特定數值資料值 NUMBER(p) 的列大小（以位元組為單位），其中 p 是給定值的精確度： 圓形((長度(p)+s)/2))+1 其中，如果數字為正，則 s 等於 1；如果數字為負，則 s 等於 XNUMX。

另外，我們摘錄一下有關儲存 Null 值的文件。

null 是指列中不存在值。空值表示缺失、未知或不適用的資料。如果空值位於具有資料值的資料列之間，則空值將儲存在資料庫中。在這些情況下，它們需要 1 個位元組來儲存列的長度（零）。行中的尾隨空值不需要存儲，因為新的行標題表明前一行中的其餘列為空。例如，如果表格的最後三列為空，則不會為這些欄位儲存任何資料。

基於這些數據，我們建立推理。我們假設資料庫使用AL32UTF8編碼。在這種編碼中，俄語字母將佔用2個位元組。

1）A和X，a字段'Y'的值佔1字節，x字段'D'的值佔2字節
2) B 和 Y，b 中的「Vasya」值將以空格填充，最多 10 個字符，將佔用 14 個位元組，d 中的「Vasya」將佔用 8 個位元組。
3）C和K。這兩個字段的值為NULL，後面有重要字段，因此佔用1個位元組。
4）C和Z。兩個欄位的值都是NULL，但欄位Z是表中的最後一個，因此它不佔空間（0位元組）。字段C佔用1位元組。
5）K和Z。與之前的情況類似。 K字段中的值佔用1個字節，在Z-0中。
6）I和J。根據文檔，這兩個值都將佔用2個位元組。我們使用文件中的公式計算長度：round( (1 + 0)/2) +1 = 1 + 1 = 2。
7) J和X。J字段中的值將佔用2個字節，X字段中的值將佔用2個位元組。

總共，正確選項是：C 和 K、I 和 J、J 和 X。

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

約數十
約數百
約數千
約數萬

回答根據Oracle文件（引自12.1）：

對於 B 樹索引，索引聚集因子衡量與索引值相關的行的物理分組。

索引群集因子幫助優化器決定對於某些查詢來說索引掃描還是全表掃描更有效。低聚類因子表示索引掃描有效。

接近表中區塊數的聚集因子表示表塊中的行按索引鍵物理排序。如果資料庫執行全表掃描，則資料庫傾向於檢索儲存在磁碟上並按索引鍵排序的行。接近行數的聚類因子表示行相對於索引鍵隨機分散在資料庫區塊中。如果資料庫執行全表掃描，則資料庫將不會按此索引鍵以任何排序順序檢索行。

在這種情況下，資料是理想排序的，因此聚類因子將等於或接近表中佔用的區塊的數量。對於 8 KB 的標準塊大小，您可以預期大約一千個窄數值將適合一個塊，因此塊的數量以及結果的聚類因子將是 大約幾十.

在標準設定的常規資料庫中，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

回答根據Oracle文件（引自11.2）：

邏輯資料庫限制

項目
限額類型
極限值

指數
索引列的總大小
資料庫區塊大小的 75% 減去一些開銷

因此，索引列的總大小不應超過 6Kb。接下來發生的情況取決於所選的基本編碼。對於 AL32UTF8 編碼，一個字元最多可以佔用 4 個位元組，因此在最壞的情況下，6 KB 可以容納大約 1500 個字元。因此，Oracle將不允許在N = 400時建立索引（當最壞情況鍵長度為1600個字元* 4位元組+ rowid長度時），而 N = 200（或更少） 創建索引將毫無問題地工作。

帶有 APPEND 提示的 INSERT 運算子旨在以直接模式載入資料。如果將其應用於觸發器掛起的表會發生什麼？

數據將以直接模式加載，觸發器將按預期工作
直接模式會載入數據，但不會執行觸發器
數據將以常規模式加載，觸發器將按其應有的方式工作
會以常規方式載入數據，但不會執行觸發器
資料不會被載入，會記錄錯誤

回答基本上，這更多的是一個邏輯問題。為了找到正確的答案，我建議採用以下推理模型：

直接插入方式是直接形成資料塊，繞過SQL引擎，速度快。因此，確保觸發器的執行即使不是不可能也是非常困難的，並且這樣做沒有任何意義，因為它仍然會從根本上減慢插入速度。
未能執行觸發器將導致這樣的事實：如果表中的資料相同，則整個資料庫（其他表）的狀態將取決於該資料插入的模式。這顯然會破壞資料完整性，並且不能作為生產中的解決方案應用。
無法執行請求的操作通常被視為錯誤。但這裡我們應該記住，APPEND是一個提示，提示的一般邏輯是，如果可能的話就考慮它們，但如果不能，則在不考慮提示的情況下執行運算符。

所以預期的答案是 資料將以正常（SQL）模式加載，觸發器將觸發。

根據Oracle文件（引自8.04）：

違反限制將導致語句使用常規插入路徑串列執行，而不會出現警告或錯誤訊息。一個例外是限制語句在事務中多次存取同一個表，這可能會導致錯誤訊息。
例如，如果表上存在觸發器或引用完整性，則當您嘗試使用直接載入 INSERT（串列或並列）以及 PARALLEL 提示或子句（如果有）時，將忽略 APPEND 提示。

執行下面的腳本會發生什麼？

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

順利完成
由於語法錯誤而失敗
錯誤：自主交易無效
與超出最大呼叫巢狀相關的錯誤
外鍵違規錯誤
與鎖相關的錯誤

回答表格和觸發器的建立非常正確，此操作不會導致問題。例如，也允許觸發器中的自主事務，否則將無法進行日誌記錄。

插入第一行後，成功觸發觸發器將導致插入第二行，從而導致觸發器再次觸發，插入第三行，依此類推，直到語句由於超出呼叫的最大巢狀數而失敗。然而，另一個微妙的點也發揮了作用。執行觸發器時，第一筆插入記錄的提交尚未完成。因此，在自治事務中執行的觸發器會嘗試在表中插入引用尚未提交的記錄的外鍵的行。這就導致了等待（自治事務等待主事務提交看是否可以插入資料），同時主事務在觸發後等待自治事務繼續工作。 發生死鎖，導致自治事務因鎖相關原因取消.

只有註冊用戶才能參與調查。登入，請。

很難嗎？

就像兩根手指一樣，我立刻就決定了一切。
不完全是，我在幾個問題上都錯了。
我正確地解決了一半。
我猜了兩次答案！
我會寫在評論裡

14 位用戶投票。 10 名用戶棄權。

來源： www.habr.com

跟隨 Highload++ Siberia 2019 的腳步 - Oracle 上的 8 個任務

執行以下腳本後我們將看到的最大序列值是多少？

運行以下腳本後，表中將有多少行？

運行以下腳本後，表中將有多少行？

哪對值會在區塊中佔用相同的空間？

T_I 索引的聚類因子大約是多少？

在標準設定的常規資料庫中，N 的值是多少時，以下腳本將成功執行？

帶有 APPEND 提示的 INSERT 運算子旨在以直接模式載入資料。 如果將其應用於觸發器掛起的表會發生什麼？

執行下面的腳本會發生什麼？

很難嗎？

添加評論 取消回复

帶有 APPEND 提示的 INSERT 運算子旨在以直接模式載入資料。如果將其應用於觸發器掛起的表會發生什麼？

添加評論取消回复