Home > DBA, SQL > Mailbag: To Upgrade, or Not to Upgrade—That is the Question

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]

Hi, M.,

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.

Best wishes,


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

Categories: DBA, SQL
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

%d bloggers like this: