Home » SQL & PL/SQL » SQL & PL/SQL » about Indexes,help me
about Indexes,help me [message #205310] Fri, 24 November 2006 04:15 Go to next message
sponguru_dba
Messages: 51
Registered: May 2005
Location: Bangalore
Member

Hi all

Off course It is miner issue for you guy's

1.How the Index works in Oracle 10g
2.Adding Column to table will it effects the existing indexes on table (Of course column adding at end of the table)
3.If suppose i have table like this (eno,ename,basic,hra,da,ta,net)
i have index on all columns independantly like(eno_idx,ename_idx,basic_idx,hra_idx,da_idx,ta_idx,net_idx)

If i used select query on this table like this


Select ename from where eno=<> and basic=<> in this query which index it is used both (eno_idx,basic_idx) indexes are completely not

4.I want to see which index is used by the particular query? How to see (how to use execution plan while query is running)


Thanks in Advance

Sreenivas
Re: about Indexes,help me [message #205327 is a reply to message #205310] Fri, 24 November 2006 04:51 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
1) Huge topic. Please be more specific.

2) No, adding a column to a table will not affect the indexes on that table.

3) In general, that is a bad indexing plan. Oracle will only (in general) use a single B-tree index to access the data. The optimiser will look at the stats to pick the most selective index.
I don't really understand what you're asking with this question.

4) V$SQL_PLAN contains the information you need.
SELECT v.* 
FROM   v$sql_plan v
WHERE  v.address = (select sql_address 
                    from   v$session 
                    where  sid = <sid you want to look at>
);
Re: about Indexes,help me [message #205382 is a reply to message #205310] Fri, 24 November 2006 07:11 Go to previous messageGo to next message
sponguru_dba
Messages: 51
Registered: May 2005
Location: Bangalore
Member

Hi
JRowbottom


Thanks for Reply

My Question was suppose my Table having 7 column one is primary key other are non-clustered indexed independently i.e i have 7 columns and 7 indexes

In above case i have query which having 4 columns after where cluase,is this query going use all indexes of 4 column or not used any index to fetch the data

Thanks in Advance
Sreenivas
Re: about Indexes,help me [message #205438 is a reply to message #205382] Fri, 24 November 2006 16:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Assuming you're using the Cost Based Optimiser, then Oracle will take a look at the 4 indices, decide which one is likely to e the most selective and use that one, ignoring the other 3.
If you had one index on the comination of all 4 columns, it would pick that one (97 times out of 100)
Re: about Indexes,help me [message #205550 is a reply to message #205438] Sat, 25 November 2006 19:56 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Further, if all of the columns you use in the SQL can be found in two of the indexes, then the CBO may decide to perform an Index Join. This is more likely to happen when Oracle thinks that a lot of rows will be returned, so not so good for scans that return just a few rows.

Another possibility is that if there are two or more indexes that the CBO thinks will return hundreds or perhaps thousands of rows each (based on the separate WHERE clauses), then it may scan all (or some) of these indexes and combine the results like bitmap indexes, hoping to return a much smaller result set. With normal b-tree indexes, this will typically only happen if you specify the /*+INDEX_COMBINE*/ hint, but it happens automatically if one or more of the indexes is a BITMAP index.

Lastly, there is a deprecated access method called AND-EQUAL, where two or more single-column indexes can be scanned if the columns are specified with equals WHERE/AND clauses. Situations where AND-EQUAL is useful are so stupendously rare, it is best to assume that if you see it happen in a query, then it is a bad thing.

Ross Leishman
Previous Topic: Different result in Oracle and SQL server
Next Topic: Function&Procedure
Goto Forum:
  


Current Time: Wed Dec 07 04:33:13 CST 2016

Total time taken to generate the page: 0.09019 seconds