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
Aug 022015
 

Godiva, mohlo by se zdát, že rok po takovém čase od vydání 12.1c nemá cenu psát o hintech z 11.2g. Nicméně tento článek jsem rozepsal před více než rokem a půl, zanechal ve složce “drafts” a zapomněl. Navíc je to tak nějak pokračování, takže by bylo fajn to dokončit, speciálně když jsem ho v draftech našel de-facto dokončený ;) Takže jen lehké update a publish.

K hintu IGNORE_ROW_ON_DUPKEY_INDEX se toho asi mnoho nedá napsat – lze jej použít jen v INSERT, je nový od 11.2g (předtím šlo v 10.2g potlačit chybu pomocí DBMS_ERRLOG).  A co je zajímavé – Oracle explicitně zmiňuje, že v případě nalezení neunikátního záznamu se pokračuje dále a row-level lock řádku pustí – tzn, ty řádky které insertuje a již existují v tabulce to nelockne.

Synaxe je následující:

Takže ukázka, data stejná jako minule:

create table tbl_ignore_key as
select rownum as col_unique from dual connect by level<6;

 

Zase nějaký unikátní index:

create unique index inx_tbl_ignore_key_col_unique on tbl_ignore_key(col_unique);

A pokus o insert s hintem IGNORE_ROW_ON_DUPKEY_INDEX:

insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(tbl_ignore_key,inx_tbl_ignore_key_col_unique) */ into
tbl_ignore_key select rownum as col_unique from dual connect by level<11;
5 rows inserted.

Dokonce kdyby nikdo nevěřil, to mohu pustit znovu :

insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(tbl_ignore_key,inx_tbl_ignore_key_col_unique) */ into
tbl_ignore_key select rownum as col_unique from dual connect by level<11;
0 rows inserted.

Kdy mi to sice nic neinsertuje, ale nespadne na constrain, zatímco bez hintu:

insert into tbl_ignore_key
select rownum as col_unique from dual connect by level<11;
00001. 00000 - "unique constraint (%s.%s) violated"

Nebo nyní nově pro nás, co se učíme švédsky ORA-00001: brott mot unik begränsning ;). Takže tolik k tomuto hintu, ještě zbývají nějaká ta upozornění – pochopitelně nelze použít v kombinaci s APPEND hitem (který bude ignorován), odkazuje se tam na konkrétní objekt – což v hintu většinou typicky znamená, že hint na neexistující objekt vyfailuje celý insert, nelze použít v bukách a je to rychlejší než EXCEPTION klauzule.

 Posted by at 12:27
Nov 042014
 

Dneska jeden velice krátký tip, který však má své vlastní kouzlo. Funguje od PL/SQL Developer verze 9. Občas se v PL/SQL Developeru prostě hodí spustit více selectů/příkazů za sebou, nějak takto:

select * from dual;
select * from dual;
select * from dual;

(nepojmenované taby)

Funguje skvěle, PL/SQL Developer umístí výsledky do záložek. Co občas vadí je pojemování těchto záložek, v případě více selectů/příkazů se záložky jmenují podobně či dokonce stejně a to aby se v tom pak prase vyznalo;)

Takže co s tím? Komentář nad příkazem, který dokáže TAB s výsledkem pojmenovat:

--TAB=Select z dualu číslo 1 a žblunk
select * from dual;
--TAB=Select z dualu číslo 2 a hafík
select * from dual;
--TAB=Select z dualu číslo 3 a budlík
select * from dual;

(pojmenované taby)

 Posted by at 01:23
Oct 072014
 

Dnešní tip na hint, který dokáže trošku podpořit odhad cardinality estimátorem. Nevýhodou tohoto hintu je, že je nedokumentovaný, takže na produkci ne-e(ačkoliv vypadá bezpečně..). Což je velká škoda, protože jinak je to neuvěřitelně sexy hint.

