Forum OpenACS Q&A: Question about an index

Collapse
1: Question about an index
Posted by David Kuczek on 09/20/01 08:27 PM
In one of our tables we have two columns called on_what_id,
on_which_table. They serve the same purpose as the columns in
general_comments. The only difference is that not every row
necessarily has a value for those two columns.

Does an index on one_of_our_tables(on_which_table, on_what_id) still
improve scalability?

Collapse
2: Response to Question about an index (response to 1)
Posted by Don Baccus on 09/20/01 10:36 PM
Only in cases where your join or restriction clause references both columns or the first (on_which_table) column.  The index won't be used if you're just using on_what_id.

For Oracle, this probably needs to be refined for the "only on_what_id" case.  It won't do a binary search on the index contents, but if the table rows are sufficiently large, it should still do a linear search on the index contents.  In this case there's some savings simply because it
only needs to sweep the smaller index rather than the larger table.

Collapse
3: Response to Question about an index (response to 1)
Posted by David Kuczek on 09/21/01 12:17 AM
Good to know... I didn't know about the importance of the order of the column names inside the brakets.

But if I had a table with four columns:

column_1 - column_2 - on_what_id - on_which_table

Additionally I had an index of the kind I mentioned above and four rows were inserted in that table.

The first two rows had values in column_1 and column_2 BUT NULL for on_which_table and on_what_id.

The last two had values in all four columns.

I heard that in case of NULLs in indexed columns the index will not be used!? What if there was a '' instead of a NULL in the first two columns?

Collapse
4: Response to Question about an index (response to 1)
Posted by Don Baccus on 09/21/01 12:28 AM
This is true in Oracle, I believe - someone who knows Oracle better than I can comment.  '' and NULL are treated equivalently (as NULL) in insert/update statements in Oracle so inserting '' won't help ...
Collapse
5: Response to Question about an index (response to 1)
Posted by Andrew Piskorski on 09/21/01 06:15 PM
Well, I'm no Oracle expert either, but Oracle does not ever index NULL values. So if you do something like: 
select foo  from my_table  where bar is null
you will force a full table scan. I don't know why Oracle does it that way - you'd think indexing nulls just like any other value would be useful. Also, that's for normal b-tree indexes - I'm not sure whether the same is true of bitmap indexes.

On the other hand, 

select foo  from my_table  where bar is NOT null
will be able to use the index just fine. (Whether Oracle actually uses the index or not is at the optimizer's discretion, of course).

On the inserting thing, yes, Don's correct - Oracle coerces empty string to null on inserts, and I believe updates as well. Funny, I can't remember ever needing to insert a real empty string into Oracle. Offhand, I can't think of a good way to do that.