FreeLists / oracle-l / Re: B-Tree to Partitioned index

[Vladimir Begun <Vladimir.Begun@xxxxxxxxxx>]

Happy New Year, All! :)

If it's not done yet during holidays downtime...

 > Is there anyway to move from a "normal" b-tree
 > index to a locally partitioned index online
 > without having to to a rebuild?

Negative.

 > We have fairly large databases (15 TB for the one
 > that we are concerned with now) and we need to get
 > one of the highest hitting indexes rebuilt into a
 > partitioned index, but we have no way to do that
 > elegantly when we are dumping nearly 50 GB an hour
 > into the database 24 hours a day.

There is an approach you can think of and evaluate(!)
it for your system. You did not mentioned Oracle version
I checked the scripts on 9.2.0.5.

There are two options, I believe, depending on the
answer to this question -- whether or not you'd
need to use 'ONLINE' option to rebuild one (I hope
it would be one, that one that experiences active
DML) index partition.

a) you do not need to use it: DEFINE value=""

b) you need to use it: DEFINE value="ONLINE"

then you definitely would have to take care of
concurrent DML activity -- on-logon trigger could
help. This is to set

ALTER SESSION SET SKIP_UNUSABLE_INDEXES=TRUE;

Example:
DROP TABLE employees;
CREATE TABLE employees (
   employee_id                      NUMBER(4) NOT NULL
, last_name                        VARCHAR2(10)
, department_id                    NUMBER(2)
)
PARTITION BY RANGE (department_id)
(
   PARTITION employees_part1 VALUES LESS THAN (11)
, PARTITION employees_part2 VALUES LESS THAN (21)
, PARTITION employees_part3 VALUES LESS THAN (31)
)
/
CREATE INDEX employees_global_idx ON employees(employee_id)
/
CREATE INDEX employees_local_idx ON employees (employee_id, SUBSTR('&&value', 
1, 1)) LOCAL UNUSABLE
/

In case for any of these statement you get ORA-54
ONLINE option can be used to rebuild the partition
(option b):

ALTER INDEX employees_local_idx REBUILD PARTITION employees_part1 NOLOGGING 
PARALLEL;
ALTER INDEX employees_local_idx REBUILD PARTITION employees_part2 NOLOGGING 
PARALLEL;
ALTER INDEX employees_local_idx REBUILD PARTITION employees_part3 NOLOGGING 
PARALLEL;

Also check DBMS_PCLXUTIL -- it can help to rebuild
unusable indexes only.

The example is simplified but it would give you an
idea towards possible solution(s). There are quite
some exceptions but I hope in your case it would
work.

Note: as soon as the 'fake' index is built you can
create the main one (w/o FBI 'part') using the same
approach.

SQL> SELECT *  FROM v$version WHERE ROWNUM = 1;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production

SQL> CREATE TABLE vb(p NUMBER, d DATE NOT NULL, v VARCHAR2(1000));

Table created.

SQL> INSERT INTO vb
   2  SELECT TRUNC(dbms_random.value * 10000), SYSDATE + dbms_random.value * 
100, dbms_random.value * 100
   3    FROM all_objects
   4   WHERE ROWNUM <= 1000;

1000 rows created.

SQL> COMMIT;

Commit complete.

SQL> CREATE INDEX idx$vb01 ON vb(p, d);

Index created.

SQL> ANALYZE INDEX idx$vb01 VALIDATE STRUCTURE;

Index analyzed.

SQL> SET AUTOTRACE OFF
SQL> SELECT * FROM index_stats;

     HEIGHT     BLOCKS NAME                           PARTITION_NAME            
        LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_ROWS    BR_BLKS 
BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS 
MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED ROWS_PER_KEY 
BLKS_GETS_PER_ACCESS   PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ---------- ------------------------------ 
------------------------------ ---------- ---------- ----------- ---------- 
---------- ---------- ----------- ---------- ----------- --------------- 
------------- ----------------- ----------- ---------- ---------- ------------ 
-------------------- ---------- ------------ -------------- ----------------
          2         16 IDX$VB01                                                 
           1000          4       22978       7996          3          1         
 33       8028           0               0          1000                 1      
 40012      23011         58            1                    3          0       
     0              0                0

SQL> BEGIN
   2    dbms_stats.gather_table_stats(ownname => USER, tabname => 'VB', CASCADE 
=> TRUE);
   3  END;
   4  /

PL/SQL procedure successfully completed.

SQL> VAR p NUMBER
SQL> VAR d VARCHAR2(10)
SQL> EXEC :p := 1; :d := '31/12/2004';

PL/SQL procedure successfully completed.

SQL> SET AUTOTRACE ON EXPLAIN
SQL> SELECT *
   2    FROM vb
   3   WHERE p = :p AND d >= TO_DATE(:d, 'DD/MM/YYYY')
   4  /

no rows selected


Execution Plan
----------------------------------------------------------
    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=51)
    1    0   TABLE ACCESS (BY INDEX ROWID) OF 'VB' (Cost=3 Card=1 Bytes=51)
    2    1     INDEX (RANGE SCAN) OF 'IDX$VB01' (NON-UNIQUE) (Cost=2 Card=1)



SQL> CREATE INDEX idx$vb02 ON vb(p, d, SUBSTR(NULL, 1, 1));

Index created.

SQL> ANALYZE INDEX idx$vb02 VALIDATE STRUCTURE;

Index analyzed.

SQL> SET AUTOTRACE OFF
SQL> SELECT * FROM index_stats;

     HEIGHT     BLOCKS NAME                           PARTITION_NAME            
        LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_ROWS    BR_BLKS 
BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS 
MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED ROWS_PER_KEY 
BLKS_GETS_PER_ACCESS   PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ---------- ------------------------------ 
------------------------------ ---------- ---------- ----------- ---------- 
---------- ---------- ----------- ---------- ----------- --------------- 
------------- ----------------- ----------- ---------- ---------- ------------ 
-------------------- ---------- ------------ -------------- ----------------
          2         16 IDX$VB02                                                 
           1000          4       23978       7996          3          1         
 33       8028           0               0          1000                 1      
 40012      24011         61            1                    3          0       
     0              0                0

SQL> BEGIN
   2    dbms_stats.gather_index_stats(ownname => USER, indname => 'IDX$VB02');
   3  END;
   4  /

PL/SQL procedure successfully completed.

SQL> DROP INDEX idx$vb01;

Index dropped.

SQL> SET AUTOTRACE ON EXPLAIN
SQL> SELECT *
   2    FROM vb
   3   WHERE p = :p AND d >= TO_DATE(:d, 'DD/MM/YYYY')
   4  /

no rows selected


Execution Plan
----------------------------------------------------------
    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=51)
    1    0   TABLE ACCESS (BY INDEX ROWID) OF 'VB' (Cost=3 Card=1 Bytes=51)
    2    1     INDEX (RANGE SCAN) OF 'IDX$VB02' (NON-UNIQUE) (Cost=2 Card=1)

-- 
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
--
http://www.freelists.org/webpage/oracle-l