Solved

Find Oracle duplicated index

Posted on 2013-06-10
16
391 Views
Last Modified: 2013-08-09
Dear all,

I come from MS SQL server background and usually as part of the maintenance operation we usually find duplicated index and drop one of them, and also we can combine index so that we can save more disk space as we drop some and combine some index.

do Oracle use this kind of concept?

Any script used to find duplicated index?

if Oracle find indexs both the same column, when Oracle load balance the use of both index so that index contention can be reduced ?
0
Comment
Question by:marrowyung
  • 9
  • 7
16 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 39234973
In oracle it is possible to create two indexes (a,b) and (b,a) on a table

There is no load balancing between them but either of them could be used for a particular query.

If you are using all columns in the index  (e.g. where a= 1 and b = 2)  then neither ordering is better than the other.

If you are using just one column of the index then the ordering does make a difference and the optimizer will usually try to use the one with the leading column.

So, if a leading "a" is useful for some queries and a leading "b" is also useful for other query then

create an index on (a)  and an index on (b)

if an index on both is useful then pick one to "double up" and leave the other as a single column index

(a,b)  and (b)

or

(a) and (b,a)

Also note,  Oracle supports index skip-scans where it doesn't have to use the leading column.

so (a,b)  could potentially be used instead of a dedicated index on (b)

Skip-scans are not as efficient as normal index scans though so you might not want to rely on them.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39235028
Here's one method for finding tables with multiple indexes on the same columns


WITH indexes
     AS (SELECT   i.table_owner,
                  i.table_name,
                  i.owner index_owner,
                  i.index_name,
                  LISTAGG(ic.column_name, ',') WITHIN GROUP (ORDER BY ic.column_name) indexed_columns,
                  LISTAGG(ic.column_name, ',') WITHIN GROUP (ORDER BY ic.column_position)
                      ordered_columns
             FROM dba_indexes i, dba_ind_columns ic
            WHERE ic.index_owner = i.owner AND ic.index_name = i.index_name
         GROUP BY i.owner, i.index_name, i.table_owner, i.table_name)
SELECT   table_owner, table_name, index_owner, index_name, ordered_columns
    FROM indexes i1
   WHERE EXISTS
             (SELECT NULL
                FROM indexes i2
               WHERE     i1.table_owner = i2.table_owner
                     AND i1.table_name = i2.table_name
                     AND i1.indexed_columns = i2.indexed_columns
                     AND (i1.index_owner != i2.index_owner OR i1.index_name != i2.index_name))
ORDER BY table_owner, table_name, index_owner, index_name
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39236781
"If you are using just one column of the index then the ordering does make a difference and the optimizer will usually try to use the one with the leading column."

please give example of what a leadaing column is .

"Also note,  Oracle supports index skip-scans where it doesn't have to use the leading column."

What goods it is ? why then you said "Skip-scans are not as efficient as normal index scans though so you might not want to rely on them. "

"if an index on both is useful then pick one to "double up" and leave the other as a single column index"

what do you mean doube up here ?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39238656
an index on (a,b)  - "a" is the leading column

skip scans:

index on (a,b)  

select * from yourtable where b=1;

a skip-scan could be used on the "b" by skipping over the "a".
This is not as efficient as an index on "b" alone or an index with "b" as the leading column.

It could still be more efficient than no index at all.

By "double up"  I mean only one of the indexes has both columns

so, instead of an index on "a" and and index on "b" and an index on (a,b) and an index on (b,a)

you pick one column  that will be paired with, doubled, included with, etc another column

so (a,b)  I'm saying "a" is doubled-up with "b"
(b) is by itself


"doubled up" is not a technical term it was simple phrasing to convey the difference between a column by itself and a column with something else
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39278392
let me check
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39384973
as "There is no load balancing between them but either of them could be used for a particular query"

then MS SQL is smarter in this way.

"Also note,  Oracle supports index skip-scans where it doesn't have to use the leading column.

