Query Efficiency

[brado]

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.

[slimdave]

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


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.

[DaPi]

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.

[slimdave]

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.

[DaPi]

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

[brado]

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.

[pando]

DB2 has analytic functions too

[slimdave]

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 (http://asktom.oracle.com/pls/ask/f?p=4950:8:120261630622596275::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1164655862293,) 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.


Originally posted by pando
DB2 has analytic functions too

shut up you fool.