Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
2 Pages V  < 1 2  
Closed TopicStart new topic
> help with indexing
SteveC
post May 11 2010, 03:35 PM
Post #16


Advanced Member
***

Group: Members
Posts: 2,835
Joined: 11-March 08
Member No.: 18,933



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.
Go to the top of the page
 
+Quote Post
helpme42
post May 11 2010, 03:45 PM
Post #17


Advanced Member
***

Group: Members
Posts: 31
Joined: 10-May 10
From: UK
Member No.: 43,076



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
Go to the top of the page
 
+Quote Post
SteveC
post May 11 2010, 03:58 PM
Post #18


Advanced Member
***

Group: Members
Posts: 2,835
Joined: 11-March 08
Member No.: 18,933



If Oracle has the data is in the index, then why go to the table to get the same information?
Go to the top of the page
 
+Quote Post
helpme42
post May 11 2010, 04:07 PM
Post #19


Advanced Member
***

Group: Members
Posts: 31
Joined: 10-May 10
From: UK
Member No.: 43,076



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?
Go to the top of the page
 
+Quote Post
helpme42
post May 12 2010, 07:35 AM
Post #20


Advanced Member
***

Group: Members
Posts: 31
Joined: 10-May 10
From: UK
Member No.: 43,076



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
Go to the top of the page
 
+Quote Post
burleson
post May 12 2010, 08:11 AM
Post #21


Advanced Member
***

Group: Members
Posts: 11,617
Joined: 26-January 04
Member No.: 13



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





--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
helpme42
post May 12 2010, 08:25 AM
Post #22


Advanced Member
***

Group: Members
Posts: 31
Joined: 10-May 10
From: UK
Member No.: 43,076



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"..
Go to the top of the page
 
+Quote Post
burleson
post May 12 2010, 01:29 PM
Post #23


Advanced Member
***

Group: Members
Posts: 11,617
Joined: 26-January 04
Member No.: 13



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





--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
helpme42
post May 12 2010, 02:24 PM
Post #24


Advanced Member
***

Group: Members
Posts: 31
Joined: 10-May 10
From: UK
Member No.: 43,076



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
Go to the top of the page
 
+Quote Post
burleson
post May 12 2010, 02:38 PM
Post #25


Advanced Member
***

Group: Members
Posts: 11,617
Joined: 26-January 04
Member No.: 13




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!


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
helpme42
post May 12 2010, 02:50 PM
Post #26


Advanced Member
***

Group: Members
Posts: 31
Joined: 10-May 10
From: UK
Member No.: 43,076



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
Go to the top of the page
 
+Quote Post
burleson
post May 12 2010, 03:16 PM
Post #27


Advanced Member
***

Group: Members
Posts: 11,617
Joined: 26-January 04
Member No.: 13



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!


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

2 Pages V  < 1 2
Closed TopicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 24th October 2014 - 03:28 AM