The set up is below and the query at the end. no matter how much I
hint the query with the OR I can not make it perform as without the OR
although I see no reason for any extra IO.
I have also posted the explain plans it gives me for them. the or
seems to generate 428 times as much IO for the IN test and then filter
it out. I don't understand why... This is giving some Java guys here
a really good excuse to use Oracle as a bit bucket so could do with
some bullets in my gun.
--
-- 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 acess 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!!
--
select count(*)
from has_permission_unit_test main
where main.primary_key <= :i_volume
and
(
main.security_node_id in
( select 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
where delegate_user_id = 'MJONES'
and sysdate between effective_from and effective_to
and is_deleted = 0
union all
select 'MJONES' user_id
from dual
)
connect by prior sn.parent_node_id = sn.node_id
)
or -- Comment these two lines out for
good performanc
( main.xdi in ('HELLOE')) -- leave in for bad
)
The Explain for the without an or is...
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=2417 r=2367 w=0 time=223355 us)
1000 HASH JOIN SEMI (cr=2417 r=2367 w=0 time=222878 us)
1000 TABLE ACCESS FULL OBJ#(150327) (cr=2370 r=2367 w=0
time=190406 us)
500 VIEW (cr=47 r=0 w=0 time=26160 us)
500 FILTER (cr=47 r=0 w=0 time=25704 us)
517 CONNECT BY WITH FILTERING (cr=36 r=0 w=0 time=24755 us)
511 COUNT (cr=18 r=0 w=0 time=8488 us)
511 HASH JOIN (cr=18 r=0 w=0 time=8043 us)
511 INDEX FAST FULL SCAN OBJ#(151390) (cr=6 r=0 w=0
time=594 us)(object id 151390)
1520 INDEX FAST FULL SCAN OBJ#(151391) (cr=12 r=0 w=0
time=1266 us)(object id 151391)
6 HASH JOIN (cr=18 r=0 w=0 time=11254 us)
511 CONNECT BY PUMP (cr=0 r=0 w=0 time=456 us)
511 HASH JOIN (cr=18 r=0 w=0 time=8323 us)
511 INDEX FAST FULL SCAN OBJ#(151390) (cr=6 r=0 w=0
time=443 us)(object id 151390)
1520 INDEX FAST FULL SCAN OBJ#(151391) (cr=12 r=0 w=0
time=1223 us)(object id 151391)
1 UNION-ALL (cr=11 r=0 w=0 time=187 us)
0 TABLE ACCESS BY INDEX ROWID OBJ#(129800) (cr=8 r=0 w=0
time=82 us)
0 INDEX RANGE SCAN OBJ#(129898) (cr=8 r=0 w=0 time=70
us)(object id 129898)
1 FILTER (cr=3 r=0 w=0 time=53 us)
1 TABLE ACCESS FULL OBJ#(222) (cr=3 r=0 w=0 time=47 us)
The explain for with the or is...
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=17782 r=2367 w=0 time=7872443 us)
1000 FILTER (cr=17782 r=2367 w=0 time=7871740 us)
1000 TABLE ACCESS FULL OBJ#(150327) (cr=2370 r=2367 w=0
time=189193 us)
428 FILTER (cr=15412 r=0 w=0 time=7666933 us)
83925 CONNECT BY WITH FILTERING (cr=15408 r=0 w=0 time=7604091
us)
218708 COUNT (cr=7704 r=0 w=0 time=2679458 us)
218708 HASH JOIN (cr=7704 r=0 w=0 time=2497588 us)
218708 INDEX FAST FULL SCAN OBJ#(151390) (cr=2568 r=0 w=0
time=174867 us)(object id 151390)
650560 INDEX FAST FULL SCAN OBJ#(151391) (cr=5136 r=0 w=0
time=496682 us)(object id 151391)
2568 HASH JOIN (cr=7704 r=0 w=0 time=3669017 us)
218708 CONNECT BY PUMP (cr=0 r=0 w=0 time=181299 us)
218708 HASH JOIN (cr=7704 r=0 w=0 time=2530172 us)
218708 INDEX FAST FULL SCAN OBJ#(151390) (cr=2568 r=0 w=0
time=177272 us)(object id 151390)
650560 INDEX FAST FULL SCAN OBJ#(151391) (cr=5136 r=0 w=0
time=491691 us)(object id 151391)
1 UNION-ALL (cr=4 r=0 w=0 time=98 us)
0 TABLE ACCESS BY INDEX ROWID OBJ#(129800) (cr=1 r=0 w=0
time=39 us)
0 INDEX RANGE SCAN OBJ#(129898) (cr=1 r=0 w=0 time=35
us)(object id 129898)
1 FILTER (cr=3 r=0 w=0 time=46 us)
1 TABLE ACCESS FULL OBJ#(222) (cr=3 r=0 w=0 time=42 us)
It would seem that 428 is a magic number...
Thanks in advance,
Mike.
Received on Wed Nov 10 2004 - 12:02:09 CST
