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)
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/，如需转载，请注明出处，否则将追究法律责任。
上一篇：
clustor factor 组合索引
下一篇：
Oracle Sql提示学习笔记（转载）
请登录后发表评论
登录
全部评论