so (a,b)  could potentially be used instead of a dedicated index on (b) "

so it seems that we can always include (a,b) for all table so that we don't have to worry about something else ? this is not computer science enougth ?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39385718
as "There is no load balancing between them but either of them could be used for a particular query"

then MS SQL is smarter in this way.


I'm not sure I follow you there.

MS SQL doesn't load balance indexes.

It's optimizer performs (roughly) the same type of evaluation.
Will a particular index help a particular query execute more efficiently?
That is, will it help reduce number of blocks read? Will it reduce sorts?
Will it reduce number of table accesses?

It doesn't load balance and say "user 1 is currently reading this index, user 2 will need to use the other index"
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39388202
sdstuber,

"MS SQL doesn't load balance indexes"

how can you know ? if we see index contention, just create one more index cover the same column, SQL will use other one when the original one is busy.

"Will a particular index help a particular query execute more efficiently?"

that's why the optimizer will think to use another index for the same query.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39389356
"original one is busy" - this is not a problem
multiple sessions can read the same index


"Will a particular index help a particular query execute more efficiently?"

you misunderstood my statement intent.

If index A applies and index B also applies,  if by chance they would be equally valid then either could be used but balancing reads between them isn't really a factor.

Easy example:
Both optimizers use cached plans.  That is, if I parse a query then I run that query again, I'll use that same plan again.  Just because you happen to be running another query that uses the index I want doesn't force me to reparse and generate a new query plan.

Databases would be constantly reparsing and replanning queries if they had to take into account the activity of another session at the same time.  Even worse, if my parse is expensive and your query is almost complete, it's possible you could already be done by the time I'm done parsing and hence the index would be available.  So the reparse and selection of a new index would be a wasted effort.

And finally, in general multiple sessions WANT to read the same indexes.  Cache those index data blocks so that when you read the index and then I need the same index, I get to pull it from the buffers in memory rather than new disk io.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39392358
"you misunderstood my statement intent.

If index A applies and index B also applies,  if by chance they would be equally valid then either could be used but balancing reads between them isn't really a factor. "

ok..

"And finally, in general multiple sessions WANT to read the same indexes.  Cache those index data blocks so that when you read the index and then I need the same index, I get to pull it from the buffers in memory rather than new disk io. ":

So you mean index contention is not a problme as everything in RAM rather than iO ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39392360
this one :

WITH indexes
     AS (SELECT   i.table_owner,
                  i.table_name,
                  i.owner index_owner,
                  i.index_name,
                  LISTAGG(ic.column_name, ',') WITHIN GROUP (ORDER BY ic.column_name) indexed_columns,
                  LISTAGG(ic.column_name, ',') WITHIN GROUP (ORDER BY ic.column_position)
                      ordered_columns
             FROM dba_indexes i, dba_ind_columns ic
            WHERE ic.index_owner = i.owner AND ic.index_name = i.index_name
         GROUP BY i.owner, i.index_name, i.table_owner, i.table_name)
SELECT   table_owner, table_name, index_owner, index_name, ordered_columns
    FROM indexes i1
   WHERE EXISTS
             (SELECT NULL
                FROM indexes i2
               WHERE     i1.table_owner = i2.table_owner
                     AND i1.table_name = i2.table_name
                     AND i1.indexed_columns = i2.indexed_columns
                     AND (i1.index_owner != i2.index_owner OR i1.index_name != i2.index_name))
ORDER BY table_owner, table_name, index_owner, index_name 

Open in new window


for Oracle ?

syntax seems for MS SQL.,.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39392368
please answer this :

"so it seems that we can always include (a,b) for all table so that we don't have to worry about something else ? this is not computer science enougth ? "
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39392797
So you mean index contention is not a problme as everything in RAM rather than iO ?

mostly yes.  A minor point though on the contention.  You don't really have contention on the index but rather the blocks it's stored in.  Same as table blocks.  A "hot block" that is being read frequently will benefit from being buffered.  A "hot block" that is being written frequently will have contention and cause waits even if buffered.  That's true regardless of whether the block stores index information or table information.


