Oracle Scratchpad

January 14, 2010

Old ideas

Filed under: Indexing,Troubleshooting — Jonathan Lewis @ 6:57 pm GMT Jan 14,2010

Here’s a question about function-based indexes that appeared a little while ago on OTN, demonstrating the problems of old information not going away. This is how the question started:

I saw a blog post namely:

The list of steps we needs to be done to use function based indexes:

You must have the system privelege query rewrite to create function based indexes on tables in your own schema. You must have the system privelege global query rewrite to create function based indexes on tables in other schemas For the optimizer to use function based indexes, the following session or system variables must be set:

For Instance,
This can be fixed using the ALTER SESSION or ALTER SYSTEM or in init.ora param file

alter session set query_rewrite_enabled = true;
alter session set query_rewrite_integrity = trusted;

It says that I have to enable query rewrite to be able to use the function index,  do i really have to enable it?

The answer to the question is, of course: “No – not since Oracle 8i (but see note 1 below)“. But if you search google for a phrase like: “query rewrite to create function based indexes” you’ll get several hits that repeat, to the letter in some cases, the reported text.

In this case, luckily, the advice is redundant rather than destructive – but there are plenty of really bad ideas on the Internet that just won’t go away because someone, somewhere is going to keep them alive by repeating them without thinking or testing.

Here’s another example, also from OTN demonstrating a variant of the problem. Note that the date on the posting is September 2009 and the advice is to read Metalink note 122008.1 on rebuilding indexes. But note 122008.1 doesn’t seem to exist: and that’s because in May 2008, 16 months earlier, I used the feedback link on Metalink to point out a few of the inappropriate statements (errors, even) that it made and it was removed – along with Metalink note 77574.1

Next time you think about quoting some advice you’ve taken off the internet, remember my guidelines about trusting information from the Internet – then try to follow the current (UK) slogan for handling swine flu: “catch it, kill it, bin it”.

Footnote: one of my guidelines is about documents being dated – but it occurs to me that many notes on Metalink have a “Last Update” date on them but no “Originally Published” date. This is bad news because some of the notes were clearly written in the days of Oracle 7 (or earlier) with a few modifications applied over the years but no major revision: a last update date can give you a false sense of security.

Update 25th Jan 2010: Here’s an example of the Metalink issue – a posting on the OTN database forum asking about changing the block size of an database, and a suggestion to read a document which has a “modified date” of July 2007 but which is basically a piece of advice written for Oracle 7.3


  1. >The answer to the question is, of course: “No – not since Oracle 8i”
    Another possible answer is “No – not since” :) I don’t know what was before 9iR2.

    Comment by Timur Akhmadeev — January 14, 2010 @ 8:13 pm GMT Jan 14,2010 | Reply

  2. “then try to follow the current (UK) slogan for handling swine flu: “catch it, kill it, bin it”.”
    Blimey, glad I’ve been vaccinated !

    Comment by Gary — January 14, 2010 @ 10:42 pm GMT Jan 14,2010 | Reply

  3. Maybe if we all asked for proper revision history in the docs, we’d all be happier. But first we’d have to convince them of the evils of purging and Newspeak.

    Comment by joel garry — January 15, 2010 @ 1:11 am GMT Jan 15,2010 | Reply

  4. This is an interesting blog post – you could possibly make this the start of a series of blog posts (a faulty quotes series?).

    Your post highlights the need for people who publish information on the Internet to not only verify that the information they provide is correct today, but also that the authors should recheck the information when new Oracle release are made available. It is a little troubling that most of the above quote may have appeared in a book written about Oracle 10g (based on Google a keyword search).

    Comment by Charles Hooper — January 16, 2010 @ 2:43 am GMT Jan 16,2010 | Reply

    • Charles,

      I didn’t think of checking if the quote was from a recent book; only for its presence on the Internet.
      I think it may be a little optimistic to hope that people would keep re-verifying their material and correcting it. (Although Oracle Corp. should, perhaps be an exception on Metalink). I have about 2,000 scripts on my laptop demonstrating various aspects of Oracle behaviour, and it would probably take about 3 years to re-run every one of them properly; there’s even one relating to a quirk of materialized views that I haven’t modified since I created it in May 2000, and the notes in the file are definitely in need of revisiting.

      But even if it’s impossible to keep repeating the tests and updating the posts, it’s easy, and important, to put at least the creation date and the versions used when the document was written.

      Comment by Jonathan Lewis — January 24, 2010 @ 9:17 pm GMT Jan 24,2010 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by