List:General Discussion« Previous MessageNext Message »
From:Lucio Chiappetti Date:September 5 2007 8:29am
Subject:Re: excessive time spent in "statistics" status
View as plain text  
On Tue, 4 Sep 2007, Shawn Green wrote:
> > On Tue, 4 Sep 2007, Lucio Chiappetti wrote:

> > For each I tested 3 cases (total 16*3=48) :
> > 
> > a) the query on the "virtual" table correlated with the external
> > (the virtual is my G left join t1 ... left join tn). [...]
> > represents my REFERENCE, 

    this case will also exploit full (or default) optimization

> > b) the query on the VIEW V with "members also" (which implies a 
> > redundant join of V with G left join t1 ... left join tn) and 
> > correlated with an external table. [...] I used here SELECT 
> > STRAIGHT_JOIN.

> > c) the same query of (b) but with a normal select, and preceded by
> >    setting optimizer_search_depth=0 (auto).

> In your b) test, did you use the SELECT STRAIGHT_JOIN as your outer 
> SELECT statement or within the CREATE VIEW statement? 

No. I left the  CREATE VIEW alone (standard LEFT JOINs) in all cases, 
since that will be fully optimized, and used by a majority of users.
SELECT STRAIGHT_JOIN was used only in (b) ["no optimization"] and not in 
(c) ["residual  optimization"].

Our inclination would now be to use SELECT STRAIGHT_JOIN because it's the 
one involving only "elegant" changes to the code. Consider again that the 
case "view + member also + other table" will be rare (very few users) 
compare to "view alone".

Anyhow all this was rather instructive.

Now I'll start experimenting with UNIONs too ...

-- 
-----------------------------------------------------------------------
Lucio Chiappetti - INAF/IASF - via Bassini 15 - I-20133 Milano (Italy)
For more info : http://www.iasf-milano.inaf.it/~lucio/personal.html
-----------------------------------------------------------------------
()  ascii ribbon campaign - against html mail 
/\                          http://arc.pasp.de/
Thread
excessive time spent in "statistics" statusLucio Chiappetti31 Aug
  • Re: excessive time spent in "statistics" statusShawn Green1 Sep
    • Re: excessive time spent in "statistics" statusLucio Chiappetti3 Sep
      • Re: excessive time spent in "statistics" statusShawn Green3 Sep
        • Re: excessive time spent in "statistics" statusLucio Chiappetti4 Sep
          • Re: excessive time spent in "statistics" statusLucio Chiappetti4 Sep
            • Re: excessive time spent in "statistics" statusShawn Green4 Sep
              • Re: excessive time spent in "statistics" statusLucio Chiappetti5 Sep
        • Re: excessive time spent in "statistics" statusJeremy Cole14 Sep
          • Re: excessive time spent in "statistics" statusLucio Chiappetti14 Sep
          • Re: excessive time spent in "statistics" statusPete Harlan17 Sep
            • Re: excessive time spent in "statistics" statusBaron Schwartz17 Sep