Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Hints NO_MERGE, NO_QUERY_TRANSFORMATION.. with nested query don't work

user966832May 19 2011 — edited May 20 2011
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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 17 2011
Added on May 19 2011
5 comments
3,632 views