syntax seems for MS SQL.,.

yes, the query I posted is for oracle, looks like you copied it to your post, if you made any small changes I couldn't tell.  In any case, it's definitely not for MS SQL.  The dictionary views referenced don't exist and the syntax isn't supported except in Oracle.



 
"so it seems that we can always include (a,b) for all table so that we don't have to worry about something else ? this is not computer science enougth ? "

I'm not sure what this is supposed to be asking, but saying "always" makes me want to say no; because there are few things that are "always" true.

Index the columns that you use.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39396036
sdstuber,

"You don't really have contention on the index but rather the blocks it's stored in.  Same as table blocks.  A "hot block" that is being read frequently will benefit from being buffered.  A "hot block" that is being written frequently will have contention and cause waits even if buffered.  That's true regardless of whether the block stores index information or table information.
"

This make me funny on this as you mean if there index store on buffers, then no contention but block/hot blocks.

it is in memory why still has blocking as the query should be fast enought already? that's why Oracle is fast ..... ?

"Index the columns that you use. "
yes

"I'm not sure what this is supposed to be asking"

What I mean is if we have column a and b, c and so on, why just don't index all of them if Oracle can do something like " index skip-scans", then let Oracle do it, right?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39396081
This make me funny on this as you mean if there index store on buffers, then no contention but block/hot blocks.

Think about it like an old card catalog at the library.  That's an index.

If I'm reading the catalog for A-books and you're reading the catalog for Z-books.  We have no contention simply because we both want to read the same index.

If we're both trying to read the catalog for M-books then we have contention because we both want the same card box (block).

BUT...  If I pull a book titled"mmmmm, the history of cooking" and you want that same book then you can benefit from the "buffering" of that because I've already pulled the card and you can read it too rather than scanning the whole box again.



why just don't index all of them

Indexes have a cost to them.  They take up space, they require updating as the table updates and generate additional redo.  So, indexing when you don't need to is not good.

Combining them all into one big index is possible but skip-scans aren't as efficient as normal index scans (range or full) and so the optimizer might not use them at all if the cost is prohibitive.  

When I say "Index the columns that you use. "

That means the indexes should correspond to the actual usage.

If you query a table by (a,b) sometimes and other times by (c)
Then you could create a single index (a,b,c)
You'll likely get better read performance with two indexes though, one for each type of query you use.  However, since there is a cost associated with maintaining two indexes, you should weigh those costs vs the benefits.

Best method is to simply try.  Create (a,b,c) run some sample queries.  Does it perform well with (a,b) and with (c) queries?  If so, great, one index solves your problems with minimal resources.  If some of your queries don't perform as well as you'd like, then try the two  index solution.  Test again.

Don't just do selects, but also consider update, insert, delete.  Those actions will cause the indexes to be updated.  If you have heavy write system then extra indexes can be cost prohibitive.  If you have a heavy read system then extra indexes maybe well worth it.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39396271
"If we're both trying to read the catalog for M-books then we have contention because we both want the same card box (block)."

but as you say we can SHARE them as we put it in the buffer, right?

"Indexes have a cost to them.  They take up space, they require updating as the table updates and generate additional redo.  So, indexing when you don't need to is not good"

save as MS SQL server, this is a normal concept, index too much take up a lot of space as the  more data input, the more disk space take up the index.

"However, since there is a cost associated with maintaining two indexes, you should weigh those costs vs the benefits.
"
what kind of cost you are talking about ? some example please.

"Best method is to simply try.  Create (a,b,c) run some sample queries.  Does it perform well with (a,b) and with (c) queries?  If so, great, one index solves your problems with minimal resources.  If some of your queries don't perform as well as you'd like, then try the two  index solution.  Test again."

this is good. trial and error.

"Don't just do selects, but also consider update, insert, delete.  Those actions will cause the indexes to be updated. "

exactly the same as MS SQL ahahhahh.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now