Query rewite Parameter

[Former Member]

Hi there,

the optimization related parameter : optimize_queryrewrite in version 7.6 and enable_queryrewrite in version 7.7 has different options.

I am not able to figure out from explain commands what MAXDB is doing with optimize_queryrewrite= statement in v7.6

How can I know that the query been actually modified on statement level and what is the altered query that is being used?

Is this statement level query rewrite removed from v7.7?

[Former Member]

Hi,

the crashing kernel can be reproduced locally with some small select.

We will check the implementation.

Sorry for any inconverniences. But it seems to be better not to use explain queryrewrite in this version.

Elke

[Former Member]

Hi,

in the explain plan you will find the information Applied Queryrewrite Rules and the name of the rule which is used. This is the information that Queryrewrite is used for this SQL command.

All rules are listed in the systemtable queryrewriterules. Here you have the possibility to activate/deactivate special rules. It is not recommended to activate special rules when they are deactivated by default.

To get the information about the by queryrewrite altered command you execute:

EXPLAIN QUERYREWRITE .....

Regards, Christiane

[Former Member]

Hello there,

ok got the details regarding the rules form the system table but

I am getting error -2000 : row too long while executing following command:

EXPLAIN QUERYREWRITE query

[lbreddemann]

Hi there,

please post the complete statement and the full version of database you're using.

regards,

Lars

[Former Member]

Hi there,

MAXDB version: 7.6.06.03

I tried the following two queries:

EXPLAIN QUERYREWRITE 
select
	s_acctbal,
	s_name,
	n_name,
	p_partkey,
	p_mfgr,
	s_address,
	s_phone,
	s_comment
from
	part,
	supplier,
	partsupp,
	nation,
	region
where
	p_partkey = ps_partkey
	and s_suppkey = ps_suppkey
	and p_retailprice :varies
	and s_nationkey = n_nationkey
	and n_regionkey = r_regionkey
	and r_name = 'EUROPE'
	and ps_supplycost <= (
		select
			min(ps_supplycost)
		from
			partsupp,
			supplier,
			nation,
			region
		where
			p_partkey = ps_partkey
			and s_suppkey = ps_suppkey
			and s_nationkey = n_nationkey
			and n_regionkey = r_regionkey
			and r_name = 'EUROPE'
			and ps_supplycost :varies
	)
order by
	s_acctbal desc,
	n_name,
	s_name,
	p_partkey

Here I am getting the error 200 row too long and for simple query like

EXPLAIN QUERYREWRITE 
select * from dbo.region

The db server is getting disconnected and i get error 816 restart required.

[lbreddemann]

Can you execute the query without error?

What are the settings for the following db parameters?

CHECK_TABLE_WIDTH

MBLOCK*_SIZE

regards,

Lars

[Former Member]

@ Lars

CHECK_TABLE_WIDTH = NO

MBLOCK_DATA_SIZE = 32768

MBLOCK_QUAL_SIZE = 32768

MBLOCK_STACK_SIZE = 32768

MBLOCK_STRAT_SIZE = 16384

@Elke

Ok would not use queryrewrite for this db version. But I need to know what is the altered query for the nested queries and nested join queries. It would be of great help if you can illustrate by an example what queryrewrite does if it is set to statement / operator value in above mentioned cases.

If there is any helpful documentation please do provide the appropriate link.

Thanks.

Priyank

[lbreddemann]

Hi Priyank,

the query rewrite functionality that is available via the parameters/hints is a flexible framework for query block analysis and rewriting.

Currently there is no specific documentation for that available externally, so you'd have to resort to the standard literature for that.

The basic idea here is that you can divide any query up into so-called query blocks that have certain features.

These queryblocks given, you can apply some heuristics to them that should result in a quicker execution.

For example you have a query where you access a view.

Now you now that you usually can apply the conditions from your query already to the query of the view (PredicatePushDown).

This is likely to make the query execution quicker since you don't have to build up the whole view result and filter it out afterwards again.

There are a bunch of heuristics available and implemented that also may be applied several times in a query.

I hope this is enough information to begin with.

BTW: Oracle, DB2, MSS and also SAP DB/MaxDB do query transformation silently all the time.

Even when you disable the query transformation in MaxDB, the "legacy" query transformation of MaxDB still does its work.

regards,

Lars