[Oracle(/sql/php)] Efficiente "limit" vervanger

[ACM]

Ik heb voor het tonen van de resultaten van een query (die in mijn geval zo'n 2500 rows vindt) een "soort limit" nodig.

Nou heb ik die gevonden in de volgende vorm:
- Bepaal $vanaf en $tot.
- Parse query, bind variabelen en execute.
- While/forloop tot $vanaf door de resultaten mbv OCIfetch, zonder wat met de resultaten te doen.
- While/forloop tot $tot-$vanaf door de resultaten en toon die.
- Gooi het statement etc leeg.

Dit werkt, in principe, prima. Behalve dat het niet al te "snel" gaat. Aangezien de eerste 20 waardes vinden ongeveer 0.014 seconden duurt, in mijn setup, en waarde 2480-2500 duurt zo'n 0.7 seconden...
Geen echt enge waarden, maar wel als er 50-100 mensen tegelijk de laatste 20 zouden willen hebben...


Is hier een efficientere oplossing voor? Het is niet echt mogelijk de output van de query verder te beperken.


_{Mocht het antwoord te vinden zijn dmv cursor's dan zou ik daar graag wat meer voorbeelden van zien aangezien ik daar nog nooit wat mee heb gedaan.}

[justmental]

Ja.

Uit mijn forum in ontwikkeling:

code:

  select pst_id
  , pst_top_id
  , pst_text
  , pst_date
  , pst_display
  , pst_flag
  , pst_usr_nick
  , pst_edit_by
  , pst_edit_date
  , pst_edit_lock
  , pst_parsed_text
  , rnum
  from   ( select pst_id
       ,      pst_top_id
       ,      pst_text
       ,      pst_date
       ,      pst_display
       ,      pst_flag
       ,      pst_usr_nick
       ,      pst_edit_by
       ,      pst_edit_date
       ,      pst_edit_lock
       ,      pst_parsed_text
       ,      rownum rnum
       from     posts_view
       where    pst_top_id = p_top_id
       )
  where rnum > (p_page-1)*p_perpage
  and   rnum <= p_page*p_perpage;

[ACM]

Op vrijdag 18 januari 2002 10:59 schreef justmental het volgende:
Ja.

Deze manier van "subquery-ing" kende ik al, maar ik meende gelezen te hebben dat dat juist niet sneller is

* ACM gaat even wat testen

[justmental]

Op vrijdag 18 januari 2002 11:00 schreef ACM het volgende:

[..]

Deze manier van "subquery-ing" kende ik al, maar ik meende gelezen te hebben dat dat juist niet sneller is

* ACM gaat even wat testen

Het doet uiteindelijk hetzelfde, alleen nu doet de query processor het.
Ik heb het niet getest, maar ik verwacht dat dit iets sneller is dan zelf dummy's fetchen.

[ACM]

Op vrijdag 18 januari 2002 11:06 schreef justmental het volgende:
Het doet uiteindelijk hetzelfde, alleen nu doet de query processor het.
Ik heb het niet getest, maar ik verwacht dat dit iets sneller is dan zelf dummy's fetchen.

Echt veel scheelt het niet

0.7 vs 0.038 seconden bij 20 resultaten per keer, bedankt!

Echter, is het wel _altijd_ ~0.038 seconden, dus zolang "$tot" kleiner is dan 100-150 is het langzamer

[justmental]

Op vrijdag 18 januari 2002 11:12 schreef ACM het volgende:

[..]

Echt veel scheelt het niet

0.7 vs 0.038 seconden bij 20 resultaten per keer, bedankt!

Echter, is het wel _altijd_ ~0.038 seconden, dus zolang "$tot" kleiner is dan 100-150 is het langzamer

Koel, bedankt voor de test
Voor mijn active topics gebruik ik 'jouw' methode omdat daar bijna altijd pagina 1 gevraagd wordt.
Voor de search en het tonen van een topic gebruik ik de 'inline view' methode.

Hoef ik niks te veranderen dus

[raptorix]

Hangt beetje van DB ook af, hoop databases cachen queries, een hoop keer een "zware" query doen kan soms voordeliger zijn als een hoop verschillende "lichte" queries.

Op zich zou je eens kunnen kijken naar het executieplan, als je db dit tenminste ondersteunt.

[ACM]

Op vrijdag 18 januari 2002 11:22 schreef raptorix het volgende:

Euh, ik geloof dat Oracle dat wel ondersteund ja
(zie topictitel )

Ik heb alleen niet zoveel kaas gegeten van Oracle dat ik dat met succes kan bekijken

[justmental]

Op vrijdag 18 januari 2002 11:28 schreef ACM het volgende:

[..]

Euh, ik geloof dat Oracle dat wel ondersteund ja
(zie topictitel )

Ik heb alleen niet zoveel kaas gegeten van Oracle dat ik dat met succes kan bekijken

Eerst deze tabel maken:

code:

create table PLAN_TABLE (
    statement_id     varchar2(30),
    timestamp       date,
    remarks     varchar2(80),
    operation       varchar2(30),
    options      varchar2(30),
    object_node   varchar2(128),
    object_owner     varchar2(30),
    object_name   varchar2(30),
    object_instance numeric,
    object_type     varchar2(30),
    optimizer    varchar2(255),
    search_columns  numeric,
    id      numeric,
    parent_id   numeric,
    position    numeric,
    other       long);

Dan doe je:

code:

explain plan set statement_id = 'temp' for <je statement>

Vervolgens lees je de plan_table uit met:

code:

col operation for a30
col options for a12
col object_name for a15
col position for 99999999
set termout on
SELECT substr(LPAD(' ',2*(LEVEL-1))||operation,1,40) operation, substr(options,1,15) options, object_name, position
from plan_table
start with id = 0 and statement_id = 'temp'
connect by prior id = parent_id and statement_id = 'temp';

Vervolgens flip je van de onbegrijpelijke output (als je tenminste geen doorgewinterde Oracle-expert bent).
En je gooit je plan_table weer leeg

[disclaimer]
Dit alles in SQL*Plus.
Alternatieven:
-SQL*Plus autotrace activeren
-SQL_TRACE aanzetten met TIMED_STATISTICS en gegenereerde tracefile bewerken met tkprof.
[/disclaimer]

[Goodielover]

Waarom werk je niet met een volgnummer die je insert met het elk record. Ik geef toe dat je dan alleen op invoertijdstip kan groeperen, maar als dit 90% van je queries is, kan je in die gevallen wel die manier van pagina ophalen gebruiken, terwijl je in de andere gevallen alsnog op justmental of je eigen oplossing kan terugvallen.

[ACM]

Op vrijdag 18 januari 2002 11:55 schreef Goodielover het volgende:
Waarom werk je niet met een volgnummer die je insert met het elk record.

Euh, wat als je iets op "naam" moet sorteren?
Maar toch 5000 rows bevat?

En een volgnummer is hier niet echt handig.

[JaQ]

als je echt je query wilt "tunen" zal je het executie plan moeten bekijken.

Dat kan inderdaad m.b.v. tkprof, zoals eerder is genoemd, maar ik raad je aan om een programma als toad te gebruiken (http://www.quest.com), daarmee kan je je executieplan gemakkelijker analyzeren. Als je echter Oracle 8.1.7.3 gebruikt zou ik dat niet doen (daar zit een bug in tkprof en de executieplanner, die je niet de resultaten teruggeven die zouden moeten, oftewel foute resultaten)

Vervolgens moet je je nog wel even verdiepen in index-hints en executie methodes., maar in het algemeen kan werkt de "ordered" hint het beste / gemakkelijkste.

Je query wordt dan dus:
select --+ ORDERED
<<columnname>>
from <<tablename>>
where <<where-clause>>

uiteraard moet je die index hint ook gebruiken in eventuele inlined-views.


Verdere meer algemene tuning tips:
- Vermijd het gebruik van "stored" views. Het ontwikkeld makkelijk maar wel bagger traag.
- als je de naam van de index(-en) weet (of weet op te zoeken), wijs die dan m.b.v. hints toe: select --+ INDEX(<<tabelnaam of alias naam>>, <<indexnaam>>)
- verwijd "between", "like" / "not like" en "in" / "not in" in je where clause (vetraagd).
- gebruik NOOIT trunc(datumveld) in je where cluase. als er namelijk een index op dat veld ligt, gebruikt oracle die niet om een of andere stomme reden (ja, dat noem je nou een bug).
- de volgorde van tabellen bij je from-clauze moet altijd van klein naar groot lopen. de eerste tabel het minst aantal rijen, de laatste het meest aantal rijen. (vermindering geheugengebruik)
- als je gebruik maakt van functies die op hun beurt weer "lookups" doen, zet deze functies dan in een package en gebruik memory-tables. (dat is wel wat ingewikkelder, maar m.b.v. http://otn.oracle.com kan je alles vinden dat je nodig hebt). Dit is sneller, omdat packages per definitie in het geheugen zitten, functies en procedure hoeven dat niet te zijn.
- pl/sql procedures zijn niet per definitie sneller dan "normale" sql-queries. grappen als decode in een sql-statement werken zeker zo snel, zo niet sneller, dan if then statements.

Ik denk dat je nu wel iets heb waarmee je verder kan.

[justmental]

Op vrijdag 18 januari 2002 14:43 schreef DrFrankenstoner het volgende:
Dat kan inderdaad m.b.v. tkprof, zoals eerder is genoemd, maar ik raad je aan om een programma als toad te gebruiken (http://www.quest.com), daarmee kan je je executieplan gemakkelijker analyzeren.

Met als opmerking dat je daar dan wel $1000 voor neer moet tellen.

- verwijd "between", "like" / "not like" en "in" / "not in" in je where clause (vetraagd).

Bij like 'abc%' wordt een index op dat veld gewoon gebruikt en dit kan heel handig zijn.

- gebruik NOOIT trunc(datumveld) in je where cluase. als er namelijk een index op dat veld ligt, gebruikt oracle die niet om een of andere stomme reden (ja, dat noem je nou een bug).

Als je trunc(datumveld) in de linkerkant van je evaluatie plaatst dan kan een index op datumveld natuurlijk nooit gebruikt worden, maar dat is niet raar.
Je zou in zo'n geval een function based index kunnen gebruiken.

- de volgorde van tabellen bij je from-clauze moet altijd van klein naar groot lopen. de eerste tabel het minst aantal rijen, de laatste het meest aantal rijen. (vermindering geheugengebruik)

Het is niet de de grootte van de tabellen die belangrijk is, maar het aantal rijen wat je er nodig hebt.
Bij de cost based optimizer is de volgorde niet belangrijk en met de rule based optimizer moet het precies andersom dan hoe jij zegt omdat de optimizer dan onderaan begint.
Met de ordered hint begint ie wel bovenaan (uiteraard werkt dit alleen met cost based optimisation).

Ik denk dat je nu wel iets heb waarmee je verder kan.

Ik kon het toch niet laten om dit even wat te nuanceren ondanks dat het hier om eenvoudige statements ging en het executieplan waarschijnlijk weinig te beinvloeden is.

Nog wel een extra tip voor ACM:
Als je de boel nog een tikje sneller wilt hebben kijk dan eens naar bitmap indexen, deze kun soms bij minder dan 50% unieke waarden al sneller zijn dan b-tree indexen.