Had a great discussion with few techi folks today. It started with SQL tuning (my area of interest ) and eventually partitioning. I was sticking towards SQL Tuning + Partition Elimination + Global Indexes. And they were sticking towards Full table Scan + Maintenance Activities + Local Indexes. The discussion was heating up and i made up my mind to switch lane :) when two more AK47 was kept before, yes Partitioned table with Tera Bytes of data + Archiving Strategy.
Does this look like OLTP vs OLAP. Big Table Maintenance + Archiving Strategy would shatter the usage of global indexes, but lets see what happens when global indexes are not present. Let's see them in detail.
Loading data
Lets check the status of my indexes
As part of archiving strategy lets drop a Partition
Lets create the unique index on column x
In just simple words, it means that the partition key and key columns should have a link
And now their impact over dropping partitions
Have loaded 12,60,000 records, which is off-course very tiny. Loaded using similar statements like below,
Does this look like OLTP vs OLAP. Big Table Maintenance + Archiving Strategy would shatter the usage of global indexes, but lets see what happens when global indexes are not present. Let's see them in detail.
SQL> set timing on
SQL>
SQL> drop table t2;
Table dropped.
Elapsed: 00:00:00.40
SQL>
SQL> CREATE TABLE t2
2 (
3 x int not null primary key,
4 dt date,
5 y varchar2(30)
6 )
7 PARTITION BY RANGE(dt)
8 (
9 PARTITION part1_FEB2013 VALUES LESS THAN (to_date('01-Feb-2013','dd-mon-yyyy')) ,
10 PARTITION part1_MAR2013 VALUES LESS THAN (to_date('01-Mar-2013','dd-mon-yyyy')) ,
11 PARTITION part1_APR2013 VALUES LESS THAN (to_date('01-Apr-2013','dd-mon-yyyy')) ,
12 PARTITION part2_MAY2013 VALUES LESS THAN (to_date('01-May-2013','dd-mon-yyyy')),
13 PARTITION junk VALUES LESS THAN (MAXVALUE)
14 );
Table created.
Elapsed: 00:00:00.10
SQL>
SQL> create index idx_y on t2(y);
Index created.
Elapsed: 00:00:00.03
SQL>
SQL> insert into t2 values (1,sysdate-120,'ORACLE'); 1 row created. Elapsed: 00:00:00.02 SQL> insert into t2 values (2,sysdate-90,'ORACLE'); 1 row created. Elapsed: 00:00:00.02 SQL> insert into t2 values (3,sysdate-60,'ORACLE'); 1 row created. Elapsed: 00:00:00.03 SQL> insert into t2 values (4,sysdate-30,'ORACLE'); 1 row created. Elapsed: 00:00:00.01 SQL> insert into t2 values (5,sysdate,'ORACLE'); 1 row created. Elapsed: 00:00:00.02 SQL> commit; Commit complete. Elapsed: 00:00:00.00 SQL>
SQL> select index_name,status from user_indexes where table_name ='T2'; INDEX_NAME STATUS ------------------------------ -------- IDX_Y VALID SYS_C0012417 VALID Elapsed: 00:00:00.08
SQL> alter table t2 drop partition part2_MAY2013; Table altered. Elapsed: 00:00:00.07 SQL> SQL> select index_name,status from user_indexes where table_name='T2' 2 ; INDEX_NAME STATUS ------------------------------ -------- IDX_Y UNUSABLE SYS_C0012417 UNUSABLE Elapsed: 00:00:00.04 SQL>
Dropping partition leading to un-usability of indexes, If i create a Global Index, then would be looking forward to use my Global Index though skip_unusable_indexes defaults to true in Oracle 11g.
In general archiving strategy can include the below options
1. Drop Partition with Update Indexes Clause.Update Global Index makes you wait
2. Truncate partition(Partition wise) with Update Indexes option + Drop Partition
3. Drop Partition + Rebuild the Index
4. Delete data partition wise + Drop partition - Delete would update the Global Index And the easiest way would be
5. Drop partitions - Table without Global Indexes.
Now the question here is if we have to create a table without global index, how would the primary key look like,
1. Drop Partition with Update Indexes Clause.Update Global Index makes you wait
2. Truncate partition(Partition wise) with Update Indexes option + Drop Partition
3. Drop Partition + Rebuild the Index
4. Delete data partition wise + Drop partition - Delete would update the Global Index And the easiest way would be
5. Drop partitions - Table without Global Indexes.
Now the question here is if we have to create a table without global index, how would the primary key look like,
SQL> CREATE TABLE t2
2 (
3 x int not null,
4 dt date not null,
5 y varchar2(30)
6 )
7 PARTITION BY RANGE(dt)
8 (
9 PARTITION part1_FEB2013 VALUES LESS THAN (to_date('01-Feb-2013','dd-mon-yyyy')) ,
10 PARTITION part1_MAR2013 VALUES LESS THAN (to_date('01-Mar-2013','dd-mon-yyyy')) ,
11 PARTITION part1_APR2013 VALUES LESS THAN (to_date('01-Apr-2013','dd-mon-yyyy')) ,
12 PARTITION part2_MAY2013 VALUES LESS THAN (to_date('01-May-2013','dd-mon-yyyy')),
13 PARTITION junk VALUES LESS THAN (MAXVALUE)
14 );
Table created.
SQL>
SQL> create unique index idx_xdt on t2(x) local;
create unique index idx_xdt on t2(x) local
*
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index
SQL> REM - Lets create it in the form of a subset of key columns SQL> SQL> create unique index idx_xdt on t2(x,dt) local; Index created. SQL> SQL> SQL> create index idx_y on t2(y); Index created. SQL> SQL> SQL> insert into t2 values (1,sysdate-120,'ORACLE'); 1 row created. SQL> insert into t2 values (2,sysdate-90,'ORACLE'); 1 row created. SQL> insert into t2 values (3,sysdate-60,'ORACLE'); 1 row created. SQL> insert into t2 values (4,sysdate-30,'ORACLE'); 1 row created. SQL> insert into t2 values (5,sysdate,'ORACLE'); 1 row created. SQL> commit; Commit complete.
SQL>
SQL> select index_name,status from user_indexes where table_name in('T2');
INDEX_NAME STATUS
------------------------------ --------
IDX_Y VALID
IDX_XDT N/A
SQL>
SQL> select index_name,locality from user_part_indexes where table_name in('T2');
INDEX_NAME LOCALI
------------------------------ ------
IDX_XDT LOCAL
SQL>
SQL>
SQL> alter table t2 drop partition part2_MAY2013;
Table altered.
SQL>
SQL> select index_name,status from user_indexes where table_name='T2';
INDEX_NAME STATUS
------------------------------ --------
IDX_Y UNUSABLE
IDX_XDT N/A
SQL>
The actual core issue is GLOBAL INDEX becoming unusable and then they have to be rebuild. It really becomes too time consuming when the table is huge and DBA's would often start praying when they rebuild index
SQL> SQL> alter index idx_y rebuild; Index altered. SQL> select index_name,status from user_indexes where table_name='T2'; INDEX_NAME STATUS ------------------------------ -------- IDX_Y VALID IDX_XDT N/A SQL>
So worried about rebuilding global index post dropping a partition as part of archival strategy, Now lets see what happens when we don't have global index. To demonstrate the same, let me create a table with 133 partitions,
drop table t2;
CREATE TABLE t2
(
x int not null,
dt date not null,
y varchar2(30)
)
PARTITION BY RANGE(dt)
(
PARTITION part1_JAN2003 VALUES LESS THAN (to_date('01-JAN-2003','dd-mon-yyyy')) ,
PARTITION part1_FEB2003 VALUES LESS THAN (to_date('01-FEB-2003','dd-mon-yyyy')) ,
PARTITION part1_MAR2003 VALUES LESS THAN (to_date('01-MAR-2003','dd-mon-yyyy')) ,
PARTITION part1_APR2003 VALUES LESS THAN (to_date('01-APR-2003','dd-mon-yyyy')) ,
PARTITION part1_MAY2003 VALUES LESS THAN (to_date('01-MAY-2003','dd-mon-yyyy')) ,
PARTITION part1_JUN2003 VALUES LESS THAN (to_date('01-JUN-2003','dd-mon-yyyy')) ,
PARTITION part1_JUL2003 VALUES LESS THAN (to_date('01-JUL-2003','dd-mon-yyyy')) ,
PARTITION part1_AUG2003 VALUES LESS THAN (to_date('01-AUG-2003','dd-mon-yyyy')) ,
PARTITION part1_SEP2003 VALUES LESS THAN (to_date('01-SEP-2003','dd-mon-yyyy')) ,
PARTITION part1_OCT2003 VALUES LESS THAN (to_date('01-OCT-2003','dd-mon-yyyy')) ,
PARTITION part1_NOV2003 VALUES LESS THAN (to_date('01-NOV-2003','dd-mon-yyyy')) ,
PARTITION part1_DEC2003 VALUES LESS THAN (to_date('01-DEC-2003','dd-mon-yyyy')) ,
PARTITION part1_JAN2004 VALUES LESS THAN (to_date('01-JAN-2004','dd-mon-yyyy')) ,
PARTITION part1_FEB2004 VALUES LESS THAN (to_date('01-FEB-2004','dd-mon-yyyy')) ,
PARTITION part1_MAR2004 VALUES LESS THAN (to_date('01-MAR-2004','dd-mon-yyyy')) ,
PARTITION part1_APR2004 VALUES LESS THAN (to_date('01-APR-2004','dd-mon-yyyy')) ,
PARTITION part1_MAY2004 VALUES LESS THAN (to_date('01-MAY-2004','dd-mon-yyyy')) ,
PARTITION part1_JUN2004 VALUES LESS THAN (to_date('01-JUN-2004','dd-mon-yyyy')) ,
PARTITION part1_JUL2004 VALUES LESS THAN (to_date('01-JUL-2004','dd-mon-yyyy')) ,
PARTITION part1_AUG2004 VALUES LESS THAN (to_date('01-AUG-2004','dd-mon-yyyy')) ,
PARTITION part1_SEP2004 VALUES LESS THAN (to_date('01-SEP-2004','dd-mon-yyyy')) ,
PARTITION part1_OCT2004 VALUES LESS THAN (to_date('01-OCT-2004','dd-mon-yyyy')) ,
PARTITION part1_NOV2004 VALUES LESS THAN (to_date('01-NOV-2004','dd-mon-yyyy')) ,
PARTITION part1_DEC2004 VALUES LESS THAN (to_date('01-DEC-2004','dd-mon-yyyy')) ,
PARTITION part1_JAN2005 VALUES LESS THAN (to_date('01-JAN-2005','dd-mon-yyyy')) ,
......
PARTITION part1_DEC2013 VALUES LESS THAN (to_date('01-DEC-2013','dd-mon-yyyy')) ,
PARTITION junk VALUES LESS THAN (MAXVALUE)
);
Table created.
SQL>
SQL> create unique index idx_xdt on t2(x,dt) local;
Index created.
SQL> select partition_count from user_part_tables where table_name = 'T2';
PARTITION_COUNT
---------------
133
Have loaded 12,60,000 records, which is off-course very tiny. Loaded using similar statements like below,
insert into t2
select level,
to_date('01-JUL-2003','dd-mon-yyyy')+ mod(level,30),
'ORACLE'||mod(level,1000)
from dual
connect by level < 10001;
The records are spread across evenly, like below
SQL> select count(*),y from t2 group by y;
COUNT(*) Y
---------- ------------------------------
1260 ORACLE90
1260 ORACLE101
1260 ORACLE102
1260 ORACLE107
1260 ORACLE110
1260 ORACLE118
1260 ORACLE124
1260 ORACLE132
1260 ORACLE141
1260 ORACLE157
1260 ORACLE163
1260 ORACLE171
1260 ORACLE172
1260 ORACLE177
1260 ORACLE181
1260 ORACLE185
....
Lets create a global index on column- Y
SQL> create index idx_y on t2(y) global;
Index created.
Elapsed: 00:00:31.30
SQL> exec dbms_stats.gather_table_stats('HARI','T2');
PL/SQL procedure successfully completed.
Elapsed: 00:00:44.39
SQL>
Issue the culprit :)
SQL> update t2 set y='ORACLE_ORACLE' where x = 124587; 1 row updated. Elapsed: 00:00:00.00 SQL> commit; Commit complete. Elapsed: 00:00:00.00 SQL>
Lets check the selection part,
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:12.13
SQL>
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.11
SQL> select * from t2 where y='ORACLE_ORACLE';
X DT Y
---------- --------- ------------------------------
124587 28-JUL-04 ORACLE_ORACLE
Elapsed: 00:00:01.37
SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID c6tupfvyvdyaq, child number 0
-------------------------------------
select * from t2 where y='ORACLE_ORACLE'
Plan hash value: 2614863613
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1265 (100)| | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| T2 | 1259 | 28957 | 1265 (0)| 00:00:16 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN | IDX_Y | 1259 | | 7 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("Y"='ORACLE_ORACLE')
19 rows selected.
Elapsed: 00:00:02.68
SQL>
Let me drop the Global Index and check the same
SQL> drop index idx_y;
Index dropped.
Elapsed: 00:00:01.68
SQL>
SQL> select * from t2 where y='ORACLE_ORACLE';
X DT Y
---------- --------- ------------------------------
124587 28-JUL-04 ORACLE_ORACLE
Elapsed: 00:00:05.72
SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID c6tupfvyvdyaq, child number 0
-------------------------------------
select * from t2 where y='ORACLE_ORACLE'
Plan hash value: 2884929167
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1486 (100)| | | |
| 1 | PARTITION RANGE ALL| | 1260 | 28980 | 1486 (2)| 00:00:18 | 1 | 133 |
|* 2 | TABLE ACCESS FULL | T2 | 1260 | 28980 | 1486 (2)| 00:00:18 | 1 | 133 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("Y"='ORACLE_ORACLE')
19 rows selected.
Elapsed: 00:00:02.47
SQL>
As expected Partition Elimination doesn't happen and the optimizer has to look into all the partition. Though the elapsed time for FTS + Partition ALL is slightly cheaper in this case, we will have to pay when the table grows and Partition Pruning doesn't happen. Global Indexes helps us in identifying the partition. But for a moment do you think you can go with Local Index, Ok lets check that too :)
SQL> create index idx_local_y on t2(y) local;
Index created.
Elapsed: 00:00:11.94
SQL> select index_name, locality from user_part_indexes where table_name='T2';
INDEX_NAME LOCALI
------------------------------ ------
IDX_XDT LOCAL
IDX_LOCAL_Y LOCAL
Elapsed: 00:00:00.23
SQL>
SQL> select * from t2 where y='ORACLE_ORACLE';
X DT Y
---------- --------- ------------------------------
124587 28-JUL-04 ORACLE_ORACLE
Elapsed: 00:00:03.26
SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID c6tupfvyvdyaq, child number 0
-------------------------------------
select * from t2 where y='ORACLE_ORACLE'
Plan hash value: 2685712566
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1396 (100)| | | |
| 1 | PARTITION RANGE ALL | | 1259 | 28957 | 1396 (1)| 00:00:17 | 1 | 133 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| T2 | 1259 | 28957 | 1396 (1)| 00:00:17 | 1 | 133 |
|* 3 | INDEX RANGE SCAN | IDX_LOCAL_Y | 1259 | | 137 (0)| 00:00:02 | 1 | 133 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("Y"='ORACLE_ORACLE')
20 rows selected.
Elapsed: 00:00:05.22
The above is a clear example of reading multiple volumes of a book with each chapter being identified through Book Index :), Got it, i.e, the local index is being used to read all partitions of the table.
Conclusion
Can partitioned table exists without a Single Global Index.Yes, Rebuilding Index is a nightmare in few circumstances, but at what cost, shouldn't i help the optimizer in performing Partition Pruning through Global Index. Dropping Partitions-Wise is definitely the efficient/effective and easiest way to perform data archival(Facilitated through Local Index). Its always Benefit Vs Cost, It is up to the application/architect team is decide to achieve maximum benefit.
Good one..:)
ReplyDeleteThanks s jaiswal
ReplyDeletevery nice one.
ReplyDelete