Re: SQL Tunning using Indexes

From: Lynux <lynux_at_netvigator.com>
Date: 24 Nov 2000 18:18:49 GMT
Message-ID: <8vmbe9$hsk2@imsp212.netvigator.com>

Actually, we experience this result when we are building a local index onto a partitioned table. The 'multiple extents' are significant in the dba_extents. However, you have given me a great hints. Maybe you are right, we can take the parallelism benefit on tables' selection using index with degree > 1 and do only single extent for insert/update (As I need to check). When building or re-building the index, we need to temporarily change it into degree 1 (noparallel) first. That is, our DBA should take a caution on the degree of these index! Please correct me if I am wrong! Thank You

Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:

: Can you take a closer look at the multiple extents : that are produced when doing parallel insert/update.  

: Since (as you point out) you are using partitioned
: tables, your insert can only execute as a parallel
: insert by splitting the data across multiple partitions -
: so your 'multiple extents' could actually be 'one extent
: per partition loaded'.
 
: --

: Jonathan Lewis
: Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk  

: Practical Oracle 8i: Building Efficient Databases  

: Publishers:  Addison-Wesley
: Book bound date: 8th Dec 2000
: See a first review at:
: http://www.ixora.com.au/resources/index.htm#practical_8i
: More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



: Lynux wrote in message <8vjaec$1fl3_at_imsp212.netvigator.com>...

:>Are there any ways or initial parameter to make parallel query on index
:>scan and retain single query on insert, update?
:>
:>From previous experience, we can parallel query using index by using
:>HARDCODED hints or change the degree of the index (it must be partitioned)
:>However, if an insert or update is performed on the table and make the
:>index grow beyond its available space. Instantly, several extends (same as
:>the index's degree) would also be producted instead of 1 extends at a
:>time. If we use degree 1 for the index, then index scan would possibly
:>need about 20 - 200 times lower than table scan.
:>
:>Do anyone got the same experience and/or share your opinions?
:>
:>Thanks in Advance!
Received on Fri Nov 24 2000 - 12:18:49 CST