Re: row_number ignores where clause w/ order by

Patrick...

Sorry, think I misread your original post...and I suspect your reply has a
typo as what you've actually posted would be a syntax error.

In answer to your actual question...

The code you posted doesn't need REGEXP_LIKE(), this will give the same
rows, but with the row_numbers() you'd expect.
select row_number() over(order by data_size desc) as rowNumber, s.*
from qSys2.sysTableStat s
where table_schema = 'CLOCF'

If you really need REGEXP_LIKE(). you'd probably want to build a CTE

with big_tbls as (
select * from qSys2.sysTableStat
where regExp_like( table_schema, 'CLOCF' )
)
select row_number() over(order by data_size desc) as rowNumber, s.*
from big_tbls;

note: huge performance improvement by using the first form :)

Now as to what's going on...
My first thought was the REGEXP_LIKE() was the only culprit...

But testing REGEXP_LIKE() on a physical table doesn't have the same issues.

Thus, the full answer seems to be that standard SQL comparisons like '=' or
'LIKE', can be pushed down into the UDTF that builds the qSys2.sysTableStat
view.
Whereas REGEXP_LIKE() can't be, so the row_number() function gets applied
first probably because the view has to be instantiated first...

Looking at the Visual Explain for both forms shows you the differences.

HTH,
Charles


On Thu, Nov 29, 2018 at 12:09 PM Patrick Conner via MIDRANGE-L <
midrange-l@xxxxxxxxxxxx> wrote:

Charles,


I tried:


select row_number(order by data_size desc) over() as rowNumber, s.*
from qSys2.sysTableStat s
where regExp_like( table_schema, 'CLOCF' )
;



...and I get the same results. First record is number 55, second is 57,
etc. The 'where' is still ignored as far as numbering the rows is
concerned.



Patrick



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

message: 5
date: Thu, 29 Nov 2018 09:56:36 -0700
from: Charles Wilt <charles.wilt@xxxxxxxxx>
subject: Re: row_number ignores where clause w/ order by

If you want the row_number to be generated in a specific order, you need to
include the `window-order-clause`

select row_number() over (order by data_size desc)
<snip>

Charles



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com