Query Efficiency

I'm trying to pull the highest valued suffix for a phone number from a phone number inventory table. When a number is first assigned to a customer it receives a suffix such as 888-437-7777-1. If that number is disconnected and than assigned to another customer it would look as follows 888-437-7777-2. I'm trying to pull all of the telephone numbers from the inventory but I only want the highest suffix of the number because that will be the current status of the number. I wrote this subquery.
select tni.tel_nbr_npa ||''||tni.tel_nbr_nxx ||''||tni.tel_nbr_line_range, tni.tel_nbr_suf, tni.tel_nbr_status
from tel_num_inv tni
where tel_nbr_suf = (select max(tel_nbr_suf) from tel_num_inv
where tni.tel_nbr_npa||''||
tni.tel_nbr_nxx ||''||tni.tel_nbr_line_range = tel_nbr_npa||''||tel_nbr_nxx ||''||tel_nbr_line_range)

It works but it is very slow. Is my syntax as efficient as possible? I'm new at writing subquerys and querys in general. Thanks in advance.

Here's quire a nice method using an analytic function ...

Code:

---

select c1,c2,c3
from
(
select tni.tel_nbr_npa
||''||tni.tel_nbr_nxx
||''||tni.tel_nbr_line_range c1,
tni.tel_nbr_suf c2,
 tni.tel_nbr_status c3,
max(tni.tel_nbr_suf) over (partition by tni.tel_nbr_npa
||''||tni.tel_nbr_nxx
||''||tni.tel_nbr_line_range) c4
from tel_num_inv tni
)
where c2 = c4

---

Have a look at the SQL reference for details of analytic functions. They can be a very efficient way of achieving tasks that are tricky using standard sql.

Haven't tested it for syntax problems -- let us know if there is a problem.

Hi slimdave, Analytic funtions are a black box for me.

Does the format of the partition clause make any difference in efficiency?
e.g. would "partition by tni.tel_nbr_npa, tni.tel_nbr_nxx, tni.tel_nbr_line_range" be better? (My first reaction was that the concatenantion would prevent use of indexes - perphaps this is irrelevent?)

As far as I can see, a FTS will be used, the challenge is to be sure that there wont't be multiple FTS's.

Hmmm, that's a good question. i expect that indexes would be a benefit to the query, and that the concatanation would probably not use them.

If there was a composite index on the three partition columns + tel_nbr_sfx then in the absence of predicates on other columns, and as long as the four columns were non-nullable, a full scan of an index + a FTS might be used to eliminate the need for a sorting stage.

I can't be bothered to test it though. It's 2am and i'm waiting for 700 index partitions to rebuild. Zzzzzzzzzzzzzzzz.

in that case I'll keep keep quiet so you can sleep

Slimdave,

That was sweet. Returned 74000 + rows in 1 minute and 15 seconds (I never even let my query finish it was going so slowly). I guess I'll be reading up on analytic functions and partitions. This must be one of the built in advantages of Oracle over SQL Server. My co-workers were suggesting putting the results from the outer query in a temporary table and than querying against it. Thanks again for your help.

DB2 has analytic functions too

Quote:

---

My co-workers were suggesting putting the results from the outer query in a temporary table and than querying against it

---

They sure love their temp tables in SQLServer. Whenever anyone suggests creating a temp table or tables, then querying from it/them, use in-line views in Oracle.

See this thread at asktom.oracle.com for the reasons why.
asktom is also excellent on analytical functions, and you/your boss should get Tom Kyte's book.

Quote:

---

Originally posted by pando
DB2 has analytic functions too

---

shut up you fool.