- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: index
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-13-2004 06:26 PM
тАО01-13-2004 06:26 PM
I'm a bit confuse.We have a complex selest statement which select 3 big table.The statement run for 30 min and finish.
Next the user delete some index on that table and he told me the queries now run faster.He told me that too many index will cause the query to be slow.
Is this true OR the guy is bluffing me ?.
regards
mB
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-13-2004 06:43 PM
тАО01-13-2004 06:43 PM
Re: index
I have seen it in the past with a Oracle 7.3.4 database that was configured with ruled based optimization. In that way it depended on the existing index's what search path it took.
When this was a realy silly path for that specific query it took very long to finish.
We dicided to reconfigure the data to cost based optimization. (This is the default in Oracle 8.1.x > )
Hopes this helps.
Regards,
Peter
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-13-2004 06:45 PM
тАО01-13-2004 06:45 PM
Re: index
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-13-2004 07:01 PM
тАО01-13-2004 07:01 PM
Re: index
As Peter said, the key point is optimization method on this issue. Sometimes optimizers may choose wrong methods to retrieve data from the tables. BTW, extra indices on a table may affect negative while inserting or updating data but positive effect in retrieving data if the optimizer chooses appropriate index.
Regards
ALPER ONEY
I.S.E TAKASBANK INC
SYBASE DBA
TEL:+902123152109
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-13-2004 08:11 PM
тАО01-13-2004 08:11 PM
Re: index
Thanks
Zafar
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-13-2004 10:38 PM
тАО01-13-2004 10:38 PM
Re: index
It's more Oracle using the wrong index(es) for the query.
(From a previous post I understand the database is Oracle !).
use explain plan before and after removing the index(es) and compare the plan.
From table size (and so on) you should understand why this is faster.
You can also run a trace on the query (before/after) and analyze the trace files with tkprof (use explain here too).
Also, this can be that the statistics on indexes/tables where not updated and Oracle chose the "wrong" path based on its "known" statistics.
let us know what are your findings.
Regards,
Jean-Luc
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-14-2004 02:47 AM
тАО01-14-2004 02:47 AM
Solution(aka: missed opportunity!).
I have experienced this in a a (Siebel) benchmark where there was an index S_AB on colums A and B and index S_BA on columns B and A. Column A, had only 100 distinct values with a stong skew to 1 value. Column B had 50,000 unique values.
So for a (sub)query WHERE A=:a AND B=:b it is best to use index S_BA, not S_AB.
The rule based optimizer does not know this, but the cost based optimizer would know... if up-to-date statistics are present.
If you are stuck with the rule based optimizer, then either go ahead and drop that index, or have the applicatio write add a query HINT to suggest Oracle to use a specific index. Something along the lines of:
SELECT /*+ INDEX ( table [index [index]...] ) */ WHERE blah blah
USe the Oracle DOc, or Google for details and full examples.
Zafar, Indeed extra (excessive!) indices will make the cost based optimizer do more work, but normally that shoudl be a minimal component compared to the actuall execution.
Malay boy,
Final resolution about your problem as to the cause can come from an 'explain plan'.
For further, detailed help, you'll need to provide at least 3 from the following missing information: Oracle version, )global/session) optimizer setting, index descriptions, explain plan outputs, table statistics settings and so on.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-14-2004 03:17 PM
тАО01-14-2004 03:17 PM
Re: index
Hein van den Heuvel summarised it well.
You should have a look at the actual execution plan, the statistics and so on. I firmly believe it is all about FULL Table Scan v/s index usage performance.
Allow me to me to quote this nice description of how it works....
A full tablescan reads the table using large sequential reads -- many blocks at a time. Sequential reads are the fastest type of IO you can do on a disk in general.
An index read will do single block, random IO's. These are generally the slowest you can do.
If you are going to read 25% of the table via an index you will probably hit MOST if not all of the blocks in the table. You will hit some blocks many times probably as well.
Ok, if you take the above as "fact" we can move on... Lets say the table is 100 blocks, with 1000 values and your buffer cache can hold 50 blocks. Let's say you will read this via an index. We will read 250 rows.
The index might tell us "goto block 1, get row 1", "goto block 2, get row 1" ... "goto block N get row 1".... "goto block 100 get row 1"
So, so far, the index has had us read each block once and get the first row on each block. The index processes the data in sorted order, not in the order it physically resided on the disk in the table. Hence, we did 100 random IO's to read 100 blocks in. Not only that -- but blocks 1-50 are no longer in the buffer cache, blocks 51-100 are. Now, resuming our query (we have 150 more rows to get!) the index tells us: "goto block 1, get row 2", "goto block 2, get row 2" ... "goto block N get row 2".... "goto block 100 get row 2"
Oh no -- we just got told to RE-READ the blocks we already read!!! Again!!! from Disk!! So, assuming the table had no blocks in the cache, we've now just done 200 random physical IO's against that table -- AND we are not done yet!!!
So, picking up where we left off -- the index now says "goto block 1, get row 3", "goto block 3, get row 1" ... "goto block N get row 3".... "goto block 50 get row 3". Bummer, 50 more random physical IO's.
Now, the full table scan comes in. Suppose our db_file_multiblock_read_count is set to 20. We do 5 sequential IO's of 20 blocks each and answer the query. Done. Period. Much faster.
Here, a full table scan does a whole lot less IO more efficiently.
best regards
Yogeeraj