Welcome to the Blog of Mike Ross

The first thing I learned was that you must use a SQL Plus session, not PL/SQL Developer. The directives just won't work from PL/SQL Developer.

We decided to look at a fairly large table from SWAMP called LID (short for line item detail). We actually used lid_bak, a backup table, just in case... The first step is to analyze the table, like this:

analyze table fic30.lid_bak compute statistics;

Once this completes, we will set autotrace to display only the explain plan (and not actually run the SQL statement), like this:

set autotrace traceonly explain

Next, go ahead and write up your SQL statement. I used a really simple one:

select * from fic30.lid_bak where preq_no = 'PRQ135670';

And this is the output I received:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1654 Card=3 Bytes=43
2)

1 0 TABLE ACCESS (FULL) OF 'LID_BAK' (Cost=1654 Card=3 Bytes=4
32)

What the heck! It's wanting to perform a full table scan to look up the info on only one preq_no? Why? Well, looking at this backup table, I see there are no indexes on it.

I then created an index of the lid_bak table on the preq_no column.

Now let's try that SQL statement again (same exact statement):

select * from fic30.lid_bak where preq_no = 'PRQ135670';

And now look at the output:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=3 Bytes=432)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'LID_BAK' (Cost=2 Card=3
Bytes=432)

2 1 INDEX (RANGE SCAN) OF 'BAKIND' (NON-UNIQUE) (Cost=1 Card
=3)

Ahhh, much better! Well, we accidentally learned something here - the value of a good index! Look at the Cost of using the index = 1 , compared to the cost without an index, or a full table scan = 1654. Very interesting.

Next, we turned our attention to a table Dale has been working with sir.raw_data. We ran the statistics on that one and then tried a couple SQL statements against it. One thing we noticed is that this table has about 6 indexes on it, and the optimizer only seems to be using one of them. This is true even if we request columns in our SQL statement that exactly match the columns the index is based on. This means these extra indexes are just excess baggage. Also this table has more than 1,000,000 rows so is a good candidate for partitioning. Next week we're going to fiddle around with this table and see if we can use some timing statistics to show improvement in queries against this table by using some of our new tuning techniques, such as removing unused indexes and partitioning.