Hi,
My DSS tool has double compatibility (Oracle & XXXXX).
I have a question about top-n queries & sorts.
Execution plans with Oracle are very different than with XXXXX : index on lancers table is not used.
Can someone explain me if I miss something obvious ?
Here is my test :
Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
create table geants(
idg integer generated always as identity primary key,
genre char(1),
taille smallint,
masse smallint,
actif integer,
devise varchar(128),
pw integer,
heureux integer,
couleur varchar(8),
veteran integer,
clan integer,
gabarit varchar(8),
revenu integer,
pm integer,
berserk integer,
tutelaire integer,
ere varchar(10),
cyclope integer);
Table created.
insert into geants(genre, taille, masse, actif, devise, pw, heureux, couleur, veteran, clan, gabarit, revenu, pm, berserk, tutelaire, ere, cyclope)
with serie(i, r1) as
(select 1, dbms_random.value from dual
UNION ALL
select i + 1, dbms_random.value from serie where i < 10000000)
select
case when dbms_random.value < 0.45 then 'M' else 'F' end,
200 + (trunc(dbms_random.value * 200 + 1)),
300 + (trunc(dbms_random.value * 200 + 1)),
case when dbms_random.value < 0.01 then 0 when dbms_random.value > 0.5 and dbms_random.value < 0.99 then 0 else 1 end,
DBMS_RANDOM.string('x',32),
(trunc(dbms_random.value*100 + 1)),
case when dbms_random.value < 0.1 then 1 else 0 end,
case when r1 <= 0.29998 then 'GRIS' when r1 <= 0.59998 then 'NOIR' else 'BLEU' end,
case when dbms_random.value < 0.9 then 1 else 0 end,
(trunc(dbms_random.value*1000 + 1)),
case when r1 <= 0.29999 then 'PETIT' when r1 <= 0.59999 then 'MOYEN' else 'GRAND' end,
(trunc(dbms_random.value*1000000 + 1)),
(trunc(dbms_random.value*10 + 1)),
case when r1 <= 0.3 then 0 when r1 <= 0.6 then 1 else null end,
(trunc(dbms_random.value*10 + 1)),
case when r1 <= 0.30001 then 'TAUREAU' when r1 <= 0.60001 then 'LICORNE' else 'DRAGON' end,
case when r1 <= 0.30002 then 0 when r1 <= 0.60002 then 1 else null end
from serie;
10000000 rows created.
commit;
Commit complete.
create table lancers(dtl timestamp, idg integer, perf integer);
Table created.
insert into lancers(dtl, idg, perf)
with serie(i) as
(select 25000000 from dual
UNION ALL
select i - 1 from serie where i > 1)
select
current_timestamp - (i / 1440),
trunc(dbms_random.value * 10000000 + 1),
case when dbms_random.value <= 0.001 then 50000 + trunc(dbms_random.value * 50000 + 1) else trunc(dbms_random.value * 50000 + 1) end
from serie;
25000000 rows created.
create index perf_i1 on lancers(perf, dtl);
Index created.
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'XXXXX', TABNAME => 'GEANTS', cascade => TRUE, estimate_percent => 100);
PL/SQL procedure successfully completed.
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'XXXXX', TABNAME => 'LANCERS', cascade => TRUE, estimate_percent => 100);
PL/SQL procedure successfully completed.
-- 5 best performers (lancers)
select idg, perf from lancers order by perf desc fetch first 5 rows only;
Execution Plan
----------------------------------------------------------
Plan hash value: 3835127587
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 260 | | 132K (2)| 00:00:06 |
|* 1 | VIEW | | 5 | 260 | | 132K (2)| 00:00:06 |
|* 2 | WINDOW SORT PUSHED RANK| | 25M| 262M| 478M| 132K (2)| 00:00:06 |
| 3 | TABLE ACCESS FULL | LANCERS | 25M| 262M| | 22615 (2)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=5)
2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("PERF") DESC )<=5)
-- 5 best performers (lancers / geants join)
select idg, taille, perf from lancers join geants using (idg) order by perf desc, taille desc fetch first 5 rows only;
IDG TAILLE PERF
---------- ---------- ----------
5583836 291 100000
4198735 376 99994
5161405 265 99992
4880197 232 99992
8207351 247 99989
Execution Plan
----------------------------------------------------------
Plan hash value: 3710710262
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 390 | | 262K (1)| 00:00:11 |
|* 1 | VIEW | | 5 | 390 | | 262K (1)| 00:00:11 |
|* 2 | WINDOW SORT PUSHED RANK| | 25M| 500M| 765M| 262K (1)| 00:00:11 |
|* 3 | HASH JOIN | | 25M| 500M| 209M| 100K (1)| 00:00:04 |
| 4 | TABLE ACCESS FULL | GEANTS | 10M| 95M| | 39914 (1)| 00:00:02 |
| 5 | TABLE ACCESS FULL | LANCERS | 25M| 262M| | 22615 (2)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_004"."rowlimit_$$_rownumber"<=5)
2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("LANCERS"."PERF") DESC
,INTERNAL_FUNCTION("GEANTS"."TAILLE") DESC )<=5)
3 - access("LANCERS"."IDG"="GEANTS"."IDG")
Best regards
Phil