Solved

WebPagiination_RecordsFilters.

Posted on 2011-09-23
10
258 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
Certified OpenStack Administrator Course

We just refreshed our COA course based on the Newton exam.  With 14 labs, this course goes over the different OpenStack services that are part of the certification: Dashboard, Identity Service, Image Service, Networking, Compute, Object Storage, Block Storage, and Orchestration.

 

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 500 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
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…
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…
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.

617 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