Re: Oracle 8.1.7 - Performance - Index, Partition and Partition-ed index

From: ddf <oratune_at_msn.com>
Date: Mon, 13 Aug 2012 09:32:26 -0700 (PDT)
Message-ID: <4f5d6973-8505-4106-9625-d3378c51878f_at_y5g2000yqj.googlegroups.com>

On Aug 13, 5:19 am, raja <dextersu..._at_gmail.com> wrote:
> Hi All,
>

Comments embedded.

> I have a table (Table1) with around 250 million records in Oracle 8.1.7., of which 2 columns (column1 and column2) based on values : A, B, C, D & W, X, Y, Z respectively.
>
> Can someone please clarify my doubts :
> 1. I have 3 scenarios for column1. Creating a Partition alone (or) Partition-ed index (or) Non-Partitioned Index on column1 :

Not really as you cannot create a partitioned index on a nonpartitioned  table, even in 11.2:

SQL> create index emp__empno_idx
  2 on emp(empno)
  3 partition by range (empno)

  4  partition_first values less than (7300)
  5  partition_second values less than (7400)
  6  partition_third values less than (7500)
  7  partition_fourth values less than (7600)
  8  partition_fifth values less than (7700)
  9  partition_sixth values less than (7800)
 10  partition_seventh values less than (7900)
 11  partition_eighth values less than (8000)
 12  partition_last values less than (maxvalue);

partition by range (empno)
*
ERROR at line 3:
ORA-02158: invalid CREATE INDEX option

SQL> So you have two options -- create a partitioned table (then create the partitioned index) or create the index.

> a. how will the data be accessed for the above 3 scenarios

Since you can't create a partitioned index on a non-partitioned table you have two scenarios. Presumably you'll get partition pruning with option 1 (create the table partitioned); you'll get, most likely, either an index scan or an index range scan for the second of your two available options. This is 8.1.7 (a museum piece) so the optimizer choices are few.

> b. which is better with respect to performance

Only you can decide that; you'll need to test this with a partitioned copy of your source table to see what the plans are and how efficient 8.1.7 is at partitioned access given the data skew in the table then test this with a non-partitioned, indexed copy of the same table and compare notes.

> 2. I am planning to create a complex partition on column1 and column2, since the queries on table1 is based on column1 and column2 :

Complex partition? You must mean a composite partition: 'main' partition on column1 with subpartition on column2. You can mix partitioning types -- range partition with a hash subpartition, for example. What that may do for or to performance you'll need to test.

> a. Are complex partitions (partition and subpartition) are based on range and hash only (or) hash and hash too is possible in Oracle 8.1.7

Subpartition types can be the same as the main partition type or different, as I mentioned above. You really need to read here:

http://docs.oracle.com/cd/A87860_01/doc/server.817/a76956/partiti.htm

http://tahiti.oracle.com is your friend if you choose to use it.

>
> Thanks in Advance.
>
> With Regards,
> Raja.

David Fitzjarrell Received on Mon Aug 13 2012 - 11:32:26 CDT