ITPub博客

首页 > Linux操作系统 > Linux操作系统 > B-tree 和位图索引的各种查询条件 执行计划比较

B-tree 和位图索引的各种查询条件 执行计划比较

原创 Linux操作系统 作者:xpj0515 时间:2011-04-30 20:14:49 0 删除 编辑
SYS@standby1/2002-01-01 00:58:10>drop table test_tab;
drop table test_tab
           *
ERROR at line 1:
ORA-00942: table or view does not exist


Elapsed: 00:00:00.18
SYS@standby1/2002-01-01 00:58:27>create table  test_tab as select rownum id,trunc(dbms_random.value(100,999)) t1,trunc(dbms_random.value(10000,99999)) t2,dbms_random.string('U',3) str1,dbms_random.string('U',5) str2 from dual connect by level <=1000000;

Table created.

Elapsed: 00:01:06.42


SYS@standby1/2002-01-01 01:02:35>col id for 9999999
SYS@standby1/2002-01-01 01:02:47>col t1 for 999999
SYS@standby1/2002-01-01 01:02:56>col t2 for 999999
SYS@standby1/2002-01-01 01:03:01>col str1 for a7
SYS@standby1/2002-01-01 01:03:09>col str2 for a7
SYS@standby1/2002-01-01 01:03:13>select * from test_tab where rownum<=10;

      ID      T1      T2 STR1    STR2
-------- ------- ------- ------- -------
       1     108   80943 JXB     AIRJK
       2     773   59153 KEM     MOMLT
       3     796   89751 HXU     QUULO
       4     703   17214 QFH     ZBQPZ
       5     603   60430 PJJ     LCMJP
       6     104   62811 RNN     ULDSM
       7     619   99953 UAP     BNMUC
       8     509   20605 AAD     DIKJD
       9     330   81171 KDC     EWBIA
      10     518   67222 QJZ     EPDVD

10 rows selected.


Elapsed: 00:00:00.01


SYS@standby1/2002-01-01 01:03:15>@table_detail.sql
Enter value for table_name: test_tab

TABLE_NAME PCT_FREE PCT_USED INI_TRANS INIT_EXT NEXT_EXT  FREELISTS CHAIN_CNT     ROWS  BLOCKS EMPTY_BLOCKS AVG_ROW_LEN
---------- -------- -------- --------- -------- -------- ---------- --------- -------- ------- ------------ -----------
TEST_TAB         10       40         1       64     1024          1

1 row selected.

Elapsed: 00:00:00.12




SYS@standby1/2002-01-01 01:04:24>analyze table  test_tab compute statistics;

Table analyzed.

Elapsed: 00:00:17.56
SYS@standby1/2002-01-01 01:05:15>@table_detail.sql
Enter value for table_name: test_tab

TABLE_NAME PCT_FREE PCT_USED INI_TRANS INIT_EXT NEXT_EXT  FREELISTS CHAIN_CNT     ROWS  BLOCKS EMPTY_BLOCKS AVG_ROW_LEN
---------- -------- -------- --------- -------- -------- ---------- --------- -------- ------- ------------ -----------
TEST_TAB         10       40         1       64     1024          1         0  1000000    4005           90        27

1 row selected.

Elapsed: 00:00:00.01


SYS@standby1/2002-01-01 01:05:28>@segment_detail.sql
Enter value for segment_name: test_tab

SEGMENT_NA SEGMENT_TY HEADER_FILE HEADER_BLOCK BYTES/1024 EXTENTS INIT_EXT NEXT_EXT  FREELISTS FLASH
---------- ---------- ----------- ------------ ---------- ------- -------- -------- ---------- -----
TEST_TAB   TABLE                1        74760      32768      47       64     1024          1 DEFAU
                                                                                               LT


1 row selected.

Elapsed: 00:00:00.12
SYS@standby1/2002-01-01 01:06:25>alter table test_tab move tablespace assm;

Table altered.

Elapsed: 00:00:10.99
SYS@standby1/2002-01-01 01:07:08>@segment_detail.sql
Enter value for segment_name: test_tab

SEGMENT_NA SEGMENT_TY HEADER_FILE HEADER_BLOCK BYTES/1024 EXTENTS INIT_EXT NEXT_EXT  FREELISTS FLASH
---------- ---------- ----------- ------------ ---------- ------- -------- -------- ---------- -----
TEST_TAB   TABLE                6         1242      32768      47       64     1024            DEFAU
                                                                                               LT


1 row selected.

Elapsed: 00:00:00.01

SYS@standby1/2002-01-01 01:07:13>@extent_detail.sql
Enter value for seg_name: test_tab

OWNER      SEGMENT_NAME    SEGMENT_TY TABLESPACE FILE_ID EXTENT_ID BLOCK_ID      BYTES BLOCKS
---------- --------------- ---------- ---------- ------- --------- -------- ---------- ------
SYS        TEST_TAB        TABLE      ASSM             6         0     1240      65536      8
SYS        TEST_TAB        TABLE      ASSM             6         1     1248      65536      8
SYS        TEST_TAB        TABLE      ASSM             6         2     1256      65536      8
SYS        TEST_TAB        TABLE      ASSM             6         3     1264      65536      8
SYS        TEST_TAB        TABLE      ASSM             6         4     1272      65536      8
SYS        TEST_TAB        TABLE      ASSM             6         5     2688      65536      8
SYS        TEST_TAB        TABLE      ASSM             6         6     2696      65536      8
SYS        TEST_TAB        TABLE      ASSM             6         7     2704      65536      8
SYS        TEST_TAB        TABLE      ASSM             6         8     2712      65536      8
SYS        TEST_TAB        TABLE      ASSM             6         9     2720      65536      8
SYS        TEST_TAB        TABLE      ASSM             6        10     2728      65536      8
SYS        TEST_TAB        TABLE      ASSM             6        11     2736      65536      8
SYS        TEST_TAB        TABLE      ASSM             6        12     2744      65536      8
SYS        TEST_TAB        TABLE      ASSM             6        13     2752      65536      8
SYS        TEST_TAB        TABLE      ASSM             6        14     2760      65536      8
SYS        TEST_TAB        TABLE      ASSM             6        15     2768      65536      8
SYS        TEST_TAB        TABLE      ASSM             6        16     2816    1048576    128
SYS        TEST_TAB        TABLE      ASSM             6        17     2944    1048576    128
SYS        TEST_TAB        TABLE      ASSM             6        18     3072    1048576    128
SYS        TEST_TAB        TABLE      ASSM             6        19     3200    1048576    128
SYS        TEST_TAB        TABLE      ASSM             6        20     3328    1048576    128
SYS        TEST_TAB        TABLE      ASSM             6        21     3456    1048576    128
SYS        TEST_TAB        TABLE      ASSM             6        22     3584    1048576    128
SYS        TEST_TAB        TABLE      ASSM             6        23     3712    1048576    128
SYS        TEST_TAB        TABLE      ASSM             6        24     3840    1048576    128
SYS        TEST_TAB        TABLE      ASSM             6        25     3968    1048576    128
SYS        TEST_TAB        TABLE      ASSM             6        26     4096    1048576    128
SYS        TEST_TAB        TABLE      ASSM             6        27     4224    1048576    128
SYS        TEST_TAB        TABLE      ASSM             6        28     4352    1048576    128
SYS        TEST_TAB        TABLE      ASSM             6        29     4480    1048576    128
SYS        TEST_TAB        TABLE      ASSM             6        30     4608    1048576    128
SYS        TEST_TAB        TABLE      ASSM             6        31     4736    1048576    128
SYS        TEST_TAB        TABLE      ASSM             6        32     4864    1048576    128
SYS        TEST_TAB        TABLE      ASSM             6        33     4992    1048576    128
SYS        TEST_TAB        TABLE      ASSM             6        34     5120    1048576    128
SYS        TEST_TAB        TABLE      ASSM             6        35     5248    1048576    128
SYS        TEST_TAB        TABLE      ASSM             6        36     5376    1048576    128
SYS        TEST_TAB        TABLE      ASSM             6        37     5504    1048576    128
SYS        TEST_TAB        TABLE      ASSM             6        38     5632    1048576    128
SYS        TEST_TAB        TABLE      ASSM             6        39     5760    1048576    128
SYS        TEST_TAB        TABLE      ASSM             6        40     5888    1048576    128
SYS        TEST_TAB        TABLE      ASSM             6        41     6016    1048576    128
SYS        TEST_TAB        TABLE      ASSM             6        42     6144    1048576    128
SYS        TEST_TAB        TABLE      ASSM             6        43     6272    1048576    128
SYS        TEST_TAB        TABLE      ASSM             6        44     6400    1048576    128
SYS        TEST_TAB        TABLE      ASSM             6        45     6528    1048576    128
SYS        TEST_TAB        TABLE      ASSM             6        46     6656    1048576    128

