Warning: Declaration of Suffusion_MM_Walker::start_el(&$output, $item, $depth, $args) should be compatible with Walker_Nav_Menu::start_el(&$output, $item, $depth = 0, $args = Array, $id = 0) in /DISK2/WWW/plsql.cz/www/wp-content/themes/suffusion/library/suffusion-walkers.php on line 39
Oct 052014
 

Pokračování krátkého zkoumání jaké hodnoty se objevují ve sloupci STARTS v pohledu V$SQL_PLAN_MONITOR. Předchozí díl se věnoval počtu startů u tabulky, která není partišnovaná a dnešní díl se tedy bude věnovat tomuto sloupci u partitiovaných tabulek. Další díl budu věnovat paralelním selectům a pak se uvidí ;) Protože je to dost zajímavý sloupec a těch kombinací a zajímavých operací je spousty, ale muj čas je omezen. Takže hurá na to!

Takže pojďme udělat rovnou tabulku s nějakýma partitions:

create table tbl_range (a_column)
PARTITION BY RANGE(a_column)
(
PARTITION p1 VALUES LESS THAN(1),
PARTITION p2 VALUES LESS THAN(2),
PARTITION p3 VALUES LESS THAN(3),
PARTITION p4 VALUES LESS THAN(4),
PARTITION p5 VALUES LESS THAN(5),
PARTITION p6 VALUES LESS THAN(6)
) as
select mod(rownum,6) from dual connect by level<1001;

A nyní select:

select /*+ MONITOR */ * from tbl_range;

Asi by člověka napadlo, že bude mít tolik startů, kolik má partitions. A přesně tak tomu je, ovšem s menším ale – protože první SQL Developer fetchne pouze 50 řádků (defalutní hodnota), takže je třeba proscrollovat a dostáváme počet startů rovných 6ti:

(počet startů – range)

Nebylo zajímavé, že? Prozradím, že s INLIST a HASH partitions to funguje naprosto stejně, daleko zajímavější je INVERVAL partitions. A vlastně důvodu proč tenhle článek vzniknul:

CREATE TABLE tbl_rang_i (a_column NUMBER(10))
PARTITION BY RANGE(a_column)
INTERVAL(13)
(PARTITION base VALUES LESS THAN (0)
);

A nyní insertneme nějaká data:

insert into tbl_rang_i
select rownum from dual connect by level<=400 union all
select rownum+800 from dual connect by level<=400;

Konkrétně je to 800 řádků. Čísla od 1..400 a 800..1200. Takže se podívejme, kolik má tabulka partitions:

select owner,table_name,partition_count from all_part_tables where lower(table_name)='tbl_rang_i';

Počet partitions v tomhle view je 1048575, překvani? Taky jsem nedávno byl ;) Ale Oracle v tomhle view pro tabulky na kterých je interval partitioning udává Oracle vždy číslo 1048575. Číslo není nějak moc magické, udává maximální počet partitions, který tabulka může být.  Takže zkusime jiné view:

select count(*) from all_tab_partitions where lower(table_name)='tbl_rang_i';

Je jich 64. Lehce podezřele binárně kulaté číslo ;) Ale to je jen náhoda – začínání od 0 a dva intervaly v rozmezí 1..400 a 800..1200 (takže něco jako select 2*(ceil(400/13))+1+1 from dual).  A nyní konečně počet startů:

select /*+ MONITOR */ * from tbl_rang_i;

(počet startů – range-interval)

94. Což je lehce podezřelé číslo. Speciálně, když čekáte až Vám doběhne select a full table scan nad tabulkou co má 64 partitions přesáhne 65 a vesele si pokračuje. Nicméně po několika mých testech jsem doše k tomu, že Oracle pro interval partitions připočítává do STARTS i “mezery”. Tedy výpočet pro tento případ je select ceil(1200/13)+1 from dual tzn. 94 (jedna je pro BASE partition).

