[SQL] - select niet performant...

[Verwijderd]

Beste SQL-vrienden,
ik zit met een SQL performance probleem, ik ga even de situatie schetsen. Ik heb gemakkelijkheidshalve alle overbodige kolommen uit de voorbeeldtabel gehaald (de werkelijke tabel is een view en bevat 33 kolommen).

ik heb een tabel SERIAL_NUMBERS:
SERIAL_NUMBER kit_number
122............4
123............5
124............5
125............NULL

ik heb als input parameter een serial_number en wil met een select alle serial_number records uit de tabel halen die hetzelfde kit_number hebben, dan de opgegeven serial_number. Het kit_number kan ook NULL zijn, dan moet ik gewoon het serienummer terugkrijgen.
Mijn select gaat als volgt:

SELECT SERIAL_NUMBER
FROM serial_numbers
WHERE SERIAL_NUMBER = 123
OR (kit_number IN
(SELECT kit_number
FROM serial_numbers
WHERE SERIAL_NUMBER = 123
)
)

Het probleem is nu dat deze select soms 2 seconden duurt, wat niet praktisch is voor de gebruiker, die moet namelijk elke keer na het inscannen van een serienummer 2 seconden wachten.
Daarbij komt nog een keer dat de tabel nu nog maar 13210 serienummers bevat, dit zal in een productieomgeving nog heel wat kunnen oplopen (het gaat om grote magazijnen vol gsm's,herlaadkaarten enz)

De database is Oracle9i Release 9.2.0.4.0 - 64bit, ik heb al verschillende optimizer modes van oracle gebruikt, serial_number is de primary key in de tabel, en er staat een index op kit_number. Ik heb ook al geprobeerd met een UNION, maar dat duurt tot 7 seconden.

Iemand een idee om de select optimaler te maken? Alvast bedankt. Tom.

[justmental]

Hoe ziet je explain plan er uit?

Wat is de query van de view?

[curry684]

edit:
nm ik lees ook een keer te snel over een alinea heen

[ Voor 64% gewijzigd door curry684 op 28-07-2004 11:16 ]

[PdeHoog]

-- ik las ook niet goed --

Al geprobeerd rechtstreeks op de tabellen te query-en?

En anders misschien eens kijken wat de vertragende factor is binnen de query. Die OR vind een dbms vaak niet zo leuk Kun je die niet herschrijven?

[ Voor 174% gewijzigd door PdeHoog op 28-07-2004 11:43 ]

[TerrorMachine]

Probeer eens een self join:

select a.*
from
serial_numbers a
,serial_numbers b
where 1=1
and b.kit_number = a.kit_number
and b.serial_number = 123

[farlane]

Mijn verwachting is dat die .. IN.. subquery het zo langzaam maakt.

[curry684]

Als je dan een self-join doet doe het dan goed:

SQL:

select k2.SERIAL_NUMBER from Kits k1 
left outer join Kits k2 on k1.Kit_number = k2.Kit_number
where k1.SERIAL_NUMBER = 45;

Volgens SQL Server Query Analyzer is deze letterlijk 4 keer sneller dan de variant van Valkske, en geeft hetzelfde resultaat.

[Verwijderd]

OMG, sql-99 standaard.... die krijg ik er dus maar niet in he!

Ik vind het sowieso raar dat die 2 seconden doet over 13000 records. Dat zou instantaan moeten zijn, ook zonder indexen.

Ik ben toch wel benieuwd naar je explain plan etc. Ook als de oplossing van curry684 afdoende is.

[curry684]

Pardon hij deed het nog even niet goed met NULLs, deze variant wel:

SQL:

SELECT isnull(k2.SERIAL_NUMBER, k1.SERIAL_NUMBER) 
FROM Kits k1 
LEFT OUTER JOIN Kits k2 ON k1.Kit_number = k2.Kit_number
WHERE k1.SERIAL_NUMBER = 65;

Even snel overigens

edit:
ik kan hier zelf niet op FTP, anders kon ik de execution plan van Query Analyzer wel even als plaatje uploaden...

[ Voor 20% gewijzigd door curry684 op 28-07-2004 13:51 ]

[Verwijderd]

Join onder oracle (hoeft) niet zo omslachtig als met het kinder speelgoed pakket SQL Server van MS.


modbreak: dit heet een troll en is op GoT niet gewenst. Als je een mening hebt mag je die onderbouwd presenteren

[ Voor 43% gewijzigd door curry684 op 28-07-2004 14:04 ]

[Verwijderd]

IK dacht dat de vraag over Oracle SQL ging, als je dan een korrekt Oracle SQL Statement plaatst verwacht ik niet dat dit met een ..... als je het doet .... doe het dan goed wordt gereplied.

Zoals een goed Microsoft SQL Server addept weet, is Oracle niet helemaal of helemaal niet ANSI SQL Compatible.

[Verwijderd]

code:

Bedankt voor alle reacties,
ik heb die select van hierboven geprobeerd, maar ik zag niet echt een verbetering en
 hij werkte niet als ik een serienummer gebruikte waarvan het kit_nr NULL is
 
ik heb hieronder deze select staan, met het explain plan
daaronder staat mijn select, met het explain plan
en daaronder de script van de view 
zoals jullie gevraagd hebben
let op, ik moet een algemene aanvraag indienen om views of tables aan te passen,
dus dat doe ik liever niet

er zitten nu 100.000 serienummers in de tabel, de eerste select doet er 30 seconden over
en de mijne doet er 15 seconden over, dus niet echt een verbetering :)