47 rows selected.

Elapsed: 00:00:00.44



SYS@standby1/2002-01-01 01:08:00>drop index pk_test_id;
drop index pk_test_id
           *
ERROR at line 1:
ORA-01418: specified index does not exist


Elapsed: 00:00:00.02
SYS@standby1/2002-01-01 01:09:03>create index pk_test_id on test_tab(id);

Index created.

Elapsed: 00:00:03.63
SYS@standby1/2002-01-01 01:09:35>create index pk_test_t1 on test_tab(t1);

Index created.

Elapsed: 00:00:03.53
SYS@standby1/2002-01-01 01:09:55>create index pk_test_t2 on test_tab(t2);

Index created.

Elapsed: 00:00:03.58


SYS@standby1/2002-01-01 01:10:05>@index_statistics.sql
Enter value for indexname: pk_test_id
Enter value for tablename: test_tab

OWNER    INDEX    TABLE    BLOCKS     ROWS  DIS_KEY   KEY_ROWS LAST_ANALYZED        LEAFBLK PART  CLU_FACTOR
-------- -------- -------- ------ -------- -------- ---------- -------------------- ------- ----- ----------
SYS      PK_TEST_ TEST_TAB   4005  1000000  1000000          1 2002-01-01 01:09:35     2226 NO          4015
         ID

SYS      PK_TEST_ TEST_TAB   4005  1000000      899       1112 2002-01-01 01:09:55     2091 NO        874010
         T1

SYS      PK_TEST_ TEST_TAB   4005  1000000    89998         11 2002-01-01 01:10:05     2231 NO        998605
         T2


3 rows selected.

Elapsed: 00:00:00.77


SYS@standby1/2002-01-01 01:10:47>explain plan for select * from test_tab where id between 1000 and 2000;

Explained.

Elapsed: 00:00:00.38
SYS@standby1/2002-01-01 01:13:42>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 952433714

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |  1002 | 19038 |    10   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_TAB   |  1002 | 19038 |    10   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | PK_TEST_ID |  1002 |       |     5   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID">=1000 AND "ID"<=2000)

SYS@standby1/2002-01-01 01:14:13>explain plan for select * from test_tab where id between 1000 and 2000 and t1 <102;

Explained.

Elapsed: 00:00:00.00
SYS@standby1/2002-01-01 01:15:18>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 952433714

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     2 |    38 |    10   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| TEST_TAB   |     2 |    38 |    10   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | PK_TEST_ID |  1002 |       |     5   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T1"<102)
   2 - access("ID">=1000 AND "ID"<=2000)

15 rows selected.

Elapsed: 00:00:00.03

SYS@standby1/2002-01-01 01:15:20>explain plan for select /*+ index(pk_test_t1)*/ * from test_tab where id between 1000 and 2000 and t1 <102;

Explained.

Elapsed: 00:00:00.01
SYS@standby1/2002-01-01 01:16:32>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 952433714

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     2 |    38 |    10   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| TEST_TAB   |     2 |    38 |    10   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | PK_TEST_ID |  1002 |       |     5   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T1"<102)
   2 - access("ID">=1000 AND "ID"<=2000)

15 rows selected.

Elapsed: 00:00:00.03
SYS@standby1/2002-01-01 01:16:33>explain plan for select /*+ index(pk_test_t1)*/ * from test_tab where id between 1000 and 200000 and t1 <102;

Explained.

Elapsed: 00:00:00.01
SYS@standby1/2002-01-01 01:17:06>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3962208483

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |   443 |  8417 |  1100   (2)| 00:00:14 |
|*  1 |  TABLE ACCESS FULL| TEST_TAB |   443 |  8417 |  1100   (2)| 00:00:14 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T1"<102 AND "ID"<=200000 AND "ID">=1000)

13 rows selected.

Elapsed: 00:00:00.02


SYS@standby1/2002-01-01 01:20:27>explain plan for select /*+ */ id,t1 from test_tab where id between 1000 and 2000 and t1 <102 or str1 like 'AA%';

Explained.

Elapsed: 00:00:00.01
SYS@standby1/2002-01-01 01:20:54>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3962208483

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |   118 |  1180 |  1108   (2)| 00:00:14 |
|*  1 |  TABLE ACCESS FULL| TEST_TAB |   118 |  1180 |  1108   (2)| 00:00:14 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("STR1" LIKE 'AA%' OR "ID"<=2000 AND "T1"<102 AND
              "ID">=1000)

14 rows selected.

Elapsed: 00:00:00.02
SYS@standby1/2002-01-01 01:20:55>explain plan for select /*+ */ id,t1 from test_tab where id between 1000 and 2000 and t1 <102 and str1 like 'AA%';

Explained.

Elapsed: 00:00:00.00
SYS@standby1/2002-01-01 01:21:21>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 952433714

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |    10 |    10   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| TEST_TAB   |     1 |    10 |    10   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | PK_TEST_ID |  1002 |       |     5   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T1"<102 AND "STR1" LIKE 'AA%')
   2 - access("ID">=1000 AND "ID"<=2000)

15 rows selected.

SYS@standby1/2002-01-01 01:26:36>@index_statistics.sql
Enter value for indexname: hg
Enter value for tablename: test_tab

OWNER    INDEX    TABLE    BLOCKS     ROWS  DIS_KEY   KEY_ROWS LAST_ANALYZED        LEAFBLK PART  CLU_FACTOR
-------- -------- -------- ------ -------- -------- ---------- -------------------- ------- ----- ----------
SYS      PK_TEST_ TEST_TAB   4005  1000000  1000000          1 2002-01-01 01:09:35     2226 NO          4015
         ID

SYS      PK_TEST_ TEST_TAB   4005  1000000      899       1112 2002-01-01 01:09:55     2091 NO        874010
         T1

SYS      PK_TEST_ TEST_TAB   4005  1000000    89998         11 2002-01-01 01:10:05     2231 NO        998605
         T2

SYS      IND_TEST TEST_TAB   4005  1000000    17576         56 2002-01-01 01:26:21     2093 NO        992960
         _STR1

SYS      IND_TEST TEST_TAB   4005  1000000   959277          1 2002-01-01 01:26:36     2370 NO        999730
         _STR2


5 rows selected.

SYS@standby1/2002-01-01 01:27:59>explain plan for select * from test_tab where str1 like 'A%';

Explained.

