Select über drei Tabellen (Performanceverbessung)

[blackswordowner]

Hallo!

Hab ein kleines Performance Problem.

DBMS ORACLE 9.2

Und zwar geht es darum aus zwei Tabellen (A, Daten auszugeben, wobei die Anzahl der auszugebenen Daten von Tabelle © abhängt.

Meine bisherige Lösung:

select A.name,B.nummer

from A,B

where A.id in (select id from C)

and B.id in (select id from C);[/PHP]

Aber Problem ist, das dieses sehr sehr lange dauert.

Tabelle A & B haben jeweils etwa 5.100.000 Einträge.

Tabelle C nur knappe 30.

Bei zwei einzelnen Select geht alles recht fix.

Hatte auch schon mit joins versucht. Einer ist kein Problem, wenn aber ein zweiter hinzukommt schmeißt er immer eine Fehlermeldung.

[PHP]select A.name,B.nummer
2 from A,B
3 join C
4 on B.id=C.id
5 join C
6 on A.id=C.id;

ERROR at line 4:
ORA-00904: "B"."ID": invalid identifier

Bin für andere Lösungsmöglichkeiten offen. Oracle Kurs ist schon nen paar Jahre wieder her.

Gruß

BSO

[ipu]

Hi,

kannst du denn nicht umgekehrt vorgehen? Erst die ID aus C und dann dazu die Einträge in A und B suchen? Oder haste das schon ausprobiert und ist genauso langsam?

Grüße von ipu

[johnhaltonx]

Kannst du mal im sqlplus ein

set autotrace explain

machen und dann deine query mal laufen lassen?

btw, warum nicht:

select a.name, b.nummer, c.id

from a,b,c

where a.id=c.id

and b.id=c.id

[ipu]

Kannst du mal im sqlplus ein

set autotrace explain

machen und dann deine query mal laufen lassen?

btw, warum nicht:

select a.name, b.nummer, c.id

from a,b,c

where a.id=c.id

and b.id=c.id

Hi!

Ich bin neugierig. Was ist "set autotrace explain"?

Grüße von ipu

[johnhaltonx]

Bloed das kein edit geht....

Koenntest du mal den Explain Plan und den Autotrace posten ? dann weiss man auch wie die Query von Oracle ausgefuehrt wird..

btw :

statt

select a.name, b.nummer

from a,b 

where A.id in (select id from C)

and B.id in (select id from C); 

wieso nicht:

select a.name, b.nummer, c.id

from a,b,c

where a.id=c.id

and b.id=c.id

Hab festgestellt das die 2 Queries unterschiedliche antworten geben, auch wenn meine schneller waere Also bleib bei deiner ersten Variante, allerdings verstehe ich nicht wieso die bei dir so langsam ist siehe unten: bei mir ca 3 Sekunden Poste doch mal den autotrace explain, vielleicht sieht man da worans liegt

create table a (id number primary key, name varchar2(100));


create sequence pk_a;


insert into a select pk_a.nextval, object_name from a;

insert into a select pk_a.nextval, name from a;

insert into a select pk_a.nextval, name from a;

...

bis ca 5 Mio records

select count(*) from a;


5018496 rows returned.


create table b (id number primary key, nummer varchar2(30));


create sequence pk_b;


insert into b select pk_b.nextval, object_id from all_objects; 

insert into b select pk_b.nextval, nummer from b;

....


select count(*) from b;


5018880 rows returned.



create table c(id number primary key);


insert into c values(2033);

insert into c values(2222);

insert into c values(4566);

insert into c values(6433);

insert into c values(74354);

insert into c values(2334);

insert into c values(5678);

insert into c values(9575);

insert into c values(9356);

insert into c values(3264);

insert into c values(2367);

insert into c values(4563);

insert into c values(4852);

insert into c values(6345);

insert into c values(7733);

insert into c values(4356);

insert into c values(5856);

insert into c values(5468);

insert into c values(6784);

insert into c values(3543);

insert into c values(8444);

insert into c values(4567);

insert into c values(3456);

insert into c values(3453);

insert into c values(7567);

insert into c values(3456);

insert into c values(4888);

insert into c values(8678);

insert into c values(3453);

insert into c values(3453);


set autotrace trace explain

set timing on


select a.name, b.nummer

from a,b 

where A.id in (select id from C)

and B.id in (select id from C);


 Elapsed: 00:00:03.13


Execution Plan

----------------------------------------------------------

0 	  	SELECT STATEMENT Optimizer=ALL_ROWS (Cost=769 Card=729 Bytes =88209) 	 

1 	0 	    NESTED LOOPS (Cost=769 Card=729 Bytes=88209) 	 

2 	1 	        MERGE JOIN (CARTESIAN) (Cost=37 Card=729 Bytes=40824) 	 

3 	2 	            NESTED LOOPS (Cost =29 Card=27 Bytes=1161) 	 

4 	3 	                INDE X (FAST FULL SCAN) OF 'SYS_C008633' (INDEX (UNIQUE)) (Cost=2 Card=27 Bytes=351) 	 

5 	3 	                TABL E ACCESS (BY INDEX ROWID) OF 'B' (TABLE) (Cost=1 Card=1 Byte s=30) 	 

6 	5 	                &nbs p;   INDEX (UNIQUE SCAN) OF 'SYS_C008632' (INDEX (UNIQU E)) (Cost=0 Card=1) 	 

7 	2 	            BUFFER (SORT) (Cos t=36 Card=27 Bytes=351) 	 

8 	7 	                INDE X (FAST FULL SCAN) OF 'SYS_C008633' (INDEX (UNIQUE)) (Cost=0 Card=27 Bytes=351) 	 

9 	1 	        TABLE ACCESS (BY INDEX ROWID) OF 'A' (TABLE) (Cost=1 Card=1 Bytes=65) 	 

10 	9 	            INDEX (UNIQUE SCAN ) OF 'SYS_C008631' (INDEX (UNIQUE)) (Cost=0 Card=1) 	 

3 Sekunden auf meinem Notebook Fedora Core 3 mit Oracle 10g (Hatte keiner 9er da)

[blackswordowner]

Moins!

Also habe mal als erstes folgende Variante ausprobiert:

select a.name, b.nummer

from a,b,c

where a.id=c.id

and b.id=c.id

[/PHP]

Dabei kommt folgende Fehlermeldung:

[code]ERROR at line 4: ORA-00904: "C"."ID": invalid identifier[/code]

Habe alles dreimal auf Rechtschreibfehler überprüft. Dann habe ich mal dieses autotrace ausprobiert. Mit dem Ergebnis:

[code]SP2-0613: Unable to verify PLAN_TABLE format or existence SP2-0611: Error enabling EXPLAIN report [/code]

Gruß

BSO

[bigpoint]

und geht so was bei Oracle ???

select A.name,B.nummer 

from A join B  on A.id=B.id

join C on A.id=C.id

[/php]

[johnhaltonx]

Die Fehlermeldung bedeutet das Tracing nicht aktiviert ist

Anleitung zum installieren von autotrace

Was den invalid identifier angeht:

select a.name, b.nummer

from a,b,c

where a.id=c.id

and b.id=c.id 

sollte:

select a.name, b.nummer,  [B]c.id[/B]

from a,b,c

where a.id=c.id

and b.id=c.id

heissen... Siehe Langes Post, am besten den code per copy & paste uebernehmen. Btw. wie lange laeuft denn bei dir die Loesung mit den 2 IN-Lists? PS: siehe vorposter, da hat wohl jemand zu lange mysql entwickelt PPS: aber unglaublich es geht

 select A.name,B.nummer

from A join B  on A.id=B.id

join C on A.id=C.id 

liefert ganz normal dier Ergebnisse , wieder was gelernt

[blackswordowner]

Moins!

 select A.name,B.nummer,C.id

  2  from A

  3  join C

  4  on B.id=C.id

  5  join C

  6  on A.id=C.id;

[/PHP]

Dauert nun genau:

[code] 26 rows selected. Elapsed: 00:00:00.01 [/code]

Meine Intention [b]Join[/b] zu nutzen und nicht where x=y and... , war dass dort erst ein Kreuzprodukt gemacht wird und dann die nicht benötigten Zeilen entfernt werden (die Tabellen mit den 5Mio einträgen haben aber 44 bzw. 90 Spalten). Wenn ich mich irren sollte bitte korrigieren.

Gruß

BSO

[johnhaltonx]

stimmt das macht sinn bei der Anzahl von Spalten wuerde das etwas viel , btw dir ist schon klar das die 2 Statements

select a.name, b.nummer

from a,b 

where A.id in (select id from C)

and B.id in (select id from C); 

und

select a.name, b.nummer, c.id

from a,b,c

where a.id=c.id

and b.id=c.id

sematisch verschieden sind, also einmal bei mri jedenfalls so ca 730 rows liefert (statement 1) und einmal nur unter 100 (statement 2)...

[blackswordowner]

Wo liegt denn genau der Unterschied zwischen den beiden Statements?

[johnhaltonx]

select a.name, b.nummer, c.id

from a,b,c

where a.id=c.id

and b.id=c.id


 0 	  	SELECT STATEMENT Optimizer=ALL_ROWS (Cost=83 Card=27 Bytes=1 242) 	 

1 	0 	    NESTED LOOPS (Cost=83 Card=27 Bytes=1242) 	 

2 	1 	        NESTED LOOPS (Cost=29 Card=27 By tes=945) 	 

3 	2 	            INDEX (FULL SCAN) OF 'SYS_C008633' (INDEX (UNIQUE)) (Cost=1 Card=27 Bytes=108) 	 

4 	2 	            TABLE ACCESS (BY I NDEX ROWID) OF 'A' (TABLE) (Cost=2 Card=1 Bytes=31) 	 

5 	4 	                INDE X (UNIQUE SCAN) OF 'SYS_C008631' (INDEX (UNIQUE)) (Cost=1 Ca rd=1) 	 

6 	1 	        TABLE ACCESS (BY INDEX ROWID) OF 'B' (TABLE) (Cost=2 Card=1 Bytes=11) 	 

7 	6 	            INDEX (UNIQUE SCAN ) OF 'SYS_C008632' (INDEX (UNIQUE)) (Cost=1 Card=1)



diese Join macht keine Karthese, sondern geht 2 mal mit nested loops ueber tabelle a und b, mit den werten aus c


select a.name, b.nummer

from a,b 

where A.id in (select id from C)

and B.id in (select id from C); 


Execution Plan

----------------------------------------------------------

0 	  	SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1499 Card=729 Byte s=36450) 	 

