Reorg Based on Index - Necessary?

[jodie]

Hi!
This is just a question thrown out there for thought... I'm interested in your opinions.

As long as I've been a DBA, we have been using Uniform Extents and now LMT with Uniform Extents. I have never seen the need to reorg unless I'm moving something to a different Tablespace with a different extent size. I've always been under the belief that you reorg if you have fragmentation.

I've been thrown into the DB2 arena lately, and they do reorginizations based on the Clustering Index or Primary Key to get the data into the table in ORDER of the index. They are ALWAYS doing this... there are even scheduled jobs to do the reorgs automatically.

Now I know that doing reorgs in DB2 is easier than in Oracle, they actually have a reorg utility. But is it worth doing in Oracle too? I never thought so, but I performed a little test, and the results are interesting.

The table has ~29 Mil Rows.
Jodie_test_rand table: 1216Meg
Jodie_test_rand_i index: 960Meg
Jodie_test_ord table: 1216Meg
Jodie_test_ord_i index: 768Meg

SQL> select count(*) from jodie_test_rand where a_memb_key=2092;

  COUNT(*)
----------
      7623

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=13)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (RANGE SCAN) OF 'JODIE_TEST_RAND_I' (UNIQUE) (Cost
          =2 Card=2 Bytes=26)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         38  consistent gets
         16  physical reads
       1080  redo size
        200  bytes sent via SQL*Net to client
        219  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select count(*) from jodie_test_ord where a_memb_key=2092;

  COUNT(*)
----------
      7623

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=13)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (RANGE SCAN) OF 'JODIE_TEST_ORD_I' (UNIQUE) (Cost=
          2 Card=2 Bytes=26)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         28  consistent gets
         14  physical reads
        884  redo size
        200  bytes sent via SQL*Net to client
        219  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

You can see the ordered table has a smaller index, did fewer consistent gets and physical reads, and had a smaller redo size.

So - Is it worth doing???

Thanks for your opinions!
Jodie

[slimdave]

Physical row ordering, like you describe, reduces what Oracle calls the "clustering factor" of the index. By colocating all the similar values of the indexed columns it makes index access more efficient (and more likely to happen, in Oracle), and it's a common practice in data warehousing.

In an OLTP system you can do the same thing without requiring regular re-ordering of the table by using a hash cluster instead of a normal heap table.

It seems odd to physically reorder on primary key columns though, since the entries are unique and there are no similar values to cluster together.

Anyway, you could try it, but it's not something i'd want to get obsessive about.

[jodie]

Thanks for your response, Dave.

I'm not really planning on doing this reorging in real life - our tables really aren't large enough to justify it. But I guess it's just something to keep in mind. I'll have to look into hash cluster.. I've never really done anything with it.

As far as ordering on Primary Key.. you're right, with a one column PK, it doesn't make much sense, unless you are doing selects based on range. With a multi-column PK, it works a little better, specially if the first column in the PK tends to be repeated.

Thanks again... and if anyone else has an opinion, I'd be glad to hear it! (I know... it's the Friday before a Holiday weekend.. I'm surprised anybody is even working today!! )

Jodie

[anandkl]

Hi Friends,
We had a very similar situation,where in we had a table with two columns, so we re-created the table as IOT(Index organised tables), with the first column having lot of repeative values, so we used the compress option in IOT and the query is really working fine with good response time.

The table is got 230 Million rows


regards
anandkl