Builder.cz - Informacni server o programovani

Odběr fotomagazínu

Fotografický magazín "iZIN IDIF" každý týden ve Vašem e-mailu.
Co nového ve světě fotografie!

 

Zadejte Vaši e-mailovou adresu:

Kamarád fotí rád?

Přihlas ho k odběru fotomagazínu!

 

Zadejte e-mailovou adresu kamaráda:



Oracle: pomaly select s pouzitim WITH klauzuly

Seznam témat     Nová odpověď

Přihlásit se     Registrace     Zapomenuté heslo

Re: Oracle: pomaly select s pouzitim WITH klauzuly

Autor: metalpalo

14:40:26 12.08.2011

RomanZ Napsal:

> ...tu podminku napis do JOINovani, priblizne
> takto>
>
> SELECT *
> FROM OperTab StartTab
> JOIN OperTab EndTab ON (StartTab.State = 1) AND
> (EndTab.State = 2) AND (StartTab.nejakeID =
> EndTab.nejakeIDpodleKterehoSeJoinuje)
>
> ...da se tim presvedcit, aby nespojoval radky,
> ktere stejne ve vysledku nechces. Pokud ty
> podminky napises az za WHERE, vyhodnocuji se asi
> az po spojeni tabulek, tzn. nad velkym mnozstvim
> dat, a to je pomale.

Vyskusol som tieto tri sposoby na realny datach:
SELECT *
FROM OperTab StartTab JOIN OperTab EndTab
ON StartTab.OperationID = EndTab.OperationID WHERE (StartTab.State = 1) AND (EndTab.State = 2) AND...

SELECT *
FROM OperTab StartTab JOIN OperTab EndTab
ON (StartTab.State = 1) AND (EndTab.State = 2) AND StartTab.OperationID = EndTab.OperationID WHERE ...

SELECT *
FROM OperTab StartTab, OperTab EndTab
WHERE (StartTab.State = 1) AND (EndTab.State = 2) AND StartTab.OperationID = EndTab.OperationID AND ...

a v kazdy dava identicky exekucny plan s rovnakou cenou

To: David.Krch
dalej co sa tyka nahradenia vyrazu 'State BETWEEN 1 AND 2
tak som vyskusal tieto dva:
State IN (1,2) - horsi vysledok
State >=1 and State <=2 - lepsi vysledok

Citovat příspěvek

 

Re: Oracle: pomaly select s pouzitim WITH klauzuly

Autor: sthruska

7:06:37 12.08.2011

Prílohy nefungujú už dlhý čas.

Citovat příspěvek

 

Re: Oracle: pomaly select s pouzitim WITH klauzuly

Autor: metalpalo

23:43:25 11.08.2011

To:David.Krch

dovolil som si to poslat na Vas email, kedze to nejde alebo to neviem uploadnut

s pozdravom

Citovat příspěvek

 

Re: Oracle: pomaly select s pouzitim WITH klauzuly

Autor: metalpalo

23:35:34 11.08.2011

skusil som to uploadnut este raz

Citovat příspěvek

 

Re: Oracle: pomaly select s pouzitim WITH klauzuly

Autor: David.Krch

23:21:03 11.08.2011

Nejak se nemuzu dostat do te prilohy, jedine co stahnu, je textak s "404 soubor nenalezen".

Citovat příspěvek

 

Re: Oracle: pomaly select s pouzitim WITH klauzuly

Autor: RomanZ

16:02:44 11.08.2011

Mozna by pomohlo preformulovat dotaz. Nenapisu to syntakticky spravne, ale snad bude jasny princip:

Misto abys dal podminku nakonec, jak ji mas ve svem prvnim prispevku:

SELECT * FROM OperTab StartTab JOIN OperTab EndTab USING (OperationID)
WHERE StartTab.State = 1 AND EndTab.State = 2
AND OperationID = 100


...tu podminku napis do JOINovani, priblizne takto>

SELECT *
FROM OperTab StartTab
JOIN OperTab EndTab ON (StartTab.State = 1) AND (EndTab.State = 2) AND (StartTab.nejakeID = EndTab.nejakeIDpodleKterehoSeJoinuje)

...da se tim presvedcit, aby nespojoval radky, ktere stejne ve vysledku nechces. Pokud ty podminky napises az za WHERE, vyhodnocuji se asi az po spojeni tabulek, tzn. nad velkym mnozstvim dat, a to je pomale.

Nevim jestli tento postup plati obecne vzdy, nebo jestli si s tim hraje optimalizator - musis si to vyzkouset na realnych datech, jestli to opravdu pomaha. U mne to pomahalo hodne.

Citovat příspěvek

 

Re: Oracle: pomaly select s pouzitim WITH klauzuly

Autor: sthruska

7:19:32 11.08.2011

