Home » SQL & PL/SQL » SQL & PL/SQL » Search Result Page query
Search Result Page query [message #249627] Thu, 05 July 2007 06:25 Go to next message
srivaths
Messages: 60
Registered: June 2005
Location: France
Member

I am working with the plsql of a search Result page.

What I usually do is

1. Get the full record count of that query(search query which is very much dynamic).

2. Using rownum I create a extra column like a serial number and get the page number and accordingly I frame the records in xml and give it to the java page. (my scope is very much with the plsql).

If you see here, I have used the same query for count and every time the user presses next page again the same query is performed again by oracle and I take the selected records from the full set of records.

Can things be done better than this?

Thanks in Advance.

Srivaths
Re: Search Result Page query [message #249630 is a reply to message #249627] Thu, 05 July 2007 06:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just one remark: each time you execute the same query you may get another result set. So you can remove the first query.

Regards
Michel

[Updated on: Thu, 05 July 2007 07:23]

Report message to a moderator

Re: Search Result Page query [message #249632 is a reply to message #249630] Thu, 05 July 2007 06:46 Go to previous messageGo to next message
srivaths
Messages: 60
Registered: June 2005
Location: France
Member

I hope you are saying about the the query which gets the search count of the records. If so Its executed only once. Say for example I need to displya 3rd page record (10 records) I have to execute the full query and extract the third page record from the query.

Something like


select record_no,col1,col2 from (
select rownum record_no,col1,col2 from table1,table2
where 
...
....) a
where a.record_no > 20
and a.record_no <=30



Can this be averted?
Re: Search Result Page query [message #249637 is a reply to message #249632] Thu, 05 July 2007 07:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But all the queries with or without count, are on the same tables with the same predicates and so on.
So you hope you will get/work on the same result set.
It is wrong.

Regards
Michel
Re: Search Result Page query [message #249639 is a reply to message #249637] Thu, 05 July 2007 07:33 Go to previous messageGo to next message
srivaths
Messages: 60
Registered: June 2005
Location: France
Member

I hope I have not brought the question clearly.

I just need how to handle the search result page query returning the result set of the page requested. I have the total count in my hand. How do I proceed with it displyaing(returning the records) to the java page?

Please asssume that the data on which the query is going to run is going to be static for that moment.



Re: Search Result Page query [message #249641 is a reply to message #249639] Thu, 05 July 2007 07:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Please asssume that the data on which the query is going to run is going to be static for that moment.

But they don't.
So the work (counting) is useless.
So you can remove it.

Regards
Michel
Re: Search Result Page query [message #249644 is a reply to message #249641] Thu, 05 July 2007 07:42 Go to previous messageGo to next message
srivaths
Messages: 60
Registered: June 2005
Location: France
Member

I have to give them(jave page) the count of the result set because they have to display something like (in the top of the search page)

2nd page of N pages. Total Records : M records
Re: Search Result Page query [message #249656 is a reply to message #249644] Thu, 05 July 2007 08:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have to give them

But you can't.

Regards
Michel
Re: Search Result Page query [message #249668 is a reply to message #249656] Thu, 05 July 2007 08:25 Go to previous messageGo to next message
srivaths
Messages: 60
Registered: June 2005
Location: France
Member

Sorry my friend. I dont understand why I cant give them the total search result count.

Say for example the page contains input for some date range and a request_number range. The user can input either or both of them. Accordingly the search query is opened in another page.

Intitally they have to show the total result set, like how many records matched up the search query requested by the user. And below that they should show the first 20 records in the page. The user have options to navigate to next-next pages each by 20records thro the search result.





Re: Search Result Page query [message #249674 is a reply to message #249668] Thu, 05 July 2007 09:29 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Is there nobody else working on the system?
So, what if I add an entry while you are viewing page 1?
Your total just got screwed..
Re: Search Result Page query [message #249677 is a reply to message #249674] Thu, 05 July 2007 09:35 Go to previous messageGo to next message
srivaths
Messages: 60
Registered: June 2005
Location: France
Member

Quote:
Please asssume that the data on which the query is going to run is going to be static for that moment.


Srivaths
Re: Search Result Page query [message #249689 is a reply to message #249677] Thu, 05 July 2007 09:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why assuming something that is wrong?
Is stupidity the motto of your entreprise?

Regards
Michel
Re: Search Result Page query [message #249698 is a reply to message #249689] Thu, 05 July 2007 10:18 Go to previous messageGo to next message
srivaths
Messages: 60
Registered: June 2005
Location: France
Member

Ok taking a count before showing the result is wrong/absurd.

Ok Can you just let me know this atleast?

How will be a normal search result page programmed(fetching of the disticnt set of records for each page)?

I very much respect the time you have taken in trying to resolve my issue. I would be glad if you dont talk about motto of the company I am working with. If it really sounds absurd, you can always choose not to answer.

Thanks
Srivaths

Re: Search Result Page query [message #249700 is a reply to message #249698] Thu, 05 July 2007 10:33 Go to previous message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think I'm free to reply what I want to reply as you are free to post what you want to post.
This said, I hold my position if a company ask you to do this it is silly.
You think you will have (assuming 10 lines per page):
SQL> create table t as select rownum id from dual connect by level <= 20;

Table created.

SQL> select count(*) from t;
  COUNT(*)
----------
        20

1 row selected.

SQL> def page=1
SQL> select id 
  2  from ( select id, rownum rn 
  3         from  ( select id from t order by id ) 
  4         where rownum<=10*&page )
  5  where rn > 10*(&page-1)
  6  /
        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

SQL> def page=2
SQL> /
        ID
----------
        11
        12
        13
        14
        15
        16
        17
        18
        19
        20

10 rows selected.

But in fact you may have:
SQL> select count(*) from t;
  COUNT(*)
----------
        20

1 row selected.

SQL> def page=1
SQL> select id 
  2  from ( select id, rownum rn 
  3         from  ( select id from t order by id ) 
  4         where rownum<=10*&page )
  5  where rn > 10*(&page-1)
  6  /
        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

SQL> def page=2
SQL> /

no rows selected

Or
SQL> select count(*) from t;
  COUNT(*)
----------
        20

1 row selected.

SQL> def page=1
SQL> select id 
  2  from ( select id, rownum rn 
  3         from  ( select id from t order by id ) 
  4         where rownum<=10*&page )
  5  where rn > 10*(&page-1)
  6  /
        ID
----------
         1
         2
         4
         5
         7
         8
        10
        11
        13
        14

10 rows selected.

SQL> def page=2
SQL> /
        ID
----------
        16
        17
        19
        20

4 rows selected.

Or any other result.

Regards
Michel
Previous Topic: idle instance with / but working instance with full string
Next Topic: date field error
Goto Forum:
  


Current Time: Tue Dec 06 10:20:33 CST 2016

Total time taken to generate the page: 0.14463 seconds