GoldenLine
Zaloguj się

konto usunięte

Temat: Oracle 10g - problem z INSERTEM; ORA-01427

Witam,

mam zapytanie, które składa się m.in. z kilku podzapytań.

SELECT na tym zapytaniu wykonuje się ~8 sekund (zapytanie na 2 tabelach; po ~1M rekordów każda) i zwraca oczekiwaną ilość rekordów (~800k).

Problem zaczyna się kiedy chcę te wyniki w jakikolwiek sposób zachować-gdziekolwiek.

Czy za pomocą INSERTA, czy za pomocą CREATE TABLE AS czy choćby zapis wyników do pliku (zapewne baza odczytuje/zapisuje w tych przypadkach dane w ten sam sposób) otrzymuję błąd:
ORA-01427:single-row subquery returns more than one row

Czy ktoś z Was ma może jakiś pomysł dlaczego tak się dzieje -> co można temu zaradzić?
Zapytanie było dość skomplikowane ale udało się je napisać. I kiedy myślałem, że już jestem blisko okazało się, że niekoniecznie - nie mogę wyników, które widzę na ekranie zapisać nigdzie (chyba, że do zeszytu ręcznie :))
Link do wypowiedziLink
Grzegorz D.

Grzegorz D. PL/SQL Developer

Temat: Oracle 10g - problem z INSERTEM; ORA-01427

Błąd wskazuje na to, że któreś podzapytanie zwraca więcej niż jeden wiersz tam, gdzie powinno zwrócić dokładnie 1.
Jesteś pewien, że select działa? Dostajesz na ekran wszystkie 800k wierszy bez błędu wykonania?
Zarzuć to zapytanie jak możesz.Grzegorz Drzymała edytował(a) ten post dnia 11.09.11 o godzinie 21:24
Link do wypowiedziLink

konto usunięte

Temat: Oracle 10g - problem z INSERTEM; ORA-01427

Grzegorz Drzymała:
Błąd wskazuje na to, że któreś podzapytanie zwraca więcej niż jeden wiersz tam, gdzie powinno zwrócić dokładnie 1.
Jesteś pewien, że select działa? Dostajesz na ekran wszystkie 800k wierszy bez błędu wykonania?
Zarzuć to zapytanie jak możesz.

No właśnie SELECT działa. Record count zlicza rekordy poprawnie. Na ekranie wyniki są poprawne, problem pojawia się dopiero przy próbie jakiegokolwiek zapisu/eksportu tych wyników.

Zapytanie wygląda w uproszczonej wersji:


SELECT a.pole1,

(SELECT b.pole2 FROM tab2 b WHERE b.klucz = a.klucz) pole2

FROM tab1 a

Powtórzę raz jeszcze: SELECT zwraca tyle rekordów ile oczekuje - wyniki na ekranie są poprawne, natomiast INSERT, CREATE TABLE AS, export do pliku zwraca błąd wymieniony w temacie.

Edit: formatowanie kodu.Rafał K. edytował(a) ten post dnia 11.09.11 o godzinie 21:35
Link do wypowiedziLink
Grzegorz D.

Grzegorz D. PL/SQL Developer

Temat: Oracle 10g - problem z INSERTEM; ORA-01427

Znacznie łatwiej by było, gdybyś wrzucił całe zapytanie.
Z tego co przedstawiłeś wynika, że masz zapytanie skorelowane, i ono zwraca więcej niż 1 wiersz co w takim przypadku jest błędem.
Nie bardzo wiem o jakim "record count" mówisz...
Jeżeli chodzi o zwykły count tego selecta to zauważ, że zapytanie


SELECT count(*) FROM (

SELECT a.pole1,

(SELECT b.pole2 FROM tab2 b WHERE b.klucz = a.klucz) pole2

FROM tab1 a )

wykona się poprawnie, natomiast sam SELECT może rzucić błędem przy dojechaniu w wyświetlaniu do feralnego przypadku. Dlatego tak się dopytuje, czy widzisz na ekranie WSZYSTKIE wyniki (800k wierszy)
Link do wypowiedziLink
Błażej Pastuszka

Błażej Pastuszka COMMIT.IT -
Właściciel,
konsultant Oracle

Temat: Oracle 10g - problem z INSERTEM; ORA-01427

Dlaczego nie zapiszesz tego:

SELECT a.pole1,b.pole2 FROM tab1 a, tab2 b WHERE b.klucz (+) = a.klucz

