query running slow.

[Natik]

1.At what record numbers in a table should partitioning be considered.

2. I have a query running against a table with 14 million records and a predicate against a number column that is indexed but is running very slow(74ms). Any ideas on how to resolve this

3. Any other solutions in addition to a partioning scheme.

[slimdave]

1. There is no magic number for this. Partitioning is just a tool that can be used to help load bulk data, query bulk data, manage bulk data, or a number of other things, but it's not a solution for all problems.

2. How many rows is it returning? To find out why it takes this amount of time you need to do some analysis of the waits involved, ad for that you need to read up on event 10046 tracing. a google search ought to suffice, or look at http://asktom.oracle.com

3. You need to disgnose (ie. identify and measure) exactly what the problem is before dabbling with partitioning or any other remedy.

[marist89]

All that good stuff being said, a table of 14 million rows wouldn't ring my bell for partitioning. 140 million, maybe.

[slimdave]

Originally posted by marist89
All that good stuff being said, a table of 14 million rows wouldn't ring my bell for partitioning. 140 million, maybe.

It might do if every query selected about 10% of table rows and there was a potential partitioning scheme that allowed partition scans instead of table scans. But it's all hypothetical.

[DaPi]

Originally posted by Natik
. . . is running very slow(74ms)

Not sure 74ms can be called very slow. This might include 3 or more disk accesses, and how about parsing?