Redbrick List Forum

Use of Indexes

Posted By:
Date:

Hi all

Lets assume there is a table with a few million rows in.
There are two indexes on this table, both are b-tree indexes.

BTREE_ONE is on column A B C and
BTREE_TWO is on column D E F

I have three scenarios that I am not sure of how Redbrick interprets the use
of indexes....

1. The query (the variables dont matter):

SELECT A, B, C, D, E
FROM TABLE
WHERE A = variable1
AND B between 'variable2' and 'variable3'
AND C in ('variable4', 'variable5', 'variable6')

will result in the index BTREE_ONE being utilised in full (correct?).

2. What will happen in the following scenarios?

SELECT A, B, C, D, E
FROM TABLE
WHERE A = variable1
AND B between 'variable2' and 'variable3'

Will the index BTREE_ONE still be used, or will a table scan occur?

3.

SELECT A, B, C, D, E
FROM TABLE
WHERE A = variable1
AND B between 'variable2' and 'variable3'
AND C in ('variable4', 'variable5', 'variable6')
AND D = 'varable7'

Will the index be used? or will a partial scan of BTREE_TWO occur?


4. How does redbrick prefer tables to be queried? Does redbrick, like oracle
prefer the larger tables queried before the smaller tables? I ask this to
inform the developers about querying fact tables and their dimensions.

5. In an Explain, the prelimanary plan is always executed, but how do I know
which of the (for instance) 3 choices were executed? Is this stored
somewhere?

6. When it comes to developing indexes on and between tables, is there a
process you guys use to build the best possible index on or between tables?
A checklist that ultimatley determines the best option for indexing on a
table.

7. Does the values in the SELECT affect the index at all, or is the use of
indexes only dependant on the WHERE clause?


Thanks!

Denzil

Redbrick List Forum is maintained by Administrator with WebBBS 5.12.