| Solution req :Performance issue with partitioned table [message #514546] |
Tue, 05 July 2011 06:07  |
 |
ganeshkn21
Messages: 36
Registered: June 2011
Location: bangalore
|
Member |
|
|
hi,
i have a small issue..
a table test is partioned ( 2 partitions by list -p1 ,p2)
partition p1 -has 100000 records
partition p2 -has 600000 records
we have a query which has about 10 tables
in the join with (+) outer joins.
The table test is indexed on the parition key.
but the query does a full table scan on test table.
we have mentioned
select test.t1, a.a1, b.b1......,f.f1
from test partition (p1) ,a,b,c...f
where test.pk=a.pk(+)
........
the response time with the partition clause & without the opartition clause is very negligible (only 1 sec improvement).
The cost reduced drastically with the partition clause(the plan showed a change from partition list all to partition table single )
any suggestions to improve the time pls
regards
ganeshkn21
|
|
|
|
|
|
| Re: Solution req :Performance issue with partitioned table [message #514551 is a reply to message #514549] |
Tue, 05 July 2011 06:28  |
|
ganeshkn21
Messages: 36
Registered: June 2011
Location: bangalore
|
Member |
|
|
hi sorry,
the response time of the query without the partition clause was about 8-10 sec approx
(explain plan cost 1932 )
select test.t1, a.a1, b.b1......,f.f1
from test ,a,b,c...f
where test.pk=a.pk(+)
........
the response time of the query with the partition clause was about 7-9 sec approx
(explain plan cost 67 )
select test.t1, a.a1, b.b1......,f.f1
from test partition (p1) ,a,b,c...f
where test.pk=a.pk(+)
........
only 1 sec improvement in response time.
the explain plan cost went down from about 1932 to
any suggestions to improve the time pls
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
