drop table tORA-00942: table or view does not existMore Details: https://docs.oracle.com/error-help/db/ora-00942
drop table t1ORA-00942: table or view does not existMore Details: https://docs.oracle.com/error-help/db/ora-00942
create table t as select * from all_objects where owner != 'SYS'Table created.
create table t1 as select * from all_tables where owner != 'SYS'Table created.
select /*+ gather_plan_statistics */ count(*)
from t, t1
where t1.owner = t1.owner
and t.object_name = t1.table_name
and object_type = 'INDEX'| COUNT(*) | 7 |
|---|
with plan_table as
(
select * from v$sql_plan_statistics_all
where sql_id = '6tzhhvvzmshdq'
and child_number = 0
)
select id "Id",
rpad(
lpad(' ',2*level) || operation || ' ' || options , 40 ) "Operation",
object_name "Name",
cardinality "Rows",
bytes "Bytes",
cost "Cost",
rtrim(to_char(trunc(time/3600),'fm00')||':'||
to_char(mod(trunc(time/60),1),'fm00')||':'||
to_char(trunc(mod(time,60)),'fm00'),':') "Time"
from plan_table
connect by prior id = parent_id
start with id = 0
union all
select null, null,null, null,null, null,null from dual
union all
select null, lpad(id,4) ||'-filter '||FILTER_PREDICATES,null, null,null, null,null
from plan_table where FILTER_PREDICATES is not null
union all
select null, lpad(id,4) ||'-access '||ACCESS_PREDICATES,null, null,null, null,null
from plan_table where ACCESS_PREDICATES is not null
order by 1 nulls last, 2 nulls first| Id | Operation | Name | Rows | Bytes | Cost | Time | 0 | SELECT STATEMENT | - | - | - | 106 | - | 1 | SORT AGGREGATE | - | 1 | 67 | - | - | 2 | HASH JOIN | - | 309 | 20703 | 106 | 00:00:01 | 3 | TABLE ACCESS STORAGE FULL | T1 | 306 | 8262 | 7 | 00:00:01 | 4 | TABLE ACCESS STORAGE FULL | T | 1216 | 48640 | 99 | 00:00:01 | - | - | - | - | - | - | - | - | 2-access "T"."OBJECT_NAME"="T1"."TABLE_NAME" | - | - | - | - | - | - | 4-filter "OBJECT_TYPE"='INDEX' | - | - | - | - | - |
|---|
with plan_table as
(
select * from v$sql_plan_statistics_all
where sql_id = '6tzhhvvzmshdq'
and child_number = 0
)
select id "Id",
rpad(
lpad(' ',2*level) || operation || ' ' || options , 40 ) "Operation",
object_name "Name",
last_starts "Starts",
cardinality "E-Rows",
LAST_OUTPUT_ROWS "A-Rows",
rtrim(to_char(trunc(time/3600),'fm00')||':'||
to_char(mod(trunc(time/60),1),'fm00')||':'||
to_char(trunc(mod(time,60)),'fm00'),':') "Time",
LAST_CR_BUFFER_GETS+LAST_CU_BUFFER_GETS "Buffers"
from plan_table p
connect by prior id = parent_id
start with id = 0
union all
select null, null,null, null,null, null,null,null from dual
union all
select null, lpad(id,4) ||'-filter '||FILTER_PREDICATES,null, null,null, null,null,null
from plan_table where FILTER_PREDICATES is not null
union all
select null, lpad(id,4) ||'-access '||ACCESS_PREDICATES,null, null,null, null,null,null
from plan_table where ACCESS_PREDICATES is not null
order by 1 nulls last, 2 nulls first| Id | Operation | Name | Starts | E-Rows | A-Rows | Time | Buffers | 0 | SELECT STATEMENT | - | 1 | - | 1 | - | 366 | 1 | SORT AGGREGATE | - | 1 | 1 | 1 | - | 366 | 2 | HASH JOIN | - | 1 | 309 | 7 | 00:00:01 | 366 | 3 | TABLE ACCESS STORAGE FULL | T1 | 1 | 306 | 306 | 00:00:01 | 17 | 4 | TABLE ACCESS STORAGE FULL | T | 1 | 1216 | 378 | 00:00:01 | 349 | - | - | - | - | - | - | - | - | - | 2-access "T"."OBJECT_NAME"="T1"."TABLE_NAME" | - | - | - | - | - | - | - | 4-filter "OBJECT_TYPE"='INDEX' | - | - | - | - | - | - |
|---|