The following two queries give the same results, except in a different order.
select pack_id ,speed , monthly_payment
from acdb_packages
where monthly_payment > all(select monthly_payment from acdb_packages where speed = '5Mbps');
select p1.pack_id, p1.speed, p1.monthly_payment
from acdb_packages p1
where p1.monthly_payment > (select max(monthly_payment) from acdb_packages p2 where p2.speed ='5Mbps');
To change the first query by using max instead of ALL, I wrote the second query. The result shows both of them with same number of rows. But the difference is the order.
The result shows that the order of the first one is monthly_payment asc, while the order of the econd one is pact_id asc. Is there any problem with in the second query? I also want same order.
CodePudding user response：
If you want a ordered result you need to add an ORDER BY clause. Otherwise your result is in "random" sort order. There are some rules that autosort your result but it's bad practice to hope the sql engine sorts things if you do not specify that you want a sorted result.
So you could just add at the end ORDER BY 3 which is short for order the result by the 3rd result column or better add ORDER BY monthly_payment
CodePudding user response：
If you want the results of a query to display in a specific order then you need to use an ORDER BY clause. Relational DBMSs have no built-in record order and the order of records is random - they are normally returned in PK order but this should not be assumed or relied upon
CodePudding user response：
I created a test table with an index on monthly_payment, and checked the execution plans for both queries. The > all version used full scans, and so returned results in the order they happened to be found in storage, and the = max version used the index, and so returned the results in the order they were returned by the index-driven nested-loop operation, meaning the order in which the values of monthly_payment are held in the index.
create table acdb_packages
( pack_id number constraint acdb_packages_pk primary key
, monthly_payment number not null
, speed varchar2(10) not null );
insert all
into acdb_packages (pack_id, monthly_payment, speed) values ( 1, 90, '200Mbps')
into acdb_packages (pack_id, monthly_payment, speed) values ( 2, 80, '175Mbps')
into acdb_packages (pack_id, monthly_payment, speed) values ( 3, 70, '150Mbps')
into acdb_packages (pack_id, monthly_payment, speed) values ( 4, 60, '100Mbps')
into acdb_packages (pack_id, monthly_payment, speed) values ( 5, 70, '10Mbps')
into acdb_packages (pack_id, monthly_payment, speed) values ( 6, 60, '5Mbps')
into acdb_packages (pack_id, monthly_payment, speed) values ( 7, 50, '4Mbps')
into acdb_packages (pack_id, monthly_payment, speed) values ( 8, 70, '20Mbps')
into acdb_packages (pack_id, monthly_payment, speed) values ( 9, 60, '5Mbps')
into acdb_packages (pack_id, monthly_payment, speed) values (10, 50, '4Mbps')
select * from dual;
create index acdb_packages_monthly_payment_ix on acdb_packages (monthly_payment);
First query:
select pack_id, speed, monthly_payment
from acdb_packages
where monthly_payment > all(select monthly_payment from acdb_packages where speed = '5Mbps');
PACK_ID SPEED MONTHLY_PAYMENT
---------- ---------- ---------------
1 200Mbps 90
2 175Mbps 80
3 150Mbps 70
5 10Mbps 70
8 20Mbps 70
5 rows selected.
Plan hash value: 2904906835
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS ANTI | | 1 | 23 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS STORAGE FULL| ACDB_PACKAGES | 10 | 130 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS STORAGE FULL| ACDB_PACKAGES | 2 | 20 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - storage("SPEED"='5Mbps' AND "MONTHLY_PAYMENT"<="MONTHLY_PAYMENT")
filter("SPEED"='5Mbps' AND "MONTHLY_PAYMENT"<="MONTHLY_PAYMENT")
Second query:
select pack_id, speed, monthly_payment
from acdb_packages
where monthly_payment > (select max(monthly_payment) from acdb_packages p2 where p2.speed ='5Mbps');
PACK_ID SPEED MONTHLY_PAYMENT
---------- ---------- ---------------
3 150Mbps 70
5 10Mbps 70
8 20Mbps 70
1 200Mbps 90
2 175Mbps 80
5 rows selected.
Plan hash value: 1313930309
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| ACDB_PACKAGES | 1 | 13 | 1 (0)| 00:00:01 |
| 2 | SORT CLUSTER BY ROWID BATCHED | | 1 | | 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | ACDB_PACKAGES_MONTHLY_PAYMENT_IX | 1 | | 1 (0)| 00:00:01 |
| 4 | SORT AGGREGATE | | 1 | 10 | | |
|* 5 | TABLE ACCESS STORAGE FULL | ACDB_PACKAGES | 2 | 20 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("P1"."MONTHLY_PAYMENT"> (SELECT MAX("MONTHLY_PAYMENT") FROM "ACDB_PACKAGES" "P2" WHERE
"P2"."SPEED"='5Mbps'))
5 - storage("P2"."SPEED"='5Mbps')
filter("P2"."SPEED"='5Mbps')