OT: V slovenčine je ten kláves, variant a komponent.

Citovat příspěvek

 

Re: Oracle: pomaly select s pouzitim WITH klauzuly

Autor: metalpalo

20:36:20 10.08.2011

zabudol som povedat ze priponu suboru treba premenova na zip

Citovat příspěvek

 

Re: Oracle: pomaly select s pouzitim WITH klauzuly

Autor: metalpalo

20:33:30 10.08.2011

Zdravim

tak som vyskusal spustit tie explain plany ale musel som to urobit na realnych tabulkach a nie na tej ktoru som uvadzal ako priklad(Operation) v selecte. V podstate sa vyberam data z dvoch tabuliek tym istym sposobom ako uvadzam v priklade vyssie. Jedna sa o dve tabulky SumRecordAction a DeviceCounterSum ktore prepajam stlpacami (DeviceClassID,DeviceID,UniqueMSID,SumRecActEvSequNo, a este CorrectionCounter co musi byt 0)ktore je vidiet z prilozenych scriptov. V podstate sa to da predstavit ako povodna tabulka Operation kde stplec ActionCode predstavuje Operation.State. V selectoch su pouzite klauzuly DISTINCT pretoze riadky ktore vyberam z tych tabuliek su po dva krat(dvakrat pre ActionCode=1 a taktiez pre ActionCode=2). Stlpcec OperationID si mozte predstavit ako trojicu DeviceClassID,DeviceID,UniqueMSID. Dufam ze je to dost jasne.

V prilohe posielam nasledovne subory:
-1.variant.sql - varianta s pouzitim WITH
-2.variant.sql - Varianta s vnorenymi selektam
-3.variant.sql - Varianta bez vnorenych selektov(klasicke joiny) :
SELECT Op1.*,Op2.* FROM Operation Op1,Operation Op2 WHERE Op1.OperationID = Op2.OperationID AND Op1.State = 1 AND Op2.State = 2

dalej exekucne plany v suboroch:
-1.explainPlan.jpg/txt - pre 1.variant.sql
-2.explainPlan.jpg/txt - pre 2.variant.sql
-3.explainPlan.jpg/txt - pre 3.variant.sql
-1.explainPlan_filter.jpg/txt - pre 1.variant.sql s odkomentovanymi dvoma riadkami na konci(konretne zariadenie)
-2.explainPlan_filter.jpg/txt - pre 2.variant.sql s odkomentovanymi dvoma riadkami na konci(konretne zariadenie)
-3.explainPlan_filter.jpg/txt - pre 3.variant.sql s odkomentovanymi dvoma riadkami na konci(konretne zariadenie)
-1.explainPlan_filter_ext.txt - pre 1.variant.sql s odkomentovanymi dvoma riadkami na konci(konretne zariadenie)
-2.explainPlan_filter_ext.txt - pre 2.variant.sql s odkomentovanymi dvoma riadkami na konci(konretne zariadenie)
-3.explainPlan_filter_ext.txt- pre 3.variant.sql s odkomentovanymi dvoma riadkami na konci(konretne zariadenie)

Poznamenavam ze dodatocne statistiky(ext) subory bez filtra mi neslo vykonat kvoli velkemu poctu zaznamom tak
posielam len pre konkretne zariadene(deviceid = 5001, deviceclassid = 22)

-vysledky trvania selectov su v subore duration.txt
-subor 1.explainPlan_inInsteadOfBetween.jpg obsahuje plan v pripade nahradenia BETWEEN 1 AND 2 vyrazom IN (1,2)

Teraz moje cudne dojmy z tychto vysledkov:
1, rozdiel trvania selektu prveho variantu s filtrom a bez je minimalny a plan je este drahsi.
V com je asi hlany problem? Cakal som ze s filtrom to bude rychlejsie aj lacnejsie ako v druhych dvoch variantach.

2, trvanie selectu tretej varianty bez filtra je najdlhsie pricom exekucny plan je najlepsi?
Co zato moze? Ze sa spaja velke mnoztvo riadkov z ktorych sa az nakoniec odstrania duplicity?

Na zaver mam jednu otazku:
Cim sa mam v buducnosti riadit? Cenou planu lebo ako som zistil nie vzdy plati ze vykonavaci cas je najkratsi.
Mam vyuzivat vnorene selecty alebo klasicke joiny ako v t sa retej variante?

inak velka vdaka vsetkym za pomoc, zase som sa daco naucil

Citovat příspěvek

 

Re: Oracle: pomaly select s pouzitim WITH klauzuly

Autor: grafnev

12:40:52 10.08.2011

