Re: Indexing Help

cxy2002um_at_yahoo.com (Anon) wrote

> I have a table with 2 million rows.

FWIW, a small table.

> I am doing the following select statement against this table.
>
> SELECT *
> FROM TableA
> WHERE A_ID = '1235586'
> AND DATE_COL1 BETWEEN to_date('01-01-2004','DD-MM-YYYY')
> AND to_date('02-01-2004','DD-MM-YYYY')
> AND ( A_TYPE IN (3, 4, 5)
> OR (A_TYPE = 16 AND A_STATUS = '0')
> )
> AND B_TYPE IN (1, 2)
> AND B_STATUS IN (1, 2)
> AND SUBSTR (C_ID, 1, 2) NOT IN ('XI', 'XO', 'XD')
And..? What are the indexes on this table? Where's the explain plan for this SQL?

Some random thoughts on the above...

A_ID seems to be a good candidate for an index - but you say that it is not a true id column? Please explain.

Columns with low cardinality can be bitmap indexed. Tables can be partitioned.

Predicate substrings on columns show IMO a poor design. Read Codd's normalisation rules.

The best way to address performance is with a good design. Good designs are by nature scalable.

What is the growth of this table? What will you do when this table hits the 10 million row mark (which is still fairly small given today's data volumes).

--
Billy