473,327 Members | 1,967 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Please advise a better index

I created an index for a table but the performance increased just a little.
What the problem is and how should I create the index?
The SQL cannot be changed and only an index is required to increase the performance.

The following is detailed enviorenment.
(1)DB: Oracle10.2.0.3
(2)SQL:
select * from
(SELECT col1,col2, col3, col4 FROM table1
where col0 = 140 AND
col2>=to_date('2011-08-30','YYYY-MM-DD') AND
col2<=to_date('2011-08-30','YYYY-MM-DD') AND
upper(col4) = 'aaa'
ORDER BY col1 ASC)
where rownum<1000000;
(3)index
create index table1_idx on table1(col0,col2,upper(col4));
(4)toltal record count in table1: 3,000,000
(5)selected record count of (2): 100,000
(6)explain plan of the SQL
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 207M|
| 7873 (1)| 00:01:35 |

|* 1 | COUNT STOPKEY | | | |
| | |

| 2 | VIEW | | 100K| 207M|
| 7873 (1)| 00:01:35 |

|* 3 | SORT ORDER BY STOPKEY | | 100K| 16M|
41M| 7873 (1)| 00:01:35 |

| 4 | TABLE ACCESS BY INDEX ROWID| TABLE1 | 100K| 16M|
| 4038 (1)| 00:00:49 |

|* 5 | INDEX RANGE SCAN | TABLE1_IDX | 97885 | |
| 374 (2)| 00:00:05 |

--------------------------------------------------------------------------------
----------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<1000000)
3 - filter(ROWNUM<1000000)
5 - access("COL0"=140 AND "COL2"=TO_DATE('2011-08-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND

UPPER("COL4")='AAA')

Please advice.
Thanks in advance.
Sep 8 '11 #1
5 1657
rski
700 Expert 512MB
It does now work

upper(col4)='aaa'
Sep 8 '11 #2
Rabbit
12,516 Expert Mod 8TB
There's not much else you can do except to include the select columns in the index as well. This would fully cover the query and it would not need to use the table references at all.
Sep 8 '11 #3
rski
700 Expert 512MB
If you do what rabbit says you will have to add to the index both col4 and upper(col4), because you use col4 in select statement and upper(col4) in where.
Sep 8 '11 #4
Thank you rski and Rabbit.

The data in table1 are created for test.
Col0 and col4 are all the same, only col2 is different.
In this case, col0=140 matches all records in the table and it the same as full scan,right?

I tested some patterns and elapsed time were almost the same.
(1)create index table1_idx on table1(col0,col2,upper(col4));
(2)create index table1_idx on table1(col0,col2,col4,upper(col4));
(3)create index table1_idx on table1(col2,col0,upper(col4));
Sep 9 '11 #5
rski
700 Expert 512MB
You have to add all columns you use in select
col1,col2, col3, col4
and also upper(col4) and col0
So create index on col0, col1,col2, col3, col4 ,upper(col4) and see if it speeds up the query.
Sep 9 '11 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Girish Agarwal | last post by:
--0-474210375-1058976151=:31789 Content-Type: text/plain; charset=us-ascii Content-Id: Content-Disposition: inline Note: forwarded message attached. __________________________________
1
by: Question | last post by:
I am making a custom small menu which is most basic. Problem is that I can't make the first step work for some reason. I have an image to the left of where would be the layer positioned. This...
1
by: artifact | last post by:
Hello Can anyone tell me what is the best way to persist a complex object across postbacks in ASP.NET eg * I have a Person class with methods to edit the person's info * I navigate to my...
7
by: SQLDBA | last post by:
I am in the process of evaluating some SQL Performance Monitoring /DBA tool to purchase (For SQL Server 2000). I have the following list of software that I came across and have to finalize which...
14
by: Sean C. | last post by:
Helpful folks, Most of my previous experience with DB2 was on s390 mainframe systems and the optimizer on this platform always seemed very predictable and consistent. Since moving to a WinNT/UDB...
1
by: Sergiy Klokov | last post by:
i need to put a water mark in my pdf... and pdf viewer ocx... not Adobe ... please advise something
4
by: mike_li | last post by:
Passed DB2 UDB 701 test, how can I prepare for 704 test (Advanced DBA)? There is not as many info. on this test as on others. Thanks. Mike
0
by: hariprakash | last post by:
Hii Dudes, Iam Hariprakash. Today I was searching for DB2 DBA in google for exam 701. I got this link. This is very interesting and just now I joined this group. I took the exam 701 last tuesday...
0
by: User | last post by:
Hi everyone, Right now, one of our client have an in-house system which stores customer profiles / data. We are building an external web based system for them which also houses the same...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.