Inu počty startů:

  • Pro range, list, hash – vždy počet partitions
  • Pro range-interval – (spread mezi LOW a HIGH hodnotou v partiton sloupci) / intervalem partišnování (případně +1 pokud base partition je mimo)
  • Precedenci berou “nižší karta” tedy full scanu nad supartišnovanou tabulkou je ve sloupcu STARTS počet čtených subpartitions.

Bohužel vzhledem k mému omezenému času tento článek končím, ale napadají mi daleko zajímavější scénáře – systém a referenční partitioning, partition wise-join a počet startů..

 Posted by at 11:52
Aug 302014
 

Vcelku častý scénář – potřebujem zvalidovat constrain a zároveň jsme si vědomi, že data jsou špinavá a validace constrainu nám hnedtak neprojde. Takže nezbývá než nějaké řádky promazat či jinak vyřešit duplikace. Dnešní tip je věnován klauzuli EXCEPTIONS INTO, která je spoučástí příkazu ALTER TABLE a umožní uložit rowid duplicitních řádků při validaci constrainu. Takže hurá do toho!

CREATE TABLE TEST_CONS_EX AS SELECT ROWNUM AS ID FROM DUAL CONNECT BY LEVEL<=666;

A ještě nějaké náhodné duplicity (29 řádků)

INSERT INTO TEST_CONS_EX SELECT A FROM (SELECT ROWNUM A FROM DUAL CONNECT BY LEVEL<=666) WHERE ORA_HASH(A,21)=7;

Nic zajimavého, tabulka s jedním sloupcem ID s čísly od 1 do 666 s tím, že duplikovaných hodnot je tam 29, vždycky po dvou kouskách. Následně je potřeba vytvořit tabulku do které se porušení constrainu bude reportovat. Oracle odkazuje na scripty UTLEXCPT.SQL nebo UTLEXPT1.SQL. Ovšem stačí mít jen tabulku ve správné struktuře a není potřeba používat tyhle scripty.

CREATE TABLE dej_to_sem(row_id ROWID,
owner VARCHAR2(30),
table_name VARCHAR2(30),
constraint VARCHAR2(30));

Dokonce se to i hezky pamatuje, rowid, owner, jméno tabulky, jméno constrainu. Velikost přestně tolik, koli to může maximálně mít. Nyní vytvořme nějaký PK klíč. Neměl by být enabled.

ALTER TABLE TEST_CONS_EX ADD CONSTRAINT test_pk PRIMARY KEY (id) DISABLE;

A nyní enable společně s exceptions into klauzulí:

ALTER TABLE TEST_CONS_EX ENABLE CONSTRAINT test_pk EXCEPTIONS INTO DEJ_TO_SEM;¨
SQL Error: ORA-00001: nesplněna podmínka jedinečnosti (AZOR3.TEST_PK)

Sice nás to vyfuckovalo, ale díky použití klauzule máme k dispozici rowid řádků, které jsou duplicitní:

Data v exceptions tabulce..

Důležité je pamatovat si, že každý řádek, který je tam duplicitní je tam tolikrát kolikrát je duplicitní (nikoliv n-1) tedy odmazat z master tabulky všechny záznamy, které jsou uvedeny v tabulce duplicit rozhodně není dobrý nápad, šéf ani Marta Jandová by za to nepochváli. Exspeciálně, když další logický krok po odmazání je zapnutí constrainu, což je příkaz ALTER TABLE, což je změna struktury tabulky a tedy nemožnost napsat nějakou verzi flashaback selectu (ORA-01466: nelze číst data – definice tabulky byla změněna) Jinak žádné záludnosti, tabulka pro uložení duplikací musí být pochopitelně přístupná majiteli constrainu.

 Posted by at 15:31
Jun 212014
 

