Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Partitioned tables & indexes

Re: Partitioned tables & indexes

From: jaromir nemec <jaromir_at_db-nemec.com>
Date: Thu, 5 May 2005 23:47:24 +0200
Message-ID: <028401c551bc$07fd7600$3c02a8c0@JARAWIN>


Hi,

> What could be done to actually start using this index?

if the typical query reads all the 30 M records of a selected month you need no index. You may try to deploy PQ to increase performance.

Otherwise if your queries doesn't read whole month (e.g. from 15 th to 15 th ) you may refine the range partitioning considering weekly or even daily partitioning.

If you read only some part of the data you may consider composite partitioning (range - hash or range - list) if this part is relatively large (say 5M records per month; but the definition of the subpartition depends on the access). Only if you select a small part of data from a partition an additional index would be useful.

You may also consider materialized views to speed up the queries (except for the current month the data is static, so the refresh wouldn't be very expencive).

Check the DW Guide anyway

http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96520.pdf

Regards

Jaromir D.B. Nemec
----- Original Message -----
From: "sol beach" <sol.beach_at_gmail.com>
To: "Oracle-L Freelists" <oracle-l_at_freelists.org> Sent: Wednesday, May 04, 2005 11:02 PM
Subject: Partitioned tables & indexes

--
http://www.freelists.org/webpage/oracle-l
Received on Thu May 05 2005 - 17:54:54 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US