help with indexing Options

[helpme42]

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

[burleson]

Hi,

>> I just wanted to know anyones reasoning why the index: create index i_p_s on tblperson(fname,sname,personid,sex) is used on a 500 row table instead of FTS, where the index create index i_p_s on tblperson(sname,personid,sex) is not used...

Before you second-guess the optimizer, have you analyzed all of the tables and indexes with dbms_stats?

http://www.dba-oracle.com/art_builder_dbms_stats.htm

Have you run dbms_stats.gather_system_stats?

http://www.dba-oracle.com/t_dbms_stats_gat...ystem_stats.htm

*************************************************************************
>> For the statement: select fname,sname,personid from tblperson where sex = 'f'

Assuming that you are not European (they have nine genders), this would retrieve half the table rows:

http://www.dba-oracle.com/t_computer_datab...ender_codes.htm

******************************************************
>> where the index create index i_p_s on tblperson(sname,personid,sex) is not used

Because the leading columns IS NOT in the WHERE clause!

This is called an "index skip scan":

http://dba-oracle.com/t_oracle_index_skip_scan_tips.htm

[helpme42]

Hi,

>> I just wanted to know anyones reasoning why the index: create index i_p_s on tblperson(fname,sname,personid,sex) is used on a 500 row table instead of FTS, where the index create index i_p_s on tblperson(sname,personid,sex) is not used...

Before you second-guess the optimizer, have you analyzed all of the tables and indexes with dbms_stats?

http://www.dba-oracle.com/art_builder_dbms_stats.htm

Have you run dbms_stats.gather_system_stats?

http://www.dba-oracle.com/t_dbms_stats_gat...ystem_stats.htm

*************************************************************************
>> For the statement: select fname,sname,personid from tblperson where sex = 'f'

Assuming that you are not European (they have nine genders), this would retrieve half the table rows:

http://www.dba-oracle.com/t_computer_datab...ender_codes.htm

******************************************************
>> where the index create index i_p_s on tblperson(sname,personid,sex) is not used

Because the leading columns IS NOT in the WHERE clause!

This is called an "index skip scan":

http://dba-oracle.com/t_oracle_index_skip_scan_tips.htm

I understand now.... i think... one last thing bugging me though (and iv read the posts to try to understand this)

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, so like steveC said why go and look for the data when its all ready in the index, so oracle uses INDEX FAST FULL SCAN....

thats all i want to know... because those links state that in an unorganised table (which it is) considering the size of the table, a full table scan is still faster than using the index

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


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

Wont ask another question like this I promise... I just dont like taking things for granted until i understand them correctly