Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Performance Question
Hi,
you should only split up this table when x5..x9 are not often filled with data. When you have to link these two tables together for every select-statement you really loose performance.
When you create a combined index over x1k, x2, x3, x4 just remember that you have to use all columns from left to right in your where clause
e.g. this index works when you use
x1k or
x1k and x2 or
x1k and x2 and x3 or
x1k and x2 and x3 and x4
in your where clause
the index works not when you use the following where clause
where x1k = 'blabla' and x3 = 'wwwwwww' because x2 is missing.
when you don't always use the same where clause consider creating separate indexes on the columns.
Hope this helps
N. Wiesemann
>Hi,
>
>I've got a table X with one primary-key-column X1K, a few
>attribute-columns X2, X3, X4 that are used a lot of times and another
>few attribute-columns (e.g. varchar2(2000)) X5, X6, X7, X8, X9 that are
>not used very often.
>
>Now the question: what is better an why:
>1) split the Table X in two tables X and Y, one with the pkey X1K and
>the often used columns X2, X3, X4 and the other with the pkey X1K and
>the other columns X5, X6, X7, X8, X9
>2) create an index over X1K, X2, X3, X4 in just the original-table?
>
>Thank you!
>
>Thomas Keller
>
Received on Thu Apr 29 1999 - 16:06:49 CDT