Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Strange CBO Behaviour with a Bitmap Index

Re: Strange CBO Behaviour with a Bitmap Index

From: Jimbo1 <jamestheboarder_at_googlemail.com>
Date: 7 Feb 2006 02:30:43 -0800
Message-ID: <1139308242.990738.83850@z14g2000cwz.googlegroups.com>


>Is it possible to partition or subpartition the table on
>REPORT_POPULATED column.
>Row movement should be enabled for the table.
>
>Joseph Amalraj

Hi Joseph,

I think that would lend itself to list partitioning extremely well. Unfortunately, I'm running in an 8i database, so that's not an option.

Anyway, my plan to use a global binary tree index and scan in descending order has worked and runs very quickly. I'll post the full methodology and benchmarking results up when I've got the chance - I'm a tad busy at the moment. Basically, I used the new index to retrieve the max and min date values with REPORT_POPULATED = 'Y', and deleted everything within that date range, with uses the composite local index on the table.

This table will always have the following logical data distribution:

TIME_STAMP        VALUE_OF_INTEREST        REPORT_POPULATED
--------------------        -----------------------------------
----------------------------------
timestamp1                 some_value1
  N
timestamp2                 some_value2
  N
timestamp3                 some_value3
  N
timestamp4                 some_value4
  N
timestamp5                 some_value5
  Y
timestamp6                 some_value6
  Y
timestamp7                 some_value7
  Y
timestamp8                 some_value8

  Y
etc.................

REPORT_POPULATED = 'N' will never be intermixed with REPORT_POPULATED = 'Y'. Received on Tue Feb 07 2006 - 04:30:43 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US