Mega Code Archive
Analyze compute statistics on table with index
<![CDATA[
SQL>
SQL> create table I1(n number primary key, v varchar2(10));
Table created.
SQL> create table I2(n number primary key, v varchar2(10));
Table created.
SQL>
SQL> create table MAP
2 (n number primary key,
3 i1 number referencing I1(n),
4 i2 number referencing I2(n));
Table created.
SQL>
SQL> create unique index IDX_MAP on MAP(i1, i2);
Index created.
SQL> set autotrace traceonly
SQL>
SQL> select * from i1,map,i2
2 where i1.n = map.i1
3 and i2.n = map.i2
4 and i1.v = 'x'
5 and i2.v = 'y';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3070822050
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 79 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 59 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | MAP | 1 | 39 | 2 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| I1 | 1 | 20 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | SYS_C008275 | 1 | | 1 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID | I2 | 1 | 20 | 1 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | SYS_C008276 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("I1"."V"='x')
5 - access("I1"."N"="MAP"."I1")
6 - filter("I2"."V"='y')
7 - access("I2"."N"="MAP"."I2")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
44 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
578 bytes sent via SQL*Net to client
369 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
SQL> analyze table i1 compute statistics;
Table analyzed.
SQL>
SQL> analyze table i2 compute statistics;
Table analyzed.
SQL>
SQL> analyze table map compute statistics;
Table analyzed.
SQL>
SQL> select * from i1,map,i2
2 where i1.n = map.i1
3 and i2.n = map.i2
4 and i1.v = 'x'
5 and i2.v = 'y';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1158434662
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 79 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 59 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | I1 | 1 | 20 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| MAP | 1 | 39 | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_MAP | 1 | | 1 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID | I2 | 1 | 20 | 1 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | SYS_C008276 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("I1"."V"='x')
5 - access("I1"."N"="MAP"."I1")
6 - filter("I2"."V"='y')
7 - access("I2"."N"="MAP"."I2")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
578 bytes sent via SQL*Net to client
369 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
SQL> set autotrace off
SQL>
SQL> create index i1_idx on i1(v);
Index created.
SQL>
SQL> analyze table i1 compute statistics;
Table analyzed.
SQL>
SQL> set autotrace traceonly
SQL>
SQL> select * from i1,map,i2
2 where i1.n = map.i1
3 and i2.n = map.i2
4 and i1.v = 'x'
5 and i2.v = 'y';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1388106715
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 79 | 3 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 59 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| I1 | 1 | 20 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | I1_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| MAP | 1 | 39 | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IDX_MAP | 1 | | 1 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID | I2 | 1 | 20 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | SYS_C008276 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("I1"."V"='x')
6 - access("I1"."N"="MAP"."I1")
7 - filter("I2"."V"='y')
8 - access("I2"."N"="MAP"."I2")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
578 bytes sent via SQL*Net to client
369 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
SQL> set autotrace off
]]>