OraFAQ Forum: Performance Tuning » Query optimisation, table partitionning and lack of index

Home » RDBMS Server » Performance Tuning » Query optimisation, table partitionning and lack of index (database 10g)

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

Query optimisation, table partitionning and lack of index [message #439116]

Thu, 14 January 2010 06:37

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

Report message to a moderator

Re: Query optimisation, table partitionning and lack of index [message #439120 is a reply to message #439116]

Thu, 14 January 2010 07:47

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Query optimisation, table partitionning and lack of index [message #439233 is a reply to message #439116]

Fri, 15 January 2010 03:18

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?

Report message to a moderator

Re: Query optimisation, table partitionning and lack of index [message #439367 is a reply to message #439233]

Fri, 15 January 2010 21:36

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

Report message to a moderator

Re: Query optimisation, table partitionning and lack of index [message #439400 is a reply to message #439116]

Sat, 16 January 2010 03:30

cookiemonster
Messages: 13958
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.

Report message to a moderator

Re: Query optimisation, table partitionning and lack of index [message #439440 is a reply to message #439400]

Sat, 16 January 2010 17:41

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

cookiemonster wrote on Sat, 16 January 2010 20:30

epically flawed

Oh man, I'm gonna use that.

Report message to a moderator

Previous Topic:	Oracle audit view performance issue
Next Topic:	how to avoid multiple Like operators in a single query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 01:02:20 CST 2024