Z nim nie powinno już być takiego problemu, a na dodatek powinno szybciej
działać ...
Link do wypowiedziLink
Paweł Pasztaleniec

Paweł Pasztaleniec Lead Consultant -
CGI

Temat: Oracle 10g - problem z INSERTEM; ORA-01427

Witam,

Wydaje się, że żeby zrobić count(*) w podanym przypadku silnik bazy danych w ogóle nie musi zaglądać do tabeli TAB2, wystarczy skorzystać z indeksu klucza głównego (lub jakiegokolwiek unikatowego, a jeżeli go nie ma to bezpośrednio z tabeli) tabeli TAB1 i już zna liczbę rekordów, więc nie będzie błędu.

W przypadku wykonywania zapytania przez jakiegoś klienta i wyświetlania wyniku na ekranie zapewne jest wykorzystywany algorytm NESTED LOOPS przy złączeniu tabel, a klient wyświetla tylko początkowe rekordy z całego zestawu wynikowego.

Przy próbie wstawienia danych do tabeli lub zapisu do pliku pobierane są wszystkie rekordy i wtedy podzapytanie trafia na większą liczbę rekordów i zwraca: ORA-01427.

Rozwiązaniem jest przepisanie podzapytania na OUTER JOIN lub znalezienie zmultiplikoanego rekordu.

Znalezienie zmultiplikowanego rekordu:

SELECT COUNT(*), b.klucz
FROM tab2 B
WHERE EXISTS
(
SELECT 1
FROM tab1 a
WHERE a.klucz = b.klucz
)
GROUP BY b.klucz
HAVING COUNT(*) > 1

Wersja z OUTER JOIN:

SELECT a.pole1,
b.pole2
FROM tab1 a LEFT JOIN tab2 b ON a.klucz = b.klucz

W pierwszym przypadku należy poprawić dane żeby zapytanie działało, w drugim przypadku zapytanie będzie działać, ale pytanie jest na poziomie biznesowym, Czy zwracane dane są poprawne?

Pozdrawiam
Paweł Pasztaleneic

P.S.
Przepraszam za ewentualne literówki, ale piszę zupełnie na sucho.

EDIT:
właśnie literówka :-)Paweł Pasztaleniec edytował(a) ten post dnia 11.09.11 o godzinie 22:07
Link do wypowiedziLink

konto usunięte

Temat: Oracle 10g - problem z INSERTEM; ORA-01427

Grzegorz Drzymała:
Znacznie łatwiej by było, gdybyś wrzucił całe zapytanie.
Z tego co przedstawiłeś wynika, że masz zapytanie skorelowane, i ono zwraca więcej niż 1 wiersz co w takim przypadku jest błędem.
Nie bardzo wiem o jakim "record count" mówisz...
Po wykonaniu się SELECT'a zliczam rekordy z poziomu klienta bazy danych (Toad, SQL Navigator etc.)
Jeżeli chodzi o zwykły count tego selecta to zauważ, że zapytanie


SELECT count(*) FROM (

SELECT a.pole1,

(SELECT b.pole2 FROM tab2 b WHERE b.klucz = a.klucz) pole2

FROM tab1 a )

wykona się poprawnie, natomiast sam SELECT może rzucić błędem przy dojechaniu w wyświetlaniu do feralnego przypadku.

Być może jest tak w przypadku nie SELECTa a INSERTa.
Dlatego tak się dopytuje, czy widzisz na ekranie WSZYSTKIE wyniki (800k wierszy)

Wydaje mi się, że jest tak, że Select zwraca na ekran jedynie pierwsze 500 rekordów, jak chcesz zobaczyć kolejne 500 to całe zapytanie jest przeliczane ponownie itd.
Odpowiadając na Twoje pytanie: nie widziałem na ekranie wszystkich wyników tylko pierwsze 500.
Link do wypowiedziLink
Izabela Korzińska

Izabela Korzińska Architekt /
Developer ETL/TEam
Leader, Roche Polska

Temat: Oracle 10g - problem z INSERTEM; ORA-01427

i o niebo lepiej count(1), niż count(*)
Link do wypowiedziLink
Łukasz Kurowski

Łukasz Kurowski Usque Ad Finem

Temat: Oracle 10g - problem z INSERTEM; ORA-01427

Rafał K.:
Zapytanie wygląda w uproszczonej wersji:


SELECT a.pole1,

