Archives of the TeradataForum

Message Posted: Mon, 30 Jun 2003 @ 17:24:36 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

If for example, we have a table with the following DDL

  CREATE TABLE TEST_TABLE
  (
      A INTEGER
    , B CHAR(10)
    , C DATE
    , D CHAR(3)
  ) UNIQUE PRIMARY INDEX (A);

Then we place a NUSI on C and one on D, which both have a low cardinality.

If we then have a piece of SQL such as

SELECT
  A
FROM TEST_TABLE
WHERE C = DATE '2003-06-30'
  AND D = 'GB';

The explain plan with should a BMSMS statement, basically bitmapping the two NUSI indexes together. This runs extremely fast, as we would expect.

Then for example we change the SQL to include a join to a second table.

SELECT
  A
FROM TEST_TABLE T1
   , CALENDAR T2
WHERE T1.C = T2.DTE_DT
  AND T1.D = 'GB'
  AND T2.C = DATE '2003-06-30'

The explain shows the CALENDAR table joining to the NUSI on column C of the TEST_TABLE, retrieving the row hash values. Then performing a retrieve based on the row hash values, with the filter of T1.D = 'GB' applied.

If the first step has retrieved all the row hash values from the NUSI on column C, by joining to the CALENDAR table, is it not possible for the RDBMS to then perform the same BMSMS operation it did when we had a filter specified on the base table directly, as in the first SQL.

Is it not the case that a BMSMS is performing a bitmapping of the row hash values, and the first step joining the NUSI to the CALENDAR table is just retrieving row hash values, placing the RDBMS into a state it could then bitmap the values against another NUSI.

It just seems to me that we are missing a possible optimisation here that could, and would in our case, improve the overall performance of the query being executed.

I would love to know if this is possible, and if so, could it be added to the list of enhancements for future releases of Teradata.

--
Regards

Matthew Winter

------------------------------------------------------------ Advanced Teradata Certified Professional
Teradata Certified Application Developer

www.teratools.com

	<Prev	Next>		<<First	<Prev	Next>	Last>>