Unique index card 10

SQL> @ indexcard.sql
SQL> whenever sqlerror exit 1
SQL> set linesize 100
SQL> set trimspool on
SQL> set echo on
SQL> variable x number
SQL> exec :x := 0

PL/SQL procedure successfully completed.

SQL> drop table puzzle;

Table dropped.

SQL>
SQL> create table puzzle (
  2  k number,
  3  l number
  4  );

Table created.

SQL> create unique index puzzle_pk on puzzle ( k  );

Index created.

SQL> insert into puzzle ( k, l ) select level, 0 from dual connect by level <= 10000;

10000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats( ownname=>user, tabname=>'PUZZLE',cascade=>true )

PL/SQL procedure successfully completed.

SQL> delete from puzzle;

10000 rows deleted.

SQL> commit;

Commit complete.

SQL> insert into puzzle ( k, l ) select level, 0 from dual connect by level <= 1000;

1000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats( ownname=>user, tabname=>'PUZZLE',cascade=>false )

PL/SQL procedure successfully completed.

SQL> select num_rows from user_tables where table_name = 'PUZZLE';

  NUM_ROWS
----------
      1000

SQL> select num_rows from user_indexes where index_name = 'PUZZLE_PK';

  NUM_ROWS
----------
     10000

SQL>
SQL> explain plan for select * from puzzle where k = :x;

Explained.

SQL> select * from table ( dbms_xplan.display );

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1561462423

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |     6 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| PUZZLE    |     1 |     6 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PUZZLE_PK |    10 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   2 - access("K"=TO_NUMBER(:X))

14 rows selected.

SQL> explain plan for select * from puzzle where k in ( select rownum from dual );

Explained.

SQL> select * from table ( dbms_xplan.display );

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 4207056078

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |     1 |    19 |     4  (25)| 00:00:01 |
|   1 |  NESTED LOOPS                |           |     1 |    19 |     4  (25)| 00:00:01 |
|   2 |   VIEW                       | VW_NSO_1  |     1 |    13 |     3  (34)| 00:00:01 |
|   3 |    COUNT                     |           |       |       |            |          |
|   4 |     FAST DUAL                |           |     1 |       |     2   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| PUZZLE    |     1 |     6 |     1   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN         | PUZZLE_PK |    10 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   6 - access("K"="ROWNUM")

Note
-----
   - this is an adaptive plan

22 rows selected.