Elapsed: 00:00:00.07
SYS@standby1/2002-01-01 01:28:10>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3962208483

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 29688 |   550K|  1105   (2)| 00:00:14 |
|*  1 |  TABLE ACCESS FULL| TEST_TAB | 29688 |   550K|  1105   (2)| 00:00:14 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("STR1" LIKE 'A%')


SYS@standby1/2002-01-01 01:28:10>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3962208483

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 29688 |   550K|  1105   (2)| 00:00:14 |
|*  1 |  TABLE ACCESS FULL| TEST_TAB | 29688 |   550K|  1105   (2)| 00:00:14 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("STR1" LIKE 'A%')

13 rows selected.

Elapsed: 00:00:00.02
SYS@standby1/2002-01-01 01:28:15>explain plan for select * from test_tab where str1 like 'AB%';

Explained.

Elapsed: 00:00:00.00
SYS@standby1/2002-01-01 01:28:52>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2308511019

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |   213 |  4047 |   215   (0)| 00:00:03 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_TAB      |   213 |  4047 |   215   (0)| 00:00:03 |
|*  2 |   INDEX RANGE SCAN          | IND_TEST_STR1 |   213 |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("STR1" LIKE 'AB%')
       filter("STR1" LIKE 'AB%')

15 rows selected.


SYS@standby1/2002-01-01 01:30:00>drop index ind_test_str1;

Index dropped.

Elapsed: 00:00:00.42
SYS@standby1/2002-01-01 01:30:55>explain plan for select * from test_tab where str1 like 'AB%';

Explained.

Elapsed: 00:00:00.02
SYS@standby1/2002-01-01 01:32:06>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3962208483

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |   213 |  4047 |  1105   (2)| 00:00:14 |
|*  1 |  TABLE ACCESS FULL| TEST_TAB |   213 |  4047 |  1105   (2)| 00:00:14 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("STR1" LIKE 'AB%')

13 rows selected.

Elapsed: 00:00:00.02

SYS@standby1/2002-01-01 01:34:16>explain plan for select * from test_tab where str1 like 'AB%' and str2 like 'A%';

Explained.

Elapsed: 00:00:00.00
SYS@standby1/2002-01-01 01:34:44>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2308511019

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     6 |   114 |   215   (0)| 00:00:03 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| TEST_TAB      |     6 |   114 |   215   (0)| 00:00:03 |
|*  2 |   INDEX RANGE SCAN          | IND_TEST_STR1 |   213 |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("STR2" LIKE 'A%')
   2 - access("STR1" LIKE 'AB%')
       filter("STR1" LIKE 'AB%')

16 rows selected.


SYS@standby1/2002-01-01 01:34:16>explain plan for select * from test_tab where str1 like 'AB%' and str2 like 'A%';

Explained.

Elapsed: 00:00:00.00
SYS@standby1/2002-01-01 01:34:44>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2308511019

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     6 |   114 |   215   (0)| 00:00:03 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| TEST_TAB      |     6 |   114 |   215   (0)| 00:00:03 |
|*  2 |   INDEX RANGE SCAN          | IND_TEST_STR1 |   213 |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("STR2" LIKE 'A%')
   2 - access("STR1" LIKE 'AB%')
       filter("STR1" LIKE 'AB%')

16 rows selected.

Elapsed: 00:00:00.02
SYS@standby1/2002-01-01 01:34:46>explain plan for select * from test_tab where str1 like 'AB%' and str2 like 'AA%';

Explained.

Elapsed: 00:00:00.01
SYS@standby1/2002-01-01 01:35:29>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1285436085

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    19 |   119   (0)| 00:00:02 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| TEST_TAB      |     1 |    19 |   119   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | IND_TEST_STR2 |   116 |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("STR1" LIKE 'AB%')
   2 - access("STR2" LIKE 'AA%')
       filter("STR2" LIKE 'AA%')

16 rows selected.

SYS@standby1/2002-01-01 01:36:38>explain plan for select * from test_tab where str1 like 'A%' and str2 like 'A%';

Explained.

Elapsed: 00:00:00.00
SYS@standby1/2002-01-01 01:36:52>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3962208483

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |   881 | 16739 |  1106   (2)| 00:00:14 |
|*  1 |  TABLE ACCESS FULL| TEST_TAB |   881 | 16739 |  1106   (2)| 00:00:14 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("STR2" LIKE 'A%' AND "STR1" LIKE 'A%')

13 rows selected.

SYS@standby1/2002-01-01 01:40:35>create bitmap index bmp_test_str1 on test_tab(str1);
create bitmap index bmp_test_str1 on test_tab(str1)
                                              *
ERROR at line 1:
ORA-01408: such column list already indexed


Elapsed: 00:00:00.01
SYS@standby1/2002-01-01 01:41:03>drop index ind_test_str1;

Index dropped.

Elapsed: 00:00:00.04
SYS@standby1/2002-01-01 01:41:20>create bitmap index bmp_test_str1 on test_tab(str1);

Index created.

Elapsed: 00:00:01.62
SYS@standby1/2002-01-01 01:41:24>explain plan for select /*+ index(*/ * from test_tab where str1 like 'A%' and str2 like 'A%';

Explained.

Elapsed: 00:00:00.07
SYS@standby1/2002-01-01 01:41:38>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2799005936

----------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |               |   881 | 16739 |       |   378   (1)| 00:00:05 |
|   1 |  TABLE ACCESS BY INDEX ROWID     | TEST_TAB      |   881 | 16739 |       |   378   (1)| 00:00:05 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |               |       |       |       |            |          |
|   3 |    BITMAP AND                    |               |       |       |       |            |          |
|   4 |     BITMAP MERGE                 |               |       |       |       |            |          |
|*  5 |      BITMAP INDEX RANGE SCAN     | BMP_TEST_STR1 |       |       |       |            |          |
|   6 |     BITMAP CONVERSION FROM ROWIDS|               |       |       |       |            |          |
|   7 |      SORT ORDER BY               |               |       |       |   480K|            |          |
|*  8 |       INDEX RANGE SCAN           | IND_TEST_STR2 |       |       |       |    73   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("STR1" LIKE 'A%')
       filter("STR1" LIKE 'A%' AND "STR1" LIKE 'A%')
   8 - access("STR2" LIKE 'A%')
       filter("STR2" LIKE 'A%' AND "STR2" LIKE 'A%')

23 rows selected.

Elapsed: 00:00:00.03


SYS@standby1/2002-01-01 01:41:43>explain plan for select /*+ index(*/ * from test_tab where str1 like 'A%' and str2 like 'AAA%';

Explained.

Elapsed: 00:00:00.00
SYS@standby1/2002-01-01 01:43:30>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1285436085

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    19 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| TEST_TAB      |     1 |    19 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_TEST_STR2 |     1 |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("STR1" LIKE 'A%')
   2 - access("STR2" LIKE 'AAA%')
       filter("STR2" LIKE 'AAA%')

16 rows selected.


SYS@standby1/2002-01-01 01:43:32>explain plan for select /*+ index(*/ * from test_tab where str1 like 'A%' and str2 like 'AA%';

Explained.

Elapsed: 00:00:00.00
SYS@standby1/2002-01-01 01:43:53>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1667178758

