| Faster Query [message #11060] |
Thu, 04 March 2004 05:11  |
Alexander Dumas
Messages: 3
Registered: January 2004
|
Junior Member |
|
|
Hi... I would like to know if I can improve the execution time for the query below.
TableA is growing quickly and is currently 500,000 rows.
TableB is small, about 50 rows and is fairly static.
I have indexes on PersonSequenceNumber an B_SequenceNumber and Name.
Is there anything else I can do? Rework the query?
******************************************************
select * from (
select
a.B_SEQUENCENUMBER,
a.PERSONSEQUENCENUMBER,
a.Name
b.LastName,
b.FirstName
from TableA a,
TableB b
where a.PersonSequenceNumber = b.PersonSequenceNumber
and a.B_SEQUENCENUMBER = 2
and UPPER(ltrim(rtrim(a.Name))) like '%JONES%'
order by BIRTHDATE desc
) b
where rownum < 7;
|
|
|
|
| Re: Faster Query [message #11061 is a reply to message #11060] |
Thu, 04 March 2004 05:53  |
Art Metzer
Messages: 2480
Registered: December 2002
|
Senior Member |
|
|
I don't have tables to test this query against, but if you are on at least version 8.1.6, you can use analytic functions to solve this kind of problem:SELECT rnkd.b_sequencenumber
, rnkd.personsequencenumber
, rnkd.name
, rnkd.lastname
, rnkd.firstname
FROM (SELECT a.b_sequencenumber
, a.personsequencenumber
, a.name
, b.lastname
, b.firstname
, <a href="http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions35a.htm#1064413">DENSE_RANK</a>() OVER (ORDER BY birthdate DESC) age_rnk
FROM tablea a
, tableb b
WHERE a.personsequencenumber = b.personsequencenumber
AND a.b_sequencenumber = 2
AND UPPER(LTRIM(RTRIM(a.name))) LIKE '%JONES%') rnkd
WHERE rnkd.age_rnk <= 6
/
I hope this helps,
Art.
|
|
|
|
|
|
| Re: Faster Query [message #11063 is a reply to message #11060] |
Thu, 04 March 2004 07:45 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
The indexes on Name (because you are wrapping the column with functions) and PersonSequenceNumber (not used for the join, on the TableA side) in TableA will do nothing for this query. An index on PersonSequenceNumber in TableB may or may not be helpful given its small size.
Out of the 500K rows in TableA, how many have a value for B_SequenceNumber = 2?
|
|
|
|
|
|
| Re: Faster Query [message #11065 is a reply to message #11064] |
Thu, 04 March 2004 08:50 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
If there are only 50 rows out of 500K, we need to look at the EXPLAIN PLAN for this query because it should be using the index on that column.
Are you familiar with how to obtain the query plan?
Once we get the query using the correct index, this should run very fast.
|
|
|
|
|
|
|
|
| Re: Faster Query [message #11083 is a reply to message #11060] |
Fri, 05 March 2004 01:25 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You might also add an index on TableB (PersonSequenceNumber).
Which table is birthdate in?
Have you analyzed your tables, indexes, and indexed columns, so that the optimizer has the statistics that it needs to choose the best execution plan? If not, then please run the following, after creating the index on TableB, then re-try your query:
ANALYZE TABLE TableA
COMPUTE STATISTICS
FOR TABLE
FOR ALL INDEXES
FOR ALL INDEXED COLUMNS;
ANALYZE TABLE TableB
COMPUTE STATISTICS
FOR TABLE
FOR ALL INDEXES
FOR ALL INDEXED COLUMNS;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
