I am posting this question here because of the fact that it has more to do with database theory than a particular product.
Supposing i have a table TABLE1(ID,ID2,field1,field2,...,fieldn)
The primary key is ID.
There is a secondary index on ID2. ID2 is a candidate key.
These are the only indices.
1)Do modern database systems like Oracle,DB2,SQL Server provide a way to execute the following query
SELECT * FROM TABLE1 ORDER BY ID
in approximately the same time taken for the foll. query
SELECT * FROM TABLE1
Particularly if the primary index (or file organisation) can be organised in a way (maybe i choose the file/index orgn. offered by the DB) so that the above is achieved.
Any help is appreciated.
Thanks in advance.For Oracle (any version > V7), your PK must have an index and
your other key, you say, is indexed so if you order by either of
these columns that are indexed, the query will not take very much
longer that your #2 statement.
You mention "approximately" so... this'll really have to do with
how many records are in the database. If all you have is a four (4)
column table with, lets say, < 5,000 rows, querying that table
and accessing index columns (in either the "where" or "Order by"
clause, will be very fast.
For exact results and complete details as to what the query will
do, you'd need to "anaylze" the query.|||One more doubt please.
With ref. to the first post,
1)SELECT * FROM TABLE1 WHERE ID>M AND ID<N
AND
2)SELECT * FROM TABLE1 WHERE ID2>M AND ID2<N
Won't 1) execute very much faster coz of using a primary index that matches the file organisation leading to virtually sequential retrieval?|||It also largly depends on your optimizer mode.
If you are using COST Based Optimisation, then the oracle database internally calculates the load and decide on wether to use the index or not.
sql