--------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |               |     3 |    57 |    21   (5)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID     | TEST_TAB      |     3 |    57 |    21   (5)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |               |       |       |            |          |
|   3 |    BITMAP AND                    |               |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS|               |       |       |            |          |
|   5 |      SORT ORDER BY               |               |       |       |            |          |
|*  6 |       INDEX RANGE SCAN           | IND_TEST_STR2 |   116 |       |     3   (0)| 00:00:01 |
|   7 |     BITMAP MERGE                 |               |       |       |            |          |
|*  8 |      BITMAP INDEX RANGE SCAN     | BMP_TEST_STR1 |       |       |            |          |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("STR2" LIKE 'AA%')
       filter("STR2" LIKE 'AA%' AND "STR2" LIKE 'AA%')
   8 - access("STR1" LIKE 'A%')
       filter("STR1" LIKE 'A%' AND "STR1" LIKE 'A%')



SYS@standby1/2002-01-01 01:43:55>explain plan for select /*+ index(*/ * from test_tab where str1 like 'A%' or str2 like 'AA%';

Explained.

Elapsed: 00:00:00.00
SYS@standby1/2002-01-01 01:46:11>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1909131729

--------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |               | 29800 |   552K|   987   (1)| 00:00:12 |
|   1 |  TABLE ACCESS BY INDEX ROWID     | TEST_TAB      | 29800 |   552K|   987   (1)| 00:00:12 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |               |       |       |            |          |
|   3 |    BITMAP OR                     |               |       |       |            |          |
|   4 |     BITMAP MERGE                 |               |       |       |            |          |
|*  5 |      BITMAP INDEX RANGE SCAN     | BMP_TEST_STR1 |       |       |            |          |
|   6 |     BITMAP CONVERSION FROM ROWIDS|               |       |       |            |          |
|   7 |      SORT ORDER BY               |               |       |       |            |          |
|*  8 |       INDEX RANGE SCAN           | IND_TEST_STR2 |       |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("STR1" LIKE 'A%')
       filter("STR1" LIKE 'A%' AND "STR1" LIKE 'A%')
   8 - access("STR2" LIKE 'AA%')
       filter("STR2" LIKE 'AA%' AND "STR2" LIKE 'AA%')


SYS@standby1/2002-01-01 01:46:12>explain plan for select /*+ index(*/ * from test_tab where str1 like 'A%' or str2 is null;

Explained.

Elapsed: 00:00:00.00
SYS@standby1/2002-01-01 01:47:58>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3962208483

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 29688 |   550K|  1107   (2)| 00:00:14 |
|*  1 |  TABLE ACCESS FULL| TEST_TAB | 29688 |   550K|  1107   (2)| 00:00:14 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("STR1" LIKE 'A%' OR "STR2" IS NULL)

13 rows selected.

Elapsed: 00:00:00.02
SYS@standby1/2002-01-01 01:47:59>explain plan for select /*+ index(*/ * from test_tab where str1 is null or str2 like 'AA%';

Explained.

Elapsed: 00:00:00.00
SYS@standby1/2002-01-01 01:48:19>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3370641980

--------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |               |   116 |  2204 |    29   (4)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID     | TEST_TAB      |   116 |  2204 |    29   (4)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |               |       |       |            |          |
|   3 |    BITMAP OR                     |               |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS|               |       |       |            |          |
|   5 |      SORT ORDER BY               |               |       |       |            |          |
|*  6 |       INDEX RANGE SCAN           | IND_TEST_STR2 |       |       |     3   (0)| 00:00:01 |
|*  7 |     BITMAP INDEX SINGLE VALUE    | BMP_TEST_STR1 |       |       |            |          |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("STR2" LIKE 'AA%')
       filter("STR2" LIKE 'AA%' AND "STR2" LIKE 'AA%')
   7 - access("STR1" IS NULL)

21 rows selected.

SYS@standby1/2002-01-01 01:49:46>explain plan for select /*+ index(*/ * from test_tab where str1 <>'AAA' and str2 like 'AA%';

Explained.

Elapsed: 00:00:00.00
SYS@standby1/2002-01-01 01:49:56>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1285436085

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |   116 |  2204 |   119   (0)| 00:00:02 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| TEST_TAB      |   116 |  2204 |   119   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | IND_TEST_STR2 |   116 |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("STR1"<>'AAA')
   2 - access("STR2" LIKE 'AA%')
       filter("STR2" LIKE 'AA%')

16 rows selected.


SYS@standby1/2002-01-01 01:49:57>drop index pk_test_t2;

Index dropped.

Elapsed: 00:00:00.05
SYS@standby1/2002-01-01 01:53:52>create bitmap index bmp_test_t2 on test_tab(t2);

Index created.

Elapsed: 00:00:02.05
SYS@standby1/2002-01-01 01:54:25>@index_statistics.sql
Enter value for indexname: fdf
Enter value for tablename: test_tab

OWNER    INDEX    TABLE    BLOCKS     ROWS  DIS_KEY   KEY_ROWS LAST_ANALYZED        LEAFBLK PART  CLU_FACTOR
-------- -------- -------- ------ -------- -------- ---------- -------------------- ------- ----- ----------
SYS      PK_TEST_ TEST_TAB   4005  1000000  1000000          1 2002-01-01 01:09:35     2226 NO          4015
         ID

SYS      PK_TEST_ TEST_TAB   4005  1000000      899       1112 2002-01-01 01:09:55     2091 NO        874010
         T1

SYS      BMP_TEST TEST_TAB   4005    89998    89998          1 2002-01-01 01:54:25      772 NO         89998
         _T2

SYS      BMP_TEST TEST_TAB   4005    17576    17576          1 2002-01-01 01:41:24      487 NO         17576
         _STR1

SYS      IND_TEST TEST_TAB   4005  1000000   959277          1 2002-01-01 01:26:36     2370 NO        999730
         _STR2


5 rows selected.

SYS@standby1/2002-01-01 01:54:45>explain plan for select /*+ index(*/ * from test_tab where str1 like 'A%' and t2  between 10000 and 20000;

Explained.

Elapsed: 00:00:00.01
SYS@standby1/2002-01-01 01:56:55>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3988437505

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |  3299 | 62681 |   574   (1)| 00:00:07 |
|   1 |  TABLE ACCESS BY INDEX ROWID | TEST_TAB      |  3299 | 62681 |   574   (1)| 00:00:07 |
|   2 |   BITMAP CONVERSION TO ROWIDS|               |       |       |            |          |
|   3 |    BITMAP AND                |               |       |       |            |          |
|   4 |     BITMAP MERGE             |               |       |       |            |          |
|*  5 |      BITMAP INDEX RANGE SCAN | BMP_TEST_STR1 |       |       |            |          |
|   6 |     BITMAP MERGE             |               |       |       |            |          |
|*  7 |      BITMAP INDEX RANGE SCAN | BMP_TEST_T2   |       |       |            |          |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("STR1" LIKE 'A%')
       filter("STR1" LIKE 'A%' AND "STR1" LIKE 'A%')
   7 - access("T2">=10000 AND "T2"<=20000)



SYS@standby1/2002-01-01 01:57:00>explain plan for select /*+ index(*/ * from test_tab where str1 like 'A%' and t2  between 10000 and 80000;

Explained.

Elapsed: 00:00:00.00
SYS@standby1/2002-01-01 01:57:26>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 709733046

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               | 23091 |   428K|   983   (1)| 00:00:12 |
|*  1 |  TABLE ACCESS BY INDEX ROWID | TEST_TAB      | 23091 |   428K|   983   (1)| 00:00:12 |
|   2 |   BITMAP CONVERSION TO ROWIDS|               |       |       |            |          |
|*  3 |    BITMAP INDEX RANGE SCAN   | BMP_TEST_STR1 |       |       |            |          |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T2"<=80000 AND "T2">=10000)
   3 - access("STR1" LIKE 'A%')
       filter("STR1" LIKE 'A%' AND "STR1" LIKE 'A%')

