IN (vs) EXISTS and NOT IN (vs) NOT EXISTS

I have the most bizarre CBO’ness (for what of a better word!) that I can not fathom. Below are the tear down and set up needed.

select * from v$banner

BANNER

—————————————————————-

Oracle9i Enterprise Edition Release 9.2.0.4.0 – 64bit Production

PL/SQL Release 9.2.0.4.0 – Production

CORE 9.2.0.3.0 Production

TNS for HPUX: Version 9.2.0.4.0 – Production

NLSRTL Version 9.2.0.4.0 – Production




— Clear down



drop table HAS_PERMISSION_UNIT_TEST

/

drop table PLT_NODE_PERMISSIONS

/

drop table PLT_SECURITY_NODES

/

drop table PLT_USER_DELEGATES

/

drop table PLT_XDI_PERMISSIONS

/

CREATE TABLE PLT_SECURITY_NODES

( NODE_ID VARCHAR2(16) NOT NULL,

PARENT_NODE_ID VARCHAR2(16),

IS_DELETED NUMBER NOT NULL,

IS_SYSTEM NUMBER NOT NULL,

LAST_MODIFIED_DATETIME DATE,

LAST_MODIFIED_USER_ID VARCHAR2(32)

)

/


CREATE INDEX PSN_PSN_FK_I ON PLT_SECURITY_NODES

(PARENT_NODE_ID)

/


CREATE UNIQUE INDEX PSN_PK ON PLT_SECURITY_NODES

(NODE_ID)

/


CREATE INDEX NODE_AND_PARENT_NODE ON PLT_SECURITY_NODES

(NODE_ID, PARENT_NODE_ID)

/


ALTER TABLE PLT_SECURITY_NODES ADD (

CONSTRAINT PSN_PK PRIMARY KEY (NODE_ID))

/


ALTER TABLE PLT_SECURITY_NODES ADD (

CONSTRAINT PSN_PSN_FK FOREIGN KEY (PARENT_NODE_ID)

REFERENCES PLT_SECURITY_NODES (NODE_ID))

/


————————————————————————————————


CREATE TABLE PLT_NODE_PERMISSIONS

( NODE_ID VARCHAR2(32) NOT NULL,

USER_ID VARCHAR2(32) NOT NULL,

ROLE_ID VARCHAR2(32) NOT NULL,

IS_DELETED NUMBER NOT NULL,

IS_SYSTEM NUMBER NOT NULL,

LAST_MODIFIED_DATETIME DATE,

LAST_MODIFIED_USER_ID VARCHAR2(32)

)

/


CREATE UNIQUE INDEX PNP_PK ON PLT_NODE_PERMISSIONS

(NODE_ID, USER_ID, ROLE_ID)

/


CREATE INDEX NODE_AND_USER ON PLT_NODE_PERMISSIONS

(NODE_ID, USER_ID)

/


CREATE INDEX PNP_PSN_FK_I ON PLT_NODE_PERMISSIONS

(NODE_ID)

/


CREATE INDEX PNP_PPR_FK_I ON PLT_NODE_PERMISSIONS

(ROLE_ID, USER_ID)

/


ALTER TABLE PLT_NODE_PERMISSIONS ADD (

CONSTRAINT PNP_PK PRIMARY KEY (NODE_ID, USER_ID, ROLE_ID))

/


ALTER TABLE PLT_NODE_PERMISSIONS ADD (

CONSTRAINT PNP_PSN_FK FOREIGN KEY (NODE_ID)

REFERENCES PLT_SECURITY_NODES (NODE_ID))

/

—————————————————————————-


CREATE TABLE PLT_USER_DELEGATES

( USER_ID VARCHAR2(32) NOT NULL,

DELEGATE_USER_ID VARCHAR2(32) NOT NULL,

EFFECTIVE_FROM DATE NOT NULL,

EFFECTIVE_TO DATE,

IS_DELETED NUMBER NOT NULL,

IS_SYSTEM NUMBER NOT NULL,

LAST_MODIFIED_DATETIME DATE,

LAST_MODIFIED_USER_ID VARCHAR2(32)

)

/


CREATE INDEX PUD_PP_FK_I ON PLT_USER_DELEGATES

(USER_ID)

/


CREATE INDEX PUD_PP_DELEGATE_FK_I ON PLT_USER_DELEGATES

(DELEGATE_USER_ID)

/


CREATE UNIQUE INDEX PUD_PK ON PLT_USER_DELEGATES

(USER_ID, DELEGATE_USER_ID, EFFECTIVE_FROM)

/


ALTER TABLE PLT_USER_DELEGATES ADD (

CONSTRAINT PUD_PK PRIMARY KEY (USER_ID, DELEGATE_USER_ID, EFFECTIVE_FROM))

/

—————————————————————————————


CREATE TABLE PLT_XDI_PERMISSIONS

(

XDI VARCHAR2(255) NOT NULL,

USER_ID VARCHAR2(32) NOT NULL,

ROLE_ID VARCHAR2(16) NOT NULL,

IS_DELETED NUMBER NOT NULL,

IS_SYSTEM NUMBER NOT NULL,

LAST_MODIFIED_DATETIME DATE,

LAST_MODIFIED_USER_ID VARCHAR2(32)

)

/


CREATE INDEX PXP_PPR_FK_I ON PLT_XDI_PERMISSIONS

(ROLE_ID, USER_ID)

/


CREATE UNIQUE INDEX PXP_PK ON PLT_XDI_PERMISSIONS

(XDI, USER_ID, ROLE_ID)

/


ALTER TABLE PLT_XDI_PERMISSIONS ADD (

CONSTRAINT PXP_PK PRIMARY KEY (XDI, USER_ID, ROLE_ID))

/

begin



— Set up 4 delegates for this user to have access via



for idx in 1..4

loop

insert into plt_user_delegates

( user_id, delegate_user_id, effective_from, effective_to, is_deleted, is_system, last_modified_datetime, last_modified_user_id)

values

(‘MJONES’, ‘MJONES_DELEGATE_’||idx, sysdate -100, sysdate + 100, 0, 0, sysdate, ‘MJONES’);



end loop;



— Loop 500 times to create some realistic volume of data that this user has



for idx in 1..500

loop

insert into plt_security_nodes

( node_id, parent_node_id, is_deleted, is_system, last_modified_datetime, last_modified_user_id )

values

(‘NODE_’||idx||’_TOP_LVL’, null, 0, 1, sysdate, ‘MJONES’);



insert into plt_security_nodes

( node_id, parent_node_id, is_deleted, is_system, last_modified_datetime, last_modified_user_id )

values

(‘NODE_’||idx||’_LEVEL_2’, ‘NODE_’||idx||’_TOP_LVL’, 0, 1, sysdate, ‘MJONES’);



insert into plt_security_nodes

( node_id, parent_node_id, is_deleted, is_system, last_modified_datetime, last_modified_user_id )

values

(‘NODE_’||idx||’_LVL_3’, ‘NODE_’||idx||’_LEVEL_2’, 0, 1, sysdate, ‘MJONES’);



— Create permission into Node



insert into plt_node_permissions ( node_id, user_id, role_id, is_deleted, is_system ) values (

‘NODE_’||idx||’_LVL_3’, ‘MJONES’, ‘UNIT_TEST’, 0, 0);

end loop;



— Now wedge up some XDI Permissions. Have a total of 100 XDI’s



for idx in 1..100

loop

insert into plt_xdi_permissions

( xdi, user_id, role_id, is_deleted, is_system)

values

( idx||’_JONES_THE_XDI’, ‘MJONES’, ‘UNIT_TEST’, 0, 0);

end loop;

end;

/

analyze table plt_node_permissions compute statistics for table for all indexes for all indexed columns

/

analyze table plt_security_nodes compute statistics for table for all indexes for all indexed columns

/

analyze table plt_user_delegates compute statistics for table for all indexes for all indexed columns

/

analyze table plt_xdi_permissions compute statistics for table for all indexes for all indexed columns

/



— Now build up a table of 100,000 rows to base all this malarkey on… Set the access up to start with so that we have access too all 100,000 records in bunches of 200 (500 access records we have access to)



create table has_permission_unit_test as

select

to_char(rownum) primary_key,

owner, object_name, subobject_name, object_id, data_object_id, object_type, created, last_ddl_time, timestamp, status, temporary, generated, secondary,

‘NODE_’||(mod(rownum,200)+1)||’_LVL_3’ security_node_id,

–decode(mod(rownum,1000),0, mod(rownum,1000)||’JONES_THE_XDI’,rownum||’_XDI_’||object_name) xdi,

rownum||’_XDI_’||object_name xdi,

0 is_deleted,

sysdate last_modified_datetime,

‘BOB’ last_modified_user_id

from

( select * from all_objects

union all

select * from all_objects

union all

select * from all_objects

union all

select * from all_objects

union all

select * from all_objects

)

where rownum <= 100000– 100K

/



alter table has_permission_unit_test modify primary_key varchar2(32)

/



alter table has_permission_unit_test add primary key (primary_key)

/



analyze table has_permission_unit_test compute statistics for table for all indexes for all indexed columns

/



— END OF SET UP


The query that behaves strangley is…

select count(*)

from has_permission_unit_test main

where main.primary_key <= :i_volume

and

(

main.security_node_id in

( select /*use_hash(sn) index_ffs( sn node_and_parent_node ) use_hash(np) index_ffs(np node_and_user)*/ np.node_id

from plt_security_nodes sn, plt_node_permissions np

where sn.node_id = np.node_id

and np.user_id in

( select user_id

from plt_user_delegates ud_first

where ud_first.delegate_user_id = ‘MJONES’

and sysdate between ud_first.effective_from and ud_first.effective_to

and ud_first.is_deleted = 0

union all

select ‘MJONES’ user_id

from dual dual_first

)

connect by prior sn.parent_node_id = sn.node_id

)

— COMMENT OUT FROM HERE

or

( main.xdi in

( select xi.xdi

from plt_xdi_permissions xi

where xi.user_id in

( select user_id

from plt_user_delegates ud_second

where ud_second.delegate_user_id = ‘MJONES’

and sysdate between ud_second.effective_from and ud_second.effective_to

and ud_second.is_deleted = 0

union all

select ‘MJONES’ user_id

from dual dual_second

)

)

)

— DOWN TO HERE

)


Running as is with :i_volume = 1000 takes around 4 seconds, and the matches are being done with the security nodes (I beleive), but if you comment out the bottom bit the plan radically changes to start FFS with a couple of indexes I can not see why the “or” changes this. Even if I change this to “or 1 in (select null from dual where 1=0) it still won’t FFS. I tried hinting (the + is removed so it won’t pick it up to no avail. Why has the performance so dropped off when I stick any OR in?

Thanks in advance, and hope this is enough info.

Mike.

Related:

Leave a Reply