Použití: Hint slouží k narovnání odhadu estimátoru (podobně jako nedokumentovaný /*+ cardinality */). Můžeme určit nejen cardinalitu u tabulky, ale dokonce i u joinu. A co více, existuje dokonce možnost odkazovat se násobky na to, co spočitá estimátor. A snad to nejlepší nakonec – lze zadat MINum a MAXimum. Prostě vymazlenej hint.

Syntaxe (s rezervou – z netu, není to dokumentované):

/*+ OPT_ESTIMATE( [query block] operation_type identifier adjustment ) */

Možné hodnoty :

operation_type = table | index_fliter | index_scan | index_skip_scan | join..

identfier = tab_alias@qrblk | tab_alias@qrblk index_name | JOIN(tab1_alias@qrblk tab2_alias@qrblk) s tím, že, query blok je nepovinný.

ajdusment  = rows -počet řádků, které očekáme | scale_rows - multiplikátor pro estimátor | min - minimální počet řádků, které očekáváme | max  maxmální počet řádků, které očekáváme

A nyní nějaké ty příklady, nejprve založme tabulku:

CREATE TABLE tbl_test AS SELECT ROWNUM AS sloupec FROM dual CONNECT BY LEVEL<784561;

A spočtěme statistiky (na 12C nemusím – ale pro lidi, co mají ještě 11.2 či hůře..)

BEGIN dbms_stats.gather_table_stats(null, 'tbl_test', method_opt => 'FOR ALL COLUMNS SIZE 1'); END;

Tabulka má přesně 784561 řádků. Takže odhad pro select count(*) from tbl_test je přesně 784561. Číslo, takhle hnusné, jsem vybral schválně ;), takže zkusme odhad pro select count(*) from tbl_test where ora_hash(id,5)=1. Zde se Oracle pekelně splete – defalutní selectivita na “=” pro filtrování pomocí funkce, tzn. where f(n)=x|const je 1%, tedy jeho odhad je 7846. Ve skutečnosti při náhodných datech ora_hash(id,5)=1 selectuje 1/6 tabulky (16,6%), protože máme rovnoměrně rozhozené hodnoty <0,5> a filtrujeme na =1.

SELECT count(*) FROM tbl_test WHERE ora_hash(sloupec,5)=1;

(predikát ora_hash(sloupec,5)=1)

Celkový vrácený počet řádků: 130521 (16,6%)
Odhad estimátoru: 7846 (1%)

A udělejme to ještě jednou, ještě jednou přidejme filtrování funkcí ať je Oracle hodně vedle:

SELECT count(*) FROM tbl_test WHERE ora_hash(sloupec,5)=1 AND ora_hash(sloupec,5)+1=2;

Přidání predikátu ora_hash(id,5)+1=2 (nemohu znovu ora_hash(id,5)=1, doublování predikátů Oracle pozná, tak jednoduše se ošálit nenechá), pro Oracle znamená znovu selectivitu 1%. Takže celkový odhad estimátoru je 0.01*0.01* num_rows=78 +/- zaokrouhlení. Z logického hlediska je to po odečtení 1 na obou stranách naprosto stejný predikát jako ora_hash(id,5)=1. Tedy stále selectujeme stále 1/6 tabulky (16,6%), podmínku jsme pouze zdvojili.

(preditkát ora_hash(sloupec,5)=1 v and s tím samým predikátem)

Celkový vrácený počet řádků: 130521 (16,6%)
Odhad estimátoru: 78 (0.0001%)

Takže spočítejme kolikrát více řádků opravdu vracíme o proti estimátoru – select ((1/6)/0.0001) from dual – 1666.6 násobek. A nyní k našemu vytouženému hintu:

SELECT /*+ OPT_ESTIMATE(TABLE a SCALE_ROWS=1667) */ count(*) FROM tbl_test2 a WHERE ora_hash(id,5)=1 AND ora_hash(id,5)+1=2;

(s predikátama a hintem opravený odhadem estimátoru)

Nyní je odhad 130786 proti skutečnému 130521 (jsme o 0,2% vedle, not bad). Výhoda tohoto hintu o proti /*+ cardinality */ je, že je to datově citlivé – přes odhad estimátoru.

