Mailbag: To Upgrade, or Not to Upgrade—That is the Question
Subject: Love the book, but have a simple question
On page 392, the last line of the SQL presented in the text reads
AND my_indexes.index_type = :index_type;
What is the reason/purpose for prefixing index_type with : ?
Also, given the choice of installing Oracle 11g R1 instead of Oracle 11g R2, do you recommend 11g R1 or 11g R2? (Based on what you wrote on https://iggyfernandez.wordpress.com/, I’m not sure of your recommendation.) If you favor 11g R1, would you give me a couple of reasons behind your thinking?
Thank you for your time,
-M. [name withheld]
The colon punctuation mark indicates that index_type is a variable. For the purposes of the examples in the chapter on SQL tuning, it should be instantiated in the following manner:
VARIABLE index_type VARCHAR2(27);
EXEC :index_type := ‘BITMAP’;
You can download and run the entire demo script for my presentation Xtreme SQL Tuning: The Tuning Limbo which is based on the examples in the chapter.
As regards the best choice of database version, the following article may be helpful: To Upgrade or Not to Upgrade.
Mailbag: Advice for an Oracle 9i DBA
Mailbag: Becoming a DBA
Mailbag: Is It Too Late For Me To Start Oracle
Mailbag: Is This Book For Me?
Mailbag: Jumpstarting an IT Career After Twelve Years
Mailbag: SQL Exercises