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:
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 220.127.116.11 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