OraFAQ Forum: SQL & PL/SQL » Query using Partitioning

Home » SQL & PL/SQL » SQL & PL/SQL » Query using Partitioning (Oracle 11 G release R1)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query using Partitioning [message #637256]

Wed, 13 May 2015 09:29

saurabh.sha
Messages: 15
Registered: March 2015
Location: india

Junior Member

Hi Friends,

I have using a query for which i was able to identify the column( status - having very less unique values) which can be used for partioining to speed up the processing however on running the query i see the execution plan of the query using partition shows that its using dynamic partition pruning, but there was no significant improvement in any of the query statistics. Need help if there are nay gaps due to which it didn't help

Do we need to rewrite the query is certain way, i am attaching the query ( it is a small query). please give your suggestions

Note:-

the field used for partition is status
I tested both the queries i.e query using non-partitioned history table and query using partitioned history table.
Flushed the shared pool before the beginning of each test

Attachment: query.txt
(Size: 0.89KB, Downloaded 1008 times)

Report message to a moderator

Re: Query using Partitioning [message #637257 is a reply to message #637256]

Wed, 13 May 2015 09:39

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof

Report message to a moderator

Re: Query using Partitioning [message #637265 is a reply to message #637256]

Wed, 13 May 2015 16:35

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

You do not use the column STATUS anywhere in your predicate or join, so there is no way that partioning on it can give any benefit. If you are using local indexes, it will probably slow things down.

Report message to a moderator

Re: Query using Partitioning [message #637275 is a reply to message #637256]

Thu, 14 May 2015 06:50

saurabh.sha
Messages: 15
Registered: March 2015
Location: india

Junior Member

HI John,

Please find attached tha explain plain ( it shows both the plan with and without partition).

i am using ors2.status+ < 9000 in the query and believe that list partiotioning will use this relational operator.

Please advise.

Thanks

Attachment: explain_plan_poc.txt
(Size: 6.76KB, Downloaded 1380 times)

Report message to a moderator

Re: Query using Partitioning [message #637286 is a reply to message #637275]

Thu, 14 May 2015 07:32

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

I don't know what you have done, or what you are trying to do. But I do know that using list partioning, which you say are using, is usless when you use a non-equality predicate.

If you want assistance, you'll have to follow the instructions that the BlackSwan gave you.

Report message to a moderator

Previous Topic:	To take Previous date
Next Topic:	want data of all columns in a single column (no concatenate)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 12:11:31 CDT 2024