Takže po té, co vím, že pro predikát “ora_hash(id,5)=1 and ora_hash(id,5)+1=2″ je potřeba magické číslo 1667 můžu to udělat s jinou tabulkou znovu:

CREATE TABLE tbl_test2 AS SELECT 'ABCD_'||ROWNUM as sloupec FROM dual CONNECT BY LEVEL<=100000;
BEGIN dbms_stats.gather_table_stats(null, 'tbl_test2', method_opt => 'FOR ALL COLUMNS SIZE 1'); END;

SELECT /*+ OPT_ESTIMATE(TABLE a SCALE_ROWS=1667) */ count(*) FROM tbl_test2 a WHERE ora_hash(id,5)=1 AND ora_hash(id,5)+1=2;

(odhad s hintem na jiné tabulce se stejným predikátem)

Celkový vrácený počet řádků: 16716 (cca (1/6)*100000)
Odhad estimátoru s hintem:   16670 (0.01*0.01*100000*1667)
Odhad estimátoru bez hintu: 10 (0.01*0.01*100000)

Myslím, hezký příklad na úpravu odhadu estimátoru. Hint má navíc i takové možnosti jako MIN a MAX. Speciálně to MIN je zajímavé, vzhledem k tomu, že většinou dochází k podhodnceni počtu řádků, které Oracle odhadne o proti skutečnosti. Myslím, že se asi každému z nás stává, že Oracle odhadne 1 (ve skutečnosti spočítá 0, ale až na menší vyjímky vždy raději počitá s tím, že se vrátí alepsoň jeden řádek – a to i za nesmyslných podmínek, aby to mohl propasovat do dalšího stupně exekučního plánu) a nakonec je tam řádků asi tak přesně bžlilion.. takže timto hintem se mu dá vnutit, že prostě musí počítat s tím, že tam minimálně xxx řádků mít může ;) Prostě sexy hint.

A ještě nějaký krátký příklad na join:

SELECT /*+ OPT_ESTIMATE(JOIN, (a, b), ROWS=15) */ * FROM dual a join dual b on (a.dummy=b.dummy);

(oprava cardinality joinu hintem)

To by bylo vše k tomuto pozoruhodnému, leč nedokumentovanému, hintu.

 Posted by at 21:59
Mar 092013
 

Dnešní rychlotip s číslem 20 je věnován vcelku neznámému, ale o to více užitečnému, příkazu CREATE SCHEMA.  Možná to má lehce matoucí název – nemá to relaci na vytvoření schéma jako takového a klasicky schéma je vytvořeno pomocí CREATE USER. Příkaz CREATE SCHEMA slouží k vytvoření několika objektů (tabulek, view a grantů) v jedné transakci. Což se typcky hodí při nějakém updatu systému/nasazení RFC – kdy je třeba nasadit více tabulek – pomocí CREATE SCHEMA je od Oracle zaručeno, že to proběhne “jako transakce” – tedy všechno nebo nic. Normálně je CREATE TABLE příkaz typu DDL, takže se při releasu může povéct založit jen nějaké tabulky a ostatní vyhodí chybu – což bohužel vykazuje nějaký effort na srovnaní nebo to všechno vydropovat, vylézt zpět na strom a spustit to celé znovu.

Tedy jednou větou – CREATE SCHEMA umí v jedné transakci založit více objektu v jedné transakci, konkrétně:

  • CREATE TABLE
  • CREATE VIEW
  • GRANT

Podporovaných DDL je opravdu málo a navíc neexistuje inverzní operace DROP SCHEMA, ač v SQL-1999 standard tento příkaz podporuje.

Jediné omezení je na CRETE TABLE – parallel je povoleno, ale ve skutečnosti parallně tabulky v CREATE SCHEMA nejsou zakládány a běží to seriově.

CREATE SCHEMA AUTHORIZATION dot
CREATE TABLE TEST_A (A NUMBER)
CREATE TABLE TEST_B as select 1 FROM DUAL
CREATE VIEW TEST_C as select * from all_objects
GRANT SELECT ON TEST_A TO SCOTT;

