Oracle 9i -- Any Options Made Free?

From: MacGregor, Ian A. <ian_at_SLAC.Stanford.EDU>
Date: Mon, 09 Oct 2000 11:47:49 -0700
Message-Id: <10644.118817@fatcity.com>

Often times when Oracle announces a major release, one or more of the database options becomes free. For instance with the 8i release the Object Option, and soon afterwards the JVM and Intermedia became free. I haven't heard of any option becoming free with 9i.

If Mr. Ellison really wants us to all have the same configurations, are we expected to be running all these options. A database with ANO installed but not switched on is different from one where it is installed a switched on. If it's really
only two products, does that mean 9i includes all the options. I don't think so.

I worried for about a minute-and-a-half. About the new features making DBA's less valuable, but I don't spend my days adjusting init.ora parameters. I do like the fact that rollback_segments are going away. I was never sure If I really had them sized optimal. Most of my tuning problems are along the following lines.

            :) Vouchsafe, O DBA, how to get our code to execute faster.
               And the DBA looked at the code and saw it was bad.
               The DBA spake unto them ...
               And the programmers saw what the DBA said was good, and
changed their code.
               And the  voices of the user multitudes sang out in praise of

the DBA. :)

A couple of weeks ago, I was handed the following code ...

SELECT PERSON_ID
FROM
SID.PERSON WHERE PERSON_ID IN (select distinct id from SID.Person_Name_Index
  where (LNAME = 'XXXXXX' AND NAME_TYPE <= '3') intersect select distinct id   from SID.Person_Name_Index where (FNAME = 'XXXXXX' AND NAME_TYPE <= '3')   intersect select distinct id from SID.Person_Name_Index where (FNAME = 'X'   AND NAME_TYPE <= '3')) OR PERSON_ID IN (select distinct id from   SID.Person_Name_Index where (LNAME = 'XXXXXX' AND NAME_TYPE <= '3')   intersect select distinct id from SID.Person_Name_Index where (LNAME = 'X'   AND NAME_TYPE <= '3') intersect select distinct id from   SID.Person_Name_Index where (FNAME = 'XXXXXX' AND NAME_TYPE <= '3')) OR   PERSON_ID IN (select distinct id from SID.Person_Name_Index where (LNAME =
'XXXXXX' AND NAME_TYPE <= '3') intersect select distinct id from
  SID.Person_Name_Index where (FNAME = 'X' AND NAME_TYPE <= '3')) OR   PERSON_ID IN (select distinct id from SID.Person_Name_Index where (LNAME =
'X' AND NAME_TYPE <= '3') intersect select distinct id from
  SID.Person_Name_Index where (FNAME = 'XXXXXX' AND NAME_TYPE <= '3')) OR   PERSON_ID IN (select distinct id from SID.Person_Name_Index where   (LNAME_SOUNDEX = 'X150' AND NAME_TYPE <= '3') intersect select distinct id   from SID.Person_Name_Index where (FNAME_SOUNDEX = 'X250' AND NAME_TYPE <=
'3') intersect select distinct id from SID.Person_Name_Index where
  (FNAME_SOUNDEX = 'X000' AND NAME_TYPE <= '3')) ORDER BY LNAME, FNAME

The name has been changed to protect the innocent. This code took several minutes to run. After changes were made, it ran on about 1 second. The changes to be made are pretty obvious, but Oracle's optimizer won't make them.

Sorry I missed the gathering. My daughter caught croup that evening.

Ian MacGregor
Stanford Linear Accelerator Center Received on Mon Oct 09 2000 - 13:47:49 CDT