create table t1(id number, ref varchar2(255))Table created.
create index i1 on t1(id)Index created.
insert into t1 select level, 'X' from dual connect by level < 100009999 row(s) inserted.
commitStatement processed.
create table t2(id1 number, id3 number, ref varchar2(255))Table created.
create table t3(id number, ref varchar2(255))Table created.
create table t4(id number, ref varchar2(255))Table created.
alter session set optimizer_adaptive_features=falseStatement processed.
exec dbms_stats.gather_table_stats(null, 't1')Statement processed.
exec dbms_stats.gather_table_stats(null, 't2')Statement processed.
exec dbms_stats.gather_table_stats(null, 't3')Statement processed.
exec dbms_stats.gather_table_stats(null, 't4')Statement processed.
explain plan for
SELECT
(
SELECT count(*)
FROM
(
SELECT /*+ NO_MERGE*/
id
FROM
t1
) t1_sq,
t2,
t3
WHERE
t1_sq.id = t2.id1
AND t2.id3 = t3.id
AND t3.id = 1
),
(
SELECT
COUNT(*)
FROM
t4
)
FROM
dualStatement processed.
select * from table(dbms_xplan.display)| PLAN_TABLE_OUTPUT | Error: cannot fetch last explain plan from PLAN_TABLE |
|---|
explain plan for
SELECT
(
SELECT count(*)
FROM
(
SELECT /*+ NO_MERGE*/
id
FROM
t1
) t1_sq,
t2,
t3
WHERE
t1_sq.id = t2.id1
AND t2.id3 = t3.id
AND t3.id = 1
),
(
SELECT
COUNT(*)
FROM
t4
)
FROM
dualStatement processed.
select * from table(dbms_xplan.display)| PLAN_TABLE_OUTPUT | Plan hash value: 3136214 | --------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | --------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 1 | | 9 (0)| 00:00:01 | | | 1 | SORT AGGREGATE | | 1 | 41 | | | | | 2 | NESTED LOOPS | | 1 | 41 | 5 (0)| 00:00:01 | | |* 3 | HASH JOIN | | 1 | 39 | 4 (0)| 00:00:01 | | |* 4 | TABLE ACCESS FULL | T2 | 1 | 26 | 2 (0)| 00:00:01 | | |* 5 | TABLE ACCESS FULL | T3 | 1 | 13 | 2 (0)| 00:00:01 | | | 6 | VIEW PUSHED PREDICATE | | 1 | 2 | 1 (0)| 00:00:01 | | |* 7 | INDEX RANGE SCAN | I1 | 1 | 4 | 1 (0)| 00:00:01 | | | 8 | SORT AGGREGATE | | 1 | | | | | | 9 | TABLE ACCESS FULL | T4 | 1 | | 2 (0)| 00:00:01 | | | 10 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | --------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 3 - access("T2"."ID3"="T3"."ID") | 4 - filter("T2"."ID3"=1) | 5 - filter("T3"."ID"=1) | 7 - access("ID"="T2"."ID1") |
|---|
create index i4 on t4 to_char(JSON_QUERY(ref, '$'))Index created.
explain plan for
SELECT
(
SELECT count(*)
FROM
(
SELECT /*+ NO_MERGE*/
id
FROM
t1
) t1_sq,
t2,
t3
WHERE
t1_sq.id = t2.id1
AND t2.id3 = t3.id
AND t3.id = 1
),
(
SELECT
COUNT(*)
FROM
t4
)
FROM
dualStatement processed.
select * from table(dbms_xplan.display)| PLAN_TABLE_OUTPUT | Plan hash value: 2694851258 | ------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | ------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 1 | | 15 (0)| 00:00:01 | | | 1 | SORT AGGREGATE | | 1 | 52 | | | | |* 2 | HASH JOIN | | 1 | 52 | 11 (0)| 00:00:01 | | | 3 | MERGE JOIN CARTESIAN| | 1 | 26 | 9 (0)| 00:00:01 | | |* 4 | TABLE ACCESS FULL | T3 | 1 | 13 | 2 (0)| 00:00:01 | | | 5 | BUFFER SORT | | 9999 | 126K| 7 (0)| 00:00:01 | | | 6 | VIEW | | 9999 | 126K| 7 (0)| 00:00:01 | | | 7 | TABLE ACCESS FULL| T1 | 9999 | 39996 | 7 (0)| 00:00:01 | | |* 8 | TABLE ACCESS FULL | T2 | 1 | 26 | 2 (0)| 00:00:01 | | | 9 | SORT AGGREGATE | | 1 | | | | | | 10 | TABLE ACCESS FULL | T4 | 1 | | 2 (0)| 00:00:01 | | | 11 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | ------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 2 - access("T1_SQ"."ID"="T2"."ID1" AND "T2"."ID3"="T3"."ID") | 4 - filter("T3"."ID"=1) | 8 - filter("T2"."ID3"=1) |
|---|