1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

indexing help

Discussion in 'General' started by dsub42, May 10, 2010.

  1. dsub42

    dsub42 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    Hi I am new to the whole area of performance and indexing.

    im using the following statement:

    select * from tblavailability where personid = 248;

    This returns 208 rows out of 10,000 in the table

    =========================================================================

    I want to attempt to improve the performance of this query, so I have added an index on the 'personid' column

    =========================================================================

    The results with no index are as follows (test ran 3 times):

    Statistics
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    82 consistent gets
    0 physical reads
    0 redo size
    8390 bytes sent via SQL*Net to client
    559 bytes received via SQL*Net from client
    15 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    208 rows processed

    timing for: timer18
    Elapsed: 00:00:00.01

    Statistics
    ----------------------------------------------------------
    1 recursive calls
    0 db block gets
    82 consistent gets
    0 physical reads
    0 redo size
    8390 bytes sent via SQL*Net to client
    559 bytes received via SQL*Net from client
    15 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    208 rows processed

    timing for: timer18
    Elapsed: 00:00:00.01

    Statistics
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    82 consistent gets
    0 physical reads
    0 redo size
    8390 bytes sent via SQL*Net to client
    559 bytes received via SQL*Net from client
    15 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    208 rows processed

    timing for: timer18
    Elapsed: 00:00:00.01

    =========================================================================
    The results from the same query with the index added are as follows (ran 3 times)

    Statistics
    ----------------------------------------------------------
    1 recursive calls
    0 db block gets
    33 consistent gets
    0 physical reads
    0 redo size
    12595 bytes sent via SQL*Net to client
    559 bytes received via SQL*Net from client
    15 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    208 rows processed

    timing for: timer18
    Elapsed: 00:00:00.01

    Statistics
    ----------------------------------------------------------
    1 recursive calls
    0 db block gets
    33 consistent gets
    0 physical reads
    0 redo size
    12595 bytes sent via SQL*Net to client
    559 bytes received via SQL*Net from client
    15 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    208 rows processed

    timing for: timer18
    Elapsed: 00:00:00.01

    Statistics
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    33 consistent gets
    0 physical reads
    0 redo size
    12595 bytes sent via SQL*Net to client
    559 bytes received via SQL*Net from client
    15 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    208 rows processed

    timing for: timer18
    Elapsed: 00:00:00.01

    ==========================================================================
    Im struggeling to see how I can tell if the index has helped improve performance of that query?... as the time is the same!

    The only other way I figured I could see a difference would be running the following 2 statements prior to each test..

    ALTER SYSTEM FLUSH BUFFER_CACHE;
    ALTER SYSTEM FLUSH SHARED_POOL;

    this clearly shows less physical reads on the queries that use the index, but the timings seem quite random for queries with and without the index.

    also I have been told not to do this (im unsure why)

    ====================================================================
    Can anyone help me, ... for the results i posted (caches not cleared).. how can i tell of a performance increase when using the index?

    Any help would be appriciated
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Your table contains 10,000 rows; that may be quite a large number to you but to Oracle it's a small number and you won't see much difference in timings between using an index on 10,000 rows and not using one. Step that up to 1,000,000 rows and you will likely see a difference if the index is actually being accessed. You did not generate an execuition plan for both configurations; not knowing how the table was created (constraints, etc) nor the data distribution it's difficult to actually say how your index will behave. You should do this:

    Code (SQL):
    SET autotrace ON
    SELECT * FROM tblavailability WHERE personid = 248;
     
    Oracle will display the query results and afterwards will reveal the execution plan it took to return those rows. Only then will you know if your index is being accessed.
     
  3. dsub42

    dsub42 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    O.k i have solved that problem, im facing a new problem....
    For example. the statement and tests below, im using an index on the where column but its giving me more consistent gets when i do (as apose to not using it)

    Could you help me analyse whats going on here.... (i would have thaught the index made the query more efficient) ... why when using an idex do i have more consistent gets?

    =========================================================================

    ===================================================================================================================================
    select * from tblrequirements where wardshiftid = 110;

    ===================================================================================================================================

    create index i_req_ws on tblrequirements(wardshiftid);

    239 rows selected.


    Execution Plan
    ----------------------------------------------------------

    ---------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost |
    ---------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 231 | 11781 | 9 |
    | 1 | TABLE ACCESS FULL| TBLREQUIREMENTS | 231 | 11781 | 9 |
    ---------------------------------------------------------------------

    Note
    -----
    - 'PLAN_TABLE' is old version


    Statistics
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    47 consistent gets
    0 physical reads
    0 redo size
    10519 bytes sent via SQL*Net to client
    581 bytes received via SQL*Net from client
    17 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    239 rows processed

    timing for: timer21
    Elapsed: 00:00:00.01


    #########################################################create index i_req_ws on tblrequirements(wardshiftid);###########################

    begin
    dbms_stats.gather_schema_stats(
    ownname => 'bsms'
    ,estimate_percent => null
    ,cascade => true
    ,method_opt => 'FOR ALL COLUMNS SIZE 5'
    );
    end;
    /

    #########################################################################################################################################

    239 rows selected.


    Execution Plan
    ----------------------------------------------------------

    -------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost |
    -------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 22 | 1122 | 7 |
    | 1 | TABLE ACCESS BY INDEX ROWID| TBLREQUIREMENTS | 22 | 1122 | 7 |
    | 2 | INDEX RANGE SCAN | I_REQ_WS | 22 | | 1 |
    -------------------------------------------------------------------------------

    Note
    -----
    - 'PLAN_TABLE' is old version


    Statistics
    ----------------------------------------------------------
    1 recursive calls
    0 db block gets
    57 consistent gets
    0 physical reads
    0 redo size
    15225 bytes sent via SQL*Net to client
    581 bytes received via SQL*Net from client
    17 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    239 rows processed

    timing for: timer21
    Elapsed: 00:00:00.01
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You now have TWO objects to read, not one, so how can the consistent gets go down? You complain this is inefficient so define what 'efficient' means to you. Indexes are used to speed data retrieval by ensuring that the total number of reads for the index and the table combined do not exceed the number of reads necessary for a full table scan, thus indexes are ideal when 40% or less of the data satisfies the given WHERE condition; the smaller the number of rows returned the better it is to use an index. You did lower the cost of the query by 2 and since cost is time you lowered the time required to return the data. Was it a substantial decrease? No, and you shouldn't expect it to be with your data volume.

    Please read here:

    http://oratips-ddf.blogspot.com/2008/11/magical-indexes.html

    and here:

    http://oratips-ddf.blogspot.com/2008/04/tale-of-two-indexes.html

    to learn more about indexes and how they behave. Read here:

    http://oratips-ddf.blogspot.com/2007/06/table-scans-histograms-and-scarlett.html

    to leran how statistics and histograms can affect whether an index is used or not.
     
  5. dsub42

    dsub42 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    Thanks for those links, im not sure how valid the blog is, see below:
     
  6. dsub42

    dsub42 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    However in the first link, indexes seem to work on columns which allow nulls, the second post then contradicts this.... puzzeling

    I have tried running a test on a table which contains a 'sex' field...

    Name Null Type
    ------------------------------ -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    PERSONID NOT NULL NUMBER(9)
    INACTIVEREASONID NUMBER(9)
    WORKCATID NUMBER(9)
    ORIGINID NUMBER(9)
    AGENCYID NUMBER(9)
    PAGERNO VARCHAR2(20)
    FNAME VARCHAR2(20)
    SNAME VARCHAR2(20)
    SEX VARCHAR2(1)

    ==================================================================

    The data in the sex field is split m/f/null

    ==================================================================

    im using the following query:

    elect fname, sname, personid from tblperson where sex = 'F';

    ==================================================================

    adding the following index and running the SQL i get these results:

    create bitmap index i_person_sex on tblperson(sex);

    ==================================================================

    Execution Plan
    ----------------------------------------------------------

    ---------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost |
    ---------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 323 | 6783 | 7 |
    | 1 | TABLE ACCESS FULL| TBLPERSON | 323 | 6783 | 7 |
    ---------------------------------------------------------------

    Note
    -----
    - 'PLAN_TABLE' is old version


    Statistics
    ----------------------------------------------------------
    1 recursive calls
    0 db block gets
    45 consistent gets
    0 physical reads
    0 redo size
    9458 bytes sent via SQL*Net to client
    647 bytes received via SQL*Net from client
    23 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    323 rows processed

    timing for: timer13
    Elapsed: 00:00:00.01

    =======================================================
    Then changing the index to compensate for null's : create bitmap index i_person_sex on tblperson(sex,0);

    ======================================================


    Execution Plan
    ----------------------------------------------------------

    ---------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost |
    ---------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 323 | 6783 | 7 |
    | 1 | TABLE ACCESS FULL| TBLPERSON | 323 | 6783 | 7 |
    ---------------------------------------------------------------

    Note
    -----
    - 'PLAN_TABLE' is old version


    Statistics
    ----------------------------------------------------------
    1 recursive calls
    0 db block gets
    45 consistent gets
    0 physical reads
    0 redo size
    9458 bytes sent via SQL*Net to client
    647 bytes received via SQL*Net from client
    23 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    323 rows processed

    timing for: timer13
    Elapsed: 00:00:00.00

    ===================================================================================

    So iv done basically what is stated in that blog post, and it does nothing
     
  7. dsub42

    dsub42 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    I have carried out the same test again, using a standard index:

    create index i_person_sex on tblperson(sex);
    create index i_person_sex on tblperson(sex,0);

    I get exactly the same results...
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You did not do what that blog post stated as examples as at no point in that post were bitmap indexes created. Bitmap indexes WILL index NULL values; B-TREE indexes will NOT index entirely NULL keys, which is why one needs to include a non-null column or value with nullable columns IF one wants an index-only access when selecting ONLY the indexed column from the table:

    Code (SQL):
    SQL> @index_ex
    SQL> SET linesize 200 trimspool ON
    SQL>
    SQL> --
    SQL> -- Create table
    SQL> --
    SQL> CREATE TABLE tblperson (PERSONID NUMBER(9) NOT NULL,
      2  INACTIVEREASONID NUMBER(9) ,
      3  WORKCATID NUMBER(9) ,
      4  ORIGINID NUMBER(9) ,
      5  AGENCYID NUMBER(9) ,
      6  PAGERNO VARCHAR2(20),
      7  FNAME VARCHAR2(20) ,
      8  SNAME VARCHAR2(20) ,
      9  SEX VARCHAR2(1)
     10  );
    TABLE created.
    SQL>
    SQL> --
    SQL> -- Load table
    SQL> --
    SQL> BEGIN
      2        FOR i IN 1..10000 loop
      3         INSERT INTO tblperson
      4         VALUES(i, MOD(i,47), MOD(i,79), MOD(i, 776), MOD(i,3),'BR549'||MOD(i,4321),'Test','Fleester', NULL);
      5        END loop;
      6  
      7        commit;
      8  END;
      9  /
    PL/SQL PROCEDURE successfully completed.
    SQL>
    SQL> UPDATE tblperson
      2  SET sex = 'F'
      3  WHERE MOD(personid, 7) = 0;
    1428 ROWS updated.
    SQL>
    SQL> commit;
    Commit complete.
    SQL>
    SQL> UPDATE tblperson
      2  SET sex = 'M'
      3  WHERE sex IS NULL
      4  AND MOD(inactivereasonid, 3) = 1;
    2918 ROWS updated.
    SQL>
    SQL> commit;
    Commit complete.
    SQL>
    SQL> EXEC dbms_stats.gather_table_stats(ownname=>'BING',tabname=>'TBLPERSON', cascade=>TRUE);
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> SET autotrace ON
    SQL>
    SQL> --
    SQL> -- Thiw is not like the query from the post
    SQL> -- but we'll run it anyway
    SQL> --
    SQL>
    SQL> SELECT fname, sname, personid FROM tblperson WHERE sex = 'F';
     
    FNAME                SNAME                  PERSONID
    -------------------- -------------------- ----------
    Test                 Fleester                    336
    Test                 Fleester                    343
    Test                 Fleester                    350
    Test                 Fleester                    357
    Test                 Fleester                    364
    Test                 Fleester                    371
    Test                 Fleester                    378
    Test                 Fleester                    385
    Test                 Fleester                    392
    Test                 Fleester                    399
    Test                 Fleester                    406
    ...
     
    1428 ROWS selected.

    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 257609698
    -------------------------------------------------------------------------------
    | Id  | Operation         | Name      | ROWS  | Bytes | Cost (%CPU)| TIME     |
    -------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |           |  2173 | 43460 |    66  (63)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| TBLPERSON |  2173 | 43460 |    66  (63)| 00:00:01 |
    -------------------------------------------------------------------------------
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       1 - FILTER("SEX"='F')

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
            164  consistent gets
              0  physical reads
              0  redo SIZE
          30903  bytes sent via SQL*Net TO client
           1537  bytes received via SQL*Net FROM client
             97  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
           1428  ROWS processed
    SQL>
    SQL> --
    SQL> -- Create index
    SQL> --
    SQL> CREATE INDEX i_person_sex ON tblperson(sex);
    INDEX created.
    SQL>
    SQL> EXEC dbms_stats.gather_table_stats(ownname=>'BING',tabname=>'TBLPERSON', cascade=>TRUE);
    PL/SQL PROCEDURE successfully completed.
    SQL>
    SQL> --
    SQL> -- Run non-post query again
    SQL> --
    SQL> SELECT fname, sname, personid FROM tblperson WHERE sex = 'F';
    FNAME                SNAME                  PERSONID
    -------------------- -------------------- ----------
    Test                 Fleester                    336
    Test                 Fleester                    343
    Test                 Fleester                    350
    Test                 Fleester                    357
    Test                 Fleester                    364
    Test                 Fleester                    371
    Test                 Fleester                    378
    Test                 Fleester                    385
    Test                 Fleester                    392
    Test                 Fleester                    399
    Test                 Fleester                    406
    ...
     
    1428 ROWS selected.

    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 264409931
    --------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name         | ROWS  | Bytes | Cost (%CPU)| TIME     |
    --------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |              |  1428 | 28560 |    56  (20)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| TBLPERSON    |  1428 | 28560 |    56  (20)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN          | I_PERSON_SEX |  1428 |       |     6  (50)| 00:00:01 |
    --------------------------------------------------------------------------------------------
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       2 - access("SEX"='F')

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
            253  consistent gets
              0  physical reads
              0  redo SIZE
          30903  bytes sent via SQL*Net TO client
           1537  bytes received via SQL*Net FROM client
             97  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
           1428  ROWS processed
    SQL>
    SQL> --
    SQL> -- Run query like the post indicates
    SQL> --
    SQL> SELECT sex FROM tblperson;
    S
    -
    M

    F

    M

    M
    F
    ...

    10000 ROWS selected.

    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 257609698
    -------------------------------------------------------------------------------
    | Id  | Operation         | Name      | ROWS  | Bytes | Cost (%CPU)| TIME     |
    -------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |           | 10000 | 20000 |    61  (60)| 00:00:01 |
    |   1 |  TABLE ACCESS FULL| TBLPERSON | 10000 | 20000 |    61  (60)| 00:00:01 |
    -------------------------------------------------------------------------------

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
            732  consistent gets
              0  physical reads
              0  redo SIZE
         184485  bytes sent via SQL*Net TO client
           7818  bytes received via SQL*Net FROM client
            668  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
          10000  ROWS processed
    SQL>
    SQL> DROP INDEX i_person_sex;
    INDEX dropped.
    SQL>
    SQL> --
    SQL> -- Creete index to include NULLs
    SQL> --
    SQL> CREATE INDEX i_person_sex ON tblperson(sex,0);
    INDEX created.
    SQL>
    SQL> EXEC dbms_stats.gather_table_stats(ownname=>'BING',tabname=>'TBLPERSON', cascade=>TRUE);
    PL/SQL PROCEDURE successfully completed.
    SQL>
    SQL> --
    SQL> -- Run your query again
    SQL> --
    SQL> SELECT fname, sname, personid FROM tblperson WHERE sex = 'F';
    FNAME                SNAME                  PERSONID
    -------------------- -------------------- ----------
    Test                 Fleester                    336
    Test                 Fleester                    343
    Test                 Fleester                    350
    Test                 Fleester                    357
    Test                 Fleester                    364
    Test                 Fleester                    371
    Test                 Fleester                    378
    Test                 Fleester                    385
    Test                 Fleester                    392
    Test                 Fleester                    399
    Test                 Fleester                    406
    ...
     
    1428 ROWS selected.

    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 264409931
    --------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name         | ROWS  | Bytes | Cost (%CPU)| TIME     |
    --------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |              |  1428 | 28560 |    41  (25)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| TBLPERSON    |  1428 | 28560 |    41  (25)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN          | I_PERSON_SEX |  1428 |       |     7  (43)| 00:00:01 |
    --------------------------------------------------------------------------------------------
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       2 - access("SEX"='F')

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
            253  consistent gets
              0  physical reads
              0  redo SIZE
          30903  bytes sent via SQL*Net TO client
           1537  bytes received via SQL*Net FROM client
             97  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
           1428  ROWS processed
    SQL>
    SQL> --
    SQL> -- Run query like the post indicates
    SQL> --
    SQL> SELECT sex FROM tblperson;
    S
    -
    F
    F
    F
    F
    F
    F
    F
    F
    F
    F
    F
    ...

    10000 ROWS selected.

    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 3954041736
    -------------------------------------------------------------------------------------
    | Id  | Operation            | Name         | ROWS  | Bytes | Cost (%CPU)| TIME     |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |              | 10000 | 20000 |    23  (61)| 00:00:01 |
    |   1 |  INDEX FAST FULL SCAN| I_PERSON_SEX | 10000 | 20000 |    23  (61)| 00:00:01 |
    -------------------------------------------------------------------------------------

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
            691  consistent gets
              0  physical reads
              0  redo SIZE
         172432  bytes sent via SQL*Net TO client
           7818  bytes received via SQL*Net FROM client
            668  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
          10000  ROWS processed
    SQL>
    SQL> DROP TABLE tblperson purge;
    TABLE dropped.
    SQL>
    SQL> spool off
    In addition notice that your query DID result in index access WHEN statistics were gathered on the table and index. You have not gathered such statistics otherwise your plan output would resemble mine.
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No, those are not 'standard' indexes they are BITMAP indexes and behave differently:


    Code (SQL):
     
    SQL> CREATE bitmap INDEX i_person_bmp ON tblperson(sex);
    INDEX created.
    SQL>
    SQL> EXEC dbms_stats.gather_table_stats(ownname=>'BING',tabname=>'TBLPERSON', cascade=>TRUE);
    PL/SQL PROCEDURE successfully completed.
    SQL>
    SQL> --
    SQL> -- Run your query again
    SQL> --
    SQL> SELECT fname, sname, personid FROM tblperson WHERE sex = 'F';
    FNAME                SNAME                  PERSONID
    -------------------- -------------------- ----------
    Test                 Fleester                    658
    Test                 Fleester                    665
    Test                 Fleester                    672
    Test                 Fleester                    679
    Test                 Fleester                    686
    Test                 Fleester                    693
    Test                 Fleester                    700
    Test                 Fleester                    707
    Test                 Fleester                    714
    Test                 Fleester                    721
    Test                 Fleester                    728
    ...
     
    1428 ROWS selected.

    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 3753811499
    ---------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name         | ROWS  | Bytes | Cost (%CPU)| TIME     |
    ---------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |              |  1428 | 28560 |    59  (17)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID | TBLPERSON    |  1428 | 28560 |    59  (17)| 00:00:01 |
    |   2 |   BITMAP CONVERSION TO ROWIDS|              |       |       |            |          |
    |*  3 |    BITMAP INDEX SINGLE VALUE | I_PERSON_BMP |       |       |            |          |
    ---------------------------------------------------------------------------------------------
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       3 - access("SEX"='F')

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
            158  consistent gets
              0  physical reads
              0  redo SIZE
          30903  bytes sent via SQL*Net TO client
           1537  bytes received via SQL*Net FROM client
             97  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
           1428  ROWS processed
    SQL>

     
    Notice again that with proper statistics gathered on the table and index we DO see index access paths taken.
     
  10. dsub42

    dsub42 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    O.k thanks, that makes sense,

    However, in the tests i have been running, where i have placed a bitmap index on sex (create bitmap index i_person_sex on tblperson(sex);), like your previous test, my index is never used in this statement:

    select fname, sname, personid from tblperson where sex = 'F';

    But it is used in this statement:

    select sex from tblperson where sex = 'F';

    Is this because the table only contains 500 rows, so when selecting columns that are not indexed, even though i have placed a bitmap index on sex, it is still quicker to do a full table scan? results are as follows:

    =================================

    select fname, sname, personid from tblperson where sex = 'F';


    ######################################################################################################################

    Execution Plan
    ----------------------------------------------------------

    ---------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost |
    ---------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 323 | 6783 | 7 |
    | 1 | TABLE ACCESS FULL| TBLPERSON | 323 | 6783 | 7 |
    ---------------------------------------------------------------

    Note
    -----
    - 'PLAN_TABLE' is old version


    Statistics
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    45 consistent gets
    0 physical reads
    0 redo size
    9458 bytes sent via SQL*Net to client
    647 bytes received via SQL*Net from client
    23 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    323 rows processed

    timing for: timer13
    Elapsed: 00:00:00.01


    323 rows selected.


    Execution Plan
    ----------------------------------------------------------

    ---------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost |
    ---------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 323 | 6783 | 7 |
    | 1 | TABLE ACCESS FULL| TBLPERSON | 323 | 6783 | 7 |
    ---------------------------------------------------------------

    Note
    -----
    - 'PLAN_TABLE' is old version


    Statistics
    ----------------------------------------------------------
    1 recursive calls
    0 db block gets
    45 consistent gets
    0 physical reads
    0 redo size
    9458 bytes sent via SQL*Net to client
    647 bytes received via SQL*Net from client
    23 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    323 rows processed

    timing for: timer13
    Elapsed: 00:00:00.00
    ######################################################################################################################

    create bitmap index i_person_sex on tblperson(sex);

    begin
    dbms_stats.gather_schema_stats(
    ownname => 'bsms'
    ,estimate_percent => null
    ,cascade => true
    ,method_opt => 'FOR ALL COLUMNS SIZE 5'
    );
    end;
    /

    ######################################################################################################################


    Execution Plan
    ----------------------------------------------------------

    ---------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost |
    ---------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 323 | 6783 | 7 |
    | 1 | TABLE ACCESS FULL| TBLPERSON | 323 | 6783 | 7 |
    ---------------------------------------------------------------

    Note
    -----
    - 'PLAN_TABLE' is old version


    Statistics
    ----------------------------------------------------------
    1 recursive calls
    0 db block gets
    45 consistent gets
    0 physical reads
    0 redo size
    9458 bytes sent via SQL*Net to client
    647 bytes received via SQL*Net from client
    23 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    323 rows processed

    timing for: timer13
    Elapsed: 00:00:00.01

    323 rows selected.


    Execution Plan
    ----------------------------------------------------------

    ---------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost |
    ---------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 323 | 6783 | 7 |
    | 1 | TABLE ACCESS FULL| TBLPERSON | 323 | 6783 | 7 |
    ---------------------------------------------------------------

    Note
    -----
    - 'PLAN_TABLE' is old version


    Statistics
    ----------------------------------------------------------
    1 recursive calls
    0 db block gets
    45 consistent gets
    0 physical reads
    0 redo size
    9458 bytes sent via SQL*Net to client
    647 bytes received via SQL*Net from client
    23 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    323 rows processed

    timing for: timer13
    Elapsed: 00:00:00.01


    ######################################################################################################################
    Hints
    ######################################################################################################################

    select /*+ index(bsms.tblperson i_person_sex) */

    323 rows selected.


    Execution Plan
    ----------------------------------------------------------

    ---------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost |
    ---------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 323 | 6783 | 7 |
    | 1 | TABLE ACCESS FULL| TBLPERSON | 323 | 6783 | 7 |
    ---------------------------------------------------------------

    Note
    -----
    - 'PLAN_TABLE' is old version


    Statistics
    ----------------------------------------------------------
    1 recursive calls
    0 db block gets
    45 consistent gets
    0 physical reads
    0 redo size
    9458 bytes sent via SQL*Net to client
    647 bytes received via SQL*Net from client
    23 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    323 rows processed

    timing for: timer13
    Elapsed: 00:00:00.01
    ====================================================================================

    so this might be where my understanding is incorrect..


    I thaught that because i had indexed the predicate in the where clause then, it doesnt matter what im selecting (in the same table), as it would just look at the rows that sex = 'F' (via the index) and then pull back whatever columns i have specified in that table that match the where criteria? ... which surely would be quicker than a full table scan regardless of size (maybe not for oracle i dont know)

    ie. 350 columns have sex = 'f' .. so just throw back whatever i have selected where the index has found sex = 'f'

    Is this not how it works?

    Sorry if this is a stupid question, im really new to this
     
  11. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No. it is not how it works. Oracle will determine, given proper statistics, whether or not to use an index based upon the projected cost of the operation and as 350(index entries read) + 350(rows fetched from table) is greater than 500 (rows scanned from table) you get a full table scan rather than an index access. You need to remember that you now have TWO objects to access, not one, and that can DOUBLE your work so index access is reserved for situations where the total access cost for both objects is less than the cost to access only the table. You're returning 65-70% of your table data so that pretty much prevents an index access path.
     
  12. dsub42

    dsub42 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    Ok. i think iv got it... for example.. (using standard indexes - slightly different scenario)

    STATEMENT: select fname, sname, personid from tblperson where sex = 'F';

    INDEX TYPE 1 : create index i_p_s on tblperson(fname,sname,personid,sex) ----> index IS used

    INDEX TYPE 2 : create index i_p_s on tblperson(fname,sname,personid) ----> index is NOT used

    index type 1 is used because. the data is held in the index, so why go to the table to get it, right?

    however in a table that contains 500 rows, that 350 of which are returned, index 2 is not used because it is quicker to do a full table scan than use what data is indexed to get row id's for the remaining un-indexed field being selected?

    however lets say there were 500,000 rows and were using index type 2

    all the data would not be contained in the index....but 280 of the 500,000 rows were selected, the index would be used because even though it doesnt hold all the data it would still be quicker than a full tablle scan using the index that is there to pull back the row id's?

    Correct?
     
  13. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No:

    Code (SQL):
     
    SQL> --
    SQL> -- Create concatenated index
    SQL> --
    SQL> CREATE INDEX i_p_s ON tblperson(fname,sname,personid,sex);
    INDEX created.
    SQL>
    SQL> EXEC dbms_stats.gather_table_stats(ownname=>'BING',tabname=>'TBLPERSON', cascade=>TRUE);
    PL/SQL PROCEDURE successfully completed.
    SQL>
    SQL> --
    SQL> -- Run your query again
    SQL> --
    SQL> SELECT fname, sname, personid FROM tblperson WHERE sex = 'F';
    FNAME                SNAME                  PERSONID
    -------------------- -------------------- ----------
    Test                 Fleester                      7
    Test                 Fleester                     14
    Test                 Fleester                     21
    Test                 Fleester                     28
    Test                 Fleester                     35
    Test                 Fleester                     42
    Test                 Fleester                     49
    Test                 Fleester                     56
    Test                 Fleester                     63
    Test                 Fleester                     70
    Test                 Fleester                     77
    ...
    Test                 Fleester                   9982
    Test                 Fleester                   9989
    Test                 Fleester                   9996
    1428 ROWS selected.

    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 1609402580
    ------------------------------------------------------------------------------
    | Id  | Operation            | Name  | ROWS  | Bytes | Cost (%CPU)| TIME     |
    ------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |       |  1428 | 28560 |    37  (55)| 00:00:01 |
    |*  1 |  INDEX FAST FULL SCAN| I_P_S |  1428 | 28560 |    37  (55)| 00:00:01 |
    ------------------------------------------------------------------------------
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       1 - FILTER("SEX"='F')

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
            144  consistent gets
              0  physical reads
              0  redo SIZE
          30903  bytes sent via SQL*Net TO client
           1537  bytes received via SQL*Net FROM client
             97  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
           1428  ROWS processed
    SQL>
    SQL> DROP INDEX i_p_s;
    INDEX dropped.

    SQL>
    SQL> CREATE INDEX i_p_s ON tblperson(fname,sname,personid,sex);
    INDEX created.
    SQL>
    SQL> EXEC dbms_stats.gather_table_stats(ownname=>'BING',tabname=>'TBLPERSON', cascade=>TRUE);
    PL/SQL PROCEDURE successfully completed.
    SQL>
    SQL> --
    SQL> -- Run your query again
    SQL> --
    SQL> SELECT fname, sname, personid FROM tblperson WHERE sex = 'F';
    FNAME                SNAME                  PERSONID
    -------------------- -------------------- ----------
    Test                 Fleester                      7
    Test                 Fleester                     14
    Test                 Fleester                     21
    Test                 Fleester                     28
    Test                 Fleester                     35
    Test                 Fleester                     42
    Test                 Fleester                     49
    Test                 Fleester                     56
    Test                 Fleester                     63
    Test                 Fleester                     70
    Test                 Fleester                     77
    ...
    Test                 Fleester                   9982
    Test                 Fleester                   9989
    Test                 Fleester                   9996
    1428 ROWS selected.
     
    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 1609402580
    ------------------------------------------------------------------------------
    | Id  | Operation            | Name  | ROWS  | Bytes | Cost (%CPU)| TIME     |
    ------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |       |  1428 | 28560 |    37  (55)| 00:00:01 |
    |*  1 |  INDEX FAST FULL SCAN| I_P_S |  1428 | 28560 |    37  (55)| 00:00:01 |
    ------------------------------------------------------------------------------
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       1 - FILTER("SEX"='F')

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
            144  consistent gets
              0  physical reads
              0  redo SIZE
          30903  bytes sent via SQL*Net TO client
           1537  bytes received via SQL*Net FROM client
             97  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
           1428  ROWS processed
    SQL>

     
    As you see both indexes are used and no table access is necessasry since all of the columns in the SELECT list are in both indexes. And an INDEX FAST FULL SCAN will trump a table scan making your deduction for INDEX TYPE 2, well, wrong. Indexes won't be used when table access is necessary and more than 40% of the table data will be returned. To repeat: Oracle will choose an INDEX FAST FULL SCAN over a full table scan every time. You're constructing examples to illustrate points you don't quite understand and in the process are getting them incorrect. All of the table data doesn't need to be contained in the index; the entire SELECT LIST must be to effect an index fast full scan so with either index from your example Oracle wll execute such a scan as proven above.
     
  14. dsub42

    dsub42 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    I wasnt speculating, .. here look at the results:

    Code (SQL):
    --create index

    CREATE INDEX i_person_sex ON tblperson(fname, sname, personid);

    -analyse TABLE

    BEGIN
    dbms_stats.gather_schema_stats(
    ownname => 'bsms'
    ,estimate_percent => NULL
    ,cascade => TRUE
    ,method_opt => 'FOR ALL COLUMNS SIZE 5'
    );
    END;
    /

    - run script

    @test.SQL

    =========================================================================

    323 ROWS selected.


    Execution Plan
    ----------------------------------------------------------                      
                                                                                   
    ---------------------------------------------------------------                
    | Id  | Operation         | Name      | ROWS  | Bytes | Cost  |                
    ---------------------------------------------------------------                
    |   0 | SELECT STATEMENT  |           |   323 |  6783 |     7 |                
    |   1 |  TABLE ACCESS FULL| TBLPERSON |   323 |  6783 |     7 |                
    ---------------------------------------------------------------                
                                                                                   
    Note                                                                            
    -----                                                                          
       - 'PLAN_TABLE' IS OLD version                                                


    Statistics
    ----------------------------------------------------------                      
              1  recursive calls                                                    
              0  db block gets                                                      
             45  consistent gets                                                    
              0  physical reads                                                    
              0  redo SIZE                                                          
           9458  bytes sent via SQL*Net TO client                                  
            647  bytes received via SQL*Net FROM client                            
             23  SQL*Net roundtrips TO/FROM client                                  
              0  sorts (memory)                                                    
              0  sorts (disk)                                                      
            323  ROWS processed                                                    

    timing FOR: timer13
    Elapsed: 00:00:00.00

    ========================================================================

    --drop index
    DROP INDEX i_p_s

    --create index

    CREATE INDEX i_person_sex ON tblperson(fname, sname, personid, sex);

    -analyse TABLE

    BEGIN
    dbms_stats.gather_schema_stats(
    ownname => 'bsms'
    ,estimate_percent => NULL
    ,cascade => TRUE
    ,method_opt => 'FOR ALL COLUMNS SIZE 5'
    );
    END;
    /

    =========================================================================

    323 ROWS selected.


    Execution Plan
    ----------------------------------------------------------                      
                                                                                   
    ---------------------------------------------------------------------          
    | Id  | Operation            | Name         | ROWS  | Bytes | Cost  |          
    ---------------------------------------------------------------------          
    |   0 | SELECT STATEMENT     |              |   323 |  6783 |     3 |          
    |   1 |  INDEX FAST FULL SCAN| I_PERSON_SEX |   323 |  6783 |     3 |          
    ---------------------------------------------------------------------          
                                                                                   
    Note                                                                            
    -----                                                                          
       - 'PLAN_TABLE' IS OLD version                                                


    Statistics
    ----------------------------------------------------------                      
              1  recursive calls                                                    
              0  db block gets                                                      
             28  consistent gets                                                    
              0  physical reads                                                    
              0  redo SIZE                                                          
           8299  bytes sent via SQL*Net TO client                                  
            647  bytes received via SQL*Net FROM client                            
             23  SQL*Net roundtrips TO/FROM client                                  
              0  sorts (memory)                                                    
              0  sorts (disk)                                                      
            323  ROWS processed                                                    

    timing FOR: timer13
    Elapsed: 00:00:00.01
     
    This is why i was saying, in my situation, in a table which has 500 rows , and a query that brings back 350 of them (well over 40%),

    then why:

    create index i_p_s on tblperson(fname,sname,personid,sex) ----> USED index
    create index i_p_s on tblperson(fname,sname,personid) ----> NOT use index

    This is where is was saying...

    In a table with more rows, say 500,000, and 280 being selected then both indexes would be used...

    But... because my table only contains 500 rows, of which over half are being returned, then using the following index....

    create index i_p_s on tblperson(fname,sname,personid)

    ...Is not as quick as a full table scan (as can be seen above)

    ..why is this?
     
  15. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Your histograms aren't accurately portraying your data; look at MY examples and you'll see BOTH indexes provide the same path. Restricting your histograms to 5 buckets can be detrimental to performance as you have just proven.
     
  16. dsub42

    dsub42 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    IN a slightly different scenario (changed index's):

    STATEMENT:" select fname, sname, personid from tblperson where sex ='F'"

    lets say the table had 500 rows in it

    create index 1 on tblperson(fname,sname,personid,sex) ... would be used

    however....

    create index 2 on tblperson(sname,personid,sex) ... would NOT be used

    Is this because in a table with 500 rows that 350 of which are returned ... the index above is not useful as it would be quicker doing a full table scan than using that index to pull back the 350 rowid' to get the extra attribute (fname)?

    But if we had 500,000 rows in the table, then index 2 would be used, because using it to get the rowids for the relevant data in this case would be more efficient than a full table scan on 500,000 rows
     
  17. dsub42

    dsub42 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    I have changed the analyser, to as you stated,... it doesnt make any difference (see below)

    Also, have you noticed that in your post you used the same index twice?

    Code (SQL):
    CREATE INDEX i_person_sex ON tblperson(fname, sname, personid);

    -analyse TABLE

    EXEC dbms_stats.gather_table_stats(ownname=>'bsms',tabname=>'TBLPERSON', cascade=>TRUE);

    - run script

    @test.SQL

    =========================================================================

    323 ROWS selected.


    Execution Plan
    ----------------------------------------------------------                      
                                                                                   
    ---------------------------------------------------------------                
    | Id  | Operation         | Name      | ROWS  | Bytes | Cost  |                
    ---------------------------------------------------------------                
    |   0 | SELECT STATEMENT  |           |   323 |  6783 |     7 |                
    |   1 |  TABLE ACCESS FULL| TBLPERSON |   323 |  6783 |     7 |                
    ---------------------------------------------------------------                
                                                                                   
    Note                                                                            
    -----                                                                          
       - 'PLAN_TABLE' IS OLD version                                                


    Statistics
    ----------------------------------------------------------                      
              1  recursive calls                                                    
              0  db block gets                                                      
             45  consistent gets                                                    
              0  physical reads                                                    
              0  redo SIZE                                                          
           9458  bytes sent via SQL*Net TO client                                  
            647  bytes received via SQL*Net FROM client                            
             23  SQL*Net roundtrips TO/FROM client                                  
              0  sorts (memory)                                                    
              0  sorts (disk)                                                      
            323  ROWS processed                                                    

    timing FOR: timer13
    Elapsed: 00:00:00.00

    ========================================================================
     
  18. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yes, I noticed tha and have corrected the problem and it's not the volume of data involved it's the query; the second index isn't used because you're accessing a column NOT in the index and having to go to the table to get it. It doesn't matter how much or how little data is in the table if the entire query can be satisfied by an index the table will NOT be accessed:

    Code (SQL):
     
    SQL> --
    SQL> -- Create concatenated index
    SQL> --
    SQL> CREATE INDEX i_p_s ON tblperson(fname,sname,personid,sex);
    INDEX created.
    SQL>
    SQL> EXEC dbms_stats.gather_table_stats(ownname=>'BING',tabname=>'TBLPERSON', cascade=>TRUE, method_opt => 'for all indexed columns size auto');
    PL/SQL PROCEDURE successfully completed.
    SQL>
    SQL> --
    SQL> -- Run your query again
    SQL> --
    SQL> SELECT fname, sname, personid FROM tblperson WHERE sex = 'F';
    FNAME                SNAME                  PERSONID
    -------------------- -------------------- ----------
    Test                 Fleester                      2
    Test                 Fleester                      3
    Test                 Fleester                      5
    Test                 Fleester                      6
    Test                 Fleester                      8
    Test                 Fleester                      9
    Test                 Fleester                     11
    Test                 Fleester                     12
    Test                 Fleester                     14
    Test                 Fleester                     15
    Test                 Fleester                     17
    ...
    Test                 Fleester                    500
    388 ROWS selected.
     
    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 1609402580
    ------------------------------------------------------------------------------
    | Id  | Operation            | Name  | ROWS  | Bytes | Cost (%CPU)| TIME     |
    ------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |       |   388 |  7760 |     4  (25)| 00:00:01 |
    |*  1 |  INDEX FAST FULL SCAN| I_P_S |   388 |  7760 |     4  (25)| 00:00:01 |
    ------------------------------------------------------------------------------
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       1 - FILTER("SEX"='F')

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
             32  consistent gets
              0  physical reads
              0  redo SIZE
           8675  bytes sent via SQL*Net TO client
            767  bytes received via SQL*Net FROM client
             27  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
            388  ROWS processed
    SQL>
    SQL> DROP INDEX i_p_s;
    INDEX dropped.

    SQL>
    SQL> CREATE INDEX i_p_s ON tblperson(fname,sname,personid);
    INDEX created.
    SQL>
    SQL> EXEC dbms_stats.gather_table_stats(ownname=>'BING',tabname=>'TBLPERSON', cascade=>TRUE, method_opt => 'for all indexed columns size auto');
    PL/SQL PROCEDURE successfully completed.
    SQL>
    SQL> --
    SQL> -- Run your query again
    SQL> --
    SQL> SELECT fname, sname, personid FROM tblperson WHERE sex = 'F';
    FNAME                SNAME                  PERSONID
    -------------------- -------------------- ----------
    Test                 Fleester                    170
    Test                 Fleester                    171
    Test                 Fleester                    173
    Test                 Fleester                    174
    Test                 Fleester                    176
    Test                 Fleester                    177
    Test                 Fleester                    179
    Test                 Fleester                    180
    Test                 Fleester                    182
    Test                 Fleester                    183
    Test                 Fleester                    185
    ...
    Test                 Fleester                    168
    388 ROWS selected.

    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 257609698
    -------------------------------------------------------------------------------
    | Id  | Operation         | Name      | ROWS  | Bytes | Cost (%CPU)| TIME     |
    -------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |           |   388 |  7760 |     6  (34)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| TBLPERSON |   388 |  7760 |     6  (34)| 00:00:01 |
    -------------------------------------------------------------------------------
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       1 - FILTER("SEX"='F')

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
             33  consistent gets
              0  physical reads
              0  redo SIZE
           8675  bytes sent via SQL*Net TO client
            767  bytes received via SQL*Net FROM client
             27  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
            388  ROWS processed
    SQL>
    SQL> --
    SQL> -- Run a query without the WHERE clause and the index IS used
    SQL> --
    SQL> SELECT fname, sname, personid FROM tblperson;
    FNAME                SNAME                  PERSONID
    -------------------- -------------------- ----------
    Test                 Fleester                      1
    Test                 Fleester                      2
    Test                 Fleester                      3
    Test                 Fleester                      4
    Test                 Fleester                      5
    Test                 Fleester                      6
    Test                 Fleester                      7
    Test                 Fleester                      8
    Test                 Fleester                      9
    Test                 Fleester                     10
    Test                 Fleester                     11
    ...
    Test                 Fleester                    499
    Test                 Fleester                    500
    500 ROWS selected.

    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 1609402580
    ------------------------------------------------------------------------------
    | Id  | Operation            | Name  | ROWS  | Bytes | Cost (%CPU)| TIME     |
    ------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |       |   500 |  9000 |     4  (25)| 00:00:01 |
    |   1 |  INDEX FAST FULL SCAN| I_P_S |   500 |  9000 |     4  (25)| 00:00:01 |
    ------------------------------------------------------------------------------

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
             40  consistent gets
              0  physical reads
              0  redo SIZE
          11129  bytes sent via SQL*Net TO client
            855  bytes received via SQL*Net FROM client
             35  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
            500  ROWS processed
    SQL>

     
    You removed the sex column from the second index and thus made it impossible for Oracle to use it to satisfy your restricted query; removing the restriction (the WHERE clause) caused Oracle to use ONLY the index to return the requested data. Again, it is not a function of the number of rows it's a function of the columns in the query (including the WHERE clause).
     
  19. dsub42

    dsub42 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    ok thanks, i understand... last scenario though....(not to do with the attribute in the where clause being indexed)

    The statement : select fname,sname,personid from tblperson where sex = 'f' brings back 350 out of 500 rows

    I was under the impression, from everything iv read, that if this were the case a full table scan is faster... (wait for it)

    using : create index i_p_s on tblperson(fname, sname, personid, sex); .... this index is used however....

    is this simply because the index matches the query used, why go and look for the data when its all ready in the index, so oracle uses INDEX FAST FULL SCAN....as in this case because the index matches the query this is faster...( if it didnt then like you said it would do a full table scan)

    thats all i want to know... because the literature states that an unorganised table (which it is) considering the size of the table, a full table scan is still faster than using the index (but is this the case with this particular index that matches the query... not an index which doesnt contain all the attributes)

    CODE
    create index i_p_s on tblperson(fname, sname, personid, sex);

    exec dbms_stats.gather_table_stats(ownname=>'bsms',tabname=>'TBLPERSON', cascade=>true);

    ================================================================================
    ========


    323 rows selected.


    Execution Plan
    ----------------------------------------------------------

    --------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost |
    --------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 323 | 6783 | 3 |
    | 1 | INDEX FAST FULL SCAN| I_P_S | 323 | 6783 | 3 |
    --------------------------------------------------------------

    Note
    -----
    - 'PLAN_TABLE' is old version


    Statistics
    ----------------------------------------------------------
    1 recursive calls
    0 db block gets
    28 consistent gets
    0 physical reads
    0 redo size
    8299 bytes sent via SQL*Net to client
    647 bytes received via SQL*Net from client
    23 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    323 rows processed

    timing for: timer13
    Elapsed: 00:00:00.00
     
  20. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    There is absolutely no need to go to the table as the index contains all of the columns the query accesses (the three in the select list and the one in the where clause). Not so for the other index (minus the sex column) as the where clause forces Oracle to access the table and then the "over 40%" rule applies and the index is not used.