SQL> SELECT NVL(k2.SERIAL_NUMBER, k1.SERIAL_NUMBER) 
  2  FROM v_serial_number_view k1 
  3  LEFT OUTER JOIN v_serial_number_view k2 ON k1.Kit_NR = k2.Kit_NR
  4  WHERE k1.SERIAL_NUMBER = '420001000000004';        

NVL(K2.SERIAL_NUMBER
--------------------
4200000000004
420001000000004


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE (Cost=585 Card=925 Bytes=74000)

   1    0   NESTED LOOPS (OUTER) (Cost=585 Card=925 Bytes=74000)
   2    1     NESTED LOOPS (OUTER) (Cost=5 Card=1 Bytes=55)
   3    2       NESTED LOOPS (OUTER) (Cost=5 Card=1 Bytes=51)
   4    3         NESTED LOOPS (OUTER) (Cost=4 Card=1 Bytes=46)
   5    4           INDEX (RANGE SCAN) OF 'PK_SERIAL_NUMBER' (UNIQUE) (Cost=3 Card=1 Bytes=22)
   6    4           TABLE ACCESS (BY INDEX ROWID) OF 'V_KIT_SERIAL_NUMBER' (Cost=1 Card=1 Bytes=24)
   7    6             INDEX (RANGE SCAN) OF 'IDX_KITSN_SERIAL_NUMBER'(NON-UNIQUE)
   8    3         TABLE ACCESS (BY INDEX ROWID) OF 'V_KITS' (Cost=1 Card=1 Bytes=5)
   9    8           INDEX (UNIQUE SCAN) OF 'PK_V_KITS' (UNIQUE)
  10    2       INDEX (UNIQUE SCAN) OF 'PK_V_SUBTAGS' (UNIQUE)
  11    1     VIEW OF 'V_SERIAL_NUMBER_VIEW' (Cost=580 Card=734 Bytes=18350)
  12   11       NESTED LOOPS (OUTER) (Cost=580 Card=13210 Bytes=726550)
  13   12         MERGE JOIN (OUTER) (Cost=580 Card=13210 Bytes=673710)
  14   13           SORT (JOIN) (Cost=577 Card=13210 Bytes=607660)
  15   14             MERGE JOIN (OUTER) (Cost=329 Card=13210 Bytes=607660)
  16   15               SORT (JOIN) (Cost=326 Card=13210 Bytes=290620)
  17   16                 INDEX (FAST FULL SCAN) OF 'PK_SERIAL_NUMBER'(UNIQUE) (Cost=221 Card=13210 Bytes=290620)
  18   15               SORT (JOIN) (Cost=4 Card=33 Bytes=792)
  19   18                 TABLE ACCESS (FULL) OF 'V_KIT_SERIAL_NUMBER'(Cost=2 Card=33 Bytes=792)
  20   13           SORT (JOIN) (Cost=4 Card=165 Bytes=825)
  21   20             TABLE ACCESS (FULL) OF 'V_KITS' (Cost=2 Card=165 Bytes=825)
  22   12         INDEX (UNIQUE SCAN) OF 'PK_V_SUBTAGS' (UNIQUE)




Statistics
----------------------------------------------------------
         14  recursive calls
         75  db block gets
       2456  consistent gets
       5692  physical reads
          0  redo size
        600  bytes sent via SQL*Net to client
        651  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          2  sorts (disk)
          2  rows processed

SQL> 

SQL> SELECT SERIAL_NUMBER
  2  FROM v_serial_number_view
  3  WHERE SERIAL_NUMBER = '420001000000004'
  4  OR (kit_nr IN
  5  (SELECT kit_nr
  6  FROM v_serial_number_view
  7  WHERE SERIAL_NUMBER = '420001000000004'
  8  )
  9  );

SERIAL_NUMBER
--------------------
4200000000004
420001000000004


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   FILTER
   2    1     NESTED LOOPS (OUTER)
   3    2       NESTED LOOPS (OUTER)
   4    3         NESTED LOOPS (OUTER)
   5    4           TABLE ACCESS (FULL) OF 'SERIAL_NUMBER'
   6    4           TABLE ACCESS (BY INDEX ROWID) OF 'V_KIT_SERIAL_NUMBER'
   7    6             INDEX (RANGE SCAN) OF 'IDX_KITSN_SERIAL_NUMBER'   (NON-UNIQUE)
   8    3         TABLE ACCESS (BY INDEX ROWID) OF 'V_KITS'
   9    8           INDEX (UNIQUE SCAN) OF 'PK_V_KITS' (UNIQUE)
  10    2       INDEX (UNIQUE SCAN) OF 'PK_V_SUBTAGS' (UNIQUE)
  11    1     NESTED LOOPS (OUTER)
  12   11       NESTED LOOPS (OUTER)
  13   12         FILTER
  14   13           NESTED LOOPS (OUTER)
  15   14             INDEX (RANGE SCAN) OF 'PK_SERIAL_NUMBER' (UNIQUE)
  16   14             TABLE ACCESS (BY INDEX ROWID) OF 'V_KIT_SERIAL_NUMBER'
  17   16               INDEX (RANGE SCAN) OF 'IDX_KITSN_SERIAL_NUMBER' (NON-UNIQUE)
  18   12         TABLE ACCESS (BY INDEX ROWID) OF 'V_KITS'
  19   18           INDEX (UNIQUE SCAN) OF 'PK_V_KITS' (UNIQUE)
  20   11       INDEX (UNIQUE SCAN) OF 'PK_V_SUBTAGS' (UNIQUE)


Statistics
----------------------------------------------------------
         14  recursive calls
          0  db block gets
     105283  consistent gets
       1573  physical reads
          0  redo size
        575  bytes sent via SQL*Net to client
        651  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL> 

CREATE OR REPLACE FORCE VIEW DCSDBA.V_SERIAL_NUMBER_VIEW
(SERIAL_NUMBER, CLIENT_ID, SKU_ID, ORDER_ID, LINE_ID, 
 TAG_ID, ORIGINAL_TAG_ID, PICK_KEY, OLD_PICK_KEY, MANIFEST_KEY, 
 OLD_MANIFEST_KEY, STATUS, SUPPLIER_ID, SITE_ID, RECEIPT_DSTAMP, 
 PICKED_DSTAMP, SHIPPED_DSTAMP, UPLOADED, REPACKED, CREATED, 
 SCREEN_MODE, STATION_ID, RECEIPT_ID, RECEIPT_LINE_ID, V_OLD_SCREEN_MODE, 
 V_KIT_SKU_ID, V_COUNT_DSTAMP, V_INTERFACE_STATUS, V_PALLET_ID, V_CONTAINER_ID, 
 KIT_NR, SUBTAG, PRODUCTION_TAG)
AS 
SELECT /*+ RULE */ SN.*, KSN.Kit_NR, NVL(K.Subtag, K.Tag_ID) Subtag,ST.tag_id production_tag
FROM SERIAL_NUMBER SN, V_KIT_SERIAL_NUMBER KSN, V_KITS K, V_SUBTAGS ST
WHERE SN.SERIAL_NUMBER = KSN.SERIAL_NUMBER (+)
AND SN.SKU_ID = KSN.SKU_ID (+)
AND SN.Client_ID = KSN.Client_ID (+)
AND KSN.Kit_NR = K.Kit_NR (+)
AND K.subtag = ST.subtag (+);

[curry684]

Verwijderd schreef op 28 juli 2004 @ 14:12:
IK dacht dat de vraag over Oracle SQL ging, als je dan een korrekt Oracle SQL Statement plaatst verwacht ik niet dat dit met een ..... als je het doet .... doe het dan goed wordt gereplied.

Fyi werkt de shorthand join zoals daar gepresenteerd ook gewoon in SQL Server, en sloeg de 'als je het doet doe het dan goed' op het feit dat ie simpelweg niet het correcte resultaat teruggaf.

Zoals een goed Microsoft SQL Server addept weet, is Oracle niet helemaal of helemaal niet ANSI SQL Compatible.

SQL Server evenmin. Dat is nog geen reden om zonder onderbouwing een van de twee kinderspeelgoed te noemen.

[Verwijderd]

Kanonne! daar zit een flinke view met allemaal outer joins achter! Hij doet sowieso een full scan op Serial_number die niet echt zuinig is.
Zijn die outer joins noodzakelijk? Kan je geen andere view of query maken die precies ophaalt wat die scan-operators willen?

[justmental]

Het probleem is dat hij in beide gevallen je beperkende criterium de view niet ingemerged krijgt vanwege respectievelijk de 'or' en de outer join.
Je kunt een merge hint gebruiken, maar ik denk niet dat hij dat gaat pakken.

Het beste is je query op de basistabellen te schrijven.

[curry684]

justmental schreef op 28 juli 2004 @ 14:26:
Het beste is je query op de basistabellen te schrijven.

Uberhaupt gaan indexed queries op een view die 5 tabellen joint in geen enkele DBMS echt lekker werken

[Verwijderd]

Query op de basistabellen is een prima idee. Ik begon dit ook te denken, toen ik het script van die view begon te lezen.
Wat mij betreft laten we het hierbij als zijnde de oplossing.

[curry684]

Verwijderd schreef op 28 juli 2004 @ 14:36:
Query op de basistabellen is een prima idee. Ik begon dit ook te denken, toen ik het script van die view begon te lezen.
Wat mij betreft laten we het hierbij als zijnde de oplossing.

Waarna ik nog steeds benieuwd ben naar het verschil tussen jouw omgebouwde oplossing en mijn omgebouwde oplossing, die op een simpele tabel hier toch echt factor 4 scheelden