Re: Accessing a large table

dischner_at_med.uni-muenchen.de (Anton Dischner) wrote:

>Hi Deon,
 

>i'd call a 25million rows table medium sized.
>I run such beasts for years now.
 

>My top tip is: make (one or several!) indexes, even if they use gigabytes
>of memory.
>Try to include a sortable field (timedate, julian day or so).
 

>With good indexes i am able to access data without worrying about size.
 

>Next tip is: Always work like this.
 

>Create table temp_table from big_table where index_field between a and b;
 

>or
 

>Create table temp_table from big_table where index_field > number;

Because it is a (relative) new feature, and not anybody may be aware of it:

Better use

create table temp table
storage (....)
UNRECOVERABLE
as
select * from big_table
where index_field between .........

>Make as less as possible where clauses in the first step.
 

>create index on you r small table>

create index ....
UNRECOVERABLE
>make whatever you want.
 

>drop small table.
 

>That's the way i do it. Every other way causes headaches for me.
 

>Best regards,
 

>Toni

The UNRECOVERABLE attribute will prevent writes to the log-file; you will not be able to recover the table or index in case of a media-failure, but if it is only a temporary table, who cares...

>--
>A. Dischner, SGI&AIX sysadmin, Oracle DBA | Don't let friends
>Institut fuer Klinische Chemie | use WinDose
>Klinikum Grosshadern | Just say NO.
>Ludwig Maximilians Universitaet Muenchen, GER |
>Marchioninistr.15 81366 Muenchen 49-89-70953202 |

Willy Klotz
Willy Klotz

Willys Mail     FidoNet   2:2474/117  2:2474/118  
                Mailbox: analog 06297 95035
                         ISDN   06297 910105
                Internet: willyk_at_t-online.de                
                ->   No Request from 06.00 to 08.00 <-
======================================================================