Archives of the TeradataForum
Message Posted: Tue, 08 Jan 2002 @ 21:04:20 GMT
| Subj: | | Re: Optimizer clues |
| |
| From: | | Anomy Anom |
<-- Anonymously Posted: Tuesday, January 08, 2002 15:56 -->
I agree with Richard that the optimiser does not find always the best plan. This is especially true in the BI environment and for ad hoc
SQL - for two different reasons (personal believe).
In the BI tool environment the SQL is usually rule driven generated. The rules are usually not based on the complete functionality of
Teradata. The number of joined tables is growing constantly. The result can be a huge strait forward simple functionality SQL with a bad
performance. Sometime a rewriting can solve to problem but this is not a acceptable solution as the SQL should be run within the BI
tool.
In the ad hoc environment the growing SQL functionality combined with lacks in the data model or missing statistics causes problems and
sometimes (even if seldom) the optimiser simply does not find the best solution for other reasons. Usually this kind of problems can be
fixed with dirty solutions like splitting the SQL in different steps, adding dummy where-conditions or rewriting the SQL. In this cases the
space requirements might be huge and not every user has the ability to store big intermediate results. Sometimes only additional statistics
(which is an request of NCR but can only be done by an DBA) or additional indices are required (which are needing space and DBAs). In this
cases you are loosing the lot of the 'ad hoc'. I spent hours by understanding very large explains to find out why it does not work or what
causes the problem. Sometimes is also very hard for NCR to figure out what the problem for a specific query is and the response time can be
long as SQL problems are usually difficult and not critical in the meaning of system critical.
But I am not sure to agree with Richards conclusion to add hint functionality into the optimiser. Personally I still believe that the
optimiser - especially in the BI-tool environment - should be improved. NCR should work harder together with the vendors to develop the
optimiser and the SQL generators. I still believe the NCR optimiser is the best in place but it still needs improvements. In a long term
there might be the risk that more and more time is needed to understand what Teradata does as it is getting more and more complex and I see
no best solution for this scenario.
| 