SYS@standby1/2002-01-01 01:57:55>explain plan for select /*+ index(*/ * from test_tab where str1 between 'AAA' and 'KKK' and t2  between 10000 and 11000;

Explained.

Elapsed: 00:00:00.00
SYS@standby1/2002-01-01 01:58:32>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3603002680

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |  4450 | 84550 |   825   (0)| 00:00:10 |
|*  1 |  TABLE ACCESS BY INDEX ROWID | TEST_TAB    |  4450 | 84550 |   825   (0)| 00:00:10 |
|   2 |   BITMAP CONVERSION TO ROWIDS|             |       |       |            |          |
|*  3 |    BITMAP INDEX RANGE SCAN   | BMP_TEST_T2 |       |       |            |          |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("STR1"<='KKK' AND "STR1">='AAA')
   3 - access("T2">=10000 AND "T2"<=11000)



SYS@standby1/2002-01-01 01:58:33>explain plan for select /*+ index(*/ * from test_tab where str1 like 'A%' or  t2  between 10000 and 11000;

Explained.

Elapsed: 00:00:00.01
SYS@standby1/2002-01-01 02:00:02>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 765455771

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               | 40480 |   751K|  1054   (1)| 00:00:13 |
|   1 |  TABLE ACCESS BY INDEX ROWID | TEST_TAB      | 40480 |   751K|  1054   (1)| 00:00:13 |
|   2 |   BITMAP CONVERSION TO ROWIDS|               |       |       |            |          |
|   3 |    BITMAP OR                 |               |       |       |            |          |
|   4 |     BITMAP MERGE             |               |       |       |            |          |
|*  5 |      BITMAP INDEX RANGE SCAN | BMP_TEST_STR1 |       |       |            |          |
|   6 |     BITMAP MERGE             |               |       |       |            |          |
|*  7 |      BITMAP INDEX RANGE SCAN | BMP_TEST_T2   |       |       |            |          |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("STR1" LIKE 'A%')
       filter("STR1" LIKE 'A%' AND "STR1" LIKE 'A%')
   7 - access("T2">=10000 AND "T2"<=11000)

SYS@standby1/2002-01-01 02:01:23>explain plan for select /*+ index(*/ * from test_tab where str1 like 'AA%' or  t2  between 10000 and 12000;

Explained.

Elapsed: 00:00:00.01
SYS@standby1/2002-01-01 02:01:29>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1769468780

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               | 22347 |   414K|   945   (1)| 00:00:12 |
|   1 |  TABLE ACCESS BY INDEX ROWID | TEST_TAB      | 22347 |   414K|   945   (1)| 00:00:12 |
|   2 |   BITMAP CONVERSION TO ROWIDS|               |       |       |            |          |
|   3 |    BITMAP OR                 |               |       |       |            |          |
|   4 |     BITMAP MERGE             |               |       |       |            |          |
|*  5 |      BITMAP INDEX RANGE SCAN | BMP_TEST_T2   |       |       |            |          |
|   6 |     BITMAP MERGE             |               |       |       |            |          |
|*  7 |      BITMAP INDEX RANGE SCAN | BMP_TEST_STR1 |       |       |            |          |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("T2">=10000 AND "T2"<=12000)
   7 - access("STR1" LIKE 'AA%')
       filter("STR1" LIKE 'AA%' AND "STR1" LIKE 'AA%')


SYS@standby1/2002-01-01 02:02:18>explain plan for select /*+ index_combine(test_tab bmp_test_str1 bmp_test_t2) */ * from test_tab where str1 like 'AA%' or  t2  between 10000 and 20000;

Explained.

Elapsed: 00:00:00.00
SYS@standby1/2002-01-01 02:04:12>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1769468780

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |   111K|  2063K|  1513   (1)| 00:00:19 |
|   1 |  TABLE ACCESS BY INDEX ROWID | TEST_TAB      |   111K|  2063K|  1513   (1)| 00:00:19 |
|   2 |   BITMAP CONVERSION TO ROWIDS|               |       |       |            |          |
|   3 |    BITMAP OR                 |               |       |       |            |          |
|   4 |     BITMAP MERGE             |               |       |       |            |          |
|*  5 |      BITMAP INDEX RANGE SCAN | BMP_TEST_T2   |       |       |            |          |
|   6 |     BITMAP MERGE             |               |       |       |            |          |
|*  7 |      BITMAP INDEX RANGE SCAN | BMP_TEST_STR1 |       |       |            |          |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("T2">=10000 AND "T2"<=20000)
   7 - access("STR1" LIKE 'AA%')
       filter("STR1" LIKE 'AA%' AND "STR1" LIKE 'AA%')

SYS@standby1/2002-01-01 02:04:57>explain plan for select /*+ index_combine(test_tab bmp_test_str1 bmp_test_t2) */ * from test_tab where str1 like 'AA%' or  t2  between 10000 and 11000;

Explained.

Elapsed: 00:00:00.00
SYS@standby1/2002-01-01 02:05:09>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1769468780

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               | 11237 |   208K|   829   (0)| 00:00:10 |
|   1 |  TABLE ACCESS BY INDEX ROWID | TEST_TAB      | 11237 |   208K|   829   (0)| 00:00:10 |
|   2 |   BITMAP CONVERSION TO ROWIDS|               |       |       |            |          |
|   3 |    BITMAP OR                 |               |       |       |            |          |
|   4 |     BITMAP MERGE             |               |       |       |            |          |
|*  5 |      BITMAP INDEX RANGE SCAN | BMP_TEST_T2   |       |       |            |          |
|   6 |     BITMAP MERGE             |               |       |       |            |          |
|*  7 |      BITMAP INDEX RANGE SCAN | BMP_TEST_STR1 |       |       |            |          |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("T2">=10000 AND "T2"<=11000)
   7 - access("STR1" LIKE 'AA%')
       filter("STR1" LIKE 'AA%' AND "STR1" LIKE 'AA%')

21 rows selected.

SYS@standby1/2002-01-01 02:06:31>explain plan for select /*+ index_combine(test_tab bmp_test_str1 bmp_test_t2 */ * from test_tab where str1 like 'AA%' or  t2  between 10000 and 11000;

Explained.

Elapsed: 00:00:00.00

SYS@standby1/2002-01-01 02:06:07>drop index bmp_test_t2;

Index dropped.

Elapsed: 00:00:00.06

SYS@standby1/2002-01-01 02:06:34>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3962208483

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 11237 |   208K|  1108   (2)| 00:00:14 |
|*  1 |  TABLE ACCESS FULL| TEST_TAB | 11237 |   208K|  1108   (2)| 00:00:14 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T2"<=11000 AND "T2">=10000 OR "STR1" LIKE 'AA%')


SYS@standby1/2002-01-01 02:08:30>create bitmap index bmp_com_test_t2_str1 on test_tab(t2,str1);

Index created.

Elapsed: 00:00:13.55
SYS@standby1/2002-01-01 02:09:51>@index_statistics.sql
Enter value for indexname: df
Enter value for tablename: test_tab

OWNER    INDEX    TABLE    BLOCKS     ROWS  DIS_KEY   KEY_ROWS LAST_ANALYZED        LEAFBLK PART  CLU_FACTOR
-------- -------- -------- ------ -------- -------- ---------- -------------------- ------- ----- ----------
SYS      PK_TEST_ TEST_TAB   4005  1000000  1000000          1 2002-01-01 01:09:35     2226 NO          4015
         ID

SYS      PK_TEST_ TEST_TAB   4005  1000000      899       1112 2002-01-01 01:09:55     2091 NO        874010
         T1

