Hotsos Symposium Speakers and Topics 2015

WOOT-WOOT!  

The selections have been made!  Check out the list and start drawing up your strategy to get the most out of the Symposium. Another great one!!

Who's your favorite? 

http://www.hotsos.com/sym15/sym_speakers.html

Selecting one row

If the table is small do I really need an index to select just one row?

Yes you do.  Here is a simple test you can use yourself to prove this to anyone one who asks.

If I have a small table with say 100 rows that all fit in a single block, it seems reasonable that a full table scan to find the one row is just fine.  After all it’s just one block so what does it matter?  Here is a simple table and some code to fill it with just 100 rows, and they all fit in one 8K block.

CREATE TABLE ONEHUNDREDROWS (ID NUMBER, FILLER VARCHAR2(2))

/

BEGIN

  FOR X IN 1..100 LOOP

      INSERT INTO ONEHUNDREDROWS VALUES (X,'AA');

  END LOOP;

END;

/

Once the table is created you can run this to see that all the rows are in one block, this returns the distinct block numbers in all the ROWIDs for the table:

SQL> SELECT distinct dbms_rowid.rowid_block_number(ROWID, 'BIGFILE') BLOCK from onehundredrows;

          BLOCK

---------------

       26209107

Now here is a block of code to select one row from this table, 5000 times.  The block will time how much elapsed and CPU time are consumed when doing this.   The select used after running the block will show use the resources used.  Of particular interest to us is the BUFFERs column, this shows how many LIOs were done.  Make sure STATISTICS_LEVEL is set to all. 

ALTER SESSION SET STATISTICS_LEVEL=ALL

/

create or replace procedure one_row_test is

   l_start_time pls_integer;

   l_start_cpu  pls_integer;

   y varchar2(2);

begin

   l_start_time := DBMS_UTILITY.GET_TIME;

   l_start_cpu  := DBMS_UTILITY.GET_CPU_TIME;

   for i in 1 .. 5000 loop

      select filler into y from onehundredrows where id = 42;

   end loop;

   DBMS_OUTPUT.put_line ('******* Select one row 5000 times *******');

   DBMS_OUTPUT.put_line ('Times in hundredths of a second');

   DBMS_OUTPUT.put_line ('**** TIME   - '||to_char(DBMS_UTILITY.get_time - l_start_time));

   DBMS_OUTPUT.put_line ('**** CPU    - '||to_char(DBMS_UTILITY.GET_CPU_TIME - l_start_cpu));

end;

/

EXEC ONE_ROW_TEST

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('9mxb9qk546vu6',NULL,'ALLSTATS LAST'))

/

Here is a run doing a full table scan:

SQL> EXEC ONE_ROW_TEST

******* Select one row 5000 times *******

Times in hundredths of a second

**** TIME   - 26

**** CPU    - 25

SQL>

SQL>

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('9mxb9qk546vu6',NULL,'ALLSTATS LAST'))

  2  /

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------------------------------------------

SQL_ID  9mxb9qk546vu6, child number 0

-------------------------------------

SELECT FILLER FROM ONEHUNDREDROWS WHERE ID = 42

Plan hash value: 35615928

----------------------------------------------------------------------------------------------

| Id  | Operation         | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |                |      1 |        |      1 |00:00:00.01 |       7 |

|*  1 |  TABLE ACCESS FULL| ONEHUNDREDROWS |      1 |      1 |      1 |00:00:00.01 |       7 |

----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("ID"=42)

Note

-----

   - dynamic statistics used: dynamic sampling (level=2)

Notice that the buffers is 7 for the full scan even though all 100 rows fit in one block, the scan has to go all the way to the high water mark of the table.  

Now let’s run the same thing but this time there will be an index on the ID column:

SQL> create index one_id on onehundredrows(id)

  2  /

SQL>

SQL>

SQL> EXEC ONE_ROW_TEST

******* Select one row 5000 times *******

Times in hundredths of a second

**** TIME   - 22

**** CPU    - 22

SQL>

SQL>

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('9mxb9qk546vu6',NULL,'ALLSTATS LAST'))

  2  /

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------------------------------------------

SQL_ID  9mxb9qk546vu6, child number 0

-------------------------------------

SELECT FILLER FROM ONEHUNDREDROWS WHERE ID = 42

Plan hash value: 3262481358

--------------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

--------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                |      1 |        |      1 |00:00:00.01 |       2 |

|   1 |  TABLE ACCESS BY INDEX ROWID| ONEHUNDREDROWS |      1 |      1 |      1 |00:00:00.01 |       2 |

|*  2 |   INDEX RANGE SCAN          | ONE_ID         |      1 |      1 |      1 |00:00:00.01 |       1 |

--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("ID"=42)

Note

-----

   - dynamic statistics used: dynamic sampling (level=2)

The LIOs for this one is 2.  You may say that what is the big deal from 7 to 2?  True enough on a one run bases, this isn’t much to worry about.  But imagine doing this millions of times.  Now that extra 5 LIOs start to add up.

Also notice that in just 5000 look ups the time goes from 22 to 26 centiseconds, about an 18% increase.  Again doesn’t seem like much but it adds up.  Also what happens if this table does grow over time?  The full scan will continue to look at more and more blocks taking more and more time and resources.  The index scan will likely stay about the same for a long time before it starts to change.  The table could easily be about 10 times the size and the index would still be about the same for LIOs and time.

If the index on ID is unique that the time drops a bit more down to 20 centiseconds, so yes it’s even better to have a unique index just to select a single row from a small table.

SQL> drop index one_id

  2  /

SQL> create unique index one_id on onehundredrows(id)

  2  /

SQL> EXEC ONE_ROW_TEST

******* Select one row 5000 times *******

Times in hundredths of a second

**** TIME   - 20

**** CPU    - 20

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('9mxb9qk546vu6',NULL,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------

SQL_ID  9mxb9qk546vu6, child number 0

-------------------------------------

SELECT FILLER FROM ONEHUNDREDROWS WHERE ID = 42

Plan hash value: 3462298723

--------------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

--------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                |      1 |        |      1 |00:00:00.01 |       2 |

|   1 |  TABLE ACCESS BY INDEX ROWID| ONEHUNDREDROWS |      1 |      1 |      1 |00:00:00.01 |       2 |

|*  2 |   INDEX UNIQUE SCAN         | ONE_ID         |      1 |      1 |      1 |00:00:00.01 |       1 |

--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("ID"=42)