Quiz: Explaining index creation

Did you know that it’s possible to use EXPLAIN PLAN FOR CREATE INDEX ON table(col1,col2,col3) syntax for explaining what exactly would be done when an index is created?

That’s useful for example for seeing the Oracle’s estimated index size without having to actually create the index.

You can also use EXPLAIN PLAN FOR ALTER INDEX i REBUILD to see whether this operation would use a FULL TABLE SCAN or a FAST FULL INDEX SCAN (offline index rebuilds of valid indexes can use this method).

Anyway, you can experiment with this yourself, but here’s a little quiz (with a little gotcha :)

What kind of index creation statement would create such an execution plan?

SQL> explain plan for create index hack_index on hack_table ....the rest is a secret for now....
Explained.

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 457720527
-------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT   |                      | 88868 |       |   657   (1)| 00:00:12 |
|   1 |  INDEX BUILD NON UNIQUE  | HACK_INDEX           |       |       |            |          |
|   2 |   SORT AGGREGATE         |                      |     1 |       |            |          |
|   3 |    VIEW                  | HACK_VIEW            | 74062 |       |   318   (1)| 00:00:06 |
|*  4 |     HASH JOIN            |                      | 74062 |  1012K|   210   (1)| 00:00:04 |
|   5 |      TABLE ACCESS FULL   | TEST_USERS           |    46 |   368 |     4   (0)| 00:00:01 |
|   6 |      INDEX FAST FULL SCAN| IDX2_INDEXED_OBJECTS | 74062 |   433K|   206   (1)| 00:00:04 |
|   7 |   SORT CREATE INDEX      |                      | 88868 |       |            |          |
|   8 |    TABLE ACCESS FULL     | HACK_TABLE           | 88868 |       |   544   (1)| 00:00:10 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("U"."USERNAME"="O"."OWNER")
Note
-----
- estimated index size: 3145K bytes

This entry was posted in Cool stuff, Oracle and tagged , , . Bookmark the permalink.

13 Responses to Quiz: Explaining index creation

  1. Boris says:

    Bitmap join index

  2. Tanel Poder says:

    @Boris

    Nope, in bitmap (join) index creation case you would see BITMAP CONSTRUCTION somewhere in the execution plan!

  3. Marcus Mönnig says:

    Due to the access to aggregate access to HACK_VIEW I’d say it’s a function based index.

  4. Thyge G. Jacobsen says:

    Hi Tanel,
    Interesting quiz. Looking forward to the solution;-)
    Note: DBMS_SPACE can also be used to estimate the size for future indexes and tables.
    Regards
    Thyge

  5. Marcus Mönnig says:

    @Marcus Mönnig

    Should be something like this:

    create index hack_index on hack_table(nvl(,select max() from Hack_view));

  6. Marcus Mönnig says:

    @Marcus Mönnig

    Should be something like this:
    create index hack_index on hack_table(nvl(column,select max(num_column) from Hack_view));

    (Sorry for the double post, less than and greater than signs didn’t make it through…)

  7. – DROP USER t CASCADE;
    GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO t IDENTIFIED BY t;
    ALTER USER t QUOTA 1M ON users;
    SET TRIMSPOOL ON TAB OFF PAGES 1000 LINES 1000 ECHO ON
    ALTER SESSION SET current_schema = T;
    SPOOL t.lst
    CREATE TABLE test_users(username) AS SELECT username FROM dba_users;
    CREATE TABLE hack_table(owner, table_name) AS SELECT table_owner, table_name FROM dba_indexes;
    CREATE INDEX idx2_indexed_objects ON hack_table(owner);
    CREATE VIEW hack_view AS SELECT username, table_name FROM hack_table, test_users WHERE username = owner;
    EXPLAIN PLAN FOR CREATE INDEX hack_index ON hack_table((SELECT MAX(username) FROM hack_view));
    SELECT * FROM TABLE(dbms_xplan.display());
    SPOOL OFF

  8. Tanel Poder says:

    Marcus & Vladimir, yeah, you are correct!

    Apparently the index creation syntax allows to use scalar subqueries, but only the syntax allows that, indexes aren’t meant to be used that way…

    Vladimir, your example is almost exactly the one I used, instead of MAX I just used COUNT(*).

    Did you try to query / modify that table after index creation? I got ORA-600’s :-)

  9. yes, it was failing, I’m going to follow up with the right team.

  10. Tanel Poder says:

    @Vladimir Begun
    Cool, thanks!

    Another fun use case for my ORA-600 troubleshooting test cases ;-)

  11. @Tanel Poder
    #9666169, it as a low priority though

    ORA-600s are boring :-) try solving this instead.

  12. Bruce says:

    Marcus & Vladimir, yeah, you are correct!

    Apparently the index creation syntax allows to use scalar subqueries, but only the syntax allows that, indexes aren’t meant to be used that way…

    Vladimir, your example is almost exactly the one I used, instead of MAX I just used COUNT(*).

    Did you try to query / modify that table after index creation? I got ORA-600’s :-)

  13. Amy says:

    @Vladimir Begun
    Cool, thanks!

    Another fun use case for my ORA-600 troubleshooting test cases ;-)

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>