PURPOSE
-------
Fact : Oracle9i Enterprise Edition Release 9.2.0.1.0 -> 9.2.0.4
[Problem Definition]
------------------------
When you are using RULE base optimizer and query has
ROUNUM column, optimizer will not use existing Index.
[Analysis]
-------------
Query with the ROWNUM column and optimizer mode is RULE then
index scan will not work in Oracle release 9.2.0.X.0
But same query will use the index in Oracle release 9.0.1.4.0 with the
column ROWNUM and RULE base optimizer.
[Test case Summary]
-----------------------
Connected to:
Oracle9i Enterprise Edition Release 9.0.1.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.0.1.4.0 - Production
SQL> set autot on explain
SQL> alter session set optimizer_mode=rule;
Session altered.
SQL> select * From emp where empno in (7788,7900) and rownum < 2;
or
SQL> select * From emp where (empno =7788 or empno =7900) and rownum < 2;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 COUNT (STOPKEY)
2 1 CONCATENATION
3 2 FILTER
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
5 4 INDEX (RANGE SCAN) OF 'EMP_EMPNO_PK' (UNIQUE)
6 2 FILTER
7 6 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
8 7 INDEX (RANGE SCAN) OF 'EMP_EMPNO_PK' (UNIQUE)
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.2.0 - Production
SQL> alter session set optimizer_mode=rule;
Session altered.
SQL> select * From emp where empno in (7788,7900) and rownum < 2;
or
SQL> select * From emp where (empno =7788 or empno =7900) and rownum < 2;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
7788 SCOTT ANALYST 7566 09-DEC-82 3000
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 COUNT (STOPKEY)
2 1 TABLE ACCESS (FULL) OF 'EMP'
WORKAROUND:
-----------
Rewrite the query to use a union all to manually concatenate the OR'd values:
select * From emp where empno = 7788 and rownum < 2
union all
select * From emp where empno = 7900 and rownum < 2 ;
[OR]
Use the /*+ use_concat */ hint, however this will force the use of the CBO.
[Test Case]
-------------
DROP TABLE EMP ;
.
CREATE TABLE EMP
(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB CHAR(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2)) ;
.
INSERT INTO EMP VALUES
(7369, 'SMITH', 'CLERK', 7902,
TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMP VALUES
(7499, 'ALLEN', 'SALESMAN', 7698,
TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO EMP VALUES
(7521, 'WARD', 'SALESMAN', 7698,
TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO EMP VALUES
(7566, 'JONES', 'MANAGER', 7839,
TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES
(7654, 'MARTIN', 'SALESMAN', 7698,
TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES
(7698, 'BLAKE', 'MANAGER', 7839,
TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES
(7782, 'CLARK', 'MANAGER', 7839,
TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES
(7788, 'SCOTT', 'ANALYST', 7566,
TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
(7839, 'KING', 'PRESIDENT', NULL,
TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES
(7844, 'TURNER', 'SALESMAN', 7698,
TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
INSERT INTO EMP VALUES
(7876, 'ADAMS', 'CLERK', 7788,
TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES
(7900, 'JAMES', 'CLERK', 7698,
TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
INSERT INTO EMP VALUES
(7902, 'FORD', 'ANALYST', 7566,
TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
(7934, 'MILLER', 'CLERK', 7782,
TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
alter table emp add constraint emp_empno_pk primary key (empno,ename) using
index tablespace indx;
commit ;
RELATED DOCUMENTS
-----------------
Bug 2068210 : "ROWNUM = constant" predicate can return wrong results with RBO
Issue reported in the Bug#:2068210 Fixed in 9.2.0.5