Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
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?
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>...Received on Fri Jan 17 2003 - 16:38:45 CST
>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,Does this version has
>partition bugs?
>
>Thanks a lot.
>
>-Xiang Zhao