Archives of the TeradataForum
Message Posted: Mon, 30 Jun 2003 @ 17:24:36 GMT
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.
Advanced Teradata Certified Professional
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|