Your estimated performance of a very simple SQL query
Hi there..
Sometimes it seems hard to find answers for the most simple questions so I hope you can help me and be overbearing ;)
I am going to have a table with a few million records in it, 30-40 columns of which 80% has data, indexes on maybe 10 columns (certainly the columns I am querying), less than ten thousand new records a day with a variety of different column values and using Oracle 10 or 11.
I am going to be running a single SQL SELECT statement quite regularily against these records to retrieve all records whose value of a single column is the same (and specified in my WHERE clause)
It will be a query like this; SELECT * FROM person where city = 'Amsterdam' AND
The column 'city' will have a simple index on it. 4-8 records will have the same value (e.g. there will only be up to 8 rows where city is 'Amsterdam' in the above example) which means that with 5 million records I will probably have between 625,000 and 1,250,000 "sets" that I am querying and retrieving per SQL. Thed atatype could be varchar or number - not sure yet.
I will retrieve the same data a lot but generally also access all the other data on a regular basis.
How should I expect this to perform? Always fast I imagine because will there will be any table scans do we think? I don't have to think about partitioning of too regular statistic calculation or anything? Anything to watch out for?
Any feedback would be greatly appreciated.
Thanks a lot in advance.
Daniel