Ahoj, nedávno se mi stala taková nepříjemná věc, kterou bylo upgrade php verze od mého hostingu, což vedlo na rozhození v IE a na čínské znaky v Chromech, Firefoxech a jiných náhrážkách.  Když už jsem musel řešit upgrade PHP, rovnou jsem se rozhodl upgradovat i verzi WordPressu a tudíš přecházím i na nové theme. Vybral jsem něco trošku jiného, tak se snad bude líbit ;)  Což berte jako omluvu za předchozí dny a možná i budoucí, kdy to budu trošku upravovat a ohýbat podle svého gusta.  Jsem ted trošku busy, ale napíšu jsem teda i v pruběhu toho nějaký ten článek – i když jsou měsíce, kdy blog vypadá mrtvě, tak normálně žiju, nic mi nepřejelo.

Dále jsem zavřel komentáře ke všem článkům. Se změnou template by to mohlo vypadat na nedemokratický režim. Pravdou však je, že počet spamových viagra komentářů (i tisíce denně) již přerostl rozumnou úroveň a já jsem se svou prostatou vcelku spokojen.. pokusím se implementovat nějěký human-check a komentáře zase otevřít.

 Posted by at 11:20
Feb 222014
 

Představení další novinky - byla vylepšena funkce SYS_CONTEXT. Dle kapitoly Changes in This Release for Oracle Database SQL Language Reference v manuálu Oracle® Database SQL Language Reference došlo k přidání nového namespace a to konkrétně SYS_SESSION_ROLES. Tedy už jsou nyní dva – USERENV a nová SYS_SESSION_ROLES, která umožňuje zjistit zda-li je role aktivní nebo neaktivní.  Příklad:

sqlplus / as sysdba;
create role developer_role;
grant developer_role to apex_workout;

Nalogování pod apex_workout a :

SET ROLE ALL EXCEPT DEVELOPER_ROLE;

Dříve to bylo zjstitelné z SESSION_ROLES pohledu. Takže spíše než novinka lehce jednodušší a rychlejší přístup. Co stojí za zmínku je, že v případě neexistující role funce vrací FALSE – tzn. to samé jako pro vypnutou roli.

Začal jsem schválně názvem kapitoly ve které se mluví o této novince. Ovšem již tam není zmínka o tom, že v SYS_CONTEXT byl vylepšen namespace USERENV a jsou tu nové atributy. Od pohledu mi připadal seznam atributů delší, takže jsem to začal zkoumat, jinak to však neni uvedeno jako změna, což je škoda, některé nové atributy jsou příjemné. Takže nové atributy:

  • CDB_NAME – jméno CDB, pokud to není multitenant container database tak NULL
  • CLIENT_PROGRAM_NAME - jméno klientského programu
  • CON_NAME - jméno containeru, pokud to není multitenant databáže tak DB_NAME
  • DB_SUPPLEMENTAL_LOG_LEVEL – vrací level v případě supplemental loggingu, jinak null. Možné hodnoty jsou stejné jako supplemental loggingu, tedy ALL_COLUMN, FOREIGN_KEY, MINIMAL, PRIMARY_KEY, PROCEDURAL a UNIQUE_INDEX
  • IS_APPLY_SERVER - pro Oracle Data Guard – true na apply server, jinak false
  • ORACLE_HOME – vrací ORACLE_HOME.  Sexy!
  • PLATFORM_SLASH - oddělovač adresářové cesty na dané platformě
  • SHEDULER_JOB - Y = je to spuštěno v dbms_scheduler,  N – není to spuštěno v dms_scheduler. Hodne sexy, předtím se to řešilo taky pomocí kontextu jestli to má nebo nemé bg_job_id či fg_job_id

U mě to vypadá nějak takto:

Asi bych čekal obrácené lomítko jako path-separator, tuhle instanci mam na Windows. Osatní sedí – nemám Oracle Data Quard, na téhle instanci ani supplemental logging a multitenant db na téhle instnaci také nemám. Hodně se mi líbí atributy SCHEDULER_JOB, CLIENT_PROGRAM_NAME, ORACLE_HOME a PLATFORM_SLASH. Všechno je pouze lehčí cesta než koukání do views (v$database, v$sesssion..). Pochopitelně mimo věcí souvisejících s  multitenant databází (CDB_NAME, CON_NAME) a PLATFORM_SLASH - zde mi nanapadá z hlavy žádné view ze kterého bych to byl schopen vytřískat. So, šikovná věcička.

 Posted by at 02:12
