Solved

WebPagiination_RecordsFilters.

Posted on 2011-09-23
10
255 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
  • 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 76

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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Does your audience prefer people in photos or no people? How can you best highlight what you’re selling? What are your competitors doing, and what can you do that is different and unique from them?  Continue reading to learn how to make your images …
When crafting your “Why Us” page, there are a plethora of pitfalls to avoid. Follow these five tips, and you’ll be well on your way to creating an effective page.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

839 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