Den 2011-06-12 07:43 skrev Pavel Zotov (JIRA) såhär: > Wrong selection of driving table for DELETE FROM T_HUGE WHERE EXISTS(SELECT * > FROM T_TINY ...) > ---------------------------------------------------------------------------------------------- > > Key: CORE-3518 > URL: http://tracker.firebirdsql.org/browse/CORE-3518 > Project: Firebird Core > Issue Type: Bug > Affects Versions: 2.5.0 > Reporter: Pavel Zotov > > > Suppose we have two tables: T_HUGE of 1'000'000 rows and T_TINY of only 100 > rows: > recreate table t_huge(id int primary key using index t_huge_pk); > recreate table t_tiny(id int primary key using index t_tiny_pk); > commit; > set term ^; > execute block as > declare n int = 1000000; > declare n0 int; > declare q int = 100; > begin > n0=n; > while (n>0) do insert into t_huge values(:n) returning :n-1 into :n; > while (q>0) do insert into t_tiny values(rand()*:n0) returning :q-1 into > :q; > end > ^set term ;^ > commit; > set statistics index t_huge_pk; > set statistics index t_tiny_pk; > commit; > > If we have to delete all such records from T_HUGE that present also in T_TINY > (i.e. they have equal IDs) we could use such statement: > delete from t_huge t where exists(select * from t_tiny s where t.id=s.id) > > But this variant has poor plan: > PLAN (S INDEX (T_TINY_PK)) > PLAN (T NATURAL) > > And also it has bad perfomance data: > > 100 record(s) was(were) deleted from T_HUGE > Execute time = 6s 94ms > ... > Memory buffers = 1 024 > Reads from disk to cache = 12 351 > Writes from cache to disk = 89 > Fetches from cache = 4 025 225 > > Table T_HUGE is *fully* scanned (NIR = 1000'000). Optimizer ignores > cardinality of T_TINY and selects T_HUGE as driving table. I thing this is > obvious mistake.
How about this: delete from t_huge t where id in (select id from t_tiny) Better plan? Kjell -- -------------------------------------- Kjell Rilbe DataDIA AB E-post: [email protected] Telefon: 08-761 06 55 Mobil: 0733-44 24 64 ------------------------------------------------------------------------------ EditLive Enterprise is the world's most technically advanced content authoring tool. Experience the power of Track Changes, Inline Image Editing and ensure content is compliant with Accessibility Checking. http://p.sf.net/sfu/ephox-dev2dev Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