Exekucni plan je samozrejme spravne reseni, ale takhle pohledove me napadl jeden zasadni rozdil:
V dotazu cislo 1 joinujete proti sobe tabulky pravdepodobne 2x vetsi (v obou jsou start i end zaznamy) nez v druhem dotazu ... nejsem si jisty, zda optimizer toto pochopi. Predpokladam, ze stroj nejprve vyrobi kartezem 4 zaznamy a pak z nich teprve vybere ten ktery ma v prvni tabulce start a v druhe tabulce end.

Konkretne vam nepomuzu delam na teradate a tam optimalizace a exekucni plany funguji jinak nez na jinych strojich (respektive sleduji se trochu jine veci) ... dokonce teradata tvrdi, ze pokud dotaz vraci stejny vysledek povede vzdy na stejny exekucni plan ... ale nevim

Citovat příspěvek

 

Re: Oracle: pomaly select s pouzitim WITH klauzuly

Autor: David.Krch

20:44:17 09.08.2011

K tomu exekucnimu planu - aneb zjistit jak vlastne databaze vas dotaz zpracovava.
Pokud pouzivate Oracle SQL Developer, pak je nejjednodussi dat dotaz do SQL Worksheetu a kliknout na ikonku Explain plan (4. ikonka v SQL Worksheetu - vypada jako soudek s hierarchii hnedych obdelnicku).

Pokud pouzivate SQL Plus, tak muzete zkusit:
[code]
EXPLAIN PLAN FOR ;
select * from table(dbms_xplan.display());
[/code]

Nebo druha varianta, ktera vam da jeste o trochu vice informaci (roztahnete si terminalove okno at vam zobrazi vice sloupcu - treba 120, pak spustte:
[code]
set linesize 120
select /*+ gather_plan_statistics */ * from......
select * from table(dbms_xplan.display_cursor(format=>'LAST ALLSTATS'));
[/code]

(Do vaseho dotazu za uvodni slovo SELECT vlozte hint /*+ gather_plan_statistics */ a spustte dotaz.)
Druhy prikaz pak vypise exekucni plan, ale zobrazi navic udaje:
E-Rows - optimalizatorem ocekavany pocet zaznamu v dane operaci
A-Rows - skutecny pocet zaznamu, ktere prosly operaci.
DBMS_XPLAN v takto jednoduche podobe, kdy zobrazuje informace o poslednim dotazu v session, nejde moc dobre pouzit v SQL Developeru - IDE si do spojeni posila ruzne dalsi dotazy - nacteni seznamu tabulek atd. - a casto tak dostanete exekucni plan jineho dotazu, nez ktery jste chtel).

Obyklym problemem vykonu dotazu je, ze optimalizator spatne odhadne pocet zaznamu, ktere splni urcitou podminku. - tj. na nektere radce exekucniho planu se vam treba i o nekolik radu rozejde E-Rows a A-Rows. A obvykle byva duvodem to, ze databaze nema k dispozici aktualni statistiky o dane tabulce/sloupci.
Zkuste to pustit pro ty vase dva dotazy a vysledek sem hodte.

Pokud jde o vasi obavu, ze WITH nepouziva index, tak tak to neni. Ze zapisu SQL prakticky nikdy nemuzete rict, co databaze presne udela a jake indexy pouzije ci nepouzije - zalezi na mnozstvi dat v tabulkach a rade jinych udaju, podle kterych se optimalizator rozhodne jaky postup zvoli. Optimalizator se take rozhodne, jestli ten "pojmenovany subselect" ktery definujete pomoci WITH nejprve zacleni do vlastniho tela dotazu, ev. do nej presune podminky z nadrizeneho dotazu a pak teprve vse provede, nebo zda proste poddotaz provede, ulozi vysledky do docasneho objektu a pak teprve provede hlavni dotaz.

Ve vasem pripade jsem si ale jeste vsiml jedne drobnosti
[code]WITH OperTab AS (SELECT OperationID,State,Value FROM Operation WHERE State BETWEEN 1 AND 2) [/code]
Jak se vykon prvniho dotazu zmeni, pokud misto "state BETWEEN 1 AND 2" pouzijete "state IN (1,2)". V zavislosti na datovem typu state, constraintech a statistikach je mozne, ze se databaze domniva, ze zatim co state=1 a state=2 vraci par zaznamu, tak state between 1 and 2 vraci spoustu zaznamu (protoze ocekava treba state=1.3,1.42,1.94442 apod.).

A jak se vykon zmeni, pokud do subselectu vubec tu podminku neuvedete a nechate to jen na nadrizenem dotazu? Oracle by mel byt schopen tu podminku z nadrizeneho dotazu propagovat do toho podrizeneho. Je mozne (ale je to jen nicim nepodlozena domnenka) ze se rozhodne to neudelat, protoze uz v tom podrizenem dotazu ma pro dany sloupec jinou podminku). Ale to jsou vsechno jen domnenky, poslete exekucni plany a uvidime.

Citovat příspěvek

 

