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 Go to next message
Kevin Meade
Messages: 1907
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 #421610 is a reply to message #421504] Wed, 09 September 2009 07:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I've not come across this before.

Does the view have an 'INSTEAD OF DELETE' trigger?
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 Go to previous messageGo to next message
Kevin Meade
Messages: 1907
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 Go to previous messageGo to next message
Kevin Meade
Messages: 1907
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 Go to previous messageGo to next message
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 #421797 is a reply to message #421504] Thu, 10 September 2009 11:19 Go to previous messageGo to next message
Kevin Meade
Messages: 1907
Registered: December 1999
Location: Connecticut USA
Senior Member
Thanks for your time my friend. At least now I know I am see straight.

TAR has been submitted to Metalink. I'll post whatever they suggest once they come through.

Kevin
Re: nested select / instead-of-trigger and views=no index used [message #501430 is a reply to message #421797] Tue, 29 March 2011 03:10 Go to previous messageGo to next message
ipynnonen
Messages: 4
Registered: March 2011
Junior Member
Hello Kevin, I have the same problem as you have described above. How did you solve the problem?
Br
Ilkka
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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;
Re: nested select / instead-of-trigger and views=no index used [message #501499 is a reply to message #501443] Tue, 29 March 2011 08:19 Go to previous messageGo to next message
Kevin Meade
Messages: 1907
Registered: December 1999
Location: Connecticut USA
Senior Member
There was no satisfactory conclusion from Oracle Corp. Maybe someone can rerun the test on an 11g 11.2 database and tell us if the problem persists.

Additionally I would suggest that everyone with this issue post a TAR w/Oracle so that support begins to understand the need to fix this problem.

Kevin
Re: nested select / instead-of-trigger and views=no index used [message #501509 is a reply to message #501499] Tue, 29 March 2011 08:47 Go to previous message
ipynnonen
Messages: 4
Registered: March 2011
Junior Member
Hi, I ran the test on an Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production.

I created a Service Request in Oracle Support. I am waiting for the reply. Hopefully they have a work around...

Br.
Ilkka
Previous Topic: Performance issue with long running query
Next Topic: Why are these explain plans missing some info and messed up like they've been simultaneously written
Goto Forum:
  


Current Time: Sun Apr 20 13:56:52 CDT 2014

Total time taken to generate the page: 0.09866 seconds