Feb 192014
 

Oracle nově vylepšil ALTER TABLE statement a vylepšil práci s PARTITIONS (i se subky). Nově je možné v jendom příkazu pracovat s více PARTITONS najednou:

  • Přidat jednu/více range, list a system partitions do tabulky
  • Pridat jednu/více range či list subpartitions do partition
  • Rozdělit partition či subpartition na dvě a více partitons či subpartitions – týká se list a range
  • Spojit partition či subpartition na dvě a více partitons či subpartitions – týká se list a range
  • Truncatovat jednu nebo více partition nebo subpartition
  • Truncatovat jednu nebo více partition nebo subpartition

Řadím do spíše do těch užitečnějších vylepšení.  Na zkoušku, že to funguje a na zapamatování, že něco takového je nyní možné – truncate, to je nejednodušší ;) Nicméně implementace a syntaxe je velice podobná pro všechny tyto změny, na 11.2g to vypadalo takto:


Nyní na 12.1c:

Tedy list oddělený čárkama. Takže na zkoušku nějaká tabulka s partišnama a truncate více partitions najednou. Ze studijních důvodů použiji system partitions ;) – těch způsobů jak tabulku partišnovat (top-level) je 7 (co, vím – tzn. minimálně), ale typicky se mluví jen o těch hash, list, range. Zrovna systémové partišnování není moc známé (a ani používané).

CREATE TABLE TBL_TEST_TRUNCATE (slova VARCHAR2(20))
PARTITION BY SYSTEM (
PARTITION p1,
PARTITION p2,
PARTITION p3
);

INSERT INTO TBL_TEST_TRUNCATE PARTITION(p1) VALUES ('Lupíčky');
INSERT INTO TBL_TEST_TRUNCATE PARTITION(p2) VALUES ('Hébičky');
INSERT INTO TBL_TEST_TRUNCATE PARTITION(p3) VALUES ('Kulíškové');
INSERT INTO TBL_TEST_TRUNCATE PARTITION(p2) VALUES ('Kozy');
commit;

A truncate:

ALTER TABLE TBL_TEST_TRUNCATE TRUNCATE PARTITION p1,p2;

Možná za zmínku stojí napsat, že je možné psát TRUNCATE PARTITION i TRUNCATE PARTITIONS. Ať s nebo bez s je možné truncatovat jednu čí více partitions. Oracle tomu řiká “sytnax clarity” a doporučení je psát to bez s nakonci pokud truncatuju jednu a opačně.

 Posted by at 12:46
Feb 022014
 

Pokračování z minula, kde byla řeč o novince, která umožní deklarovat PL/SQL funkci v rámci sql selectu. Dnes hint PLSQL_WITH a o tom, jak je to možné použít v DELETE, INSERT, UPDATE. Nějaká omáčka, byla minule takže rovnou příklady a ukázky. Nejprve klasicky zakládám nějakou tabulku na které to bude dobře vidět. Často když se tu snažím něco ukázat tak použivám typicky CONNECT BY LEVEL<xxx na získání xxx-1 řádků a CTAS (create table as select) z duálu. Dnes je tam navíc PL/SQL funkce z minulé novinky. Tedy ano, WITH a PL/SQL funkci mohu použít v CTASu. A v rámci cvičení takto činním (nomrálně raději pomocí RPAD):

create table tbl_test_function as
with function with_f_add_xxx(p_a varchar2,p_b number) return varchar2
is
ls_result varchar2(20);
begin
ls_result:=p_a;
for i in 1..p_b loop
ls_result:=ls_result || 'x';
end loop;
return ls_result;
end;
select with_f_add_xxx('F',MOD(ROWNUM,10)) as sloupec from dual connect by level<101;

