Re: The 20% rule

On Jul 25, 4:37 pm, DA Morgan <damor..._at_psoug.org> wrote:
> One of the issues I have with the demo I posted, and as I indicated
> I only posted it to prove the rule was mythology, is that the bytes
> for an index row greater than for a table row. One can easily reverse
> that with something like this:
>
> CREATE TABLE index_demo (
> person_id NUMBER(10),
> gender    VARCHAR2(1),
> state     VARCHAR2(2),
> textcol   VARCHAR2(2000));
>
> DECLARE
>   g index_demo.gender%TYPE := 'F';
> BEGIN
>    FOR i IN 1 .. 250000
>    LOOP
>      INSERT INTO index_demo
>      (person_id, gender, state, textcol)
>      VALUES
>      (i, g, 'WA', RPAD('x', 1799, 'x'));
>
>      IF g = 'F' THEN
>        g := 'M';
>      ELSE
>        g := 'F';
>      END IF;
>    END LOOP;
>    COMMIT;
>
>    UPDATE index_demo
>    SET state = 'OR'
>    WHERE person_id LIKE '%1';
>
>    UPDATE index_demo
>    SET state = 'CA'
>    WHERE person_id LIKE '%2';
>
>    UPDATE index_demo
>    SET state = 'ID'
>    WHERE person_id LIKE '%3';
>
>    UPDATE index_demo
>    SET state = 'NY'
>    WHERE person_id LIKE '%4';
>
>    UPDATE index_demo
>    SET state = 'MA'
>    WHERE person_id LIKE '%5';
>
>    UPDATE index_demo
>    SET state = 'MN'
>    WHERE person_id LIKE '%6';
>
>    UPDATE index_demo
>    SET state = 'VA'
>    WHERE person_id LIKE '%7';
>
>    UPDATE index_demo
>    SET state = 'NC'
>    WHERE person_id LIKE '%8';
>
>    UPDATE index_demo
>    SET state = 'MI'
>    WHERE person_id like '%9';
>
>    COMMIT;
> END;
> /
>
> I use them in demos of bitmap indexes in part to demonstrate
> why gender is not the ideal usage. Some people may want to
> use it to construct test data of their own.
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -

Thanks for providing PL/SQL code.

I started running the script and then started performing some calculations while I was waiting for the PL/SQL code to complete. At a little less than 2,000 bytes per row, there would be roughly 4 rows per 8KB block. 8KB * 250,000/4 = ~512MB. Then I started wondering why you were using PL/SQL rather than SQL (possibly to test some other performance metric during the updates?). For example: INSERT INTO INDEX_DEMO
  (PERSON_ID,
  GENDER,
  STATE,
  TEXTCOL)
SELECT
  ROWNUM,
  DECODE(MOD(ROWNUM,2),0,'M','F'),
  DECODE(MOD(ROWNUM,10),1,'OR',2,'CA',3,'ID',4,'NY',5,'MA',6,'MN', 7,'VA',8,'NC',9,'MI','WA'),
  RPAD('x', 1799, 'x')
FROM
  (SELECT
    ROWNUM RN
  FROM
    DUAL
  CONNECT BY
    LEVEL<=250) T1,
  (SELECT
    ROWNUM RN
  FROM
    DUAL
  CONNECT BY
    LEVEL<=1000) T2;

Your test setup could be interesting - I wonder if you would need more than 10 states in the data set to determine the tipping point percentage were an index scan or table scan would be more efficient to retrieve a particular subset of data in a particular database instance.

Would you need multiple bitmap indexes on multiple columns (with one column per bitmap index) in the hope that predicates would be applied to multiple columns in order to allow bitmap combining operations in order to effectively use bitmap indexes on columns with few distinct values? I thought that I remembered reading an article by Richard Foote that showed demonstrated such a use of bitmap indexes.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri Jul 25 2008 - 20:39:17 CDT