Synaxe je jednoduchá CREATE SCHEMA AUTHORIZATION <user_schema> a pak seznam tabulek, view a grantu bez středníku, středník až na konci, který zavírá statement CREATE SCHEMA.

Takže po spuštění:

SQL Error: ORA-02425: nepodařilo se vytvořit tabulku
ORA-00998: tento výraz je třeba pojmenovat alternativním jménem sloupce
02425. 00000 - "create table failed"

Protože jsem jelito a hajdalák a v TEST_B selectuji jedničku z dualu a nedal jsem tam alias, nicméně alespoň důkaz, že opravdu CREATE SCHEMA funguje a nezaložila se tabulka TEST_A ani nic jiného:

Create schema - Fail

Create schema - Fail

Takže po opravě:

CREATE SCHEMA AUTHORIZATION dot
CREATE TABLE TEST_A (A NUMBER)
CREATE TABLE TEST_B as select 1 as haf FROM DUAL
CREATE VIEW TEST_C as select * from all_objects
GRANT SELECT ON TEST_A TO SCOTT;

Je výstup následující:

schema AUTHORIZATION created.

A nyní test, že se všechno založilo i nagrantovalo:

Create schema- Success

Create schema- Success

Pro nasazovací scripty šikovná věcička, co?

 Posted by at 23:08
Feb 162013
 

Minutý rychlotip byl o funkci DBMS_FREQUENT_ITEMSET.FI_HORIZONTAL, která vytvoří z hodnot sloupců všechny možné množiny a spočítá jejich výskyt zkrt všechny řádky – funkce zajimavá, o praktickém použití v denním programování to však není. Balík DBMS_FREQUENT_ITEMSET obsahuje ještě jednu pipelined funkci - FI_TRANSACTIONAL.  Signatura funkce je stejná jako u FI_HORIZONTAL:



DBMS_FREQUENT_ITEMSET.FI_TRANSACTIONAL (
 tranx_cursor IN SYSREFCURSOR,
 support_threshold IN NUMBER,
 itemset_length_min IN NUMBER,
 itemset_length_max IN NUMBER,
 including_items IN SYS_REFCURSOR DEFAULT NULL,
 excluding_items IN SYS_REFCURSOR DEFAULT NULL)
RETURN TABLE OF ROW (
 itemset [Nested Table of Item Type DERIVED FROM tranx_cursor],
 support NUMBER,
 length NUMBER,
total_tranx NUMBER);

Zatímco první funkce FI_HORIZONTAL tvoří množiny všech množin z hodnot ve sloupích a výskyt množin pak počítá skrz řádky, FI_TRANSACTIONAL vypadá, že by se mohla chovat opačně, respektive být otočená o 90% – tvořit množiny všech množin zkz sloupce a počítat je zkz řádky – není tomu tak! Narozdíl od FI_HORIZONTAL kam může vstupovat select, který má v projection libovolný počet sloupců (ale stejného typu) do FI_TRANSACTIONAL může vstupovat do parametru tranx_cursor pouze select jež má v projection pouze dva sloupce  – (number,<nějaký_jednoduchý_typ>), kde první sloupce typu number určuje skupinu zkz kterou se mají generovat všechny množiny a ve druhém jsou hodnoty.

Takže go, hébičky go, pojďme si to zkusit:
Tabulka je velice podobná jako zde, s tím rozdílem, že jsem se rozhodl přidat ještě jeden sloupec – name_6:

create table tbl_drks
(name_1 varchar2(20),
name_2 varchar2(20),
name_3 varchar2(20),
name_4 varchar2(20),
name_5 varchar2(20),
name_6 varchar2(20));

A nyní insert nějakých dat (stejné jako minule + sloupec name_6):

