Nye funktioner til begrænsning af rækker i det resulterende datasæt i Oracle 12c

Oracle databasen har indtil version 12c altid været svag, når det handlede om at returnere dele af en query’s resultat. Man kunne begrænse antallet af rækker, man fik retur, dog altid startende ved første række og med store problemer med sortering

Normalt ville man begrænse antallet af rækker med ”rownum” funktionen. Dvs. at man, for kun at få f.eks. de første 5 resultater fra en query, kunne skrive:

Select * from personer where rownum<=5

Resultat:
 NR    FORNAVN        EFTERNAVN
 1     Hans           Jensen
 2     Carsten        Mogensen
 3     Svend          Frandsen
 4     Poul           Sørensen
 5     Jane           Karlsen

Dette fungerede fint, og man fik de 5 første rækker i tilfældig rækkefølge, som det er kutyme i en relationel database. Men tilføjede man en sortering, ville resultatet ikke blive som man umiddelbart kunne forvente. Denne query:

Select * from personer where rownum<=5 order by fornavn

ville godt nok returnere 5 rækker, men det ville ikke nødvendigvis være de 5 første rækker, hvis man sorterede tabellen alfabetisk. I stedet ville det være 5 “tilfældige” rækker (de første Oracle databasen støder på), og så efterfølgende sorteret internt:

Resultat:
 NR         FORNAVN        EFTERNAVN
 2          Carsten        Mogensen
 1          Hans           Jensen
 5          Jane           Karlsen
 4          Poul           Sørensen
 3          Svend          Frandsen

For at undgå dette brugte man normalt en ”select inden i en select”. Dvs. resultatet af den indre select blev det datasæt, som den ydre select brugte som udgangspunkt, som i dette eksempel:

select * from (select * from personer order by fornavn) where rownum<=10

Resultat:
 NR         FORNAVN        EFTERNAVN
 6          Anders         Madsen
 7          Bente          Poulsen
 2          Carsten        Mogensen
 8          Diana          Hansen
 1          Hans           Jensen

Dette gav det forventede resultat, men var en ret besværlig metode for at opnå et relativt simpelt output.

Endnu værre blev det, hvis man ønskede f.eks. data fra række 100 til række 200, hvor man ville være nødt til at bruge en ”select inden i en select inden i en select” med både en ”rownum <=” og en ”rownum >=” indbygget i query’en.

Andre databaser som f.eks. Microsoft SQL Server har i mange år haft langt mere fleksible måder at lave sådanne udtræk på. Heldigvis har Oracle med deres version 12c, der kom i 2013, nu fået lignende muligheder.

I Oracle hedder de nye funktioner ”fetch first”, ”fetch next” og ”offset”, og de bruges således:

Hent kun de første 5 rækker sorteret efter fornavn:

Select * from personer order by fornavn fetch first 5 rows only

Resultat:
 NR        FORNAVN        EFTERNAVN
 6         Anders         Madsen
 7         Bente          Poulsen
 2         Carsten        Mogensen
 8         Diana          Hansen
 1         Hans           Jensen

Hent kun rækkerne efter række 3 og 2 rækker frem sorteret efter fornavn:

Select * from personer order by fornavn offset 3 rows fetch next 2 rows only

Resultat:
 NR         FORNAVN        EFTERNAVN
 8          Diana          Hansen
 1          Hans           Jensen

Den anden konstruktion bruges ofte til f.eks. GUI sidestyring ved store datamængder, hvor en GUI implementation ikke længere behøver at hente alle rækker for kun at vise f.eks. række 1.001 og 1.010 på en side (ofte kendt som “The Late Filtering Anti-Pattern”), og heller ikke behøver at konstruere en query med 3 select sætninger inden i hinanden, hvilket i sig selv kan give en dårlig performance, hvis man er lidt uheldig med de valg databasens optimizer træffer.

Så alt i alt er det en god forbedring i Oracle 12c, som kommer til at gøre udviklernes job lettere – og som man må sige, at Oracle virkelig har været lang tid om at levere.

I have been working as a system developer, data modeler and DBA on the Oracle platform using SQL, Pl/SQL, Forms, Reports, JSP, HTML and Javascript since 1996, and have been involved in both client/server and web-based projects for 25+ different customers. I'm normally known as a problem solver