That crazy CBO.....

Ok, I'm a bit at a loss to explain this.....

I have two tables, ADDS_USERS, which has AUSR_ID as the primary key, and AUTHORIZED_IP_ADDRESSES. Now, my original query looks like this:
SELECT A.AUSR_LOGIN_SCREEN_NAME FROM ADDS_USERS A, AUTHORIZED_IP_ADDRESSES B
WHERE A.AUSR_ID =3D B.AUSR_ID AND :B1 BETWEEN B.AIA_IP_ADDRESS_START AND B.AIA_IP_ADDRESS_END; and when run w/ autotrace set to traceonly, produces the following output:

Execution Plan

   0 SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D13 Card=3D110 = Bytes=3D46

          20)

   1 0 NESTED LOOPS (Cost=3D13 Card=3D110 Bytes=3D4620)

   2    1     INDEX (RANGE SCAN) OF 'AIA_INDX_PR01' (NON-UNIQUE) (Cost
          =3D4 Card=3D110 Bytes=3D3080)

   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'ADDS_USERS' (Cost=3D2 Ca
          rd=3D1 Bytes=3D14)

   4    3       INDEX (UNIQUE SCAN) OF 'AUSR_PK' (UNIQUE)

Statistics

          0  recursive calls
          0  db block gets
       1321  consistent gets
        864  physical reads
          0  redo size
        513  bytes sent via SQL*Net to client
        652  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Now, that plan looks quite reasonable, but, 1321 buffer gets is too many, not to mention the physical I/O. This is a very frequently called SQL statement.

So, I tried:
re-creating ADDS_USERS ordered by AUSR_ID, to improve AUSR_PK index clustering factor. No use.
creating AUTHORIZED_IP_ADDRESSES as an IOT. Also useless.

So, I went back to SQL hacking, and finally came up with the somewhat odd looking, but effective:
SELECT (select A.AUSR_LOGIN_SCREEN_NAME FROM ADDS_USERS A where a.ausr_id =3D b.ausr_id) from AUTHORIZED_IP_ADDRESSES B WHERE :B1 BETWEEN B.AIA_IP_ADDRESS_START AND B.AIA_IP_ADDRESS_END; which produces an output like:
Execution Plan

   0 SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D2 Card=3D110 = Bytes=3D308

          0)

   1 0 TABLE ACCESS (BY INDEX ROWID) OF 'ADDS_USERS' (Cost=3D2 Card

          =3D1 Bytes=3D14)

   2 1 INDEX (UNIQUE SCAN) OF 'AUSR_PK' (UNIQUE) (Cost=3D1 = Card=3D1

          )

   3 0 INDEX (RANGE SCAN) OF 'AIA_INDX_PR01' (NON-UNIQUE) (Cost=3D4

           Card=3D110 Bytes=3D3080)

Statistics

          0  recursive calls
          0  db block gets
         67  consistent gets
          0  physical reads
          0  redo size
        562  bytes sent via SQL*Net to client
        652  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Now, that's a bit odd looking plan, but, look at those consistent gets! Down to 67 from 1321!

So, I guess what I'm wondering is why this re-write is so effective? I'm happy that I found a solution, but, I'm curious what's going on here. Intuitively, I'm thinking that all I'm doing with the re-write is forcing the optimizer to do what it seems to claim to be already doing with the original version . With the scalar subquery, it seems the nested loop join is implicit in the select statement.

Well, anyhow, I thought this was pretty odd.

Thoughts, anyone?

-Mark
--

Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
"On two occasions, I have been asked [by members of Parliament], "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
-- Charles Babbage (1791-1871)

--

http://www.freelists.org/webpage/oracle-l Received on Wed Oct 27 2004 - 15:36:13 CDT