Shulou(Shulou.com)06/01 Report--

If you want to create a large index, but do not want to allocate space for it, but first determine whether the optimizer will choose to use the index, then you can use NOSEGMENT to create the index in 11g. If you determine that the index is useful, you can delete the index and then rebuild it using a statement that does not contain NOSEGMENT.

SQL > create index idx_emp on emp (employee_id) nosegment

Index created.

SQL > set autot traceonly

SQL > select * from APP.EMP t where t.employeeemployees

Execution Plan

Plan hash value: 3956160932

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 1 | 133 | 3 (0) | 00:00:01 |

| | * 1 | TABLE ACCESS FULL | EMP | 1 | 133 | 3 (0) | 00:00:01 |

Predicate Information (identified by operation id):

1-filter ("T". "EMPLOYEE_ID" = 105)

Note

-

-dynamic sampling used for this statement (level=2)

Statistics

4 recursive calls

0 db block gets

10 consistent gets

0 physical reads

0 redo size

1305 bytes sent via SQL*Net to client

519 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL > set autot off

SQL > ALTER SESSION SET "_ use_nosegment_indexes" = true; set autot traceonly

SQL > set lines 900

SQL > select * from APP.EMP t where t.employeeemployees

Execution Plan

Plan hash value: 306890541

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 1 | 133 | 2 (0) | 00:00:01 |

| | 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 133 | 2 (0) | 00:00:01 |

| | * 2 | INDEX RANGE SCAN | IDX_EMP | 1 | | 1 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

2-access ("T". "EMPLOYEE_ID" = 105)

Note

-

-dynamic sampling used for this statement (level=2)

Statistics

0 recursive calls

0 db block gets

5 consistent gets

0 physical reads

0 redo size

1305 bytes sent via SQL*Net to client

519 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.