Re: Oracle: pomaly select s pouzitim WITH klauzuly

Autor: metalpalo

16:22:52 09.08.2011

Zabudol som podotknut ze cast WHERE klauzuly OperationID = 100 je nahradna filtrovacia podmienka. Ta sa bude spajat s dalsimi tabulky v mojom velkom SELECTE. preto to nemozem davat dnu ako ste napisali. Predstavte si to bez nej, ze tam bude nejaka join podmienka napr OperationID = DeviceOperation.OperationID AND ......

Citovat příspěvek

 

Re: Oracle: pomaly select s pouzitim WITH klauzuly

Autor: vlnk

15:28:25 09.08.2011

select a.*,b.* from operation a, operation b
where a.OperationID = 100 and a.State=1 and b.OperationID = 100 and b.State=2

Citovat příspěvek

 

Re: Oracle: pomaly select s pouzitim WITH klauzuly

Autor: metalpalo

15:24:45 09.08.2011

no to by slo ak by tam bol UNION ale nie JOIN

Citovat příspěvek

 

Re: Oracle: pomaly select s pouzitim WITH klauzuly

Autor: xyz3

15:21:57 09.08.2011

> Viete mi niekto povedat preco je ten prvy select taky pomaly.

To ti řekne sám Oracle, hledej EXPLAIN PLAN.

> Vie mi niekto povedat v com je hlavny problem?

Optimizer nepochopil správně jaký je tvůj úmysl.
Zkus to takhle jako jeden záznam:

select *
from
(SELECT OperationID,State,Value FROM Operation WHERE OperationID=100 and State = 1) A
full outer join
(SELECT OperationID,State,Value FROM Operation WHERE OperationID=100 and State = 2) B
on A.OperationID=B.OperationID

nebo takhle jako dva záznamy

SELECT OperationID,State,Value
FROM Operation
WHERE OperationID=100 and ((State = 1) or (State = 2))

Citovat příspěvek

 

Re: Oracle: pomaly select s pouzitim WITH klauzuly

Autor: sthruska

14:58:07 09.08.2011

Poradiť neviem, ale nebolo by toto

(SELECT OperationID,State,Value FROM Operation WHERE State = 1)
JOIN
(SELECT OperationID,State,Value FROM Operation WHERE State = 2)

lepšie takto

(SELECT OperationID,State,Value FROM Operation WHERE State = 1
or
State = 2)

Citovat příspěvek

 

Oracle: pomaly select s pouzitim WITH klauzuly

Autor: metalpalo

14:49:16 09.08.2011

Zdravim

Snazim sa odladit jeden SELECT nad mojou databazou. Tento query pracuje s jednou tabulkou OPERATION (primarny kluc: OperationID,State) s snazi sa vypisat hodnoty operacie v stave 1(start) a 2(end). Tabulka je fiktivna a query nad nou predstavuje v podstate to co vykonavam nad realnymi datami(tabulkami). Tie vsak neuvadzam pre jednoduchost.

Moj priklad spociva v tom ze tabulku Operation mam dvakrat vo klauzule FROM pre porovnanie hodnot ako spominam vyssie:

Tu je su dve varianty mojho selectu:
1.Varianta s klauzulou WITH:
WITH OperTab AS (SELECT OperationID,State,Value FROM Operation WHERE State BETWEEN 1 AND 2)
SELECT * FROM OperTab StartTab JOIN OperTab EndTab USING (OperationID)
WHERE StartTab.State = 1 AND EndTab.State = 2
AND OperationID = 100

2.Varianta s vnorenymi selektami:
SELECT * FROM
(SELECT OperationID,State,Value FROM Operation WHERE State = 1)
JOIN
(SELECT OperationID,State,Value FROM Operation WHERE State = 2)
USING (OperationID)
WHERE OperationID = 100

Najprv som sa snazil pouzit prvu variantu ale zdalo sa mi ze trva prilis dlho
(cca 5 sekund). Potom som to prepisal na druhy priklad a vysledok bol 0,07sekundy.

Viete mi niekto povedat preco je ten prvy select taky pomaly. Co sa tam najskor vykonava, ten join alebo filtrovanie podla where klauzuly? Dalej som zistil ze ked odstranim posledny riadok z druheho prikladu tak trvanie je priblizne rovnake ako prvy variant. Z toho sa mi zda ako keby sa najprv spravilo spajanie a potom filtrovanie podla WHERE. Nemam vedomosti ako vlastne Oracle vyhodnocuje SELECTY ale hrozne by ma zaujimalo ako to vlastne je.

Vie mi niekto povedat v com je hlavny problem? Cital som ze WITH pracuje bez indexov. Moze byt aj to problem?

Citovat příspěvek

 

 

 

Přihlášení k mému účtu

Uživatelské jméno:

Heslo: