RE: Partitioning in 9.2 without ANALYZING tables
- From: "Laimutis Nedzinskas" <lnd@xxxxxxx>
- To: <Oracle-L@xxxxxxxxxxxxx>
- Date: Tue, 8 Nov 2005 16:50:46 -0000
1. ...one of our contractors made some noise that partitioning cannot be
'performant' (e.g indexes wont be used without explicit hints if you had
partitioned tables).
My personnal expierence is such: sometimes index would not be picked by CBO on
partitioned table also non partitioned version would work just fine. It quite
depends on Oracle version I believe. In my expierence - the later the version
the better. A lot depends on CBO configuration as well.
2. "However, I want to partition the tables" There are implications:
a. queries dependant on RULE optimization and RULE hints will ignore them
as sson as query table(s) is(are) partitioned. You can only hope that CBO will
be able to pick the correct plan. It may end up into re-tuning some of the
queries. I don't know if it is possible to do something with outlines, I mean
export outlines of old system and import outlines into the new system.
b. non prefixed (by the partition key) local indexes will only slow down
performance. More partitions means slower performance for such indexes as the
query needs to scan every partition. Global indexes do not suffer that but they
make the table not 100% partitioned. This is especially true for 24x7
environments. Modifying partition would invalidate global indexes.
BTW: Back in Oracle 8i I had to create a global index for one query as Oracle
CBO refused to pick a local index with or without statistics. I think this is
one of the cases I mentioned above regarding CBO and partitioned tables. I
believe your contractor expierenced something like that.
4. Please dont tell me about the reasons to go cost based
There are reasons not to go as long as "it ain't broken"
5. and have pushed but mgt. had not agreed.
if you can prove mgt. that existing plans can be preserved one way or another
if needed then they may agree. Otherwise I understand your mgt.: "if it ain't
broken don't fix it!"
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Vikas Gautam
Sent: 8. nóvember 2005 16:19
To: oracle-l@xxxxxxxxxxxxx
Subject: Partitioning in 9.2 without ANALYZING tables
We have a legacy oracle 9.2 database with no, repeat no, statistics as
we run rule based (dont ask why) optimizer.
Please dont tell me about the reasons to go cost based as I know and
have pushed but mgt. had not agreed.
However, I want to partition the tables and one of our contractors made
some noise that partitioning cannot be 'performant' (e.g indexes wont be used
without explicit hints if you had partitioned tables).
My question is whether this is true and unless we analyze the tables
first (and move to CBO) we cannot do partitioning.
Thanks
Vikas
Other related posts:
