这是CBO无法解决的问题么？

husthxd

如果在A,B字段上建立复合索引.......?

adamyang

最初由 husthxd 发布
[B]如果在A,B字段上建立复合索引.......? [/B]

可是还有这样的SQL：
select * from test1 where B=1234 and  C=012
......

husthxd

最初由 adamyang 发布
[B]

可是还有这样的SQL：
select * from test1 where B=1234 and  C=012
...... [/B]

还有索引BC_INDEX (B,C)

btw:这个问题好像转变成如何合理的在合适的字段上建立索引了.

adamyang

husthxd 版主，
这种组合可能还很多（偶只举了两个SQL），偶这一个大表上不能由于不同的几个SQL就不断添加组合索引呀

合理字段上的索引可以解决一部分问题

其实目的是想看看，如何解决CBO在多个索引中的选择

zjxs

我觉得表中300万记录，而A字段'~'就有100万的话，如果收集了column的histogram数据，CBO应该是能够选择正确的索引。查询语句2，就算是用全表扫描，估计也比用a_index要快，不能理解

查询dba_tab_histograms，看看是否有a、b、c字段的相关信息，或者用10053来看看CBO的行为

adamyang

最初由 husthxd 发布
[B]

还有索引BC_INDEX (B,C)

btw:这个问题好像转变成如何合理的在合适的字段上建立索引了. [/B]

其实这个测试表一开始主要执行的SQL：
select * from test1 where A='ABC'
select * from test1 where B=1234 and C=012
所以索引是这样建立的：
A_INDEX (A)
BC_INDEX (B,C)

这时新增了SQL:
select * from test1 where A='~' and B=1234
select * from test1 where A='ABC' and B=1234
执行次数不是很多

如果加上A,B上的索引是解决一些问题，
不过如果是一个字段多的表，SQL的种类多了，我们不能不断的为每个不同的SQL做新索引呀。

问题还是CBO对多索引的选择

adamyang

最初由 zjxs 发布
[B]我觉得表中300万记录，而A字段'~'就有100万的话，如果收集了column的histogram数据，CBO应该是能够选择正确的索引。查询语句2，就算是用全表扫描，估计也比用a_index要快，不能理解

查询dba_tab_histograms，看看是否有a、b、c字段的相关信息，或者用10053来看看CBO的行为 [/B]

偶的分析方法上面回贴写了，
dba_tab_histograms已经有a、b、c字段的相关信息

adamyang

FT,

select * from test1 where A='~' and B=1234
Cost=9
执行时间　00: 01: 23.01

select/*+index(test1 bc_index)*/ * from test1 where A='~' and B=1234
Cost=5293
执行时间　00: 00: 00.02

这个CBO......

zjxs

贴个加hint和不加hint的plan出来吧

eygle

正常情况下，拥有足够的信息，Oracle应该可以识别这个差异的。

以下是构造的一个测试例子：

[php]

SQL> create table test (a varchar2(30),b number,c number);

Table created.

SQL> insert into test select object_name,rownum,rownum+10 from dba_objects;

6307 rows created.

SQL> commit;

Commit complete.

SQL> insert into test select * from test;

6307 rows created.

SQL> /

12614 rows created.

SQL> /

25228 rows created.

SQL> /

50456 rows created.

SQL> commit;

Commit complete.

SQL> insert into test select * from test;

100912 rows created.

SQL> commit;

Commit complete.

SQL> insert into test select * from test;


201824 rows created.

SQL> SQL>
SQL> commit;

Commit complete.

SQL> select count(*) from test;

  COUNT(*)
----------
    403648

SQL>
SQL> insert into test select * from test;

403648 rows created.


SQL> commit;

Commit complete.

--'构造80w测试记录'

SQL> update test set b=1 where rownum <10000;

9999 rows updated.

SQL> commit;

--'b=1记录为1w-1条'

Commit complete.

SQL> update test set a = 'eygle' where rownum <30000;

29999 rows updated.

--'eygle记录为3w-1条'

SQL> commit;

Commit complete.


SQL> update test set a='eygle.com' where a<>'eygle';

777297 rows updated.

--'eygle.com记录为777297 条'


SQL> commit;

Commit complete.

SQL> create index i_a on test (a);

Index created.

SQL> create index i_bc on test(b,c);

Index created.


SQL> analyze table test compute statistics for table for all indexes for all indexed columns;

Table analyzed.

--'收集统计信息，包括柱状图'

SQL> set autotrace traceoly
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
SQL> set autotrace traceonly
SQL> set timing on
SQL> select count(*) from test where a='eygle' and b=1;

Elapsed: 00:00:00.55

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=212 Card=1 Bytes=12)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=212 Card=9 Bytes=108)
   3    2       INDEX (RANGE SCAN) OF 'I_A' (NON-UNIQUE) (Cost=90 Card=29999)

--'注意此时选择i_a索引，这是差别不大的'


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        942  consistent gets
        194  physical reads
          0  redo size
        381  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select count(*) from test where a='eygle.com' and b=1;

Elapsed: 00:00:00.35

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=255 Card=1 Bytes=12)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=255 Card=242 Bytes=2904)
   3    2       INDEX (RANGE SCAN) OF 'I_BC' (NON-UNIQUE) (Cost=3 Card=252)

--'此时Oracle优选了索引i_bc'
--'注意我构造的数据，柱状图差异明显，如过差别不大，执行计划可能不同'

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       7803  consistent gets
         63  physical reads
          0  redo size
        380  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

[/php]