operators used by MAXDB

[Former Member]

Hello there,

I would like to know which operator is used by the search strategy given by the EXPLAIN JOIN statement. I am looking to find the type of join used by MAX DB in selecting the best search strategy.

Waiting for reply.

[lbreddemann · Accepted solution]

Hi Priyank,

I really don't understand what your question is.

Please be more precise here.

MaxDB has different join implementations at hand which will be used depending on the parameter setup, the table statistics and of course the query you run.

If you want to compare it with Oracle, then there is a "SORT JOIN", a "NESTED LOOP" and a "HASH JOIN" available.

In general the nested loop join will be used as the two other join methods require a intermediate result to be materialized.

> Waiting for reply.

well, next time I read such a thing, I leave the author in that state...

regards,

Lars

[Former Member · Accepted solution]

Hello,

My question is that how can we find out which operators have been used to carry out the join between two or multiple tables. That is how can we find out the type of logical operators (viz., inner join, outer join , aggregate operators for more than one tables and operators like sort,table scan for single tables etc). Is there any table or view available which can provide this information?

Or is it that the join implementations suggested by you mean the same as mentioned above.?

[lbreddemann · Accepted solution]

Ok, this is going to get a bit complicated...

> My question is that how can we find out which operators have been used to carry out the join between two or multiple tables.

There is always just a join between two tables. Never less, never more.

If there are more tables touched in a statement we join two of them and move on to join the result of the fist join-operation with the next table.

Obviously, figuring out, which order of join operations would be the fastest is the problem here.

That's what the optimizer needs the statistics for.

That is how can we find out the type of logical operators (viz., inner join, outer join , aggregate operators for more than one tables and operators like sort,table scan for single tables etc). Is there any table or view available which can provide this information?

>

> Or is it that the join implementations suggested by you mean the same as mentioned above.?

No, really not.

As I understand you now, you want to know what relational operators have been processed with any step of the execution plan.

I guess you want to see something similar to the extended explain plan of oracle?

Well actually most of this information is already visible in the normal execution plan (not explain join) output.

explain
SELECT
	 CUSTOMER.CNO,
	 CUSTOMER.TITLE,
	 CUSTOMER.NAME,
	 CUSTOMER.ZIP,
	 CITY.NAME,
	 CITY.STATE,
	 CUSTOMER.ADDRESS 
FROM HOTEL.CUSTOMER,
	 HOTEL.CITY 
WHERE CUSTOMER.ZIP = CITY.ZIP
AND   CUSTOMER.NAME like 'A%'

SCHEMANAME  TABLENAME  COLUMN_OR_INDEX  STRATEGY                                PAGECOUNT
HOTEL       CUSTOMER   FULL_NAME_INDEX  RANGE CONDITION FOR INDEX                        1
                       NAME                  (USED INDEX COLUMN)                
HOTEL       CITY       ZIP              JOIN VIA KEY COLUMN                              1
                                             NO TEMPORARY RESULTS CREATED       
                                             RESULT IS COPIED   , COSTVALUE IS           4

We see that the predicate 'CUSTOMER.NAME like 'A%'' is evaluated via the index range scan index FULL_NAME_INDEX first.

With the rows we found we perform the join to the CITY table by using the key column ZIP.

Is this what you wanted to see?

regards,

Lars

[Former Member · Accepted solution]

Hello there,

I have gone through the Explain Statement but still the information provided is very less.

I require the information similar to that given by mssql on executing the query after "set showplan_all on" command.

Is there any way MAXDB can provide similar information?

[lbreddemann · Accepted solution]

> I have gone through the Explain Statement but still the information provided is very less.

Less than what?

How about you come up with an example that we can discuss?

> I require the information similar to that given by mssql on executing the query after "set showplan_all on" command.

>

> Is there any way MAXDB can provide similar information?

Well, I'm not a SQL Server expert.

Talk Oracle - fine, talk MaxDB - great, but MSSQL? no idea.

What information do you get there?

Show us an example.

regards,

Lars

[Former Member · Accepted solution]

OK.

In Oracle we have plan_table. it has one of the column operation which shows what operation would be done on (or with) object_name.

I am not able to find the similar information regarding operation in MAX DB.

[lbreddemann · Accepted solution]

Let's see...

Oracle

explain plan for select * from usl where id =10;

Explained.

xxxx@TDB > r
  1* select * from table(dbms_xplan.display)

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 2619539866

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    30 |     1 (100)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| USL   |     1 |    30 |     0   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | I_USL |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------

   2 - access("ID"=10)

14 rows selected.

MaxDB:

explain select * from usl where id =10
SCHEMANAME  TABLENAME  COLUMN_OR_INDEX  STRATEGY                                PAGECOUNT
LARS        USL                         EQUAL CONDITION FOR KEY                          1
                       ID                    (USED KEY COLUMN)                  
                                           RESULT IS NOT COPIED , COSTVALUE IS           1
                                        QUERYREWRITE - APPLIED RULES:           
                                           DistinctPullUp                                1
 

In both explains we see:

a) what db objects are touched (columns 'NAME' and 'TABLENAME'/'COLUMN_OR_INDEX')

b) which data access technique/strategy/option was used (columns 'OPERATION'/'STRATEGY')

c) estimates on the workload ('PAGECOUNT' and 'COST'/'TIME')

So what is it exactly you don't find?

Bring up an example - maybe then it's easier to understand what your problem is.

regards,

Lars

[Former Member · Accepted solution]

Hello there,

first of all thanx for your replies.

the confusion between operation/strategy is now clear.

just one last question. how do we find parent_id(as in oracle plan table) in MAX DB?

[lbreddemann · Accepted solution]

> just one last question. how do we find parent_id(as in oracle plan table) in MAX DB?

You don't.

Actualy MaxDB just provides a kind of linear execution plan display with the current versions.

So even for results of joins where logically the access of both tables would have to be on the same line, you'll find them on below the other - more reflecting the actual join order.

An enhanced explain plan is planned now for a long time - but not yet completely implemented or released.

So basically as for now, you've to switch your explain plan reading habits from Oracle-style (from inner to outer operation) to MaxDB-style (from top to bottom) to understand what's going on.

regards,

Lars

[Former Member · Accepted solution]

Thanx a ton Lars. You been a great help.

Do continue the good work.

Priyank.