Červeně výše zmíněná novinka z minula, druhá novinka, která tu není vidět, jsou počítané statistiky v rámci CTASu.  Takže to by byla nějaká taková testovací tabulka, data asi takto:

Data po WITH

Takže zkusme DELETE:

delete /*+ with_plsql */ tbl_test_function my_alias
where sloupec=(with function print_delete(p_what varchar2) return varchar2
is begin
dbms_lock.sleep(0.25);
dbms_output.put_line('Mazani: '||p_what);
return p_what;
end;
select print_delete(my_alias.sloupec) from dual
);

Funkce (respektive její provolání z duálu) vrací zpět tu samou hodnotu, takže dojde ke smazání všech řádků. Po každém řádku se čeká 250ms a na output je vytištěna konkrétní hodnota. To by byl DELETE, tabulka je prázdná, takže se nabízí zkusit si to zase na INSERT:

insert /*+ with_plsql */ into tbl_test_function (sloupec)
with function just_return_x return varchar2 is
begin
return 'x';
end;
select just_return_x from dual;

Což insertne jeden řádek s hodnotou “x” v jednom jedinném sloupci. UPDATE:

update /*+ with_plsql */ tbl_test_function my_alias set
sloupec=(with function set_upper(p_sl varchar2) return varchar2
is
begin
return upper(p_sl);
end;
select set_upper(my_alias.sloupec) from dual)
where sloupec=(with function just_return_x return varchar2 is
begin
return 'x';
end;
select just_return_x from dual);

Funkci jde deklarovat jak v set klauzuli (u mě set_upper, která vrací pouze UPPER), tak ve where podmínce (u mě just_return_x, která vrací pouze ‘x’. A poslední věc, která chybí je MERGE:

alter table tbl_test_function add (sloupec_2 varchar2(10));

merge /*+ with_plsql */ into tbl_test_function my_alias
using (with function return_big_x(p_rownum number) return varchar2 is
begin
if p_rownum=1 then return 'X'; else return 'A'; end if;
end;
select return_big_x(rownum) fn_result from dual connect by level <= 1) s
on (my_alias.sloupec = s.fn_result)
when matched then update set my_alias.sloupec_2 =
(with function just_return_2x return varchar2 is
begin
return 'xx';
end;
select just_return_2x from dual)
when not matched then insert (my_alias.sloupec) values ('P');

Tak a to by byl příklad za merge. Kupodivu jsem nanašel, že by to Oracle podporovalo i v insert části MERGE. Jinak to jde pl/sql funkci napsat snad všude ;) ALTER table předtím, protože nelze udělat tento merge nad tabulkou s jedním sloupcem (nelze updatovat sloupec, který je vzároven v on klauzuli).

 Posted by at 16:37
Feb 012014
 

Jedna z nejužitečnějších věcí, které máme nově k dispozici. Nyní je ve 12c možnost v SQL selectu uvést WITH sekci a v ní nově PL/SQL funkci, což je mazec ;) Rozhodl jsem se rozhodit to do dvou postů – protože je to samé možné udělat i s UPDATE, DELETE, INSERT i MERGE – ovšem je na to třebas nový hint WITH_PLSQL.

Zkrátka – nyní lze definovat PL/SQL funkce přímo v SELECTu. Nejprve možná pro připomenutí, jak vypadá klasické WITH:

with pojmenovano as
(select dummy as my_dummy from dual)
select
my_dummy
from pojmenovano;

A takto se tam dá naférkovu nyní natláskat PL/SQL funkce:

with function with_result(x varchar2) return varchar2
is
begin
return x|| ' - x';
end;
select
with_result(dummy) as with_result_on_dummy
from dual;

To by bylo asi vše k WITH a PL/SQL funkci. Ohledně omezení – platí stejná jako pro pro volání standardní funkce v selectu z databáze. Příště určitě WITH_PLSQL hint a jak se to dá použít v DML.

 Posted by at 11:52