Re: Accessing a large table

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;

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

create index on you r small table>

make whatever you want.

drop small table.

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

Best regards,

Toni

In article <31F3D62D.528A_at_iafrica.com>, Deon de Villiers <tsunix_at_iafrica.com> wrote:

> Hi All
>
> Should Oracle handle a table of approx. 25 million rows without any
> problems? A particular SQL query when run on a subset of the large table
> of approx 100 000 rows returns a result fairly promptly (less than one
> minute), but when run on the large table the query ran for days without
> returning a result.
>
> Any ideas?
>
> Thanks
> Deon de Villiers
 

-- 
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 |