Home » RDBMS Server » Performance Tuning » Query optimisation, table partitionning and lack of index (database 10g)
Query optimisation, table partitionning and lack of index [message #439116] Thu, 14 January 2010 06:37 Go to next message
didiera
Messages: 134
Registered: August 2007
Location: Mauritius
Senior Member
Hello friends,

I would like to ask if table partitionning could be used as an alternative to relieve full table scans in queries involving filters based on fields that have not been indexed. To be more clear, we have a production table with many records already (easily millions) and we are not considering altering the existing indexes. I have this query to run against that table which filters records on the record creation date field (for which no index is available) and when analysing the execution plan i find that a costly full table scan goes in the background. In your opinion, could table partitionning serve as some alternative to indexing?

regards,
Didier
Re: Query optimisation, table partitionning and lack of index [message #439120 is a reply to message #439116] Thu, 14 January 2010 07:47 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If it can Oracle does partition pruning, that is it only scans the partitions that are useful.
Of course, it can only to do it if there is some filtering or join condition on the partition key.

Regards
Michel
Re: Query optimisation, table partitionning and lack of index [message #439233 is a reply to message #439116] Fri, 15 January 2010 03:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I find it incredible to believe that you are willing to partition your tables rather than add new indexes or alter existing ones.

You do know that partitioning is an extra option that you have to buy, don't you?
Re: Query optimisation, table partitionning and lack of index [message #439367 is a reply to message #439233] Fri, 15 January 2010 21:36 Go to previous messageGo to next message
didiera
Messages: 134
Registered: August 2007
Location: Mauritius
Senior Member
Hello,

yes I know this all sounds crazy. The fact is that internal unofficial "standards" are very jumpy on us developpers introducing 'ground breaking' changes to production structures via new developments. Do believe that if it it were for me I would just be adding an extra index. Thanks anyway

regards,
Didier
Re: Query optimisation, table partitionning and lack of index [message #439400 is a reply to message #439116] Sat, 16 January 2010 03:30 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
If your production standards make it easier to partition a table than to add an index to it then they are epically flawed.
Partitioning is a much bigger change than adding an index.
Re: Query optimisation, table partitionning and lack of index [message #439440 is a reply to message #439400] Sat, 16 January 2010 17:41 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
cookiemonster wrote on Sat, 16 January 2010 20:30
epically flawed


./fa/7314/0/

Oh man, I'm gonna use that.
Previous Topic: Oracle audit view performance issue
Next Topic: how to avoid multiple Like operators in a single query
Goto Forum:
  


Current Time: Tue Sep 27 04:15:18 CDT 2016

Total time taken to generate the page: 0.10542 seconds