Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Strange CBO Behaviour with a Bitmap Index
>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
etc.................
REPORT_POPULATED = 'N' will never be intermixed with REPORT_POPULATED = 'Y'. Received on Tue Feb 07 2006 - 04:30:43 CST