 |
IDS Forum
Re: Fw: Query troubles
Posted By: Art Kagel
Date: Tuesday, 13 March 2012, at 1:09 p.m.
In Response To: Re: Fw: Query troubles (Peter_Logan@spartanstores.com)
Odd indeed. I'd need significant hands-on there to figure this one out,
and the solution may be a rewrite of the query.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Tue, Mar 13, 2012 at 12:56 PM, Peter_Logan@spartanstores.com <
Peter_Logan@spartanstores.com> wrote:
> The really strange thing is that once I add any sort of distributions on
> this table, the query goes out to lunch. The week that generally runs in
> 2 or 3 mins goes to about 37 mins....
>
> Peter Logan
> Senior Database Administrator
> Phone: 616/878-8309
>
> From: "Art Kagel" <art.kagel@gmail.com>
> To: ids@iiug.org
> Date: 03/13/2012 11:03 AM
> Subject: Re: Fw: Query troubles [26510]
> Sent by: ids-bounces@iiug.org
>
> OK, I've taken the time to at least scan the SET EXPLAIN output below.
> Most of the row estimates for over half of the tables in the query are WAY
>
> OFF including the efin_wk_line_cls table. The estimates for both versions
> of the query guess that the engine will return 66.6million rows when
> actually only about 755thousand rows are ultimately returned from that
> table. The estimates on the joins are similarly off which affects the
> order of processing the tables. I'm back to saying it sounds like the
> stats are stale or insufficiently detailed (MEDIUM sampling is too small
> or
> HIGH should be used instead or the resolution is too low - ie not enough
> bins).
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other organization with which I am associated either explicitly,
> implicitly, or by inference. Neither do those opinions reflect those of
> other individuals affiliated with any entity with which I am affiliated
> nor
> those of the entities themselves.
>
> On Tue, Mar 13, 2012 at 10:50 AM, Art Kagel <art.kagel@gmail.com> wrote:
>
> > What level of stats (MEDIUM, HIGH) do you have on the fragmentation
> column
> > (week#?) and at what resolution/confidence/sampling? How many total rows
>
> > in the table? According to sysdistrib when were stats last calculated on
>
> > the table?
> >
> > I would initially suggest forcing stats updates, but if last year
> behaves
> > the same way for the weeks in the fragment after the first that would
> point
> > to something other than the engine ignoring the update stats commands.
> Is
> > it just the fragment with weeks 37-40 each year or the 2nd-4th weeks in
> > many fragments? I'm assuming fiscal years and retail weeks here. What
> > calendar dates do weeks 37-4 correspond to this year? Could there be a
> > natural data skew in one of the four weeks in this fragment that is
> > confusing the optimizer?
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
>
> > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
>
> > other organization with which I am associated either explicitly,
> > implicitly, or by inference. Neither do those opinions reflect those of
> > other individuals affiliated with any entity with which I am affiliated
> nor
> > those of the entities themselves.
> >
> >
> >
> >
> > On Tue, Mar 13, 2012 at 10:37 AM, Peter_Logan@spartanstores.com <
> > Peter_Logan@spartanstores.com> wrote:
> >
> >> IDS 11.70.FC3XC
> >> Aix 6.1
> >>
> >> Before I contact IBM to see if I'm running into some defect...thought
> I'd
> >> send this to the list. Couple of additional items, efin_wk_line_cls is
> a
> >> fragmented table. It has 40 fragments. Each fragment contains 4 weeks
> of
> >> data. Statistics haven't been updated with the new fragment level
> >> stuff... Weeks 37 - 40 are in the same fragment, and week 37 works as
> >> expected, and everything after week 37 is slow. If we do a different
> >> year, IE. last year the same behavior occurs...I am totally at a loss
> with
> >> this...
> >>
> >> Thanks ...
> >>
> >> Peter Logan
> >> Senior Database Administrator
> >> Phone: 616/878-8309
> >> ----- Forwarded by Peter Logan/Corporate/Spartan on 03/13/2012 10:32 AM
>
> >> -----
> >>
> >> From: Bruce Farwell/OTI/Spartan
> >> To: Peter Logan/Corporate/Spartan@SpartanStore
> >> Cc: Steve Baar/Corporate/Spartan@SpartanStore
> >> Date: 03/12/2012 11:02 AM
> >> Subject: Query troubles
> >>
> >> Peter, I have a SQL query that is causing me problems in EIS. The
> >> calculation is for year to date sales, which is done with a
> transformation
> >> table. I give a week_id to the query and it uses the transformation
> table
> >> (week_to_year_lkp) to determine sales for all the weeks from the start
> of
> >> the year.
> >>
> >> The problem is that from weeks 1 to 37 the query runs in about 4
> minutes,
> >> but from week 38 on the query plan changes and it takes approx 20
> minutes.
> >> This is slowing down report execution for the users.
> >>
> >> Can you take a look and see what can be done to avoid this problem?
> >>
> >> -Bruce
> >>
> >> The two query explains are below:
> >>
> >> Query 1 is week 37 and it run in about 4 minutes:
> >>
> >> QUERY: (OPTIMIZATION TIMESTAMP: 03-12-2012 09:47:32)
> >> ------
> >> select a18.mdse_grp_key mdse_grp_key,
> >>
> >> a12.fiscal_week_id fiscal_week_id,
> >>
> >> a13.catgy_manager_key catgy_manager_key,
> >>
> >> (sum(a11.total_sales_amt) * 0.001) WJXBFS1,
> >>
> >> (sum(a11.ext_profit_amt) * 0.001) WJXBFS2,
> >>
> >> sum(a11.total_sales_amt) WJXBFS3,
> >>
> >> sum(a11.ext_profit_amt) WJXBFS4
> >> from efin_wk_line_cls a11,
> >>
> >> week_to_year_lkp a12,
> >>
> >> mdse_class_manager a13,
> >>
> >> line a14,
> >>
> >> chain a15,
> >>
> >> channel a16,
> >>
> >> mdse_class a17,
> >>
> >> mdse_category a18,
> >>
> >> mdse_group a19,
> >>
> >> department a110,
> >>
> >> business_dept a111
> >> where a11.fiscal_week_id = a12.fiscal_wty_id and
> >>
> >> a11.mdse_class_key = a13.mdse_class_key and
> >>
> >> a11.sales_line_id = a14.sales_line_id and
> >>
> >> a14.sales_chain_id = a15.sales_chain_id and
> >>
> >> a15.sales_channel_id = a16.sales_channel_id and
> >>
> >> a11.mdse_class_key = a17.mdse_class_key and
> >>
> >> a17.mdse_catgy_key = a18.mdse_catgy_key and
> >>
> >> a18.mdse_grp_key = a19.mdse_grp_key and
> >>
> >> a19.dept_key = a110.dept_key and
> >>
> >> a110.bus_dept_key = a111.bus_dept_key
> >>
> >> and (a16.enterprise_id in (18)
> >>
> >> and a13.catgy_manager_key in (70)
> >>
> >> and a110.dept_grp_key not in (525, 9)
> >>
> >> and a14.format_type_id in ('SUPERMKT ')
> >>
> >> and a12.fiscal_week_id in (201237)
> >>
> >> and a111.dept_grp_type_key in (1))
> >> group by a18.mdse_grp_key,
> >>
> >> a12.fiscal_week_id,
> >>
> >> a13.catgy_manager_key
> >> into temp ZZT6JQNPTNRMD003 with no log
> >>
> >> Estimated Cost: 220818
> >> Estimated # of Rows Returned: 1499
> >> Maximum Threads: 25
> >> Temporary Files Required For: Group By
> >>
> >> 1) whmgr.a16: INDEX PATH
> >>
> >> (1) Index Name: whmgr.channel_i2
> >>
> >> Index Keys: enterprise_id (Parallel, fragments: ALL)
> >>
> >> Lower Index Filter: whmgr.a16.enterprise_id = 18
> >>
> >> 2) whmgr.a15: INDEX PATH
> >>
> >> (1) Index Name: whmgr.chain_i2
> >>
> >> Index Keys: sales_channel_id (Parallel, fragments: ALL)
> >>
> >> Lower Index Filter: whmgr.a15.sales_channel_id =
> >> whmgr.a16.sales_channel_id
> >> NESTED LOOP JOIN
> >>
> >> 3) whmgr.a14: INDEX PATH
> >>
> >> Filters: whmgr.a14.format_type_id = 'SUPERMKT '
> >>
> >> (1) Index Name: whmgr.line_i2
> >>
> >> Index Keys: sales_chain_id (Parallel, fragments: ALL)
> >>
> >> Lower Index Filter: whmgr.a14.sales_chain_id =
> >> whmgr.a15.sales_chain_id
> >> NESTED LOOP JOIN
> >>
> >> 4) whmgr.a111: SEQUENTIAL SCAN
> >>
> >> Filters: whmgr.a111.dept_grp_type_key = 1
> >> NESTED LOOP JOIN
> >>
> >> 5) whmgr.a12: SEQUENTIAL SCAN
> >>
> >> Filters: whmgr.a12.fiscal_week_id = 201237
> >> NESTED LOOP JOIN
> >>
> >> 6) whmgr.a110: SEQUENTIAL SCAN
> >>
> >> Filters:
> >>
> >> Table Scan Filters: whmgr.a110.dept_grp_key NOT IN (525 , 9 )
> >>
> >> DYNAMIC HASH JOIN
> >>
> >> Dynamic Hash Filters: whmgr.a110.bus_dept_key =
> >> whmgr.a111.bus_dept_key
> >>
> >> 7) whmgr.a19: SEQUENTIAL SCAN
> >>
> >> DYNAMIC HASH JOIN
> >>
> >> Dynamic Hash Filters: whmgr.a19.dept_key = whmgr.a110.dept_key
> >>
> >> 8) whmgr.a18: SEQUENTIAL SCAN
> >>
> >> DYNAMIC HASH JOIN
> >>
> >> Dynamic Hash Filters: whmgr.a18.mdse_grp_key = whmgr.a19.mdse_grp_key
> >>
> >> 9) whmgr.a17: SEQUENTIAL SCAN
> >>
> >> DYNAMIC HASH JOIN
> >>
> >> Dynamic Hash Filters: whmgr.a17.mdse_catgy_key =
> >> whmgr.a18.mdse_catgy_key
> >>
> >> 10) whmgr.a13: INDEX PATH
> >>
> >> (1) Index Name: whmgr.mdse_class_mgr_i1
> >>
> >> Index Keys: catgy_manager_key (Parallel, fragments: ALL)
> >>
> >> Lower Index Filter: whmgr.a13.catgy_manager_key = 70
> >>
> >> DYNAMIC HASH JOIN
> >>
> >> Dynamic Hash Filters: whmgr.a13.mdse_class_key =
> >> whmgr.a17.mdse_class_key
> >>
> >> 11) whmgr.a11: INDEX PATH
> >>
> >> (1) Index Name: whmgr.efin_wk_ln_cls_i
> >>
> >> Index Keys: fiscal_week_id sales_line_id mdse_class_key
> >> discount_type_cd (Parallel, fragments: ALL)
> >>
> >> Lower Index Filter: ((whmgr.a11.mdse_class_key =
> >> whmgr.a13.mdse_class_key AND whmgr.a11.fiscal_week_id =
> >> whmgr.a12.fiscal_wty_id ) AND whmgr.a11.sales_line_id =
> >> whmgr.a14.sales_line_id )
> >> NESTED LOOP JOIN
> >>
> >> Query statistics:
> >> -----------------
> >>
> >> Table map :
> >> ----------------------------
> >> Internal name Table name
> >> ----------------------------
> >> t1 a16
> >> t2 a15
> >> t3 a14
> >> t4 a111
> >> t5 a12
> >> t6 a110
> >> t7 a19
> >> t8 a18
> >> t9 a17
> >> t10 a13
> >> t11 a11
> >> t12 zzt6jqnptnrmd003
> >>
> >> type table rows_prod est_rows rows_scan time est_cost
> >> -------------------------------------------------------------------
> >> scan t1 7 7 7 00:00.00 2
> >>
> >> type table rows_prod est_rows rows_scan time est_cost
> >> -------------------------------------------------------------------
> >> scan t2 9 25 9 00:00.00 0
> >>
> >> type rows_prod est_rows time est_cost
> >> -------------------------------------------------
> >> nljoin 9 6 00:00.01 5
> >>
> >> type table rows_prod est_rows rows_scan time est_cost
> >> -------------------------------------------------------------------
> >> scan t3 137 23 173 00:00.00 1
> >>
> >> type rows_prod est_rows time est_cost
> >> -------------------------------------------------
> >> nljoin 137 6 00:00.02 15
> >>
> >> type table rows_prod est_rows rows_scan time est_cost
> >> -------------------------------------------------------------------
> >> scan t4 1507 11 3014 00:00.00 3
> >>
> >> type rows_prod est_rows time est_cost
> >> -------------------------------------------------
> >> nljoin 1507 63 00:00.01 30
> >>
> >> type table rows_prod est_rows rows_scan time est_cost
> >> -------------------------------------------------------------------
> >> scan t5 55759 28 18849556 00:04.88 426
> >>
> >> type rows_prod est_rows time est_cost
> >> -------------------------------------------------
> >> nljoin 55759 1771 00:04.90 26987
> >>
> >> type table rows_prod est_rows rows_scan time est_cost
> >> -------------------------------------------------------------------
> >> scan t6 25 25 27 00:00.00 3
> >>
> >> type rows_prod est_rows rows_bld rows_prb novrflo time est_cost
> >>
> >>
> >>
>
> ------------------------------------------------------------------------------
>
> >> hjoin 70966 2013 25 55759 0 00:01.16
> >> 27319
> >>
> >> type table rows_prod est_rows rows_scan time est_cost
> >> -------------------------------------------------------------------
> >> scan t7 722 722 722 00:00.00 34
> >>
> >> type rows_prod est_rows rows_bld rows_prb novrflo time est_cost
> >>
> >>
> >>
>
> ------------------------------------------------------------------------------
>
> >> hjoin 2336809 53840 722 70966 0 00:01.92
> >> 27854
> >>
> >> type table rows_prod est_rows rows_scan time est_cost
> >> -------------------------------------------------------------------
> >> scan t8 3807 3807 3807 00:00.00 173
> >>
> >> type rows_prod est_rows rows_bld rows_prb novrflo time est_cost
> >>
> >>
> >>
>
> ------------------------------------------------------------------------------
>
> >> hjoin 13209814 283888 3807 2336809 0 00:10.94
> >> 38624
> >>
> >> type table rows_prod est_rows rows_scan time est_cost
> >> -------------------------------------------------------------------
> >> scan t9 8102 8102 8102 00:00.00 410
> >>
> >> type rows_prod est_rows rows_bld rows_prb novrflo time est_cost
> >>
> >>
> >>
>
> ------------------------------------------------------------------------------
>
> >> hjoin 24848238 604167 8102 13209814 0 00:37.15
> >> 92809
> >>
> >> type table rows_prod est_rows rows_scan time est_cost
> >> -------------------------------------------------------------------
> >> scan t10 270 270 270 00:00.01 13
> >>
> >> type rows_prod est_rows rows_bld rows_prb novrflo time est_cost
> >>
> >>
> >>
>
> ------------------------------------------------------------------------------
>
> >> hjoin 1358492 20092 270 24848238 0 01:11.38
> >> 204318
> >>
> >> type table rows_prod est_rows rows_scan time est_cost
> >> -------------------------------------------------------------------
> >> scan t11 755416 66768516 755416 06:00.20 1
> >>
> >> type rows_prod est_rows time est_cost
> >> -------------------------------------------------
> >> nljoin 755416 1347 08:06.32 218612
> >>
> >> type rows_prod est_rows rows_cons time est_cost
> >> ------------------------------------------------------------
> >> group 19 1499 755416 04:03.93 2207
> >>
> >> type rows_prod est_rows rows_cons time
> >> -------------------------------------------------
> >> group 19 1499 19 04:03.93
> >>
> >> type table rows_ins time
> >> -----------------------------------
> >> insert t12 19 16:15.75
> >>
> >> Query 2 is week 38 and it ran in approx 40 minutes:
> >>
> >> QUERY: (OPTIMIZATION TIMESTAMP: 03-12-2012 09:55:55)
> >> ------
> >> select a18.mdse_grp_key mdse_grp_key,
> >>
> >> a12.fiscal_week_id fiscal_week_id,
> >>
> >> a13.catgy_manager_key catgy_manager_key,
> >>
> >> (sum(a11.total_sales_amt) * 0.001) WJXBFS1,
> >>
> >> (sum(a11.ext_profit_amt) * 0.001) WJXBFS2,
> >>
> >> sum(a11.total_sales_amt) WJXBFS3,
> >>
> >> sum(a11.ext_profit_amt) WJXBFS4
> >> from efin_wk_line_cls a11,
> >>
> >> week_to_year_lkp a12,
> >>
> >> mdse_class_manager a13,
> >>
> >> line a14,
> >>
> >> chain a15,
> >>
> >> channel a16,
> >>
> >> mdse_class a17,
> >>
> >> mdse_category a18,
> >>
> >> mdse_group a19,
> >>
> >> department a110,
> >>
> >> business_dept a111
> >> where a11.fiscal_week_id = a12.fiscal_wty_id and
> >>
> >> a11.mdse_class_key = a13.mdse_class_key and
> >>
> >> a11.sales_line_id = a14.sales_line_id and
> >>
> >> a14.sales_chain_id = a15.sales_chain_id and
> >>
> >> a15.sales_channel_id = a16.sales_channel_id and
> >>
> >> a11.mdse_class_key = a17.mdse_class_key and
> >>
> >> a17.mdse_catgy_key = a18.mdse_catgy_key and
> >>
> >> a18.mdse_grp_key = a19.mdse_grp_key and
> >>
> >> a19.dept_key = a110.dept_key and
> >>
> >> a110.bus_dept_key = a111.bus_dept_key
> >>
> >> and (a16.enterprise_id in (18)
> >>
> >> and a13.catgy_manager_key in (70)
> >>
> >> and a110.dept_grp_key not in (525, 9)
> >>
> >> and a14.format_type_id in ('SUPERMKT ')
> >>
> >> and a12.fiscal_week_id in (201238)
> >>
> >> and a111.dept_grp_type_key in (1))
> >> group by a18.mdse_grp_key,
> >>
> >> a12.fiscal_week_id,
> >>
> >> a13.catgy_manager_key
> >> into temp ZZT6JQNPTNRMD003 with no log
> >>
> >> Estimated Cost: 297043
> >> Estimated # of Rows Returned: 925
> >> Maximum Threads: 26
> >> Temporary Files Required For: Group By
> >>
> >> 1) whmgr.a13: INDEX PATH
> >>
> >> (1) Index Name: whmgr.mdse_class_mgr_i1
> >>
> >> Index Keys: catgy_manager_key (Parallel, fragments: ALL)
> >>
> >> Lower Index Filter: whmgr.a13.catgy_manager_key = 70
> >>
> >> 2) whmgr.a17: INDEX PATH
> >>
> >> (1) Index Name: whmgr. 314_609
> >>
> >> Index Keys: mdse_class_key (Parallel, fragments: ALL)
> >>
> >> Lower Index Filter: whmgr.a13.mdse_class_key =
> >> whmgr.a17.mdse_class_key
> >> NESTED LOOP JOIN
> >>
> >> 3) whmgr.a18: INDEX PATH
> >>
> >> (1) Index Name: whmgr. 292_608
> >>
> >> Index Keys: mdse_catgy_key (Parallel, fragments: ALL)
> >>
> >> Lower Index Filter: whmgr.a17.mdse_catgy_key =
> >> whmgr.a18.mdse_catgy_key
> >> NESTED LOOP JOIN
> >>
> >> 4) whmgr.a19: INDEX PATH
> >>
> >> (1) Index Name: whmgr. 291_611
> >>
> >> Index Keys: mdse_grp_key (Parallel, fragments: ALL)
> >>
> >> Lower Index Filter: whmgr.a18.mdse_grp_key =
> >> whmgr.a19.mdse_grp_key
> >> NESTED LOOP JOIN
> >>
> >> 5) whmgr.a110: INDEX PATH
> >>
> >> Filters: whmgr.a110.dept_grp_key NOT IN (525 , 9 )
> >>
> >> (1) Index Name: whmgr. 300_606
> >>
> >> Index Keys: dept_key (Parallel, fragments: ALL)
> >>
> >> Lower Index Filter: whmgr.a19.dept_key = whmgr.a110.dept_key
> >> NESTED LOOP JOIN
> >>
> >> 6) whmgr.a111: SEQUENTIAL SCAN
> >>
> >> Filters:
> >>
> >> Table Scan Filters: whmgr.a111.dept_grp_type_key = 1
> >>
> >> DYNAMIC HASH JOIN
> >>
> >> Dynamic Hash Filters: whmgr.a110.bus_dept_key =
> >> whmgr.a111.bus_dept_key
> >>
> >> 7) whmgr.a16: INDEX PATH
> >>
> >> (1) Index Name: whmgr.channel_i2
> >>
> >> Index Keys: enterprise_id (Parallel, fragments: ALL)
> >>
> >> Lower Index Filter: whmgr.a16.enterprise_id = 18
> >> NESTED LOOP JOIN
> >>
> >> 8) whmgr.a15: INDEX PATH
> >>
> >> (1) Index Name: whmgr.chain_i2
> >>
> >> Index Keys: sales_channel_id (Parallel, fragments: ALL)
> >>
> >> Lower Index Filter: whmgr.a15.sales_channel_id =
> >> whmgr.a16.sales_channel_id
> >> NESTED LOOP JOIN
> >>
> >> 9) whmgr.a14: SEQUENTIAL SCAN
> >>
> >> Filters:
> >>
> >> Table Scan Filters: whmgr.a14.format_type_id = 'SUPERMKT '
> >>
> >> DYNAMIC HASH JOIN
> >>
> >> Dynamic Hash Filters: whmgr.a14.sales_chain_id =
> >> whmgr.a15.sales_chain_id
> >>
> >> 10) whmgr.a12: SEQUENTIAL SCAN
> >>
> >> Filters: whmgr.a12.fiscal_week_id = 201238
> >> NESTED LOOP JOIN
> >>
> >> 11) whmgr.a11: INDEX PATH
> >>
> >> (1) Index Name: whmgr.efin_wk_ln_cls_i
> >>
> >> Index Keys: fiscal_week_id sales_line_id mdse_class_key
> >> discount_type_cd (Parallel, fragments: ALL)
> >>
> >> Lower Index Filter: ((whmgr.a11.mdse_class_key =
> >> whmgr.a13.mdse_class_key AND whmgr.a11.fiscal_week_id =
> >> whmgr.a12.fiscal_wty_id ) AND whmgr.a11.sales_line_id =
> >> whmgr.a14.sales_line_id )
> >> NESTED LOOP JOIN
> >>
> >> Query statistics:
> >> -----------------
> >>
> >> Table map :
> >> ----------------------------
> >> Internal name Table name
> >> ----------------------------
> >> t1 a13
> >> t2 a17
> >> t3 a18
> >> t4 a19
> >> t5 a110
> >> t6 a111
> >> t7 a16
> >> t8 a15
> >> t9 a14
> >> t10 a12
> >> t11 a11
> >> t12 zzt6jqnptnrmd003
> >>
> >> type table rows_prod est_rows rows_scan time est_cost
> >> -------------------------------------------------------------------
> >> scan t1 270 270 270 00:00.01 13
> >>
> >> type table rows_prod est_rows rows_scan time est_cost
> >> -------------------------------------------------------------------
> >> scan t2 268 8102 268 00:00.17 1
> >>
> >> type rows_prod est_rows time est_cost
> >> -------------------------------------------------
> >> nljoin 268 270 00:00.21 374
> >>
> >> type table rows_prod est_rows rows_scan time est_cost
> >> -------------------------------------------------------------------
> >> scan t3 268 3807 268 00:00.00 1
> >>
> >> type rows_prod est_rows time est_cost
> >> -------------------------------------------------
> >> nljoin 268 270 00:00.21 583
> >>
> >> type table rows_prod est_rows rows_scan time est_cost
> >> -------------------------------------------------------------------
> >> scan t4 268 722 268 00:00.02 1
> >>
> >> type rows_prod est_rows time est_cost
> >> -------------------------------------------------
> >> nljoin 268 270 00:00.22 736
> >>
> >> type table rows_prod est_rows rows_scan time est_cost
> >> -------------------------------------------------------------------
> >> scan t5 268 25 268 00:00.00 0
> >>
> >> type rows_prod est_rows time est_cost
> >> -------------------------------------------------
> >> nljoin 268 251 00:00.17 785
> >>
> >> type table rows_prod est_rows rows_scan time est_cost
> >> -------------------------------------------------------------------
> >> scan t6 11 11 22 00:00.00 3
> >>
> >> type rows_prod est_rows rows_bld rows_prb novrflo time est_cost
> >>
> >>
> >>
>
> ------------------------------------------------------------------------------
>
> >> hjoin 268 126 11 268 0 00:00.07 835
> >>
> >> type table rows_prod est_rows rows_scan time est_cost
> >> -------------------------------------------------------------------
> >> scan t7 1876 7 1876 00:00.03 1
> >>
> >> type rows_prod est_rows time est_cost
> >> -------------------------------------------------
> >> nljoin 1876 878 00:00.17 987
> >>
> >> type table rows_prod est_rows rows_scan time est_cost
> >> -------------------------------------------------------------------
> >> scan t8 2412 25 2412 00:00.04 0
> >>
> >> type rows_prod est_rows time est_cost
> >> -------------------------------------------------
> >> nljoin 2412 669 00:00.22 1132
> >>
> >> type table rows_prod est_rows rows_scan time est_cost
> >> -------------------------------------------------------------------
> >> scan t9 137 23 230 00:00.00 14
> >>
> >> type rows_prod est_rows rows_bld rows_prb novrflo time est_cost
> >>
> >>
> >>
>
> ------------------------------------------------------------------------------
>
> >> hjoin 36716 645 137 2412 0 00:00.15 1274
> >>
> >> type table rows_prod est_rows rows_scan time est_cost
> >> -------------------------------------------------------------------
> >> scan t10 1395208 39 459243728 01:56.18 426
> >>
> >> type rows_prod est_rows time est_cost
> >> -------------------------------------------------
> >> nljoin 1395208 24945 01:56.51 273898
> >>
> >> type table rows_prod est_rows rows_scan time est_cost
> >> -------------------------------------------------------------------
> >> scan t11 775830 66768516 775830 58:43.74 1
> >>
> >> type rows_prod est_rows time est_cost
> >> -------------------------------------------------
> >> nljoin 775830 1861 65:49.52 293661
> >>
> >> type rows_prod est_rows rows_cons time est_cost
> >> ------------------------------------------------------------
> >> group 19 925 775830 32:56.45 3383
> >>
> >> type rows_prod est_rows rows_cons time
> >> -------------------------------------------------
> >> group 19 925 19 32:56.45
> >>
> >> type table rows_ins time
> >> -----------------------------------
> >> insert t12 19 131:45.81
> >>
> >>
> >>
> >>
>
>
> *******************************************************************************
>
> >> Forum Note: Use "Reply" to post a response in the discussion forum.
> >>
> >>
> >
>
> --e89a8ff2437da3caae04bb21266c
>
>
>
> *******************************************************************************
>
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--047d7b15a12fef0c9e04bb22ecb8
Messages In This Thread
IDS Forum is maintained by Administrator with WebBBS 5.12.
|
|
