Re: Indexing Help

vslabs_at_onwe.co.za (Billy Verreynne) wrote in message news:<1a75df45.0312182213.7494ca04_at_posting.google.com>...
> 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).

There are no index on this table, however this table is partitioned into A_TYPE in (3,4,5,16) all other A_TYPES go into another partition.  This was thought to help query performance.

A_ID is not a unique per record, it is a group of records that will have the same A_ID.

There is a true ID column where the ID is unique per record, however I don't have a need to use that field in this query.

Thanks for the comments on the poor design. I will read. The source of this table is not of my design (thank god). This table will be truncated every night and rebuilt with the same about of roles each time. That query mentioned about is fetching records for batch processing.

Thanks. Received on Mon Dec 22 2003 - 11:10:33 CST