SYS      BMP_TEST TEST_TAB   4005    89998    89998          1 2002-01-01 02:08:22      772 NO         89998
         _T2

SYS      BMP_TEST TEST_TAB   4005    17576    17576          1 2002-01-01 01:41:24      487 NO         17576
         _STR1

SYS      IND_TEST TEST_TAB   4005  1000000   959277          1 2002-01-01 01:26:36     2370 NO        999730
         _STR2

SYS      BMP_COM_ TEST_TAB   4005   999654   999654          1 2002-01-01 02:09:51     4046 NO        999654
         TEST_T2_
         STR1


6 rows selected.

Elapsed: 00:00:00.75


SYS@standby1/2002-01-01 02:11:32>explain plan for select /*+ index(test_tab bmp_com_test_t2_str1) */ * from test_tab where str1 like 'AA%' or  t2  between 10000 and 11000;

Explained.

Elapsed: 00:00:00.01
SYS@standby1/2002-01-01 02:12:19>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2752414759

-----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      | 11237 |   208K|   867   (1)| 00:00:11 |
|   1 |  TABLE ACCESS BY INDEX ROWID | TEST_TAB             | 11237 |   208K|   867   (1)| 00:00:11 |
|   2 |   BITMAP CONVERSION TO ROWIDS|                      |       |       |            |          |
|   3 |    BITMAP OR                 |                      |       |       |            |          |
|   4 |     BITMAP MERGE             |                      |       |       |            |          |
|*  5 |      BITMAP INDEX RANGE SCAN | BMP_COM_TEST_T2_STR1 |       |       |            |          |
|   6 |     BITMAP MERGE             |                      |       |       |            |          |
|*  7 |      BITMAP INDEX RANGE SCAN | BMP_TEST_STR1        |       |       |            |          |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("T2">=10000 AND "T2"<=11000)
       filter("T2"<=11000 AND "T2">=10000)
   7 - access("STR1" LIKE 'AA%')
       filter("STR1" LIKE 'AA%' AND "STR1" LIKE 'AA%')

SYS@standby1/2002-01-01 02:20:11>explain plan for select * from test_tab where t1 between 100 and 110 and t2 !=12345;

Explained.

Elapsed: 00:00:00.01
SYS@standby1/2002-01-01 02:20:33>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3962208483

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 12248 |   227K|  1101   (2)| 00:00:14 |
|*  1 |  TABLE ACCESS FULL| TEST_TAB | 12248 |   227K|  1101   (2)| 00:00:14 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T1"<=110 AND "T2"<>12345 AND "T1">=100)



SYS@standby1/2002-01-01 02:44:11>explain plan for select /*+ index(bmp_test_t2*/ * from test_tab where t1 between 100 and 110 and t2 between 12345 and 14556;

Explained.

Elapsed: 00:00:00.01
SYS@standby1/2002-01-01 02:44:29>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2720818643

--------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |             |   301 |  5719 |       |   299   (2)| 00:00:04 |
|   1 |  TABLE ACCESS BY INDEX ROWID     | TEST_TAB    |   301 |  5719 |       |   299   (2)| 00:00:04 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |             |       |       |       |            |          |
|   3 |    BITMAP AND                    |             |       |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS|             |       |       |       |            |          |
|   5 |      SORT ORDER BY               |             |       |       |   208K|            |          |
|*  6 |       INDEX RANGE SCAN           | PK_TEST_T1  |       |       |       |    28   (0)| 00:00:01 |
|   7 |     BITMAP CONVERSION FROM ROWIDS|             |       |       |       |            |          |
|   8 |      SORT ORDER BY               |             |       |       |   400K|            |          |
|*  9 |       INDEX RANGE SCAN           | IND_TEST_T2 |       |       |       |    57   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("T1">=100 AND "T1"<=110)
   9 - access("T2">=12345 AND "T2"<=14556)


SYS@standby1/2002-01-01 02:46:07>create index ind_test_t1_t2 on test_tab(t1,t2);

Index created.

Elapsed: 00:00:04.02
SYS@standby1/2002-01-01 02:46:59>@index_statistics.sql
Enter value for indexname: f
Enter value for tablename: test_tab

OWNER    INDEX    TABLE    BLOCKS     ROWS  DIS_KEY   KEY_ROWS LAST_ANALYZED        LEAFBLK PART  CLU_FACTOR
-------- -------- -------- ------ -------- -------- ---------- -------------------- ------- ----- ----------
SYS      PK_TEST_ TEST_TAB   4005  1000000  1000000          1 2002-01-01 01:09:35     2226 NO          4015
         ID

SYS      PK_TEST_ TEST_TAB   4005  1000000      899       1112 2002-01-01 01:09:55     2091 NO        874010
         T1

SYS      IND_TEST TEST_TAB   4005  1000000    89998         11 2002-01-01 02:28:48     2231 NO        998605
         _T2

SYS      BMP_TEST TEST_TAB   4005    17576    17576          1 2002-01-01 01:41:24      487 NO         17576
         _STR1

SYS      IND_TEST TEST_TAB   4005  1000000   959277          1 2002-01-01 01:26:36     2370 NO        999730
         _STR2

SYS      IND_TEST TEST_TAB   4005  1000000   993888          1 2002-01-01 02:46:59     2785 NO        999768
         _T1_T2


6 rows selected.

Elapsed: 00:00:00.75


SYS@standby1/2002-01-01 02:47:14>explain plan for select /*+ index(bmp_test_t2*/ * from test_tab where t1 between 100 and 120 and t2 between 12345 and 14556;

Explained.

Elapsed: 00:00:00.00
SYS@standby1/2002-01-01 02:48:03>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3597771286

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |   575 | 10925 |   644   (1)| 00:00:08 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_TAB       |   575 | 10925 |   644   (1)| 00:00:08 |
|*  2 |   INDEX RANGE SCAN          | IND_TEST_T1_T2 |   575 |       |    68   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1">=100 AND "T2">=12345 AND "T1"<=120 AND "T2"<=14556)
       filter("T2"<=14556 AND "T2">=12345)

15 rows selected.

SYS@standby1/2002-01-01 02:49:37>explain plan for select /*+ index(ind_test_t1_t2)*/ * from test_tab where t1 between 100 and 110 and t2 between 12345 and 12556;

Explained.

Elapsed: 00:00:00.00
SYS@standby1/2002-01-01 02:49:51>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 983300099

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |    29 |   551 |    59   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_TAB       |    29 |   551 |    59   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | IND_TEST_T1_T2 |    28 |       |    30   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1">=100 AND "T2">=12345 AND "T1"<=110 AND "T2"<=12556)
       filter("T2"<=12556 AND "T2">=12345)

SYS@standby1/2002-01-01 03:02:29>explain plan for select /*+ index(ind_test_t1_t2)*/ * from test_tab where t1 between 100 and 120 and t2 between 12345 and 15556;

Explained.

Elapsed: 00:00:00.01
SYS@standby1/2002-01-01 03:03:17>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2720818643

--------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |             |   835 | 15865 |       |   534   (2)| 00:00:07 |
|   1 |  TABLE ACCESS BY INDEX ROWID     | TEST_TAB    |   835 | 15865 |       |   534   (2)| 00:00:07 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |             |       |       |       |            |          |
|   3 |    BITMAP AND                    |             |       |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS|             |       |       |       |            |          |
|   5 |      SORT ORDER BY               |             |       |       |   376K|            |          |
|*  6 |       INDEX RANGE SCAN           | PK_TEST_T1  |       |       |       |    51   (0)| 00:00:01 |
|   7 |     BITMAP CONVERSION FROM ROWIDS|             |       |       |       |            |          |
|   8 |      SORT ORDER BY               |             |       |       |   576K|            |          |
|*  9 |       INDEX RANGE SCAN           | IND_TEST_T2 |       |       |       |    82   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("T1">=100 AND "T1"<=120)
   9 - access("T2">=12345 AND "T2"<=15556)

