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

Home -> Community -> Usenet -> c.d.o.server -> Re: adding the partition column to the right side of every local non-prefixed index to improve performance?

Re: adding the partition column to the right side of every local non-prefixed index to improve performance?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 17 Jan 2003 22:38:45 -0000
Message-ID: <b0a178$7aj$1$8302bc10@news.demon.co.uk>

That is a very odd piece of advice, and is quite likely to be a waste of space in most common scenarios.

Indexes should be defined on columns which are inherently useful. If it would make sense to add the column on a non-partitioned table, then you probably should add it - if it wouldn't. then don't.

Optimisation features relating to partitioning keep improving, and I believe 8.1.7.2 was pretty good, but you would be better off upgrading.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______January 21/23
____USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





Xiang Zhao wrote in message
<7c33c36d.0301130745.780c16b1_at_posting.google.com>...

>Hello all,
>
>I have heard oracle have some performance issues on local
non-prefixed
>index.
>And one expert told me to add the partition column to the right side
>of every non-prefixed local index to improve performance.
>I mean if the table has three columns: A,B,C, and the partitioned on
>the column A.
>and a local index on the column B, should be converted to a composite
>index on column B,A.
>
>I'm not quite sure about this method. Here I'd like someone to share
>his expeerience on the local non-prefixed index.
>
>And my oracle version is 8.1.7.2.0&#65292;Does this version has
>partition bugs?
>
>Thanks a lot.
>
>-Xiang Zhao
Received on Fri Jan 17 2003 - 16:38:45 CST

Original text of this message

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