Re: Need Help Creating Index

"anuragmin" <anurag_minocha_at_hotmail.com> wrote in message news:1277.990795302194_at_iw0.mailusenet.com...
> Hi,
> I have a table with 6 1/2 million records which will be basically
> used for querying over the web.
>
> The data will be inserted weekly. First all the data in the table
> will be truncated and then the whole thing will be uploaded
> again. The average table growth is approximately 25000
> records per month.
>
> Below is the table structure with the number of distinct values
> the column has in the right most column
>
> SQL> desc test
> Name Null? Type Distinct
> ------------------------------- -------- ---- --------
> COMPANY VARCHAR2(10) 478
> TIME VARCHAR2(10) 39
> REGION VARCHAR2(10) 43
> LINEITEM VARCHAR2(15) 872
> ACTUAL NUMBER(20,6) 1087248
> ACTUAL_US NUMBER(20,6) 1158499
>
>
> The type off queries that will be run on this table are
> * Give me all the records for a particular company for a
> particular time.
>
> *Give me the sum of all the actual and actualus for a
> particular company
>
> etc etc.....
>
> I need suggestions on how to create index on this table and
> anything else which you guys can think of which will speed
> up the queries.
>
> Thanks
> Anurag
>
> ----
> Posted via http://www.etin.com - the FREE public USENET portal on the Web
> Complete SEARCHING, BROWSING, and POSTING of text and BINARY messages!

The problem here is the answer lies in the etc.. etc... as the two statements above would be using the primary key which is supposedly (why didn't you list it?) company
time
region
lineitem

Region is also an obvious candidate for indexing (I suspect a FK here) maybe time, the other columns definitely not.

Hth,

Sybrand Bakker, Oracle DBA Received on Wed May 30 2001 - 11:13:10 CDT