(SELECT b.pole2 FROM tab2 b WHERE b.klucz = a.klucz) pole2

FROM tab1 a

Powtórzę raz jeszcze: SELECT zwraca tyle rekordów ile oczekuje - wyniki na ekranie są poprawne, natomiast INSERT, CREATE TABLE AS, export do pliku zwraca błąd wymieniony w temacie.

Puść zapytanie:


SELECT b.klucz, count(*) FROM tab2 b GROUP BY b.klucz HAVING count(*) > 1

i jeśli zwróci Ci jakiś wynik, to jesteś na minie. Pomiędzy tabelami jest relacja jeden do wielu (wiele po stronie tab2). Sposobów na rozwiązanie problemu jest kilka, ale to już zależy od tego co chcesz osiągnąć.
Link do wypowiedziLink
Grzegorz D.

Grzegorz D. PL/SQL Developer

Temat: Oracle 10g - problem z INSERTEM; ORA-01427

Rafał K.:
Grzegorz Drzymała:
Znacznie łatwiej by było, gdybyś wrzucił całe zapytanie.
Z tego co przedstawiłeś wynika, że masz zapytanie skorelowane, i ono zwraca więcej niż 1 wiersz co w takim przypadku jest błędem.
Nie bardzo wiem o jakim "record count" mówisz...
Po wykonaniu się SELECT'a zliczam rekordy z poziomu klienta bazy danych (Toad, SQL Navigator etc.)
Jeżeli chodzi o zwykły count tego selecta to zauważ, że zapytanie


SELECT count(*) FROM (

SELECT a.pole1,

(SELECT b.pole2 FROM tab2 b WHERE b.klucz = a.klucz) pole2

FROM tab1 a )

wykona się poprawnie, natomiast sam SELECT może rzucić błędem przy dojechaniu w wyświetlaniu do feralnego przypadku.

Być może jest tak w przypadku nie SELECTa a INSERTa.
Dlatego tak się dopytuje, czy widzisz na ekranie WSZYSTKIE wyniki (800k wierszy)

Wydaje mi się, że jest tak, że Select zwraca na ekran jedynie pierwsze 500 rekordów, jak chcesz zobaczyć kolejne 500 to całe zapytanie jest przeliczane ponownie itd.
Odpowiadając na Twoje pytanie: nie widziałem na ekranie wszystkich wyników tylko pierwsze 500.

Więc Paweł, 2 posty wyżej, dał Ci już odpowiedź.
Przepisz na OUTER JOIN i zobacz gdzie masz duplikaty.
Link do wypowiedziLink
Grzegorz D.

Grzegorz D. PL/SQL Developer

Temat: Oracle 10g - problem z INSERTEM; ORA-01427

Izabela Korzińska:
i o niebo lepiej count(1), niż count(*)

Dla optymalizatora to chyba bez znaczenia.

Edit:
Sprawdziłem jeszcze dla pewności - http://asktom.oracle.com/pls/asktom/f?p=100:11:1696494...

Jeżeli Tom pisze w 2001 roku:
"count(*) counts records in a table (it does NOT have to get the full record, it just
needs to know there is a record and increments a count)

count(1) counts non null occurences of the constant 1 in a table. It does not have to
get the full record, it just needs to know there is a record and increments a count.

they are the same, identical, not different."
To ja mu wierzę ;)Grzegorz Drzymała edytował(a) ten post dnia 11.09.11 o godzinie 23:02
Link do wypowiedziLink

konto usunięte

Temat: Oracle 10g - problem z INSERTEM; ORA-01427

Grzegorz Drzymała:

Więc Paweł, 2 posty wyżej, dał Ci już odpowiedź.
Przepisz na OUTER JOIN i zobacz gdzie masz duplikaty.

Tak, zgadza się. Dzięki.
Link do wypowiedziLink
Izabela Korzińska

Izabela Korzińska Architekt /
Developer ETL/TEam
Leader, Roche Polska

Temat: Oracle 10g - problem z INSERTEM; ORA-01427

Grzegorz Drzymała:
they are the same, identical, not different."
To ja mu wierzę ;)
Aż porównałam plan wykonania i szybkość działania po wyczyszczeniu cache'a. I też już wierzę :) Mądry optymalizator.
Link do wypowiedziLink
« Wróć do tematów
Odpowiedz

Podobne tematy


Następna dyskusja:

Oracle 10g -> ODBC ->...




Wyślij zaproszenie do
Anuluj