Ua raws li cov kauj ruam ntawm Highload++ Siberia 2019 - 8 txoj haujlwm ntawm Oracle

Nyob zoo!

Thaum Lub Rau Hli 24-25, Lub Rooj Sib Tham Highload++ Siberia 2019 tau muaj nyob rau hauv Novosibirsk. qhia "Oracle thawv databases (CDB / PDB) thiab lawv cov tswv yim siv rau kev txhim kho software", peb yuav tshaj tawm cov ntawv nyeem me ntsis tom qab. Nws yog txias, ua tsaug olegbunin ua rau lub koom haum, nrog rau txhua tus neeg tuaj.

Hauv cov ntawv tshaj tawm no, peb xav qhia rau koj txog cov teeb meem uas peb muaj ntawm peb lub rooj muag khoom kom koj tuaj yeem sim koj qhov kev paub Oracle. Hauv qab qhov kev txiav yog 8 qhov teeb meem, teb cov kev xaiv thiab piav qhia.

Dab tsi yog qhov txiaj ntsig ntau tshaj plaws uas peb yuav pom los ntawm kev ua raws li tsab ntawv hauv qab no?

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
• Tsis yog, yuav muaj qhov yuam kev

TebRaws li Oracle cov ntaub ntawv (los ntawm 8.1.6):
Hauv ib nqe lus SQL, Oracle yuav nce qhov sib lawv liag ib zaug xwb. Yog tias ib nqe lus muaj ntau dua ib qho kev siv rau NEXTVAL rau ib ntu, Oracle nce qib ib zaug thiab rov qab tus nqi tib yam rau txhua qhov tshwm sim ntawm NEXTVAL. Yog tias daim ntawv tshaj tawm muaj cov ntaub ntawv hais txog ob qho tib si CURRVAL thiab NEXTVAL, Oracle nce qib thiab rov qab tus nqi tib yam rau ob qho tib si CURRVAL thiab NEXTVAL tsis hais lawv qhov kev txiav txim hauv nqe lus.

Yog li, tus nqi siab tshaj plaws yuav sib npaug rau cov kab, uas yog 5.

Muaj pes tsawg kab yuav nyob rau hauv lub rooj raws li kev khiav cov ntawv hauv qab no?

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

TebRaws li Oracle cov ntaub ntawv (los ntawm 11.2):

Ua ntej ua tiav ib nqe lus SQL, Oracle kos ib qho chaw txuag (tsis muaj rau koj). Tom qab ntawd, yog tias cov lus tsis ua tiav, Oracle dov nws rov qab thiab xa rov qab cov lej yuam kev rau SQLCODE hauv SQLCA. Piv txwv li, yog tias INSERT nqe lus ua rau muaj kev ua yuam kev los ntawm kev sim ntxig tus nqi sib npaug hauv qhov ntsuas qhov tshwj xeeb, cov lus tau muab rov qab.

Hu rau HP los ntawm tus neeg siv khoom kuj raug txiav txim siab thiab ua tiav raws li ib nqe lus. Yog li, thawj HP hu ua tiav, tau tso peb cov ntaub ntawv; qhov thib ob HP hu xaus nrog qhov yuam kev thiab yob rov qab cov ntaub ntawv plaub uas nws tau tswj kom ntxig; thib peb hu tsis tau, thiab muaj peb cov ntaub ntawv nyob rau hauv lub rooj.

Muaj pes tsawg kab yuav nyob rau hauv lub rooj raws li kev khiav cov ntawv hauv qab no?

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

TebRaws li Oracle cov ntaub ntawv (los ntawm 11.2):

Ib daim tshev txwv tso cai rau koj teev cov xwm txheej uas txhua kab hauv lub rooj yuav tsum txaus siab. Txhawm rau kom txaus siab rau qhov txwv, txhua kab hauv lub rooj yuav tsum ua qhov xwm txheej tiag tiag lossis tsis paub (vim yog qhov tsis muaj). Thaum Oracle ntsuam xyuas qhov kev txwv nruj rau ib kab tshwj xeeb, txhua kab npe hauv cov xwm txheej xa mus rau kab qhov tseem ceeb hauv kab ntawd.

Yog li, tus nqi null yuav dhau daim tshev, thiab qhov thaiv tsis qhia npe yuav raug ua tiav kom txog thaum muaj kev sim ntxig rau tus nqi 3. Tom qab no, qhov yuam kev tuav thaiv yuav tshem tawm qhov kev zam, tsis muaj rollback yuav tshwm sim, thiab yuav muaj plaub kab laug nyob rau hauv lub rooj nrog tus nqi 1, null, 2 thiab null dua.

Cov khub twg ntawm qhov tseem ceeb yuav siv tib qhov chaw ntawm qhov thaiv?

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 thiab X
• B thiab Y
• C thiab K
• C thiab Z
• K thiab Z
• Kuv thiab J
• J thiab X
• Txhua teev

TebNov yog cov ntsiab lus los ntawm cov ntaub ntawv (12.1.0.2) ntawm kev khaws ntau hom ntaub ntawv hauv Oracle.

CHAR Data Type
CHAR cov ntaub ntawv hom qhia txog qhov ntev-ntev cov cim hlua hauv cov cim database. Koj teev cov cim database teeb thaum koj tsim koj cov ntaub ntawv. Oracle xyuas kom meej tias tag nrho cov txiaj ntsig khaws cia hauv CHAR kem muaj qhov ntev uas tau teev tseg los ntawm qhov loj me hauv cov lus xaiv ntev. Yog tias koj ntxig tus nqi uas luv dua ntawm kab ntev, ces Oracle blank-pads tus nqi rau kab ntev.

VARCHAR2 Cov ntaub ntawv hom
Hom VARCHAR2 cov ntaub ntawv qhia txog qhov sib txawv-ntev cov cim kab hauv cov cim database. Koj teev cov cim database teeb thaum koj tsim koj cov ntaub ntawv. Oracle khaws cov cim tus nqi hauv VARCHAR2 kem raws nraim li koj teev nws, tsis muaj qhov khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob

NUMBER Cov ntaub ntawv hom
NUMBER cov ntaub ntawv hom khw muag khoom xoom nrog rau cov lej zoo thiab qhov tsis zoo uas muaj qhov tseem ceeb ntawm 1.0 x 10-130 mus rau tab sis tsis suav nrog 1.0 x 10126. Yog tias koj qhia tus lej lej uas nws tus nqi muaj qhov muaj txiaj ntsig ntau dua lossis sib npaug. 1.0 x 10126, ces Oracle rov qab ua yuam kev. Txhua tus nqi NUMBER yuav tsum tau los ntawm 1 txog 22 bytes. Ua raws li qhov no rau hauv tus account, kem loj hauv bytes rau ib qho tshwj xeeb cov ntaub ntawv tus nqi NUMBER(p), qhov twg p yog qhov tseeb ntawm tus nqi muab, tuaj yeem suav nrog cov qauv hauv qab no: ROUND((ntev(p)+s)/2))+1 qhov twg s sib npaug zero yog tus lej yog qhov zoo, thiab s sib npaug 1 yog tus lej tsis zoo.

Tsis tas li ntawd, cia peb muab ib qho excerpt los ntawm cov ntaub ntawv hais txog kev khaws cia Null qhov tseem ceeb.

Ib qho null yog qhov tsis muaj tus nqi hauv ib kab. Nulls qhia tias cov ntaub ntawv ploj lawm, tsis paub, lossis siv tsis tau. Nulls khaws cia rau hauv cov ntaub ntawv yog tias lawv poob ntawm kab nrog cov ntaub ntawv muaj nuj nqis. Hauv cov xwm txheej no, lawv xav tau 1 byte los khaws qhov ntev ntawm kab (zero). Trailing nulls nyob rau hauv ib kab yuav tsum tsis txhob cia vim kab tshiab header qhia tias cov seem nyob rau hauv kab dhau los yog null. Piv txwv li, yog tias peb kab lus kawg ntawm ib lub rooj yog null, ces tsis muaj cov ntaub ntawv khaws tseg rau cov kab no.

Raws li cov ntaub ntawv no, peb tsim qhov laj thawj. Peb xav tias cov ntaub ntawv siv AL32UTF8 encoding. Nyob rau hauv no encoding, Lavxias teb sab ntawv yuav nyob 2 bytes.

1) A thiab X, tus nqi ntawm daim teb 'Y' siv 1 byte, tus nqi ntawm daim teb x 'D' siv 2 bytes
2) B thiab Y, 'Vasya' hauv b tus nqi yuav padded nrog qhov chaw mus txog 10 cim thiab yuav siv 14 bytes, 'Vasya' hauv d yuav siv 8 bytes.
3) C thiab K. Ob daim teb muaj tus nqi NULL, tom qab lawv muaj cov teb tseem ceeb, yog li lawv nyob 1 byte.
4) C thiab Z. Ob daim teb muaj tus nqi NULL, tab sis teb Z yog qhov kawg hauv lub rooj, yog li nws tsis siv qhov chaw (0 bytes). Field C occupies 1 byte.
5) K thiab Z. Zoo ib yam li rooj plaub dhau los. Tus nqi hauv K teb occupies 1 byte, hauv Z - 0.
6) Kuv thiab J. Raws li cov ntaub ntawv, ob qho tib si qhov tseem ceeb yuav siv 2 bytes. Peb xam qhov ntev uas siv cov qauv coj los ntawm cov ntaub ntawv: round((1 + 0)/2) +1 = 1 + 1 = 2.
7) J thiab X. Tus nqi hauv J teb yuav siv 2 bytes, tus nqi hauv X teb yuav siv 2 bytes.

Hauv tag nrho, cov kev xaiv raug yog: C thiab K, I thiab J, J thiab X.

Qhov kwv yees li cas yuav yog qhov sib koom ua ke ntawm T_I Performance index?

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

• Txog kaum tawm
• Txog pua pua
• Txog txhiab
• Kwv yees li kaum txhiab

TebRaws li Oracle cov ntaub ntawv (los ntawm 12.1):

Rau B-ntoo qhov ntsuas, qhov ntsuas qhov ntsuas qhov ntsuas ntsuas lub cev ntawm cov kab sib txuas nrog tus nqi ntsuas.

Qhov ntsuas ntsuas qhov ua tau zoo pab tus neeg ua kom zoo txiav txim siab seb qhov ntsuas ntsuas qhov ntsuas lossis ntsuas lub rooj tag nrho yog qhov ua tau zoo dua rau qee cov lus nug). Ib qho tsis tshua muaj pawg qhia tau hais tias qhov ntsuas ntsuas tau zoo.

Ib qho kev sib koom ua ke uas nyob ze rau cov naj npawb ntawm cov blocks hauv ib lub rooj qhia tias cov kab yog lub cev raug txiav txim hauv cov lus blocks los ntawm qhov ntsuas qhov tseem ceeb. Yog tias cov ntaub ntawv ua tiav tag nrho cov lus scan, tom qab ntawd cov ntaub ntawv zoo li khaws cov kab raws li lawv khaws cia rau hauv disk txheeb los ntawm qhov ntsuas qhov tseem ceeb. Ib qho kev sib koom ua ke uas nyob ze rau tus lej ntawm kab qhia tau hais tias cov kab tau tawg ua ntu zus nyob rau hauv cov ntaub ntawv blocks hauv kev cuam tshuam rau qhov ntsuas qhov tseem ceeb. Yog hais tias lub database ua ib tug tag nrho cov lus scan, ces lub database yuav tsis retrieve kab nyob rau hauv tej kev txiav txim los ntawm no index yuam sij.

Nyob rau hauv cov ntaub ntawv no, cov ntaub ntawv yog qhov zoo tagnrho sorted, yog li cov pawg yuav sib npaug los yog ze rau tus naj npawb ntawm cov blocks nyob hauv lub rooj. Rau tus qauv thaiv qhov loj ntawm 8 kilobytes, koj tuaj yeem cia siab tias kwv yees li ib txhiab tus lej nqaim yuav haum rau hauv ib qho thaiv, yog li tus naj npawb ntawm cov blocks, thiab vim li ntawd, qhov sib koom ua ke yuav yog. txog kaum.

Ntawm qhov txiaj ntsig ntawm N yuav ua li cas cov ntawv hauv qab no tau ua tiav hauv cov ntaub ntawv tsis tu ncua nrog cov qauv teeb tsa?

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

TebRaws li Oracle cov ntaub ntawv (los ntawm 11.2):

Logical Database txwv

Yam
Hom txwv
Txwv tus nqi

Lub ntsiab lus
Tag nrho qhov loj ntawm indexed kab
75% ntawm cov ntaub ntawv block loj rho tawm qee qhov nyiaj siv ua haujlwm

Yog li, tag nrho qhov loj ntawm indexed kab yuav tsum tsis pub tshaj 6Kb. Yuav ua li cas tom ntej no nyob ntawm lub hauv paus encoding xaiv. Rau AL32UTF8 encoding, ib tus cim tuaj yeem nyob qhov siab tshaj plaws ntawm 4 bytes, yog li hauv qhov xwm txheej phem tshaj plaws, txog 6 tus cim yuav haum rau 1500 kilobytes. Yog li ntawd, Oracle yuav tsis tso cai tsim qhov Performance index ntawm N = 400 (thaum qhov phem tshaj qhov tseem ceeb ntev yog 1600 cim * 4 bytes + rowid ntev), thaum N = 200 (los yog tsawg dua) tsim qhov ntsuas yuav ua haujlwm yam tsis muaj teeb meem.

Tus neeg teb xov tooj INSERT nrog APPEND hint yog tsim los thauj cov ntaub ntawv hauv hom ncaj qha. Yuav ua li cas yog hais tias nws yog siv rau lub rooj uas lub trigger hangs?

• Cov ntaub ntawv yuav raug thauj mus rau hauv hom ncaj qha, tus txhais yuav ua haujlwm raws li qhov xav tau
• Cov ntaub ntawv yuav raug thauj mus rau hauv hom ncaj qha, tab sis tus txhais yuav tsis raug tua
• Cov ntaub ntawv yuav raug loaded nyob rau hauv cov pa hom, tus txhais yuav ua hauj lwm raws li nws yuav tsum tau
• Cov ntaub ntawv yuav raug loaded nyob rau hauv cov pa hom, tab sis lub trigger yuav tsis raug tua
• Cov ntaub ntawv yuav tsis raug thauj khoom, qhov yuam kev yuav raug kaw

TebYeej, qhov no yog ntau ntawm cov lus nug ntawm logic. Txhawm rau nrhiav cov lus teb kom raug, kuv xav qhia cov qauv kev xav hauv qab no:

1. Kev ntxig rau hauv hom ncaj qha yog ua los ntawm kev tsim cov ntaub ntawv thaiv, hla lub cav SQL, uas ua kom muaj kev kub ceev. Yog li, kom ntseeg tau tias qhov kev tua ntawm tus txhais yog qhov nyuaj heev, yog tias tsis tuaj yeem ua tsis tau, thiab tsis muaj qhov taw tes rau qhov no, vim nws tseem yuav ua rau qeeb qeeb ntawm qhov ntxig.
2. Kev ua tsis tiav ntawm qhov ua kom yuam kev yuav ua rau muaj qhov tseeb tias, yog tias cov ntaub ntawv hauv lub rooj yog tib yam, lub xeev ntawm cov ntaub ntawv tag nrho (lwm cov ntxhuav) yuav nyob ntawm hom uas cov ntaub ntawv no tau muab tso rau. Qhov no yuav pom tseeb rhuav tshem cov ntaub ntawv kev ncaj ncees thiab tsis tuaj yeem siv los ua kev daws teeb meem hauv kev tsim khoom.
3. Qhov tsis muaj peev xwm ua tau qhov kev thov ua haujlwm feem ntau raug kho raws li qhov yuam kev. Tab sis ntawm no peb yuav tsum nco ntsoov tias APPEND yog ib qho lus qhia, thiab cov ntsiab lus dav dav ntawm cov lus qhia yog tias lawv raug coj mus rau hauv tus account yog tias ua tau, tab sis yog tias tsis yog, tus neeg teb xov tooj raug tua yam tsis tau hais txog qhov lus pom.