insert into tbl_drks values('RedBull','BigShock','RockStar',null,'Monster','A');
insert into tbl_drks values('BigShock','RedBull',null, null,'Monster','B');
insert into tbl_drks values('BigShock','RedBull','BigShock',null,'RedBull','C');
insert into tbl_drks values('BigShock','RedBull',null, null,'Monster','D');
insert into tbl_drks values('Monster','RedBull',null, null,'Monster','E');
insert into tbl_drks values('Monster','RedBull','RockStar','BigShock','Monster','F');
insert into tbl_drks values('RockStar','RedBull','BigShock','BigShock','Monster','G');
insert into tbl_drks values('RockStar','RedBull','RockStar','RockStar','Monster','H');
commit;

Což v tabulce pak vypadá nějak takto:

Vstupní data pro DBMS_FREQUENT_ITEMSET.fi_transactional

Funkce DBMS_FREQUENT_ITEMSET.fi_transactional povoluje tedy jako vstup select s projection se sloupcem typu number a druhým sloupcem s hodnotou, tedy nebude stačit jen select * from tbl_drinks, ale je třeba to rozdělit po skupinách – každý sloupec jako jiná skupina, nejjednodušeji tedy pomocí union all:

SELECT CAST(itemset as typ_name)itemset, support, length, total_tranx
FROM table(DBMS_FREQUENT_ITEMSET.FI_TRANSACTIONAL(
 CURSOR(SELECT 1 as skupina_1,name_1 FROM TBL_DRKS
  union all
  SELECT 2 as skupina_2,name_2 FROM TBL_DRKS
   union all
  SELECT 3 as skupina_3,name_3 FROM TBL_DRKS
   union all
  SELECT 4 as skupina_4,name_4 FROM TBL_DRKS
   union all
  SELECT 5 as skupina_5,name_5 FROM TBL_DRKS),
0,1,8));

Výsledky dbms_frequent_itemset.fi_transactional

Příklad intepratace výsledku - třeba jednoprvková množina (Monster), která se vyskytuje ve dvou sloupcích( name_1 a name_5) a proto má support 2, jedná se o jednoprvkovou množinu, proto je length 1 a konečně počet skupin (name_1..name_5) bylo 5 tedy total_tranx 5. Parametr 0 řiká, že neomezujeme výskyt na žádné minumum, parametr 1 říká, že minimální množina je jednoprvková a parametr 8, že maximální dovolená množina je 8 prvková.

Poslední sloupec – name_6 jsem jako další skupinu dal až v následujícím selectu:

Ukázka výsledku DBBMS_FRQUENT_ITEMSET.fi_transactional - count

Ukázka výsledku DBBMS_FRQUENT_ITEMSET.fi_transactional - count

Proč pouze count? Neb mimo 15 množin, které vznikly ze sloupců name_1..name_5 jsou tu ještě monžiny, které vzniknou z 8 řádků slupce name_6. A vzhledem k hodnotám A,B,C,D,E,F,G,H vznikne vcelku dost rozdílných množin: (A),(A,B) .. Konkrétně by to měly být všechny jednoprovkové, dvojprvkové, tříprvkové.. něco takového:

Počet všech možných množin z prvků A,B,C,D,E,F,G,H

Počet všech možných množin z prvků A,B,C,D,E,F,G,H

Na spočítání takovéhleho výrazu je třeba umět spočítat faktorial:

create or replace function factorial(a number) return number
 is
  ret number:=1;
 begin
 if a=0 then return 1; end if;
 for i in 1..a loop ret:=ret*i; end loop;
 return ret;
 end;

Ok, není to zrovna hezky napsaná funkce, nicméně umožní nám to spočítat výsledek:

select sum(factorial(8)/(factorial(rownum)*factorial(8-rownum))) from dual connect by level<=8;
255

Což v kombinaci s počtem množin vzniklých z name_1..name_2 – 15 dává dohromady 15+255=270. Tedy funguje dle očekávání trhu ;)
To by bylo asi vše k DMBS_FREQUENT_ITEMSET.fi_transactional – všechno ostatní funguje jako u fi_horizontal – opakující prvky se nepočítají (tzn, každá z 255 množin ze sloupce name_6 bude mít support=1) a je možné postrčit jako nepovinný parametr hodnoty, které být musí v množinách a naopak se zbavit množiny které mají nějaký námi určený prvek. A podobně jako předchozí rychlotip, exekuční plán:

