Home » RDBMS Server » Performance Tuning » Createing index (Oracle 10g)
Createing index [message #361952] Fri, 28 November 2008 12:56 Go to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 #362480 is a reply to message #361952] Tue, 02 December 2008 07:21 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Need suggestion for the above post.

Regards,
Oli
Re: Createing index [message #362661 is a reply to message #362480] Wed, 03 December 2008 11:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What are COL_M and COL_N - they don't show up in your list of indexed columns at all.
Re: Createing index [message #362674 is a reply to message #362661] Wed, 03 December 2008 12:52 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
Index created for COL_N (timetsamp) in table2??

[Updated on: Wed, 03 December 2008 12:53]

Report message to a moderator

Re: Createing index [message #362777 is a reply to message #361952] Thu, 04 December 2008 02:09 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12403
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 Go to previous messageGo to next message
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 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: how to find list of sql statements ran in the past time.
Next Topic: how to find the query optimized or Not?
Goto Forum:
  


Current Time: Sun Dec 04 19:09:09 CST 2016

Total time taken to generate the page: 0.08294 seconds