topic Re: Sorting in relational databases in Operating System - OpenVMS

Sorting in relational databases

Wim Van den Wyngaert — Mon, 20 Sep 2004 07:02:58 GMT

We have an application that does selects and joins on very big tables (at least 1 milion entries each). And is using order by.

Does anyone know which sort algorithm is used by the databases (e.g. Sybase / Oracle) ? Is it the VMS sort (I don't think so) ? Link to doc ?

Wim

Re: Sorting in relational databases

labadie_1 — Mon, 20 Sep 2004 07:20:01 GMT

Rdb uses a copy of the VMS sort, modified because it must run in exec mode.

Other tools such as RMU/LOAD uses directly the Vms sort.

For Ingres or others, I do not know.

Re: Sorting in relational databases

Wim Van den Wyngaert — Mon, 20 Sep 2004 07:34:22 GMT

Gerard,

But RDB isn't an real Oracle product and is only running on ex-DEC. But good to know anyway ...

Wim

Re: Sorting in relational databases

labadie_1 — Mon, 20 Sep 2004 07:38:57 GMT

Wim wrote
But RDB isn't an real Oracle product.

This reminds me of what says a guy at Rdb engineering: "Oracle, the other product of the company :-)"

Re: Sorting in relational databases

Wim Van den Wyngaert — Mon, 20 Sep 2004 07:48:10 GMT

If the good ones always won, we wouldn't be using Windows but Apples.

I worked with RDB for 2 months in a time that we didn't yet know what a relational database was (1987).

Wim

Re: Sorting in relational databases

Willem Grooters — Mon, 20 Sep 2004 07:52:29 GMT

First question: if the select executed on indexed fields? If not search will be done throughout the whole table, which has a major impact on performance! Indexes should be not too fragmented since that would trigger a full tabel scan as well (by experience) - even if there is an index on the attribute.....

Sorting will indeed likely NOT be done using VMS sort. I consulted a collegue with Sybase knowledge and he told me it may depend on a parameter (generic or SORT related) to instruct RDBMS to use memory or temporary tables. In the first case, the OS will take care of paging, in the latter data will be written into the database.
For Oracle, give the document on performence (http://www.oracle.com/technology/products/bi/pdf/o9i_optimization_twp.pdf) memory will be allocated and used - where the OS will take care of paging as well. You'll see it in the pagefault rate of the main Oracle process - or one of it's subprocesses.
I know, for Oracle, that there are quite a number of measures to be taken in database design (DB pagesize, for instance) and configuration to improve performance. Part is DBA's job....

Willem

Re: Sorting in relational databases

Wim Van den Wyngaert — Mon, 20 Sep 2004 08:00:05 GMT

Willem,

It is http://www.oracle.com/technology/products/bi/pdf/o9i_optimization_twp.pdf ?

Wim