RedBrick: statistics and query plan

RedBrick: statistics and query plan

Post by Ettore Saltarell » Wed, 05 Sep 2001 01:13:59



I'd like to know how and which statistics RedBrick uses to decide the
execution plan.

In the "Administrator's Guide", in chapter "Tuning a Warehouse for
Performace", a flow chart diagram is shown.
There, the join algorithm selection problem is described.

Well, I'm working with a star schema.
When I submit a query with one Fact Table and more than one Dimension Table
without any selection clause, I can't understand, for example, how the
optimizer selects the order of the DTs for the join with the FT.
I'm afraid that simply it reflects the order in which I put them in the FROM
clause.

Thank you.

 
 
 

RedBrick: statistics and query plan

Post by Dirk Moolma » Wed, 05 Sep 2001 15:27:19


I haven't been working with Redbrick for long and can't give you too much
advice, but don't forget your foreign key relationships, from the fact table
to the dimension tables.

Regards
Dirk

-----Original Message-----


Sent: Monday, September 03, 2001 6:14 PM

Subject: RedBrick: statistics and query plan

I'd like to know how and which statistics RedBrick uses to decide the
execution plan.

In the "Administrator's Guide", in chapter "Tuning a Warehouse for
Performace", a flow chart diagram is shown.
There, the join algorithm selection problem is described.

Well, I'm working with a star schema.
When I submit a query with one Fact Table and more than one Dimension Table
without any selection clause, I can't understand, for example, how the
optimizer selects the order of the DTs for the join with the FT.
I'm afraid that simply it reflects the order in which I put them in the FROM
clause.

Thank you.

==========================================================
This message contains information intended for the perusal, and/or use (if
so stated), by the stated addressee(s) only. The information is
confidential and privileged. If you are not an intended recipient, do not
peruse, use, disseminate, distribute, copy or in any manner rely upon the
information contained in this message (directly or indirectly). The sender
and/or the entity represented by the sender shall not be held accountable
in the event that this prohibition is disregarded. If you receive this
message in error, notify the sender immediately by e-mail, fax or telephone
representations contained in this message, whether express or implied, are
those of the sender only, unless that sender expressly states them to be
the views or representations of an entity or person, who shall be named by
the sender and who the sender shall state to represent. No liability shall
otherwise attach to any other entity or person.
==========================================================


 
 
 

RedBrick: statistics and query plan

Post by Al Shark » Wed, 05 Sep 2001 23:23:20



> I'd like to know how and which statistics RedBrick uses to decide the
> execution plan.

> In the "Administrator's Guide", in chapter "Tuning a Warehouse for
> Performace", a flow chart diagram is shown.
> There, the join algorithm selection problem is described.

> Well, I'm working with a star schema.
> When I submit a query with one Fact Table and more than one Dimension
> Table without any selection clause, I can't understand, for example,
> how the optimizer selects the order of the DTs for the join with the
> FT.  I'm afraid that simply it reflects the order in which I put them
> in the FROM clause.

What do you mean "without any selection clause"?  Do you mean "without
a 'where' clause"?  I wouldn't use "natural" joins, as I wouldn't
trust them to behave the same way from one major revision to the next.
RedBrick already changed that behavior once between either 2.0 -> 3.0
or 3.0 -> 4.0 (I can't remember exactly).

In any case, it shouldn't matter.  If you have a star index it will use
that, and it won't matter what order you specify the tables.  The
way to get performance gains via the database is to create alternate
star indexes, or to create separate target indexes on the individual
key columns of the fact table so that the query compiler can (maybe)
choose the better option.  Even then, you can't override its choices.
Yet.  You can also segment the first key _dimension_ table so that
the "smart query" will limit which index segments to search through
during a data retrieval.  (assuming that your fact indexes are
segmented).

Of course, throwing hardware at it wouldn't hurt either, but that's a
whole separate discussion.

 
 
 

1. query plan statistics

Hi All,

I'm wondering if anyone can suggest a way to return a T-SQL result set of
statistics of a query plan for a query without actually running the query
itself. Again, I would like to be able to access it in a result set so the
Query Analyzer's 'Show Execution Plan' is not really what I'm looking for...

Also, can SQL provide realtime performance statistics in a resultset?

Rein Petersen

2. Some problem due to the length limit of SQL sentence

3. Computing statistics and query plans

4. Recent ODBC update with IIS4

5. Fw: Redbrick Memory Management & Statistics

6. Error (3420) when closing a database

7. Redbrick Memory Management & Statistics

8. more than 256 tables in one guery

9. Why is Stored Proc plan slower than query plan

10. Execution plan,Query plan?

11. Locking Plan part of Query Plan?

12. Querying a Linked Redbrick Database

13. Different query approaches in Redbrick