22 rows selected.

Elapsed: 00:00:00.02
SYS@standby1/2002-01-01 03:03:19>create index ind_test_t1_t2 on test_tab(t1,t2);

Index created.

Elapsed: 00:00:04.07
SYS@standby1/2002-01-01 03:03:47>explain plan for select /*+ index(ind_test_t1_t2)*/ * from test_tab where t1 between 100 and 120 and t2 between 12345 and 15556;

Explained.

Elapsed: 00:00:00.00
SYS@standby1/2002-01-01 03:03:52>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3597771286

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |   835 | 15865 |   904   (1)| 00:00:11 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_TAB       |   835 | 15865 |   904   (1)| 00:00:11 |
|*  2 |   INDEX RANGE SCAN          | IND_TEST_T1_T2 |   835 |       |    68   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1">=100 AND "T2">=12345 AND "T1"<=120 AND "T2"<=15556)
       filter("T2"<=15556 AND "T2">=12345)

15 rows selected.



SYS@standby1/2002-01-01 02:54:10>alter index ind_test_t1_t2 unusable;

Index altered.

Elapsed: 00:00:00.11
SYS@standby1/2002-01-01 02:54:12>explain plan for select /*+ index(bmp_test_t1_t2)*/ * from test_tab where t1 between 100 and 120 and t2 between 12345 and 12556;

Explained.

Elapsed: 00:00:00.00
SYS@standby1/2002-01-01 02:54:19>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1734833153

--------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |             |    55 |  1045 |       |   169   (3)| 00:00:03 |
|   1 |  TABLE ACCESS BY INDEX ROWID     | TEST_TAB    |    55 |  1045 |       |   169   (3)| 00:00:03 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |             |       |       |       |            |          |
|   3 |    BITMAP AND                    |             |       |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS|             |       |       |       |            |          |
|   5 |      SORT ORDER BY               |             |       |       |       |            |          |
|*  6 |       INDEX RANGE SCAN           | IND_TEST_T2 |       |       |       |     8   (0)| 00:00:01 |
|   7 |     BITMAP CONVERSION FROM ROWIDS|             |       |       |       |            |          |
|   8 |      SORT ORDER BY               |             |       |       |   376K|            |          |
|*  9 |       INDEX RANGE SCAN           | PK_TEST_T1  |       |       |       |    51   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("T2">=12345 AND "T2"<=12556)
   9 - access("T1">=100 AND "T1"<=120)

22 rows selected.



SYS@standby1/2002-01-01 03:01:57>explain plan for select /*+ index(ind_test_t1_t2*/ * from test_tab where t1 between 100 and 120 and t2 between 12345 and 15556;

Explained.

Elapsed: 00:00:00.01
SYS@standby1/2002-01-01 03:02:26>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2720818643

--------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |             |   835 | 15865 |       |   534   (2)| 00:00:07 |
|   1 |  TABLE ACCESS BY INDEX ROWID     | TEST_TAB    |   835 | 15865 |       |   534   (2)| 00:00:07 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |             |       |       |       |            |          |
|   3 |    BITMAP AND                    |             |       |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS|             |       |       |       |            |          |
|   5 |      SORT ORDER BY               |             |       |       |   376K|            |          |
|*  6 |       INDEX RANGE SCAN           | PK_TEST_T1  |       |       |       |    51   (0)| 00:00:01 |
|   7 |     BITMAP CONVERSION FROM ROWIDS|             |       |       |       |            |          |
|   8 |      SORT ORDER BY               |             |       |       |   576K|            |          |
|*  9 |       INDEX RANGE SCAN           | IND_TEST_T2 |       |       |       |    82   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("T1">=100 AND "T1"<=120)
   9 - access("T2">=12345 AND "T2"<=15556)

SYS@standby1/2002-01-01 03:12:35>explain plan for select /*+ index(ind_test_t1_t2)*/ * from test_tab where t1 between 100 and 104 and t2 between 12345 and 12556 or str1 like 'AA%';

Explained.

Elapsed: 00:00:00.01
SYS@standby1/2002-01-01 03:13:29>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 885139567

---------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |               |   129 |  2451 |    53   (6)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID      | TEST_TAB      |   129 |  2451 |    53   (6)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS     |               |       |       |            |          |
|   3 |    BITMAP OR                      |               |       |       |            |          |
|   4 |     BITMAP MERGE                  |               |       |       |            |          |
|*  5 |      BITMAP INDEX RANGE SCAN      | BMP_TEST_STR1 |       |       |            |          |
|   6 |     BITMAP AND                    |               |       |       |            |          |
|   7 |      BITMAP CONVERSION FROM ROWIDS|               |       |       |            |          |
|   8 |       SORT ORDER BY               |               |       |       |            |          |
|*  9 |        INDEX RANGE SCAN           | IND_TEST_T2   |       |       |     8   (0)| 00:00:01 |
|  10 |      BITMAP CONVERSION FROM ROWIDS|               |       |       |            |          |
|  11 |       SORT ORDER BY               |               |       |       |            |          |
|* 12 |        INDEX RANGE SCAN           | PK_TEST_T1    |       |       |    14   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("STR1" LIKE 'AA%')
       filter("STR1" LIKE 'AA%' AND "STR1" LIKE 'AA%')
   9 - access("T2">=12345 AND "T2"<=12556)
  12 - access("T1">=100 AND "T1"<=104)



SYS@standby1/2002-01-01 03:17:10>explain plan for select /*+ index(ind_test_t1_t2)*/ * from test_tab where t1 between 100 and 104 and t2 between 12345 and 15556 or str1 like 'A%';

Explained.

Elapsed: 00:00:00.00
SYS@standby1/2002-01-01 03:17:28>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3962208483

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 29880 |   554K|  1108   (2)| 00:00:14 |
|*  1 |  TABLE ACCESS FULL| TEST_TAB | 29880 |   554K|  1108   (2)| 00:00:14 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("STR1" LIKE 'A%' OR "T1"<=104 AND "T2"<=15556 AND
              "T2">=12345 AND "T1">=100)

14 rows selected.

Elapsed: 00:00:00.02
SYS@standby1/2002-01-01 03:17:29>explain plan for select /*+ index(ind_test_t1_t2)*/ * from test_tab where t1 between 100 and 104 and t2 between 12345 and 15556 or str1 like 'AA%';

Explained.

Elapsed: 00:00:00.01
SYS@standby1/2002-01-01 03:17:35>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1096960729

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |               |   315 |  5985 |       |   309   (2)| 00:00:04 |
|   1 |  TABLE ACCESS BY INDEX ROWID      | TEST_TAB      |   315 |  5985 |       |   309   (2)| 00:00:04 |
|   2 |   BITMAP CONVERSION TO ROWIDS     |               |       |       |       |            |          |
|   3 |    BITMAP OR                      |               |       |       |       |            |          |
|   4 |     BITMAP AND                    |               |       |       |       |            |          |
|   5 |      BITMAP CONVERSION FROM ROWIDS|               |       |       |       |            |          |
|   6 |       SORT ORDER BY               |               |       |       |       |            |          |
|*  7 |        INDEX RANGE SCAN           | PK_TEST_T1    |       |       |       |    14   (0)| 00:00:01 |
|   8 |      BITMAP CONVERSION FROM ROWIDS|               |       |       |       |            |          |
|   9 |       SORT ORDER BY               |               |       |       |   576K|            |          |
|* 10 |        INDEX RANGE SCAN           | IND_TEST_T2   |       |       |       |    82   (0)| 00:00:01 |
|  11 |     BITMAP MERGE                  |               |       |       |       |            |          |
|* 12 |      BITMAP INDEX RANGE SCAN      | BMP_TEST_STR1 |       |       |       |            |          |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   7 - access("T1">=100 AND "T1"<=104)
  10 - access("T2">=12345 AND "T2"<=15556)
  12 - access("STR1" LIKE 'AA%')
       filter("STR1" LIKE 'AA%' AND "STR1" LIKE 'AA%')

