RE: Partitioning in 9.2 without ANALYZING tables

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:

1. 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.
2. 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.
3. Please dont tell me about the reasons to go cost based There are reasons not to go as long as "it ain't broken"
4. 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Vikas Gautam Sent: 8. nóvember 2005 16:19
To: oracle-l_at_freelists.org
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
	 

--

http://www.freelists.org/webpage/oracle-l Received on Tue Nov 08 2005 - 10:54:09 CST