1 	0 	    NESTED LOOPS (Cost=1499 Card=729 Bytes=36450) 	 

2 	1 	        MERGE JOIN (CARTESIAN) (Cost=37 Card=729 Bytes=13851) 	 

3 	2 	            NESTED LOOPS (Cost =29 Card=27 Bytes=405) 	 

4 	3 	                INDE X (FULL SCAN) OF 'SYS_C008633' (INDEX (UNIQUE)) (Cost=1 Card =27 Bytes=108) 	 

5 	3 	                TABL E ACCESS (BY INDEX ROWID) OF 'B' (TABLE) (Cost=2 Card=1 Byte s=11) 	 

6 	5 	                &nbs p;   INDEX (UNIQUE SCAN) OF 'SYS_C008632' (INDEX (UNIQU E)) (Cost=1 Card=1) 	 

7 	2 	            BUFFER (SORT) (Cos t=35 Card=27 Bytes=108) 	 

8 	7 	                INDE X (FAST FULL SCAN) OF 'SYS_C008633' (INDEX (UNIQUE)) (Cost=0 Card=27 Bytes=108) 	 

9 	1 	        TABLE ACCESS (BY INDEX ROWID) OF 'A' (TABLE) (Cost=2 Card=1 Bytes=31) 	 

10 	9 	            INDEX (UNIQUE SCAN ) OF 'SYS_C008631' (INDEX (UNIQUE)) (Cost=1 Card=1)

Hier wird eine Karthese gemacht und aufgrund von c eingeschraenkt. Ich weiss ja nicht was du genau fuer Ergebnisse haben willst, davon haengt es ab welche der beiden Abfragen das ist was du willst.