oracle查看列状态,一个列为unique，一个列为几个状态，这两个列的组合查询，索引怎么建...

继续上面的试验，发现那些一致性读和递归调用在两种索引的第一次使用时也都有，第二次使用时又都没有了，这样在只返回一行的情况下，建在两个栏位上的索引可以少用一个物理读。

当然这些的实验是建立在下面前提下的：

查询返回不涉及其它字段。

我想，在这个假设不存在时，用一个栏位的索引肯定会更好些。

[php]

SQL> drop index tmp_idx ;

Index dropped.

SQL> create unique index tmp_idx on tmp(object_id);

Index created.

SQL> select object_id,object_type from tmp where object_id = 5040 and object_type = 'PACKAGE';

Execution Plan

----------------------------------------------------------

0      SELECT STATEMENT Optimizer=CHOOSE

1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TMP'

2    1     INDEX (UNIQUE SCAN) OF 'TMP_IDX' (UNIQUE)

Statistics

----------------------------------------------------------

71  recursive calls

0  db block gets

32  consistent gets

1  physical reads

0  redo size

249  bytes sent via SQL*Net to client

344  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 object_id,object_type from tmp where object_id = 5040 and object_type = 'PACKAGE';

Execution Plan

----------------------------------------------------------

0      SELECT STATEMENT Optimizer=CHOOSE

1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TMP'

2    1     INDEX (UNIQUE SCAN) OF 'TMP_IDX' (UNIQUE)

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

3  consistent gets

0  physical reads

0  redo size

250  bytes sent via SQL*Net to client

344  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 object_id,object_type from tmp where object_id = 5040 and object_type = 'PACKAGE';

Execution Plan

----------------------------------------------------------

0      SELECT STATEMENT Optimizer=CHOOSE

1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TMP'

2    1     INDEX (UNIQUE SCAN) OF 'TMP_IDX' (UNIQUE)

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

3  consistent gets

0  physical reads

0  redo size

249  bytes sent via SQL*Net to client

344  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL> drop index tmp_idx;

Index dropped.

SQL> create index tmp_idx on tmp(object_id,object_type);

Index created.

SQL> select object_id,object_type from tmp where object_id = 5040 and object_type = 'PACKAGE';

Execution Plan

----------------------------------------------------------

0      SELECT STATEMENT Optimizer=CHOOSE

1    0   INDEX (RANGE SCAN) OF 'TMP_IDX' (NON-UNIQUE)

Statistics

----------------------------------------------------------

71  recursive calls

0  db block gets

32  consistent gets

1  physical reads

0  redo size

249  bytes sent via SQL*Net to client

344  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 object_id,object_type from tmp where object_id = 5040 and object_type = 'PACKAGE';

Execution Plan

----------------------------------------------------------

0      SELECT STATEMENT Optimizer=CHOOSE

1    0   INDEX (RANGE SCAN) OF 'TMP_IDX' (NON-UNIQUE)

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

3  consistent gets

0  physical reads

0  redo size

250  bytes sent via SQL*Net to client

344  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]