The query optimizer analyses your query and then decides if an access path
and which access path is used.
If and which access path is used depends on the following criteria:
1. Join Fields/Columns (if multiple tables are joined)
2. Where Conditions with Equal, In and other where conditions with other
comparison operators. BTW it is even possible that for the same query
multiple access paths on the same table are used.
3. Group By
4. WHERE conditions
In either way if you only want to have 10 rows from 200 millions in a
specific sequence, the table is NOT!!! (re)sorted.
The data is selected and finally sort.
The optimizer can even return the data first in a different sequence and
then sort the result.
If you want to have your 200 millions of records sorted in a specific way,
you should create an index in the predefined sequence.
And even if a table scan must be performed, the table itself is NOT
(re)sorted.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Mark
Villa
Sent: Donnerstag, 24. September 2020 12:09
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: help with SQL performance when using ORDER BY
Hi all,
I have been doing a lot of "SQL checking" recently and without breaking out
the manuals, I am just curious what you guys do when you use ORDER BY. With
tables greater than 10 or 20 mil rows it really tanks. Otherwise, my
responses are usually subsecond.
It makes the SQL engine (and myself) look dumb when I can't say "select
these 100 rows" then ORDER THEM, don't look at 20 million rows, don't need
anything sorted but the final set.
--
Thank You,
Mark Villa
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