Yog li cov lus teb xav tau yog cov ntaub ntawv yuav raug loaded nyob rau hauv ib txwm (SQL) hom, tus txhais yuav tua.

Raws li Oracle cov ntaub ntawv (los ntawm 8.04):

Kev ua txhaum cai ntawm cov kev txwv yuav ua rau cov nqe lus ua kom tiav serially, siv cov pa tso rau hauv txoj kev, tsis muaj lus ceeb toom lossis cov lus yuam kev. Ib qho kev zam yog qhov txwv ntawm cov lus nkag mus rau tib lub rooj ntau dua ib zaug hauv kev sib pauv, uas tuaj yeem ua rau cov lus yuam kev.
Piv txwv li, yog tias qhov tshwm sim lossis kev lees paub kev ncaj ncees muaj nyob rau ntawm lub rooj, ces APPEND hint yuav raug tsis quav ntsej thaum koj sim siv ncaj qha-load INSERT (serial lossis parallel), nrog rau PARALLEL hint lossis clause, yog tias muaj.

Yuav ua li cas thaum cov ntawv hauv qab no raug ua tiav?

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

• Kev ua tiav tiav
• Ua tsis tiav vim syntax yuam kev
• Yuam kev: Autonomous Transaction tsis siv tau
• Yuam kev cuam tshuam txog kev hu xov tooj ntau tshaj plaws
• Txawv teb chaws yuam kev yuam kev
• Qhov yuam kev cuam tshuam nrog cov xauv

TebLub rooj thiab txhais tau tsim tau zoo heev thiab qhov kev ua haujlwm no yuav tsum tsis txhob ua rau muaj teeb meem. Kev ua lag luam ywj pheej hauv ib qho txiaj ntsig kuj raug tso cai, txwv tsis pub txiav yuav tsis ua tau, piv txwv li.

Tom qab tso thawj kab, qhov kev ua tau zoo ntawm kev tua hluav taws yuav ua rau cov kab thib ob tau muab tso rau, ua rau lub tshuab hluav taws xob rov qab, ntxig rau kab thib peb, thiab lwm yam kom txog thaum cov lus tsis ua tiav vim yog qhov siab tshaj plaws ntawm kev hu xov tooj. Txawm li cas los xij, lwm qhov hloov maj mam los rau hauv kev ua si. Thaum lub sij hawm lub trigger raug tua, kev cog lus tseem tsis tau ua tiav rau thawj cov ntaub ntawv tso. Yog li ntawd, ib qho kev ua haujlwm nyob rau hauv kev lag luam autonomous sim ntxig rau hauv lub rooj kab uas hais txog tus yuam sij txawv teb chaws rau cov ntaub ntawv uas tseem tsis tau ua tiav. Qhov no tshwm sim nyob rau hauv ib tug tos (kev lag luam autonomous tos rau lub ntsiab kev lag luam los cog lus saib seb nws puas tuaj yeem ntxig cov ntaub ntawv) thiab tib lub sij hawm qhov kev lag luam tseem ceeb tos rau kev lag luam autonomous mus txuas ntxiv ua haujlwm tom qab qhov tshwm sim. Ib qho kev tuag tshwm sim thiab, raws li qhov tshwm sim, qhov kev hloov pauv ntawm tus kheej raug muab tso tseg vim yog vim li cas cuam tshuam nrog cov xauv.

Tsuas yog cov neeg siv sau npe tuaj yeem koom nrog hauv daim ntawv ntsuam xyuas. Kos npe rau hauvthov.

Nws nyuaj rau?

• Zoo li ob tug ntiv tes, kuv tam sim ntawd txiav txim siab txhua yam kom raug.

• Tsis yog tiag, kuv ua yuam kev rau ob peb nqe lus nug.

• Kuv daws tau ib nrab ntawm nws kom raug.

• Kuv twv cov lus teb ob zaug!

• Kuv mam li sau rau hauv cov lus

14 cov neeg siv pov npav. 10 cov neeg siv txwv tsis pub siv.

Tau qhov twg los: www.hab.com