Exekuční plán pro dbms_frequent_itemset.fi_transactional

Exekuční plán pro dbms_frequent_itemset.fi_transactional

 Posted by at 12:03
Feb 072013
 


Dnešní rychlotip se týká šikovné funkce, která jako vstup dostane jména dvou objektů a vrátí takové statementy, které alterují objekt číslo jedna takovým  způsobem, aby se z něj stal strukturně stejný objekt číslo dva. Tato báječná  funkce se jmenuje COMPARE_ALTER a je z package DBMS_METADATA_DIFF.

Signatura funkce je následující:

   FUNCTION compare_alter (
                object_type     IN VARCHAR2,
                name1           IN VARCHAR2,
                name2           IN VARCHAR2,
                schema1         IN VARCHAR2 DEFAULT NULL,
                schema2         IN VARCHAR2 DEFAULT NULL,
                network_link1   IN VARCHAR2 DEFAULT NULL,
                network_link2   IN VARCHAR2 DEFAULT NULL)
        RETURN CLOB;

 Myslím, že názvy parametrů mluví za vše – typ objektu (TABLE, INDEX..) jejich jména a kde jsou umístěny (schéma a případně databázový link).

 1) Založme dvě malé, leč rozlišné tabulky:

   create table tbl_a(a number,
   b varchar2(10) not null);

 create table tbl_b(a number not null,
  b varchar2(10),
  c number primary key);

2) Zavolání funkce compare_alter
select dbms_metadata_diff.compare_alter('TABLE','TBL_A','TBL_B') from dual;

Výsledek:
ALTER TABLE "AZOR"."TBL_A" ADD ("C" NUMBER)
ALTER TABLE "AZOR"."TBL_A" MODIFY ("A" NOT NULL ENABLE)
ALTER TABLE "AZOR"."TBL_A" MODIFY ("B" NULL)
ALTER TABLE "AZOR"."TBL_A" ADD PRIMARY KEY ("C") USING INDEX PCTFREE 10 INITRANS 2 ENABLE
ALTER TABLE "AZOR"."TBL_A" RENAME TO "TBL_B"

3) Nyní aplikace (mimo přejmenování na stejný název) získaných ALTER statementů:

table TBL_A altered.
table TBL_A altered.
table TBL_A altered.
table TBL_A altered.

 4) A ukázka toho, že jsou objekty stejné:

Stejná struktura tabulek

Stejná struktura tabulek

A jeden tip na využití:
Pokud kopírujeme strukturu tabulky, tak typicky používáme CTAS + nesplnitelnou podmínku:

create table tbl_c as select * from tbl_a where 1=2;

Což bohužel nezachovává všechny constrainty, tohle ano.
Šikovná věcička, co? Bohužel – triggery to neumí, indexy to neumí a co hůře – občas to vyhodí nevalidní statement (prázdné závorky u storage, například).

 Posted by at 17:52
Jan 302013
 

Po další době nějaký ten rychlotip, protože poslední rychlotip číslo #15 byl transformace sloupců do řádků je logické následovat opačnou tranformací – tedy transformace ze řádků do sloupců.

Nechť je jako vstup výstup z předchozího rychlotipu:

Transformace řádků do sloupců

Pro transformaci slouží klauzule PIVOT:

Transformace řádků do sloupců
Syntaxe je následující:
SELECT * FROM TABLE
PIVOT ( <agregační funkce>(<pro který sloupec>)
FOR IN (hodnoty v řádcích, případně s aliasem)
)

Pokud řádek obsahuje uvedenou hodnotu, je transformována do sloupce, ostatní hodnoty v řádích, kde byly jiné hodnoty jsou však, null. Takže je vhodné výsledek nakonec nějak vykrášlit, například zgrupovat:

Transformace řádků do sloupců

 Posted by at 00:00