27 rows selected.

Elapsed: 00:00:00.02

SYS@standby1/2002-01-01 03:24:53>explain plan for select /*+ index(ind_test_str1)*/ * from test_tab where   str1='AAA';

Explained.

Elapsed: 00:00:00.02
SYS@standby1/2002-01-01 03:25:15>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1957485552

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |    57 |  1083 |    13   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | TEST_TAB      |    57 |  1083 |    13   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS|               |       |       |            |          |
|*  3 |    BITMAP INDEX SINGLE VALUE | BMP_TEST_STR1 |       |       |            |          |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("STR1"='AAA')

15 rows selected.

Elapsed: 00:00:00.03
SYS@standby1/2002-01-01 03:25:16>explain plan for select /*+ index(ind_test_str1)*/ * from test_tab where (t1 is  null and t2 is  null) or  str1='AAA';

Explained.

Elapsed: 00:00:00.01
SYS@standby1/2002-01-01 03:25:30>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3962208483

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |    57 |  1083 |  1103   (2)| 00:00:14 |
|*  1 |  TABLE ACCESS FULL| TEST_TAB |    57 |  1083 |  1103   (2)| 00:00:14 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("STR1"='AAA' OR "T1" IS NULL AND "T2" IS NULL)

13 rows selected.

SYS@standby1/2002-01-01 03:25:32>create bitmap index ind_test_t1_t2 on test_tab(t1,t2);

Index created.

Elapsed: 00:00:15.31
SYS@standby1/2002-01-01 03:27:06>explain plan for select /*+ index(ind_test_str1)*/ * from test_tab where (t1 is  null and t2 is  null) or  str1='AAA';

Explained.

Elapsed: 00:00:00.00
SYS@standby1/2002-01-01 03:27:13>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2608760277

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |    57 |  1083 |    15   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | TEST_TAB       |    57 |  1083 |    15   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS|                |       |       |            |          |
|   3 |    BITMAP OR                 |                |       |       |            |          |
|*  4 |     BITMAP INDEX SINGLE VALUE| BMP_TEST_STR1  |       |       |            |          |
|*  5 |     BITMAP INDEX SINGLE VALUE| IND_TEST_T1_T2 |       |       |            |          |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("STR1"='AAA')
   5 - access("T1" IS NULL AND "T2" IS NULL)
       filter("T2" IS NULL AND "T1" IS NULL)


SYS@standby1/2002-01-01 03:31:43>explain plan for select /*+ index(ind_test_str1)*/ * from test_tab where t1 between 100 and 114  or  str1='AAA';

Explained.

Elapsed: 00:00:00.01
SYS@standby1/2002-01-01 03:31:51>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3174527751

----------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |               | 16758 |   310K|       |   990   (1)| 00:00:12 |
|   1 |  TABLE ACCESS BY INDEX ROWID     | TEST_TAB      | 16758 |   310K|       |   990   (1)| 00:00:12 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |               |       |       |       |            |          |
|   3 |    BITMAP OR                     |               |       |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS|               |       |       |       |            |          |
|   5 |      SORT ORDER BY               |               |       |       |   272K|            |          |
|*  6 |       INDEX RANGE SCAN           | PK_TEST_T1    |       |       |       |    37   (0)| 00:00:01 |
|*  7 |     BITMAP INDEX SINGLE VALUE    | BMP_TEST_STR1 |       |       |       |            |          |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("T1">=100 AND "T1"<=114)
   7 - access("STR1"='AAA')

20 rows selected.

SYS@standby1/2002-01-01 03:31:52>drop index pk_test_t1;

Index dropped.

Elapsed: 00:00:00.04


SYS@standby1/2002-01-01 03:33:08>drop index ind_test_t1_t2;

Index dropped.

Elapsed: 00:00:00.03

SYS@standby1/2002-01-01 03:33:21>explain plan for select /*+ no_index(ind_test_t1_t2)*/ * from test_tab where t1 between 100 and 114  or  str1='AAA';

Explained.

Elapsed: 00:00:00.00
SYS@standby1/2002-01-01 03:33:24>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3962208483

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 16758 |   310K|  1105   (2)| 00:00:14 |
|*  1 |  TABLE ACCESS FULL| TEST_TAB | 16758 |   310K|  1105   (2)| 00:00:14 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T1"<=114 AND "T1">=100 OR "STR1"='AAA')


SYS@standby1/2002-01-01 03:35:09>explain plan for select /*+ no_index(ind_test_t1_t2)*/ * from test_tab where t1 between 100 and 101  or  str1='AAA';

Explained.

Elapsed: 00:00:00.00
SYS@standby1/2002-01-01 03:35:26>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3962208483

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |  2283 | 43377 |  1105   (2)| 00:00:14 |
|*  1 |  TABLE ACCESS FULL| TEST_TAB |  2283 | 43377 |  1105   (2)| 00:00:14 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T1"<=101 AND "T1">=100 OR "STR1"='AAA')

13 rows selected.

Elapsed: 00:00:00.02
SYS@standby1/2002-01-01 03:35:27>create index ind_test_t1 on test_tab(t1);

Index created.

Elapsed: 00:00:03.10
SYS@standby1/2002-01-01 03:36:25>explain plan for select /*+ no_index(ind_test_t1_t2)*/ * from test_tab where t1 between 100 and 101  or  str1='AAA';

Explained.

Elapsed: 00:00:00.00
SYS@standby1/2002-01-01 03:36:28>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2585452176

--------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |               |  2283 | 43377 |   370   (1)| 00:00:05 |
|   1 |  TABLE ACCESS BY INDEX ROWID     | TEST_TAB      |  2283 | 43377 |   370   (1)| 00:00:05 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |               |       |       |            |          |
|   3 |    BITMAP OR                     |               |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS|               |       |       |            |          |
|   5 |      SORT ORDER BY               |               |       |       |            |          |
|*  6 |       INDEX RANGE SCAN           | IND_TEST_T1   |       |       |     7   (0)| 00:00:01 |
|*  7 |     BITMAP INDEX SINGLE VALUE    | BMP_TEST_STR1 |       |       |            |          |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("T1">=100 AND "T1"<=101)
   7 - access("STR1"='AAA')

SYS@standby1/2002-01-01 03:37:31>explain plan for select /*+ no_index(ind_test_t1_t2)*/ * from test_tab where t1 between 100 and 101 or t1 is  null  or  str1='AAA';

Explained.

Elapsed: 00:00:00.00
SYS@standby1/2002-01-01 03:39:13>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3962208483

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |  2283 | 43377 |  1106   (2)| 00:00:14 |
|*  1 |  TABLE ACCESS FULL| TEST_TAB |  2283 | 43377 |  1106   (2)| 00:00:14 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T1"<=101 AND "T1">=100 OR "STR1"='AAA' OR "T1" IS NULL)










来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24890594/viewspace-694177/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-11-08

  • 博文量
    32
  • 访问量
    52395