Home » RDBMS Server » Performance Tuning » nested select / instead-of-trigger and views=no index used (10g 10.2.0.4)
| nested select / instead-of-trigger and views=no index used [message #421504] |
Tue, 08 September 2009 15:52  |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
Hi all
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
5 rows selected.
I have a problem with views and nested selects which I cannot explain. Here is a trimed down version of the research I have done.
Please notice the following:
1) all code is executed from the same user CDRNORMALCODE. this user has all views and procedural code
2) all data is owned by a different user CDRDATA. This user has no views and no code.
My problem is this:
If I reference the table directly with a delete statement that uses a nested select (i.e. IN clause with select), the index I expect and want is used.
But if I execute the same delete but reference even the most simple of views (select * from <table>) instead of the table itself, then a full table scan is done of the table.
Here is an execute against the table directly (owned by cdrdata). Notice the reference to the table in the table schema on line 3. Also please notice INDEX RANGE SCAN BSNSS_CLSS_CASE_RULE_FK1 at the bottom of the plan.
SQL> show user
USER is "CDRNORMALCODE"
SQL>
SQL> explain plan for
2 delete
3 from cdrdata.bsnss_clss_mnr_case_rule
4 where bsnss_clss_mnr_case_id in
5 (select bsnss_clss_mnr_case_id from cdrnormalcode.bsnss_clss_mnr_case where bsnss_clss_case_id in
6 (select bsnss_clss_case_id from cdrnormalcode.kev_hig30k_case_fvw)
7 )
8 /
Explained.
Elapsed: 00:00:01.03
SQL> @showplan
ID PARENT_ID CARDINALITY Query Plan
---------- ---------- ----------- --------------------------------------------------------------------------------------
0 71 DELETE STATEMENT Cost = 4
1 0 DELETE BSNSS_CLSS_MNR_CASE_RULE
2 1 10 TABLE ACCESS BY INDEX ROWID BSNSS_CLSS_MNR_CASE_RULE
3 2 71 NESTED LOOPS
4 3 7 VIEW VW_NSO_1
5 4 7 SORT UNIQUE
6 5 7 TABLE ACCESS BY INDEX ROWID BSNSS_CLSS_MNR_CASE
7 6 7 NESTED LOOPS
8 7 1 INDEX FULL SCAN KEV_HIG30K_CASE_FVW_PK
9 7 8 INDEX RANGE SCAN BSNSS_CLSS_MNR_CASE_FK2
10 3 10 INDEX RANGE SCAN BSNSS_CLSS_CASE_RULE_FK1
11 rows selected.
Here we see the same query done but using a simple view (view text to follow but it is just select * from <table>). Notice we are referencing a view. The view text is below. See how simple it is. The problem is now we are doing a FULL TABLE SCAN. The time this is taking is way more than when we use the index.
SQL> l
1 explain plan for
2 delete
3 from cdrnormalcode.bsnss_clss_mnr_case_rule_sv
4 where bsnss_clss_mnr_case_id in
5 (select bsnss_clss_mnr_case_id from cdrnormalcode.bsnss_clss_mnr_case where bsnss_clss_case_id in
6 (select bsnss_clss_case_id from cdrnormalcode.kev_hig30k_case_fvw)
7* )
SQL> /
Explained.
Elapsed: 00:00:01.04
SQL> @showplan
ID PARENT_ID CARDINALITY Query Plan
---------- ---------- ----------- ---------------------------------------------------------------------------
0 71 DELETE STATEMENT Cost = 11353
1 0 DELETE BSNSS_CLSS_MNR_CASE_RULE_SV
2 1 71 HASH JOIN RIGHT SEMI
3 2 7 VIEW VW_NSO_1
4 3 7 TABLE ACCESS BY INDEX ROWID BSNSS_CLSS_MNR_CASE
5 4 7 NESTED LOOPS
6 5 1 INDEX FULL SCAN KEV_HIG30K_CASE_FVW_PK
7 5 8 INDEX RANGE SCAN BSNSS_CLSS_MNR_CASE_FK2
8 2 10510002 VIEW BSNSS_CLSS_MNR_CASE_RULE_SV
9 8 10510002 TABLE ACCESS FULL BSNSS_CLSS_MNR_CASE_RULE
10 rows selected.
The view text is as simple as it gets. All we do is select from the table, no WHERE clause at all. This is the same table in CDRDATA that we referenced before in our first query example.
SQL> select text
2 from user_views
3 where view_name = 'BSNSS_CLSS_MNR_CASE_RULE_SV';
TEXT
--------------------------------------------------------------------------------
select "BSNSS_CLSS_MNR_CASE_RULE_ID","BSNSS_CLSS_MNR_CASE_ID","BSNSS_CLSS_RULE_I
D","BSNSS_CLSS_MNR_CASE_RULE_ORDR","DLT_FLAG","LINK_ID","CMMNT_TEXT","BSNSS_CLSS
_RULE_MDFR_ID"
from cdrdata.bsnss_clss_mnr_case_rule
1 row selected.
I can even show this truely simple example where there are no other table involed but we do a very simple in. Still the underlying data table is scanned rather than having the index I want be used.
SQL> explain plan for
2 delete
3 from cdrnormalcode.bsnss_clss_mnr_case_rule_sv
4 where bsnss_clss_mnr_case_id in (select 1 from dual)
5 /
Explained.
SQL> @showplan
ID PARENT_ID CARDINALITY Query Plan
---------- ---------- ----------- --------------------------------------------------------------
0 10 DELETE STATEMENT Cost = 11354
1 0 DELETE BSNSS_CLSS_MNR_CASE_RULE_SV
2 1 10 HASH JOIN
3 2 1 VIEW VW_NSO_1
4 3 1 FAST DUAL
5 2 10510002 VIEW BSNSS_CLSS_MNR_CASE_RULE_SV
6 5 10510002 TABLE ACCESS FULL BSNSS_CLSS_MNR_CASE_RULE
7 rows selected.
Here are the indexes on the table.
SQL> @showindexes cdrdata bsnss_clss_mnr_case_rule
INDEX_NAME COLUMN_NAME UNIQUENES TABLESPACE_NAME
------------------------------ ------------------------------ --------- --------------------
BSNSS_CLSS_CASE_RULE_FK1 BSNSS_CLSS_MNR_CASE_ID NONUNIQUE CDR_INDEX
BSNSS_CLSS_CASE_RULE_FK2 BSNSS_CLSS_RULE_ID NONUNIQUE CDR_INDEX
BSNSS_CLSS_CASE_RULE_PK BSNSS_CLSS_MNR_CASE_RULE_ID UNIQUE CDR_INDEX
BSNSS_CLSS_CASE_RULE_UK1 BSNSS_CLSS_MNR_CASE_ID UNIQUE CDR_INDEX
BSNSS_CLSS_RULE_ID UNIQUE CDR_INDEX
BSNSS_CLSS_MNR_CASE_RULE_FK3 LINK_ID NONUNIQUE CDR_INDEX
BSNSS_CLSS_MNR_CASE_RULE_FK4 BSNSS_CLSS_RULE_MDFR_ID NONUNIQUE CDR_INDEX
7 rows selected.
So what is going on here? Someone has to have an idea. Why does putting a view between me and my data cause this problem?
As one final twist, if we create this view in the data schema and execute the query from there, all is well again.
(Here you will see the session user is now CDRDATA, but I have included the schema name everywhere just so there is no ambiguity in what you are ready out of the test case).
SQL> show user
USER is "CDRDATA"
SQL>
SQL> l
1 create or replace view cdrdata.bsnss_clss_mnr_case_rule_sv
2 as
3 select *
4* from cdrdata.bsnss_clss_mnr_case_rule
SQL> /
View created.
SQL> delete from plan_table;
7 rows deleted.
SQL> explain plan for
2 delete
3 from cdrdata.bsnss_clss_mnr_case_rule_sv
4 where bsnss_clss_mnr_case_id in (select 1 from dual)
5 /
Explained.
SQL> @showplan
ID PARENT_ID CARDINALITY Query Plan
---------- ---------- ----------- -------------------------------------------------------------------------
0 10 DELETE STATEMENT Cost = 4
1 0 DELETE BSNSS_CLSS_MNR_CASE_RULE
2 1 10 NESTED LOOPS
3 2 1 VIEW VW_NSO_1
4 3 1 FAST DUAL
5 2 10 TABLE ACCESS BY INDEX ROWID BSNSS_CLSS_MNR_CASE_RULE
6 5 10 INDEX RANGE SCAN BSNSS_CLSS_CASE_RULE_FK1
7 rows selected.
Someone give me a clue please.
Thanks, Kevin
OK, here is an update. The views I am useing normally have instead of triggers on them. If I remove the instead of trigger the problem looks like it goes away, when I put the trigger back the problem comes back.
But why would an instead-of-trigger change the query plan for a view?
SQL> DELETE FROM PLAN_TABLE;
5 rows deleted.
SQL> explain plan for
2 delete
3 from BSNSS_CLSS_MNR_CASE_RULE_SV
4 where bsnss_clss_mnr_case_id = (select 1 from dual)
5 /
Explained.
SQL> @showplan
ID PARENT_ID CARDINALITY Query Plan
---------- ---------- ----------- ------------------------------------------------------------------------
0 10510002 DELETE STATEMENT Cost = 11188
1 0 DELETE BSNSS_CLSS_MNR_CASE_RULE_SV
2 1 10510002 VIEW BSNSS_CLSS_MNR_CASE_RULE_SV
3 2 10510002 TABLE ACCESS FULL BSNSS_CLSS_MNR_CASE_RULE
4 2 1 FAST DUAL
5 rows selected.
SQL> drop trigger io_snss_clss_mnr_case_rule_sv;
Trigger dropped.
SQL> DELETE FROM PLAN_TABLE;
5 rows deleted.
SQL> explain plan for
2 delete
3 from BSNSS_CLSS_MNR_CASE_RULE_SV
4 where bsnss_clss_mnr_case_id = (select 1 from dual)
5 /
Explained.
SQL> @showplan
ID PARENT_ID CARDINALITY Query Plan
---------- ---------- ----------- ------------------------------------------------------------------------
0 10 DELETE STATEMENT Cost = 3
1 0 DELETE BSNSS_CLSS_MNR_CASE_RULE
2 1 10 TABLE ACCESS BY INDEX ROWID BSNSS_CLSS_MNR_CASE_RULE
3 2 10 INDEX RANGE SCAN BSNSS_CLSS_CASE_RULE_FK1
4 3 1 FAST DUAL
5 rows selected.
SQL>
Kevin
[Updated on: Tue, 08 September 2009 17:03] Report message to a moderator |
|
|
|
|
|
| Re: nested select / instead-of-trigger and views=no index used [message #421667 is a reply to message #421504] |
Wed, 09 September 2009 12:11  |
|
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
It has INSTEAD OF INSERT OR UPDATE OR DELETE on it.
An update does this, a Delete does this, a Select works correctly by using the index, and an Insert as Select works using the index.
Again, if I remove the trigger, it uses the index, if I put the trigger back it does not.
Thanks for your reply. Kevin
CREATE OR REPLACE TRIGGER CDRNORMALCODE.io_snss_clss_mnr_case_rule_sv
instead of insert or update or delete on bsnss_clss_mnr_case_rule_sv
for each row
begin
if inserting then
insert into cdrdata.bsnss_clss_mnr_case_rule
(
bsnss_clss_mnr_case_rule_id
,bsnss_clss_mnr_case_id
,bsnss_clss_rule_id
,bsnss_clss_mnr_case_rule_ordr
,dlt_flag
,link_id
,cmmnt_text
,bsnss_clss_rule_mdfr_id
)
values
(
:new.bsnss_clss_mnr_case_rule_id
,:new.bsnss_clss_mnr_case_id
,:new.bsnss_clss_rule_id
,:new.bsnss_clss_mnr_case_rule_ordr
,null
,:new.link_id
,:new.cmmnt_text
,:new.bsnss_clss_rule_mdfr_id
);
elsif updating then
update cdrdata.bsnss_clss_mnr_case_rule set
bsnss_clss_mnr_case_rule_ordr = :new.bsnss_clss_mnr_case_rule_ordr
,dlt_flag = :new.dlt_flag
,link_id = :new.link_id
,cmmnt_text = :new.cmmnt_text
,bsnss_clss_rule_mdfr_id = :new.bsnss_clss_rule_mdfr_id
where bsnss_clss_mnr_case_rule_id = :old.bsnss_clss_mnr_case_rule_id
;
else
delete from cdrdata.bsnss_clss_mnr_case_rule
where bsnss_clss_mnr_case_rule_id = :old.bsnss_clss_mnr_case_rule_id
;
end if;
end;
/[Updated on: Wed, 09 September 2009 12:15] Report message to a moderator |
|
|
|
| Re: nested select / instead-of-trigger and views=no index used [message #421688 is a reply to message #421504] |
Wed, 09 September 2009 16:55 |
|
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
Here is a simple test case.
/*
drop view va;
drop table a;
*/
/*
shows how adding an instead-of-trigger to a view has altered the execution plan of updates and deletes
in this test case we will use a delete
we have tried to create as simple a test scenario as we could to demonstrate the problem
we are not really sure how much data is needed in the table but this amount worked first time out
so we are going with it
you will see that the query plan without the instead of trigger on the view, uses the index we want it to use
when we add an instead of trigger to the view and run the same delete statement the plan changes to FTS of the underlying table
as proof the trigger is part of the problem, we drop the trigger and the plan goes back to what we want
the trigger code does not seem to matter. In our test case we actually do nothing at all
but if we put typical insert/update/delete logic in the trigger, the same behavior is seen
so we figure the actual trigger logic in not important, only the existence of the trigger
*/
create table a (a number not null,b number not null,c varchar2(100))
/
insert into a values (9999,9999,lpad('a',100,'a'))
/
insert into a select * from a
/
/
/
/
/
/
/
/
/
/
/
/
/
update a set a = rownum,b = mod(rownum,100)
/
alter table a
add constraint a_pk primary key (a)
/
create index ai1 on a(b)
/
exec dbms_stats.gather_table_stats(<MYUSERNAMEHERE>,'A',cascade=>true)
create or replace view va as select * from a
/
delete from plan_table
/
explain plan for
delete
from va
where a in (select 0 from dual)
/
@showplan
create or replace trigger io_va
instead of insert or update or delete on va
for each row
begin
null;
end;
/
show errors
delete from plan_table
/
explain plan for
delete
from va
where a in (select 0 from dual)
/
@showplan
drop trigger io_va;
delete from plan_table
/
explain plan for
delete
from va
where a in (select 0 from dual)
/
@showplan
col "Query Plan" format a300
col "Query Plan" clear
col "Query Plan" format a300
SELECT id, parent_id,cardinality,
lpad(' ', 2*(level-1))||operation||' '||options||' '||object_name||' '||
decode(id, 0, 'Cost = '||position) "Query Plan"
FROM plan_table
START WITH id = 0
CONNECT BY prior id = parent_id
and prior nvl(statement_id,0) = nvl(statement_id,0)
/[Updated on: Wed, 09 September 2009 17:01] Report message to a moderator |
|
|
|
| Re: nested select / instead-of-trigger and views=no index used [message #421762 is a reply to message #421688] |
Thu, 10 September 2009 05:32 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Good test case - Michel would be proud.
I get the same results as you on 32 bit 10.2.0.4 for Windoze.
I reckon this in one for Metalink.
Interestingly, I get a different result on 9.2.0.8 - all the table access there is Full Table scans:drop view va succeeded.
drop table a succeeded.
create table succeeded.
1 rows inserted
1 rows inserted
2 rows updated
alter table a succeeded.
create index succeeded.
anonymous block completed
create or replace view succeeded.
7 rows deleted
explain plan succeeded.
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
-----------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 8168 | 65344 | | 42 |
| 1 | DELETE | A | | | | |
|* 2 | HASH JOIN | | 8168 | 65344 | | 42 |
| 3 | TABLE ACCESS FULL | A | 2 | 12 | | 10 |
| 4 | VIEW | VW_NSO_1 | 8168 | 16336 | | 29 |
| 5 | SORT UNIQUE | | 8168 | | 74000 | 29 |
| 6 | TABLE ACCESS FULL| DUAL | 8168 | | | 17 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."A"="VW_NSO_1"."0")
Note: cpu costing is off
19 rows selected
trigger io_va Compiled.
No Errors.
7 rows deleted
explain plan succeeded.
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
-----------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 8168 | 119K| | 33 |
| 1 | DELETE | VA | | | | |
|* 2 | HASH JOIN | | 8168 | 119K| | 33 |
| 3 | VIEW | VA | 2 | 26 | | 1 |
| 4 | INDEX FULL SCAN | A_PK | 2 | 6 | | 1 |
| 5 | VIEW | VW_NSO_1 | 8168 | 16336 | | 29 |
| 6 | SORT UNIQUE | | 8168 | | 74000 | 29 |
| 7 | TABLE ACCESS FULL| DUAL | 8168 | | | 17 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("VA"."A"="VW_NSO_1"."0")
Note: cpu costing is off
20 rows selected
drop trigger io_va succeeded.
8 rows deleted
explain plan succeeded.
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
-----------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 8168 | 65344 | | 42 |
| 1 | DELETE | A | | | | |
|* 2 | HASH JOIN | | 8168 | 65344 | | 42 |
| 3 | TABLE ACCESS FULL | A | 2 | 12 | | 10 |
| 4 | VIEW | VW_NSO_1 | 8168 | 16336 | | 29 |
| 5 | SORT UNIQUE | | 8168 | | 74000 | 29 |
| 6 | TABLE ACCESS FULL| DUAL | 8168 | | | 17 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."A"="VW_NSO_1"."0")
Note: cpu costing is off
19 rows selected
|
|
|
|
|
|
|
|
| Re: nested select / instead-of-trigger and views=no index used [message #501442 is a reply to message #501430] |
Tue, 29 March 2011 04:00 |
 |
ipynnonen
Messages: 4
Registered: March 2011
|
Junior Member |
|
|
Hi again, I created a new test scenario based on the one above. It shows that the precence of instead-of-trigger is changing the execution plan.
spool problem_demo.log
set serveroutput on;
set heading off;
set echo on;
drop view va;
drop table a CASCADE CONSTRAINTS PURGE;
drop table b CASCADE CONSTRAINTS PURGE;
-- ----------------------------------------------------------------------------
-- Set up table A
-- ----------------------------------------------------------------------------
create table a (a_id number not null,b_id number not null,description varchar2(100))
/
insert into a values (9999,9999,lpad('a',100,'a'))
/
insert into a select * from a
/
/
/
/
/
/
/
/
/
/
/
/
/
/
/
/
/
/
/
update a set a_id = rownum,b_id = DECODE(mod(rownum,10000),0,1,mod(rownum,10000))
/
commit
/
alter table a
add constraint a_pk primary key (a_id)
/
create unique index ai1 on a(b_id,a_id)
/
exec dbms_stats.gather_table_stats(USER,'A',cascade=>true)
-- ----------------------------------------------------------------------------
-- Set up table B
-- ----------------------------------------------------------------------------
create table b (b_id number not null,num number not null,description varchar2(100))
/
insert into b(b_id, num, description)
select distinct b_id, b_id, description
from a
/
update b set num = rownum
/
commit
/
alter table b
add constraint b_pk primary key (b_id)
/
create unique index bi1 on b(num,b_id)
/
exec dbms_stats.gather_table_stats(USER,'B',cascade=>true)
--
ALTER TABLE a ADD CONSTRAINT fk_a_b FOREIGN KEY (b_id) REFERENCES b(b_id);
-- ----------------------------------------------------------------------------
-- Create view VA
-- ----------------------------------------------------------------------------
create or replace view va as select * from a
/
delete from plan_table
/
explain plan for
update va
set description='updated 1'
where va.a_id in
(select va.a_id
from b, va
where b.num = 2
and va.b_id = b.b_id
)
/
col "Query Plan" clear
col "Query Plan" format a300
SELECT id, parent_id,cardinality,
lpad(' ', 2*(level-1))||operation||' '||options||' '||object_name||' '||
decode(id, 0, 'Cost = '||position) "Query Plan"
FROM plan_table
START WITH id = 0
CONNECT BY prior id = parent_id
and prior nvl(statement_id,0) = nvl(statement_id,0)
/
create or replace trigger trg_va
instead of insert or update on va
for each row
begin
null;
end;
/
show errors
delete from plan_table
/
explain plan for
update va
set description='updated 1'
where va.a_id in
(select va.a_id
from b, va
where b.num = 2
and va.b_id = b.b_id
)
/
SELECT id, parent_id,cardinality,
lpad(' ', 2*(level-1))||operation||' '||options||' '||object_name||' '||
decode(id, 0, 'Cost = '||position) "Query Plan"
FROM plan_table
START WITH id = 0
CONNECT BY prior id = parent_id
and prior nvl(statement_id,0) = nvl(statement_id,0)
/
drop trigger trg_va
/
delete from plan_table
/
explain plan for
update va
set description='updated 1'
where va.a_id in
(select va.a_id
from b, va
where b.num = 2
and va.b_id = b.b_id
)
/
SELECT id, parent_id,cardinality,
lpad(' ', 2*(level-1))||operation||' '||options||' '||object_name||' '||
decode(id, 0, 'Cost = '||position) "Query Plan"
FROM plan_table
START WITH id = 0
CONNECT BY prior id = parent_id
and prior nvl(statement_id,0) = nvl(statement_id,0)
/
spool off;
|
|
|
|
| Re: nested select / instead-of-trigger and views=no index used [message #501443 is a reply to message #501442] |
Tue, 29 March 2011 04:01 |
|
ipynnonen
Messages: 4
Registered: March 2011
|
Junior Member |
|
|
Spool file of the script:
SQL> drop view va;
View dropped.
SQL> drop table a CASCADE CONSTRAINTS PURGE;
Table dropped.
SQL> drop table b CASCADE CONSTRAINTS PURGE;
Table dropped.
SQL> -- ----------------------------------------------------------------------------
SQL> -- Set up table A
SQL> -- ----------------------------------------------------------------------------
SQL> create table a (a_id number not null,b_id number not null,description varchar2(100))
2 /
Table created.
SQL> insert into a values (9999,9999,lpad('a',100,'a'))
2 /
1 row created.
SQL> insert into a select * from a
2 /
1 row created.
SQL> /
2 rows created.
SQL> /
4 rows created.
SQL> /
8 rows created.
SQL> /
16 rows created.
SQL> /
32 rows created.
SQL> /
64 rows created.
SQL> /
128 rows created.
SQL> /
256 rows created.
SQL> /
512 rows created.
SQL> /
1024 rows created.
SQL> /
2048 rows created.
SQL> /
4096 rows created.
SQL> /
8192 rows created.
SQL> /
16384 rows created.
SQL> /
32768 rows created.
SQL> /
65536 rows created.
SQL> /
131072 rows created.
SQL> /
262144 rows created.
SQL> update a set a_id = rownum,b_id = DECODE(mod(rownum,10000),0,1,mod(rownum,10000))
2 /
524288 rows updated.
SQL> commit
2 /
Commit complete.
SQL> alter table a
2 add constraint a_pk primary key (a_id)
3 /
Table altered.
SQL> create unique index ai1 on a(b_id,a_id)
2 /
Index created.
SQL> exec dbms_stats.gather_table_stats(USER,'A',cascade=>true)
PL/SQL procedure successfully completed.
SQL> -- ----------------------------------------------------------------------------
SQL> -- Set up table B
SQL> -- ----------------------------------------------------------------------------
SQL> create table b (b_id number not null,num number not null,description varchar2(100))
2 /
Table created.
SQL> insert into b(b_id, num, description)
2 select distinct b_id, b_id, description
3 from a
4 /
9999 rows created.
SQL> update b set num = rownum
2 /
9999 rows updated.
SQL> commit
2 /
Commit complete.
SQL> alter table b
2 add constraint b_pk primary key (b_id)
3 /
Table altered.
SQL> create unique index bi1 on b(num,b_id)
2 /
Index created.
SQL> exec dbms_stats.gather_table_stats(USER,'B',cascade=>true)
PL/SQL procedure successfully completed.
SQL> --
SQL> ALTER TABLE a ADD CONSTRAINT fk_a_b FOREIGN KEY (b_id) REFERENCES b(b_id);
Table altered.
SQL> -- ----------------------------------------------------------------------------
SQL> -- Create view VA
SQL> -- ----------------------------------------------------------------------------
SQL> create or replace view va as select * from a
2 /
View created.
SQL> delete from plan_table
2 /
0 rows deleted.
SQL> explain plan for
2 update va
3 set description='updated 1'
4 where va.a_id in
5 (select va.a_id
6 from b, va
7 where b.num = 2
8 and va.b_id = b.b_id
9 )
10 /
Explained.
SQL> col "Query Plan" clear
SP2-0046: COLUMN 'Query Plan' not defined
SQL> col "Query Plan" format a300
SQL> SELECT id, parent_id,cardinality,
2 lpad(' ', 2*(level-1))||operation||' '||options||' '||object_name||' '||
3 decode(id, 0, 'Cost = '||position) "Query Plan"
4 FROM plan_table
5 START WITH id = 0
6 CONNECT BY prior id = parent_id
7 and prior nvl(statement_id,0) = nvl(statement_id,0)
8 /
0 52
UPDATE STATEMENT Cost = 109
1 0
UPDATE A
2 1 52
NESTED LOOPS
3 2 52
VIEW VW_NSO_1
4 3 52
SORT UNIQUE
5 4 52
NESTED LOOPS
6 5 1
INDEX RANGE SCAN BI1
7 5 52
INDEX RANGE SCAN AI1
8 2 1
INDEX UNIQUE SCAN A_PK
9 rows selected.
SQL> create or replace trigger trg_va
2 instead of insert or update on va
3 for each row
4 begin
5 null;
6 end;
7 /
Trigger created.
SQL> show errors
No errors.
SQL>
SQL> delete from plan_table
2 /
9 rows deleted.
SQL> explain plan for
2 update va
3 set description='updated 1'
4 where va.a_id in
5 (select va.a_id
6 from b, va
7 where b.num = 2
8 and va.b_id = b.b_id
9 )
10 /
Explained.
SQL> SELECT id, parent_id,cardinality,
2 lpad(' ', 2*(level-1))||operation||' '||options||' '||object_name||' '||
3 decode(id, 0, 'Cost = '||position) "Query Plan"
4 FROM plan_table
5 START WITH id = 0
6 CONNECT BY prior id = parent_id
7 and prior nvl(statement_id,0) = nvl(statement_id,0)
8 /
0 52
UPDATE STATEMENT Cost = 2475
1 0
UPDATE VA
2 1 52
HASH JOIN RIGHT SEMI
3 2 52
VIEW VW_NSO_1
4 3 52
NESTED LOOPS
5 4 1
INDEX RANGE SCAN BI1
6 4 52
INDEX RANGE SCAN AI1
7 2 524288
VIEW VA
8 7 524288
TABLE ACCESS FULL A
9 rows selected.
SQL> drop trigger trg_va
2 /
Trigger dropped.
SQL> delete from plan_table
2 /
9 rows deleted.
SQL> explain plan for
2 update va
3 set description='updated 1'
4 where va.a_id in
5 (select va.a_id
6 from b, va
7 where b.num = 2
8 and va.b_id = b.b_id
9 )
10 /
Explained.
SQL> SELECT id, parent_id,cardinality,
2 lpad(' ', 2*(level-1))||operation||' '||options||' '||object_name||' '||
3 decode(id, 0, 'Cost = '||position) "Query Plan"
4 FROM plan_table
5 START WITH id = 0
6 CONNECT BY prior id = parent_id
7 and prior nvl(statement_id,0) = nvl(statement_id,0)
8 /
0 52
UPDATE STATEMENT Cost = 109
1 0
UPDATE A
2 1 52
NESTED LOOPS
3 2 52
VIEW VW_NSO_1
4 3 52
SORT UNIQUE
5 4 52
NESTED LOOPS
6 5 1
INDEX RANGE SCAN BI1
7 5 52
INDEX RANGE SCAN AI1
8 2 1
INDEX UNIQUE SCAN A_PK
9 rows selected.
SQL> spool off;
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Nov 24 20:27:49 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
