Hints NO_MERGE, NO_QUERY_TRANSFORMATION.. with nested query don't work.
With pseudocolumn "rownum" the nested query is evaluated first
Tests performed on:
Oracle 10.2.0.5 Windows Vista, Windows Server 2003
Oracle 10.2.0.1, Windows Server 2003
We have tables with many records (~ 1 mill).
All users have the right to select some attributes of the tables ("public" attributes).
For selecting all attributes or editing records a sophisticated check (using the function
"FN_CHECK") is necessary.
The user selects some records (usually 1-100) from one of the tables by "public" attributes.
Only the subset of selected records for which the user has the rights to see/edit all attributes
are presented to the user for further selecting/editing.
To avoid the time consuming sophisticated check on millions of records we want to check
only the preselected records.
We used the /*+ NO_MERGE */ hint for the preselection
But we see that all records are checked and the function for the check consumes a lot of time.
Here is a simplified example, for testing (from BEGIN to END)
*/
-- BEGIN
-- A Table GZP with 2 fields: OBJECTID (primary key) and
-- GZP_ID (used for preselection), 1000 records
drop table gzp;
create table gzp (objectid number(10) not null, gzp_id number(10),
constraint gzp_pk primary key(objectid));
-- we populate the table GZP with 1000 rows
insert into gzp
select rownum, 1 from dual connect by rownum< 1001;
update gzp set gzp_id=2 where objectid=1;
commit;
-- A table A_SELECTSET with the preselected records, 1 record
drop table a_selectset;
create table a_selectset (oid number(10) not null,
table_name varchar2(30) not null,
username varchar2(30) not null,
constraint a_s_pk1
primary key (oid, table_name, username));
insert into a_selectset values(2,'GZP',user);
commit;
-- The join of A_SELECTSET and GZP gives 1 record.
set timing on;
select objectid, gzp_id from
a_selectset, gzp where
table_name='GZP' and OID=gzp_id;
/* RESULT
OBJECTID GZP_ID
---------- ----------
1 2
Elapsed: 00:00:00.01
*/
-- We need a function to check the result
-- The function for checking is called FN_CHECK, here is a simplified version.
-- We use the sequencer inside the function to see how many times the function
-- is used in a query.
DROP SEQUENCE S_TEST;
CREATE SEQUENCE S_TEST INCREMENT BY 1 START WITH 1
MAXVALUE 100000 MINVALUE 1 CYCLE
CACHE 20 NOORDER;
CREATE OR REPLACE FUNCTION FN_CHECK (pOBJECTID number)
return number
as
value number;
begin
select s_test.nextval into value from dual;
return value;
end;
/
-- the query is now
select objectid, gzp_id from
a_selectset, gzp where
table_name='GZP' and OID=gzp_id and FN_CHECK (OBJECTID)>0;
-- We want to apply the function FN_CHECK only to the preselected records
-- Before and after the query we select the value of the sequence S_TEST to see
-- how many times the function was used.
-- We see the function is called for each record (1000)
-- in table GZP and the sequence
-- is increased.
-- The hint /*+ NO_MERGE */ has no effect.
-- FN_CHECK is used 1000 times.
select s_test.currval from dual;
/*
CURRVAL
----------
1000
*/
select /*+ NO_MERGE(x) */ x.objectid, x.gzp_id from
(select objectid, gzp_id from
a_selectset, gzp where
username=user and
table_name='GZP' and OID=gzp_id) x
where FN_CHECK(x.objectid)>0
;
/* RESULT
OBJECTID GZP_ID
---------- ----------
1 2
*/
select s_test.currval from dual;
/*
CURRVAL
----------
2000
the function was called 1000 times!!!
*/
-- In the Execution Plan ID=3 TABLE ACCESS FULL| GZP
/*
Execution Plan
----------------------------------------------------------
Plan hash value: 3699291090
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 52 | 3 (0)| 00:00:01 |
| 1 | VIEW | | 2 | 52 | 3 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 2 | 146 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| GZP | 5 | 130 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN| A_S_PK1 | 1 | 47 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("FN_CHECK"("OBJECTID")>0)
4 - access("OID"="GZP_ID" AND "TABLE_NAME"='GZP' AND
"USERNAME"=USER@!)
*/
-- If we add the pseudocolumn rownum, the subquery is evaluated first and
-- then function FN_CHECK is called only once.
-- We don't need any hint.
select s_test.nextval from dual;
/*
NEXTVAL
----------
2001
*/
select x.objectid, x.gzp_id from
(select objectid, gzp_id , rownum from
a_selectset, gzp where
username=user and
table_name='GZP' and OID=gzp_id) x
where FN_CHECK(objectid) >0;
/* RESULT
OBJECTID GZP_ID
---------- ----------
1 2
*/
select s_test.currval from dual;
/*
CURRVAL
----------
2002
*/
-- We have a full table scan too, but the critical function
-- FN_CHECK is applied only once after the
-- preselection in the inner query (x).
/*
Execution Plan
----------------------------------------------------------
Plan hash value: 3482941984
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 650 | 3 (0)| 00:00:01 |
|* 1 | VIEW | | 50 | 650 | 3 (0)| 00:00:01 |
| 2 | COUNT | | | | | |
| 3 | NESTED LOOPS | | 50 | 3650 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| GZP | 99 | 2574 | 3 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN| A_S_PK1 | 1 | 47 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FN_CHECK"("OBJECTID")>0)
5 - access("OID"="GZP_ID" AND "TABLE_NAME"='GZP' AND
"USERNAME"=USER@!)
*/
--- END
Another option is to add a index on colum GZP_ID in table GZP.
Edited by: BluShadow on 19-May-2011 16:04
added {noformat}
{noformat} tags. Please read {message:id=9360002} to learn how to post code/data