Sorting in relational databases

[Wim Van den Wyngaert]

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

[labadie_1]

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.

[Wim Van den Wyngaert]

Gerard,

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

Wim

[labadie_1]

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 :-)"

[Wim Van den Wyngaert]

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

[Willem Grooters]

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

[Wim Van den Wyngaert]

Willem,

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

Wim