| Createing index [message #361952] |
Fri, 28 November 2008 12:56  |
Olivia
Messages: 519
Registered: June 2008
|
Senior Member |
|
|
I want to know can creating index on a particular columns twice
but with different set of columns can have bad affect on queries from performance perspective? I have an index on column
timestamp and another index with combination of other columns as new index.
I have created and index ind_a on (col1, col2, col3).Say I have a query that does not uses col2 in where condition. Then is both Col1 and col2 is going to be used for the index? ( Somewhere I heard that Oracle does not consider this logic) .I need some documentation/link where I can have a look at it and prove.
Regards,
Oli
|
|
|
|
| Re: Createing index [message #362011 is a reply to message #361952] |
Sat, 29 November 2008 21:18  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Let's just talk B-Tree indexes (forget Bitmap indexes):
If one index is a LEADING SUBSET of another index then it is redundant. eg:
INDEX1: (col1, col2, col3)
INDEX2: (col1, col2) <= This is redundant
However if an index is a NON-leading subset, then the indexes are both useful:
INDEX1: (col1, col2, col3)
INDEX2: (col1, col3) <= OK
INDEX3: (col2, col3) <= OK
INDEX4: (col1) <= Redundant
INDEX5: (col2) <= Redundant
INDEX6: (col3) <= OK
Also worthy of note is the INDEX SKIP SCAN capability. This can be useful when you have a composite index (2 or more cols) where the first column has low cardinality (not many different values). Oracle can use an Index Skip Scan to scan on the trailing subset of the index, effectively ignoring the first column.
Note that thais does not come without a cost. Oracle effectively performs N separate scans, where N is the number of different values of Column 1 in the index. If Column 1 had 1M different values then 1M index scan would clearly be a problem.
Ross Leishman
|
|
|
|
| Re: Createing index [message #362189 is a reply to message #362011] |
Mon, 01 December 2008 05:16 |
Olivia
Messages: 519
Registered: June 2008
|
Senior Member |
|
|
Thanks for the update.
Need suggestion for the below scenario:
Table 1 contains millions of rows, its around 10,65876
Below are the index on table1:
index1: (COL1, COL2,COL4, COL5,COL7)
index2: (COL7,COL6)
Case1:
SELECT COL1 FROM TABLE1 WHERE COL1 IN
(SELECT DISTINCT COL_M FROM TABLE2
WHERE COL_N BETWEEN TO_DATE('03/07/2008','DD/MM/yyyy') AND
TO_DATE('08/07/2008','DD/MM/yyyy'))
AND COL3='POS' AND COL6 ='PRE'
ORDER BY COL1;
A query something like above is going for FULL TABLE SCAN on table1.
Case2:
SELECT COL1 FROM TABLE1 WHERE COL1 IN
(SELECT DISTINCT COL_M FROM TABLE2
WHERE COL_N BETWEEN TO_DATE('03/07/2008','DD/MM/yyyy') AND
TO_DATE('08/07/2008','DD/MM/yyyy'))
--AND COL3='POS' AND COL6 ='PRE'
ORDER BY COL1;
While removing Line 5 it uses index1.
My question is
1. Is it a good idea to create index on a column first ( index2( col7,col6) which is used as predicate for a specific range ?
In index2, col7 is timetstamp which is used as a range predicates in most queries.
| Quote: | The use of a range predicate (>[=], <[=], LIKE, BETWEEN) or an IN list forces that column to be the last one used in the scan.
|
2. Do I need to modify the indexes for CASE1. I have a concern that any modification may cause other queries to perform bad.
Suggestion from one of the DBA is that I cant create composite index on (COL3,COL6)
Need your suggesion.
Regards,
Oli
[Updated on: Mon, 01 December 2008 05:17] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Createing index [message #362777 is a reply to message #361952] |
Thu, 04 December 2008 02:09 |
Olivia
Messages: 519
Registered: June 2008
|
Senior Member |
|
|
Below are the index on table1:
index1: (COL1, COL2,COL4, COL5,COL7)
index2: (COL7,COL6)
Index on table2: index_1(col_N)
Regards,
Oli
|
|
|
|
| Re: Createing index [message #362810 is a reply to message #361952] |
Thu, 04 December 2008 03:20 |
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Give us the results of the following queries:
SELECT COUNT(*) FROM TABLE1 WHERE COL1 IN
(SELECT DISTINCT COL_M FROM TABLE2
WHERE COL_N BETWEEN TO_DATE('03/07/2008','DD/MM/yyyy') AND
TO_DATE('08/07/2008','DD/MM/yyyy'))
AND COL3='POS' AND COL6 ='PRE';
SELECT COUNT(*) FROM TABLE1 WHERE COL1 IN
(SELECT DISTINCT COL_M FROM TABLE2
WHERE COL_N BETWEEN TO_DATE('03/07/2008','DD/MM/yyyy') AND
TO_DATE('08/07/2008','DD/MM/yyyy'));
SELECT COUNT(*) FROM TABLE1
WHERE COL3='POS' AND COL6 ='PRE';
SELECT COUNT(*) FROM TABLE1;
[Updated on: Thu, 04 December 2008 03:22] Report message to a moderator |
|
|
|
| Re: Createing index [message #362844 is a reply to message #362189] |
Thu, 04 December 2008 05:00 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Have you analysed your tables?
The second query will probably do a Fast Full Index scan of index 1, as it can get all the data it needs for the query from the index - ie it doesn't need to acess the table at all.
The first query requires access to the table to check the conditions on Col_3 and Col_6, so it does a full index scan of table 1.
You don't need the distinct in the subquery.
Can you post the whole Plan for the queries - you've been here long enought that we shouldn't have to ask for this for a tuning question.
|
|
|
|
| Re: Createing index [message #362852 is a reply to message #362844] |
Thu, 04 December 2008 05:30 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
How many rows in TABLE1 (you say millions, then 10,65876 - which is a little over 1 million if commas were right)?
How many of them match the COL3/COL6 predicates?
How many match the sub-query (but not the COL3/COL6) predicates?
How many does the query return in total?
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
