| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: B-Tree to Partitioned index
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.
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 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
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-lReceived on Sat Jan 01 2005 - 20:10:15 CST
 |
 |