Home » RDBMS Server » Performance Tuning » Table Partioning Issue (Oracle 11g)
|
|
|
|
| Re: Table Partioning Issue [message #580829 is a reply to message #580826] |
Thu, 28 March 2013 08:13   |
 |
VersatileAjay
Messages: 4
Registered: March 2013
Location: Pune
|
Junior Member |
|
|
Hi Team
As per Oracle Base article:I have tested same for below 1 million data in 11g database,but,I dont see any difference in explain plan.Does range partion works in real time?
Anyone can copy this code and execute in DB in few mins to test it.
TABLE PARTION OF AN EXISTING TABLE DATA
Create a Sample Schema
First we create a sample schema as our starting point.
-- Create and populate a small lookup table.
CREATE TABLE lookup (
id NUMBER(10),
description VARCHAR2(50)
);
ALTER TABLE lookup ADD (
CONSTRAINT lookup_pk PRIMARY KEY (id)
);
INSERT INTO lookup (id, description) VALUES (1, 'ONE');
INSERT INTO lookup (id, description) VALUES (2, 'TWO');
INSERT INTO lookup (id, description) VALUES (3, 'THREE');
COMMIT;
-- Create and populate a larger table that we will later partition.
CREATE TABLE big_table (
id NUMBER(10),
created_date DATE,
lookup_id NUMBER(10),
data VARCHAR2(50)
);
DECLARE
l_lookup_id lookup.id%TYPE;
l_create_date DATE;
BEGIN
FOR i IN 1 .. 1000000 LOOP
IF MOD(i, 3) = 0 THEN
l_create_date := ADD_MONTHS(SYSDATE, -24);
l_lookup_id := 2;
ELSIF MOD(i, 2) = 0 THEN
l_create_date := ADD_MONTHS(SYSDATE, -12);
l_lookup_id := 1;
ELSE
l_create_date := SYSDATE;
l_lookup_id := 3;
END IF;
INSERT INTO big_table (id, created_date, lookup_id, data)
VALUES (i, l_create_date, l_lookup_id, 'This is some data for ' || i);
END LOOP;
COMMIT;
END;
/
-- Apply some constraints to the table.
ALTER TABLE big_table ADD (
CONSTRAINT big_table_pk PRIMARY KEY (id)
);
CREATE INDEX bita_created_date_i ON big_table(created_date);
CREATE INDEX bita_look_fk_i ON big_table(lookup_id);
ALTER TABLE big_table ADD (
CONSTRAINT bita_look_fk
FOREIGN KEY (lookup_id)
REFERENCES lookup(id)
);
-- Gather statistics on the schema objects
EXEC DBMS_STATS.gather_table_stats(USER, 'LOOKUP', cascade => TRUE);
EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE', cascade => TRUE);
Create a Partitioned Interim Table
Next we create a new table with the appropriate partition structure to act as an interim table.
-- Create partitioned table.
CREATE TABLE big_table2 (
id NUMBER(10),
created_date DATE,
lookup_id NUMBER(10),
data VARCHAR2(50)
)
PARTITION BY RANGE (created_date)
(PARTITION big_table_2011 VALUES LESS THAN (TO_DATE('01/01/2012', 'DD/MM/YYYY')),
PARTITION big_table_2012 VALUES LESS THAN (TO_DATE('01/01/2013', 'DD/MM/YYYY')),
PARTITION big_table_2013 VALUES LESS THAN (MAXVALUE));
With this interim table in place we can start the online redefinition.
Start the Redefintion Process
First we check the redefinition is possible using the following command.
EXEC Dbms_Redefinition.Can_Redef_Table('PRESTAGE', 'BIG_TABLE');
If no errors are reported it is safe to start the redefintion using the following command.
BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => 'PRESTAGE',
orig_table => 'BIG_TABLE',
int_table => 'BIG_TABLE2');
END;
/
Depending on the size of the table, this operation can take quite some time to complete.
Create Constraints and Indexes
If there is delay between the completion of the previous operation and moving on to finish the redefinition, it may be sensible to resynchronize the interim table before building any constraints and indexes. The resynchronization of the interim table is initiated using the following command.
-- Optionally synchronize new table with interim data before index creation
BEGIN
dbms_redefinition.sync_interim_table(
uname => USER,
orig_table => 'BIG_TABLE',
int_table => 'BIG_TABLE2');
END;
/
The constraints and indexes from the original table must be applied to interim table using alternate names to prevent errors. The indexes should be created with the appropriate partitioning scheme to suit their purpose.
-- Add new keys, FKs and triggers.
ALTER TABLE big_table2 ADD (
CONSTRAINT big_table_pk2 PRIMARY KEY (id)
);
CREATE INDEX bita_created_date_i2 ON big_table2(created_date) LOCAL;
CREATE INDEX bita_look_fk_i2 ON big_table2(lookup_id) LOCAL;
ALTER TABLE big_table2 ADD (
CONSTRAINT bita_look_fk2
FOREIGN KEY (lookup_id)
REFERENCES lookup(id)
);
-- Gather statistics on the new table.
EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE2', cascade => TRUE);
Complete the Redefintion Process
Once the constraints and indexes have been created the redefinition can be completed using the following command.
BEGIN
dbms_redefinition.finish_redef_table(
uname => USER,
orig_table => 'BIG_TABLE',
int_table => 'BIG_TABLE2');
END;
/
At this point the interim table has become the "real" table and their names have been switched in the data dictionary. All that remains is to perform some cleanup operations.
-- Remove original table which now has the name of the interim table.
DROP TABLE big_table2;
-- Rename all the constraints and indexes to match the original names.
ALTER TABLE big_table RENAME CONSTRAINT big_table_pk2 TO big_table_pk;
ALTER TABLE big_table RENAME CONSTRAINT bita_look_fk2 TO bita_look_fk;
ALTER INDEX big_table_pk2 RENAME TO big_table_pk;
ALTER INDEX bita_look_fk_i2 RENAME TO bita_look_fk_i;
ALTER INDEX bita_created_date_i2 RENAME TO bita_created_date_i;
The following queries show that the partitioning was successful.
SELECT partitioned
FROM user_tables
WHERE table_name = 'BIG_TABLE';
PAR
---
YES
1 row selected.
SELECT partition_name
FROM user_tab_partitions
WHERE table_name = 'BIG_TABLE';
PARTITION_NAME
------------------------------
BIG_TABLE_2011
BIG_TABLE_2012
BIG_TABLE_2013
3 rows selected.
|
|
|
|
| Re: Table Partioning Issue [message #580830 is a reply to message #580826] |
Thu, 28 March 2013 08:18 |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
cookiemonster wrote on Thu, 28 March 2013 12:33A lot of people here can't/won't download attachments.
You should post the details of what you have directly, with code in code tags as described here
So you didn't bother to read the 2nd part of the 2nd sentence then?
That's not a good way to get people to help you.
In addition to posting properly, can you show us the query that isn't making use of the partitioning and the explain plan for it.
|
|
|
|
| Re: Table Partioning Issue [message #580835 is a reply to message #580830] |
Thu, 28 March 2013 08:56 |
|
VersatileAjay
Messages: 4
Registered: March 2013
Location: Pune
|
Junior Member |
|
|
My sincere apology,I used formatting,but,I am not able to do it I wanted to paste text here.
Actually,I work on TOAD and see the explain plan there itself.
Here,is from sql*plus:
******Explain Plan for converted Range Partioned table : BIG_TABLE
SQL> SET AUTOTRACE TRACEONLY
SQL> SELECT *
FROM big_table a, lookup b
WHERE a.id <> b.id
AND TRUNC (created_date) < TO_DATE ('01/01/2013', 'dd/mm/yyyy'); 2 3 4
1999999 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2541085105
--------------------------------------------------------------------------------
------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
| Pstart| Pstop |
--------------------------------------------------------------------------------
------------------
| 0 | SELECT STATEMENT | | 1907K| 185M| 5836 (2)| 00:01:11
| | |
| 1 | NESTED LOOPS | | 1907K| 185M| 5836 (2)| 00:01:11
| | |
| 2 | TABLE ACCESS FULL | LOOKUP | 3 | 120 | 3 (0)| 00:00:01
| | |
| 3 | PARTITION RANGE ALL| | 635K| 37M| 1944 (2)| 00:00:24
| 1 | 3 |
|* 4 | TABLE ACCESS FULL | BIG_TABLE | 635K| 37M| 1944 (2)| 00:00:24
| 1 | 3 |
--------------------------------------------------------------------------------
------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(TRUNC(INTERNAL_FUNCTION("CREATED_DATE"))<TO_DATE(' 2013-01-01 00:0
0:00',
'syyyy-mm-dd hh24:mi:ss') AND "A"."ID"<>"B"."ID")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
153568 consistent gets
0 physical reads
116 redo size
114191209 bytes sent via SQL*Net to client
1467187 bytes received via SQL*Net from client
133335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1999999 rows processed
******Explain Plan for Non-Partioned table : BIG_TABLE2
SQL> SET AUTOTRACE TRACEONLY
SQL> SELECT *
FROM big_table2 a, lookup b
WHERE a.id <> b.id
AND TRUNC (created_date) < TO_DATE ('01/01/2013', 'dd/mm/yyyy'); 2 3 4
1999999 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3392949065
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 2007K| 195M| 5718 (2)| 00:01:09
|
| 1 | NESTED LOOPS | | 2007K| 195M| 5718 (2)| 00:01:09
|
| 2 | TABLE ACCESS FULL| LOOKUP | 3 | 120 | 3 (0)| 00:00:01
|
|* 3 | TABLE ACCESS FULL| BIG_TABLE2 | 669K| 39M| 1905 (2)| 00:00:23
|
--------------------------------------------------------------------------------
-
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(TRUNC(INTERNAL_FUNCTION("CREATED_DATE"))<TO_DATE('
2013-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A"."ID"<>"B".
"ID")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
153196 consistent gets
0 physical reads
0 redo size
130064937 bytes sent via SQL*Net to client
1467186 bytes received via SQL*Net from client
133335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1999999 rows processed
|
|
|
|
| Re: Table Partioning Issue [message #580836 is a reply to message #580835] |
Thu, 28 March 2013 09:00 |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
VersatileAjay wrote on Thu, 28 March 2013 13:56My sincere apology,I used formatting,but,I am not able to do it I wanted to paste text here.
So you haven't read the link I posted then. Because if you have, and can't manage to follow it's incredibly simple instructions then there is no way you are capable of programming.
Read the link, follow the instructions.
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Dec 18 01:12:43 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
,actually,my main concern was,after converting 1 million data into range-partition for year 2011,2012,2013 (each contains 3.5 lack records).