Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

WebPagiination_RecordsFilters.

Posted on 2011-09-23
10
Medium Priority
?
259 Views
Last Modified: 2012-06-21

If I have a catalog table with 100,000 rows, and I am displaying 500 rows per page using the pagination query.

If I need to display a dropdown list for all pages with data (word column) describing the data like this, so when user selects that page it displays the other rows

Page 1 (Row1 aaa - Row 499 bbb)
Page 2 (Row 500 ccc - Row 999 fff)
Page 3 (Row 1000 word - Row 1500 word)

How do you implement this (sql or plsql or dedicated table refreshed nightly)?
I dont think something like this would be efficient.

select word from (SUBQUERY for RESULTSET) where rownum in (1,499,500,999,....)

I know you dont like this but it is a must requirement for one application.
0
Comment
Question by:sam15
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 36588505
select * from
(select x.*, rownum rn from
(select * from your table
order by some_column) x
where rownum <=  :your_upper_limit)
where rn >= :your_lower_limit
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36588592
Why not the row_number() over(order by some_column) and remove one nested select?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36588605
row_number will force full evaluation of the entire recordset

using the nested queries with ROWNUM will allow your execution plan to introduce a STOP KEY operation
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:sam15
ID: 36588647
I already have the pagination query. I think you misunderstood me.

The think is i need to display a dropdownlist with all the pages and the word range from those records on each page. So when user selects (Page 11 yaaa - zaaa) it will navigate those records.

For ths dropdown list i would only need to obtain only the records 1, 499, 500, 999,1000, 1499, 1500, etc. of the result set. Is there a fast way to do this in sql.

My thinking is this must be a full table scan (subquery) and then somhow selecting those rows using a incremetning variable but there might be a better way.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 36588677
oh yuck!

in that case do something like the pagination but use an inlist instead of a range to select the individual rows you want.


select * from
(select x.*, rownum rn from
(select * from your table
order by some_column) x
where rownum <=  :your_upper_limit)
where rn in (1,499,500,999,1000,1499,1500)


if you don't know how many there will be then try something like this...

select * from
(select x.*, rownum rn from
(select * from your table
order by some_column) x
where rownum <=  :your_upper_limit)
where rn = 1 or mod(rn,500) in (0,499)

0
 

Author Comment

by:sam15
ID: 36588777
well it is a dynamic table (records added weekly) so do I need to coune records first?

But I am thinking how your query would retrieve those selected records. Let us say the count was 100,000.

select * from
(select x.*, rownum rn from
(select * from your table
order by some_column) x
where rownum <=  100000)
where rn = 1 or mod(rn,500) in (0,499)

I have not tested this yet but would this give me ROW1, ROW499, Row500, ROW999, ROWS1000, etc..

I assume also you are doing a FULL TABLE SCAN and there is no way around it? right
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36588950
it might do an index scan based on the order by clause

depends on how many blocks in the table the query will have to visit to find the rows.

if you have a billion rows,  and "some_column" is relatively clustered, then it's possible finding the first 100000 rows will be a relatively lightweight index lookup

otherwise, it'll probably do a table scan
0
 

Author Comment

by:sam15
ID: 36589896
I did a quick test using one table column with 100,000 rows. see attached text file for results.
It took 1 second. It might take more with actual table.

Do you think it might be better to create an MV that stores this list and refresh it nightly. That would be much quicker if the table does not change much every day.
testT5.txt
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36591066
If you have the storage, and your data is relatively static, then sure, an MV sounds like a reasonable idea for performance improvement.
0
 

Author Comment

by:sam15
ID: 36593357
sdstuber: you deserve a promotion to super genius Rank now.

thanks,
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question