Help - Search - Members - Calendar
Full Version: help with indexing
Oracle DBA Forums > Oracle > Oracle Forum
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, and welcome to the forum!

>> I want to attempt to improve the performance of this query

OK what is your performance goal?

Do you want to optimize for high throughput or fast response time?

http://www.remote-dba.net/oracle_10g_tunin...uning_goals.htm

**********************************************************
>> Elapsed: 00:00:00.01

Your timings are all super-fast!

**********************************************************
>> select * from tblavailability where personid = 248;

In this simple query, there are only two possible plans:

1 - index access via personid
2 - full scan using parallel query (if you have an SMP server with many CPU's)

If you are using the index, the only way to go even faster is to reorg the table to put all related personid's on adjacent data blocks.

You can use this, and probably get the rows with only a few gets:

CODE
create table t1 as select * from tblavailability order by personid:
rename tblavailability to oldtab;
rename t1 to tblavailability;
create index . . .
alter table add primary key constraiont . . .


http://www.remote-dba.net/t_oracle_net_reorganize_tables.htm

**********************************************************

You got 208 rows in 33 gets, that not too bad!

The only way to possible do even better is to examine your WHERE clause and see if you might have a more restrictive consition that you can index on.

Remember, in Oracle, you can index on functions too:

http://www.dba-oracle.com/t_function_based_indexes.htm
helpme42
Wow, Thee Donald K Burleson .. Im reading your book currently 'Oracle Solid State Disk Tuning' - Im not using SSD for this by the way (thats not the reason for fast speeds smile.gif)

Im new to the whole area of oracle performance tuning, I specialise in .Net stuff, so your help is much appriciated.

When you say, ami optimising for performance time or high throughput.. origionally i have been attempting performance time, I am unaware how to look at the stats to determine throughput?

Thanks for the advise.. Maybe you could help me with this one...

================================================================================
===========================
A table called 'tblrequirements' contains 4000 rows, there is a column called 'wardshiftID'

im using the following SQL statement

"select * from tblrequirements where wardshiftid = 110;"

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

I have ran many tests on the table with no indexes, and then many tests on the table with an index on the column used in the where clause 'wardshiftid' , However adding the index seems to make the query perform worse... im baffled... could you have a look at the results and see what you think:

I have posted 2 general results which are representative of all the tests , the first is using no indes, the second is using the index
=========================================================================

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
SteveC
The problem is that since you have run the same query more than once, the blocks are already in memory. You would need to flush the buffer cache - each run - to get a better comparison. You can see that the consistent gets (blocks being read that are in memory) are lower because not all blocks have to be read now.
helpme42
The second test with the index on shows more consistent gets though...if what your saying is correct shouldn't this show less consistent gets?

In a production environment, if there were an index on the table, the second time the query is ran it would perform worse... with an index then without an index?

The reason im not flushing the buffer cache, I wouldnt have thought made a difference in terms of optimisation, as im considering the performance of the query beyond the first time its ran,

After the first time its ran, (regardless of clearing the cache or not) the query with an index on it, shows more consistant gets than a query without an index on it... do you see what I mean?

Also, I have been told never to flush the buffer cache, (I figured that it would be the best move also, but i got told off for doing it on a different forum)
SteveC
Doing it in production is kind of a no-no, but since you are running tests, one would assume you have a test/dev/QA system to be doing this on.
helpme42
I have ran the same test by flushing the buffer cache before each one.

Theres the same problem....

-More consistent gets on the indexed table
-More Physical Reads on the indexed table
- Query takes longer to run

Why is this?

Sample of results are as follows:

================================================================================
======================
No index on where clause
================================================================================
======================

239 rows selected.


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

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

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


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
47 consistent gets
29 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.04

================================================================================
======================
index ON where clause
================================================================================
======================
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
----------------------------------------------------------
0 recursive calls
0 db block gets
57 consistent gets
48 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.09
helpme42
is it anything to do with the analyzer im using:

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

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

I have tried using AUTO on column size ( and i get the same results for both indexed and non-indexed) :

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

>> I have ran the same test by flushing the buffer cache before each one.

After you flush, ALL the block reads require a phycical disk read!

Also, YOUR TIMINGS are TOO close to be statisticaly meaningful!

If you want to get some real-world tuimings, make a million row table . . .

***********************************************
>> analyze

No, re-running dbms_stats make no difference since there are only 2 choices, use an index or do a full-scan . .

>> I am unaware how to look at the stats to determine throughput?

It's too much for a forum, but I explain it in great detail in my tuning book:

http://www.rampant-books.com/book_0501_awr...tive_tuning.htm
helpme42
O.K ... Aside from the timings , if the column in the where clause is indexed then how:

When flushing the cache: why do i get more physical reads and more consistent reads on the indexed table...?

When not flushing the cache: why do i get more consistent reads on the indexed table..?

I thaught that indexing the where clause in that statement would make it more efficient, why does it appear to make it less efficient?

This doesnt make any sense, in a table with 4000 rows, and a query that returns 239 of them, which is simply selecting * from table where col = xx , then why would indexing that column cause more physical reads (possibly bacause it has to read the index?) but more importantly why more consistant gets?

Sorry if this is a daft question, but I cant get my head around it
helpme42
A query with less consistent gets is more efficient is it not? ... so the index is making the query less efficient (which it should do the opposite in this situation?)

If i were to summarise this, then I would have to write in a report that placing an index on the wardshiftid column makes the query less efficient would i not? but why does it make it less efficient?
helpme42
After adding half a million record to the table I am now getting the following results: (first with no index, second with index)

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

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

---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 22 | 1122 | 1947 |
| 1 | TABLE ACCESS FULL| TBLREQUIREMENTS | 22 | 1122 | 1947 |
---------------------------------------------------------------------

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


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7142 consistent gets
0 physical reads
0 redo size
10517 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.04

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

239 rows selected.


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

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 704 | 34496 | 11 |
| 1 | TABLE ACCESS BY INDEX ROWID| TBLREQUIREMENTS | 704 | 34496 | 11 |
| 2 | INDEX RANGE SCAN | I_REQ_WS | 704 | | 4 |
-------------------------------------------------------------------------------

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


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
58 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.00

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

What i do not understand however, and please please could someone help me...

Now that there are over half a million rows, im getting lower consistant reads in the indexed table ( 52 in the indexed and 7000+ in none indexed)

but....

before I added the extra rows, I was getting higher consistent reads in the indexed table than the none indexed ( 57 indexed / 47 non-indexed)

Why is this .... with lower number of rows why does the indexed table use more consistent gets? ... surely no matter howmany rows are in the table the indexed table should have the same or lower number of consistent gets as the non-indexed???
helpme42
Sorry iv figured that one out, the extra consistent gets... im using 2 objects the index and the table, so its going to be more? right?

I have a bigger problem now, and iv put this on a couple of forums but people are stumped.

I have tried running a test on a table which contains a 'sex' field... (table around 500 rows (300ish = F, 150ish = M, 50ish = null ---in the sex column)

When i place a bitmap index on the sex column, it is never used, no idea why?

I have read a blog post (link below and my results) and tried to incorporate this, it still will not use the index, I have also tried to force it using hints, still no luck


Any ideas?

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:

select 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);

this info taken from here:

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

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


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

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

after further testing i have noticed....

The two following queries do use the index:

==========================================
select sex from tblperson;
select sex from tblperson where sex = 'F'
==========================================

However these two dont use the index...

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

Crazy?!?!
SteveC
It is not a huge table, and you are selecting a data item that is not indexed (fname). When you did the select sex statement, that information can be read directly from the index. When you select the fname, Oracle has to figure what is easier to do: a FTS or use the index to get the table rowid.
helpme42
QUOTE (SteveC @ May 11 2010, 03:34 PM) *
It is not a huge table, and you are selecting a data item that is not indexed (fname). When you did the select sex statement, that information can be read directly from the index. When you select the fname, Oracle has to figure what is easier to do: a FTS or use the index to get the table rowid.


ok, so this might be where my understanding is incorrect..

if i use the statement : slelect fname, sex from tblperson where sex = 'f' .... because i have indexed sex and not fname, then oracle presumes FTS is better way? (due to the table being small)

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?

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
SteveC
Make one record F and the rest M. Then do a select fname from table where sex = 'F' and I bet you see bitmap index usage. Then select fname from table where sex = 'M' and you will more than likely see full table scan.
helpme42
QUOTE (SteveC @ May 11 2010, 04:36 PM) *
Make one record F and the rest M. Then do a select fname from table where sex = 'F' and I bet you see bitmap index usage. Then select fname from table where sex = 'M' and you will more than likely see full table scan.


That makes sense to me, and yes you are quite right , thanks.

what about when im using a standard index... do you know why the following happens:

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

my point i think is, when i just index sex, then the index is not used ... quicker fts
when i index all the columns in the select including columns in the where clasuse .. the index is used! (why is it not quicker still to do a full table scan)



Could you help me understand the logic behind this? .. and then ill leave you alone smile.gif
SteveC
If Oracle has the data is in the index, then why go to the table to get the same information?
helpme42
QUOTE (SteveC @ May 11 2010, 04:59 PM) *
If Oracle has the data is in the index, then why go to the table to get the same information?


ok, but from that , lets say the table had 500, 000 rows in it...

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

and all the data would not be contained in the index....

so lets say 280 of the 500,000 rows were selected, would the index above 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?

where as in my situation, a table with 500 rows that pulls back 350 ... 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 rows?
helpme42
sorry what i ment is , lets say the table had 500 rows in it

create index 1 on tblperson(fname,sname,personid,sex) ... would be used (as all the data is contained within the index.. so like you say "why go to the table")

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 in the origional select statement?

But if we had 500,000 rows in the table, then index 2 would be used, because using it to get the rowids and extra data from the select statement would be quicker in this case than using a FTS?

I will leave you alone after this, once i understand this everything will fit together
burleson
Hi,

>> I will leave you alone after this

I did not mean to be rude to you, it's just that we all have day jobs, and precious little time to help beginners!

Feel free to ask us question, the more specific the question, the better!

****************************************
>> But if we had 500,000 rows in the table, then index 2 would be used

I think that yo are asking about the threshold of when a full-scan is fater than an index scan!

In sone cases, (non-clustered rows with OPQ), a query that only reads 10% of the blocks is faster with a full-scan!

See here:

http://www.praetoriate.com/t_op_sql_threshold_table_scan.htm

http://www.dba-oracle.com/art_orafaq_oracl..._table_scan.htm

http://www.praetoriate.com/t_op_sql_block_threshold.htm



helpme42
QUOTE (burleson @ May 12 2010, 09:12 AM) *
Hi,

>> I will leave you alone after this

I did not mean to be rude to you, it's just that we all have day jobs, and precious little time to help beginners!

Feel free to ask us question, the more specific the question, the better!

****************************************
>> But if we had 500,000 rows in the table, then index 2 would be used

I think that yo are asking about the threshold of when a full-scan is fater than an index scan!

In sone cases, (non-clustered rows with OPQ), a query that only reads 10% of the blocks is faster with a full-scan!

See here:

http://www.praetoriate.com/t_op_sql_threshold_table_scan.htm

http://www.dba-oracle.com/art_orafaq_oracl..._table_scan.htm

http://www.praetoriate.com/t_op_sql_block_threshold.htm


Thanks I have read that material, i have looked and looked for literature to explain what im asking (for days now) and i cant find anything, i just wanted someones opinion, or guestimate.

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

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... it seems to go against all literature.


From the previouse links you sent, my only thaughts would be, that the second index does not work because it is retrieving 70% of the data from an unordered table, so FTS is quicker.... but the first query which is used is also retrieving the same amount of data from the same unordered table is used....... and the only reason i can think of why is because like SteveC said... "why go to the table when all the data is allready stored in the index"..
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
QUOTE (burleson @ May 12 2010, 02:30 PM) *
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
burleson

Hi,

>> 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

No they didn't!

The choice of which is faster depends on MANY factors besides clustering of the rows, it depends on disk stripe size, RAID level, degree of parallelism and MANY OTHER factors . . .

Oracle optimizer is a proprietary secret, they do not disclose their internals. . . .

There are no equations, no rules, the only way to know the best for your query is to experiment and see.

But like I said, your tables sizes are way too small to be meaningful.

It looks like you are playing on a PC with a single spindle . . You need a real server for a meaningful type of testing.

Can you take a class in database management at your local university?

Most people only start learning Oracle after completing their masters degree, that how I started.

Read this:

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

It appears as-if you have not had the required classes in IT and computer science to fully underdstand what you are doing . . .

**************************************************
>> that if this were the case a full table scan is faster... (wait for it)

You never did say what your optimizer_mode is . . .

Check it.

CODE
SQL> show parameter optimizer


If it says all_rows, then you ARE NOT optimizing for fast response time!

Good Luck!
helpme42
Yes im currently studying for my masters (computer science), and I have OCA, but i specialise in OO-programming. Its a great deal different than DB performance tuning

Also OCA doesnt cover anything indepth, especially performance tuning (they cover generic rules of thumb which have no applicability when you actually start to get your hands dirty), i think the hardest thing is going along with the whole test it and see idea, i feel very unconfortable not knowing the actual mechanics and rules behind it. Comming from an academic background it goes against everything, to test something and base a judgement on 'because thats how it happens'

I would like to take the performance tuning course, but its not really an option for me to pay 3500 for it (i dont have that sort of cash), so im determined to teach my self, only problem is because there is no one to ask its easy to convince your self something is correct when it is infact wrong.

I have noticed that this is not the forum i should be asking questions on smile.gif. Ill go and find one which is more appropriate for beginners.

Thats another problem... I can ask one question on 3 different forums, and get radically and conceptually different answers ... its hard to tell the difference between people who play at bein DBA, and those who are DBA ... makes it even more fun to learn though i guess.

Thanks for your help anyway

Much appriciated
burleson
Hi,

>> im currently studying for my masters (computer science), and I have OCA,

Good start! Also, make sure to take some IT courses from the college of business (database management) to be well-rounded.

Remember, most DBA's are business managers and spend a lot of time communicating with the end-user managers . . .

*********************************************************************
>> Comming from an academic background it goes against everything, to test something and base a judgement on 'because thats how it happens'

Er, I disagree!

ORACLE IS NOT A SCIENCE! It's human software, and you cannot see the source code!

In virtually ALL of my statistics courses we learned to sample from the universe and derive the rules!

You need to take a few semester of statistics, and include multivariate statistics, for sure!

***************************************************************
>> but its not really an option for me to pay 3500 for it (i dont have that sort of cash), so im determined to teach my self

Here is the USA almost all employers will pay for the Oracle University training, it's part of the benefit package . . .

After all, the average DBA salary is now over $100k:

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

*****************************************************************
>> I can ask one question on 3 different forums, and get radically and conceptually different answers

Yep! Many Oracle forums are populated by "posers", people without any work experience in Oracle at all!

http://www.dba-oracle.com/t_avoiding_bad_O...nternet_web.htm

Me, I've been working as a full-time DBA since 1983 . . .

As a general rule, you should only take advice from people who are willing to display their experience . . .

Posers will hide their resume, real working DBA's are proud of their experience!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.