Home » RDBMS Server » Performance Tuning » Table Partioning Issue (Oracle 11g)
Table Partioning Issue [message #580825] Thu, 28 March 2013 07:15 Go to next message
VersatileAjay
Messages: 4
Registered: March 2013
Location: Pune
Junior Member
Hi Team

As per Article mentioned in Oracle Base,
I have converted non-partioned table (1 million data) into range-partion table,but,I dont see performance improvement in explain Confused

Attached complete detais.

Your expert assistance will be great help for me.


Thanks
Re: Table Partioning Issue [message #580826 is a reply to message #580825] Thu, 28 March 2013 07:33 Go to previous messageGo to next message
cookiemonster
Messages: 11177
Registered: September 2008
Location: Rainy Manchester
Senior Member
A 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

[Updated on: Thu, 28 March 2013 07:33]

Report message to a moderator

Re: Table Partioning Issue [message #580829 is a reply to message #580826] Thu, 28 March 2013 08:13 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 11177
Registered: September 2008
Location: Rainy Manchester
Senior Member
cookiemonster wrote on Thu, 28 March 2013 12:33
A 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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 11177
Registered: September 2008
Location: Rainy Manchester
Senior Member
VersatileAjay wrote on Thu, 28 March 2013 13:56
My 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.
Re: Table Partioning Issue [message #580842 is a reply to message #580836] Thu, 28 March 2013 09:47 Go to previous messageGo to next message
cookiemonster
Messages: 11177
Registered: September 2008
Location: Rainy Manchester
Senior Member
And what makes you think your partitioning scheme would be of any use for the query you are trying to run?
Re: Table Partioning Issue [message #580887 is a reply to message #580842] Fri, 29 March 2013 07:40 Go to previous messageGo to next message
rleishman
Messages: 3701
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Try
AND created_date < TO_DATE ('01/01/2013', 'dd/mm/yyyy')


Ross Leishman
Re: Table Partioning Issue [message #580990 is a reply to message #580887] Sat, 30 March 2013 12:09 Go to previous messageGo to next message
Flyby
Messages: 146
Registered: March 2011
Location: Belgium
Senior Member
perhaps if you get rid of TRUNC(create_date). As trunc is a function, oracle needs to recalculate. See the documentation on using indexes.
->AND create_date < TO_DATE ('01/01/2013', 'dd/mm/yyyy')
Re: Table Partioning Issue [message #581042 is a reply to message #580990] Mon, 01 April 2013 05:02 Go to previous message
VersatileAjay
Messages: 4
Registered: March 2013
Location: Pune
Junior Member
Thanks a ton Oracle experts,it worked Smile,actually,my main concern was,after converting 1 million data into range-partition for year 2011,2012,2013 (each contains 3.5 lack records).
There is no much difference in performance.

Previous Topic: Why would you want to avoid and how do you limit oracle redo?
Next Topic: Full table scan. Indexes are available.
Goto Forum:
  


Current Time: Sun Nov 23 21:08:18 CST 2014

Total time taken to generate the page: 0.05598 seconds