Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Vikas.

Asked: June 29, 2002 - 12:33 pm UTC

Last updated: January 30, 2009 - 12:08 pm UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi tom,
I have two question to ask. Many thanks in advance. great site and yor book ultimate i have ever come across.
Q.1 I have a cursor for example
cursor c1 is select * from emp;
I want to know that how many row will this cursor will return when opened. It is some thing like in oracle forms when you issue a query you can trap how many row will be that query return. by traping message 'Query will Reterive..' on-message trigger. How does form do it.

I have read you book about the multi versioning and rollback segment. can i make use multi versioning and rollback segment logic to get this. If yes the how?

Q.2 I am developing web application using mod_plsql. I which i have introduced paging. by using following logic
select * from (select rownum rx,e.* from
( select * from emp ) e where rownum <=:bv_endrow) where rx >= :bv_startrow ;

Now in first page the bv_endrow = 5 and bv_startrow = 1. So i will get first five rows. after getting 5 rows i want to know more row exists or not if exist then i will show next>> button or i will not show if no further record found.

The one way i can see is after execution of the first five rows set execute the query for the next 5 rows and collect the values into record type collection(table of record type) and store it on the current form in hidden item . if the count of table type return > 0 then show the next button and pass the hidden values when next button is clicked, or if count is 0 then i will not show the the next button. to be more clear i want to achive following

o i want to include paging of a resultset return from a query.
o I want to show the total no of the rows return by the query.
o I want my next button enable when if more rows are there.

It is somthing like your search which shows the total no of the record found then shows
10 rows than if more records are there then next button img.

Can we do it in one sql statement of a cursor. Kindly let me know the best possible way to achieve it.

Regards,
Vikas Sharma
India


and Tom said...

q1) forms does it the only way you can (and it is a horrible way). It does "select count(*) from ( YOUR_QUERY )". either that, or it fetches all possible rows (and then it knows). Not only is is horrible (slow, wasteful, etc) -- it can be wrong (since the count(*) and YOUR_QUERY are executed at two different points in time, the results can differ.

We do not know the number of rows your query will fetch until it fetches the very very last row. Consider "select * from one_billion_row_table", we do not make you wait till we fetch the last row to show you the first one!


q2) I always assume that if I fetched PAGESIZE rows -- then yes, there is a next page so show the next button. At worst, you'll show a blank page once in a while.

alternatively, use "rownum <= :bv_endrow+1". Don't print the last row, just use it as a flag to see whether you should put the button or not.



Paging -- you already know how to do that (you've got the query right there)
Total number of rows -- not knowable unless you really want to make them wait
Either assume there is a next page, if current page is full or overfetch by one.

The "total" records on my site is a guesstimate supplied by interMedia text -- it is not "general" purpose and only works with a text index (and it is not 100%, its an good approximation).

Rating

  (29 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

ashraf, June 30, 2002 - 7:41 am UTC

hi tom

i get your book it is very intrested aren't you plan to
to make a small one for new future for 9i for tuning and cluster


thanks

Vikas Sharma, July 02, 2002 - 7:22 am UTC

Hi

It is a surprized to know the way form does that.

Thanks lot,


Juan Pena, September 04, 2002 - 3:50 pm UTC

Hi there. I've been trying to do this for a long while, and only when I saw it here I could figure it out! Thanks!!!

One little doubt, though:

You did this:

select * from (
select rownum rx,e.* from (
select * from emp
) e
where rownum <=:bv_endrow
) where rx >= :bv_startrow

But this will give the same result:

select * from (
select rownum rx, * from emp
)
where rx between :bv_startrow and :bv_endrow

It seems you didn't want to use the between operand. Am I right? Why didn't you use it? Performance?

Thanks a lot again!!!!

Tom Kyte
September 04, 2002 - 3:59 pm UTC

see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:127412348064 <code>

for that answer to that (yes, performance is the reason)

askTom.oracle.com RULES!!!!!!!!!

Juan Pena, September 05, 2002 - 9:10 am UTC

Keep it up Tom! Your site rules!!!
I got the answers I was looking for.

Order By

Juan Guascarancas pena, October 07, 2002 - 3:16 pm UTC

Hey Tom, I read somewhere else on your site that a subquery cannot contain an ORDER BY clause before Oracle 8.1.
Is there a workaroung on Oracle 8.0 to get a "paged" resultset ordered?

Thanks.

Tom Kyte
October 07, 2002 - 4:46 pm UTC

Nope, you'll have to fetch and throw away the first N rows to get rows N through M.

How will i know how many search results have come

Lakshmana Swamy, October 08, 2002 - 3:16 am UTC

Hi Tom,
this is the first time i came to this forum. searching on Paging problem i came to here and i found very very valuable information.
My question is How would we know about how many search results we got?
Am i clear?



Tom Kyte
October 08, 2002 - 7:36 am UTC

It is clear -- but I thought my answer was clear:

We do not know the number of rows your query will fetch until it fetches the
very very last row. Consider "select * from one_billion_row_table", we do not
make you wait till we fetch the last row to show you the first one!


In short, until you fetch the very last row, no one knows how many rows it'll return. See also

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:3489618933902 <code>

and look for "google"

Lakshmana Swamy, October 09, 2002 - 2:18 am UTC

Hi Tom,
Thank you.
I have one question. I don't know whether placing this question here is right or wrong.
we are implementing paging concept. In our implementation we should know the total number of records. I need small help on this.
In our implementation we are using ResultSet object to retrieve all the records at once. using ResultSet.last() and ResultSet.getrow() we are getting the total number of records. My collegus and superiors are saying that ResultSet will not occupy much memory. so there is no problem with memory, if there are so many records and so many users.
Is it true?
what i am arguing is we should not get the whole resultset at once and we should get that page only i.e. the number of records we are displaying.
Can you tell me which is the correct way?

and one more thing is i thought in another way. i don't know whether it is possible to implement or not. My thinking is as folows.
If we maintain the search result in CachedRowset and this CachedRowset is stored in implicit application object so that every user can access that object. If anyone updating or modifying the database has to reexecute the query and set the CachedRowset to the latest value. Did you got my point?
Is it possible to implement and how good this approach is?

Thank you
Laxman

Tom Kyte
October 09, 2002 - 7:02 am UTC

Measure the memory used -- should be easy to do from the operating system

Ask them how much ram 1000, 10000 rows would take -- the result set is cached somewhere.


My way, if you believe "my way" to be the correct way, would be the google way or the way I do it here. the number of hits is an APPROXIMATION (you could get that by explaining the query or in 9i from the v$ tables). If the approximation is "wrong", so be it, we adjust.

Approxmiation ??

Vikas Sharma, October 09, 2002 - 1:31 pm UTC

Hi tom,
i shall be thaknful if you can tell how to get approxmation by query explain plan and how do we get it from v$tables in 9i. Please explain both for 9i and 8i.

Regards,

Vikas Sharma

Tom Kyte
October 09, 2002 - 5:15 pm UTC

You would use the documented EXPLAIN plan command in 8i and then a query similar to the one autotrace uses.  It could be like this:


ops$tkyte@ORA920.LOCALHOST> create or replace function get_est_rows( p_query in varchar2 ) return number
  2  as
  3     pragma AUTONOMOUS_TRANSACTION;
  4     l_card  number;
  5     l_stmtid varchar2(25) default 'ESTIMATED';
  6  begin
  7     execute immediate
  8     'EXPLAIN PLAN SET STATEMENT_ID= ''' || l_stmtid || ''' FOR  ' || p_query;
  9
 10     select cardinality into l_card
 11       from plan_table
 12          where statement_id = l_stmtid
 13            and id = 0;
 14
 15     delete from plan_table where statement_id = l_stmtid;
 16     commit;
 17
 18     return l_card;
 19  end;
 20  /

Function created.


So, now, just like autotrace:ops$tkyte@ORA920.LOCALHOST> set autotrace traceonly explain
ops$tkyte@ORA920.LOCALHOST> select /*+ first_rows */ * from dual;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=11 <b>Card=8168 </b> Bytes=16336)
   1    0   TABLE ACCESS (FULL) OF 'DUAL' (Cost=11 Card=8168 Bytes=16336)


ops$tkyte@ORA920.LOCALHOST> exec dbms_output.put_line( get_est_rows('select /*+ first_rows */ * from dual') );<b>
8168</b>

PL/SQL procedure successfully completed.


you can get the estimated cardinality.

Note: this info is available ONLY with the CBO 

note2: see the estimated cardinality from DUAL???  Make sure you have good stats or this will basically be a random number. 

What about in 9i v$tables?

Vikas Sahrma, October 10, 2002 - 12:24 am UTC

Hi Tom,

Thanks a lot for approxmation review follow up.

Kindly explain how do we do it via v$table. are the same statistics of plan_table are available in the v$tables. I am sorry i dont have 9i to see but soon we will have.

Regards,
Vikas Sharma

Tom Kyte
October 10, 2002 - 6:47 am UTC

It is basically the same structure as the plan table itself.

You don't need to have 9i installed to see this stuff. all of the docs are on OTN.

Here:

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96536/ch3194.htm#1101574 <code>

is v$sql_plan specifically.

Acuuracy of query estimation

Tom, October 11, 2002 - 4:02 am UTC

Tom,

Assuming that you have up to date statistics on all tables used in the query and statistics on all indexed columns [with histograms where necessary], how accurate are estimated cardinalities likely to be?

Will this accuracy decrease as the query gets more complex?

Thanks

Tom Kyte
October 11, 2002 - 8:14 pm UTC

As good as you could guess yourself.


Say I give you a query:


select * from emp where ename like '%A%';


we won't be very good -- it'll be a guesstimate.

Now, say we give you:

select * from emp where ename = :X;


Now, we'd be very good.


Both of those queries are "simple" -- neither "complex". The complexity matters not -- it is the specificity that matters. Can you look at the question and say "this will return N rows" and be confident. If you can, we can -- if you cannot, we cannot.

Lakshmana Swamy, October 11, 2002 - 4:36 am UTC

Hi Tom,
Thank you for your information.
But, i did't understand whether ResultSet in JAVA occupies memory or not. I have searched so many forums regarding this. Some people say the ResultSet will not occupy memory, it only contains a reference. Some people like YOU are saying that ResultSet will be cached somewhere. So, i was in confusion.
I am believing your's is the correct way. but i have to clarify my doubt.
I decide to do a practical. I have created a table which contains username and password. and this table contains 120,000 records.
I took two Servlets. In First Servlet i retrieved whole Resultset and displayed first 20 records. i retrieved total number of records by ResulSet.last() and ResultSet.getRow() methods. In second servlet i used two statements. in first statement i retrieved the count and in second statement i retrieved the particular 20 records using your technique and displayed them.
I have executed these two in Tomcat 4.0.5 Server and My System has 256MB RAM and i am using Oracle9i. The first Servlet took around 17seconds to execute where as the Second Servlet executed in 2 seconds.

ON This I would like to ask a question.
What is the main reason per such a low performance by the First Servlet. Is it Database related or JAVA ResultSet related.
Any clarification is most valuable to me.

Thanks & Best Regards
laxman

Tom Kyte
October 11, 2002 - 8:17 pm UTC

It is going to depend on the jdbc driver, the implementation, the version, lots of stuff.



The first one, by going resultset.last, had to fetch over 120,000 rows (it would have been interesting for you to measure their MEMORY CONSUMPTION).

The other one had to fetch over 20 rows + 1 count (that was probably done via a index).

Me, I would make it even faster.

Blow off the count, just get the 20 rows. Instantaneous!

Approxmiation

A reader, October 12, 2002 - 4:00 am UTC

Hi Tom,

when I use BC4J it gives me correct estimate of the number of rows returned, although it is the estimate but "MOST" of the times it is accurate,
what technique is bc4j people using to get this ?..

can you put some light on this.


Tom Kyte
October 12, 2002 - 3:33 pm UTC

They do it just like forms. From a BC4J developer:

...
If they want to see all of the SQL that the BC4J framework
is issuing, they can start their application with the
Java VM command line flag:

-Djbo.debugoutput=console

and they'll see everything.

For our getEstimatedRowCount() method, we execute
the following query:

select count(*) from (............)

where the ........... is the query for the ViewObject in question,
with all it's where clause and bind variables set as they are
currently set.
........


That is why "most" of the times it is accurate -- it is the count of the rows your query will return AT THAT point in time (which is different from the actual point in time at which you run the query to get the results...)


My reply to them was:

yuck, hope no one uses it or at least they understand that this is like setting "slow=true" in their application!





Approximation

A reader, October 22, 2002 - 1:06 am UTC

It's disappointing to hear that "Experts at Oracle" are
using such not so good techniques.

Thanks for the insight on what BC4J was doing in getEstimatedRowCount() method.


how about this technique

Asim Naveed, May 11, 2003 - 3:21 am UTC

I want to use the following technique to get
the total no. of rows in a resultset before fetching
the whole reslutset. Sir Tom and others please comment
on peformance or other issue in this technique.

What ever SELECT you write just add an item COUNT(*) OVER()
at the end of the select list.
e.g
To get the total no. of rows of the resultset of

SELECT deptno, job
FROM dept, emp
WHERE dept.deptno = emp.deptno
GROUP BY deptno, job


just convert the above select into

SELECT deptno, job, COUNT(*) OVER()
FROM dept, emp
WHERE dept.deptno = emp.deptno
GROUP BY deptno, job


the last column will give the total no. of rows
returned by the query. Then in your program
just fetch the first row and read the third column.



Tom Kyte
May 11, 2003 - 9:55 am UTC

that works -- it gets an accurate (but to me "so what") number of rows in that result set however, it forces the entire result set to be gathered before the first row can be returned.

If you like that "side effect" (hey, on my search page would you like me to start counting exactly ever hit you have before I give you the first 10? or, do you just want me to get the first 10 asap? I get you the first 10 asap which means I can run asktom on 1/10th the hardware I would need otherwise. No point in my counting that there are 1,325 hits when you only care about the first 10) go for it.

Me, when I work on a web project, that is the first "feature" I go after and kill. All of a sudden, lots of stuff runs many times faster, with many orders of magnitude less logical IO, only a fraction of the hardware required before the change.

Performance when paging

Simphiwe Tshume, July 14, 2004 - 8:11 am UTC

Hi Tom,

Here is my query:

select * from (
select rownum rn, a.* from
(
select * from pipeline_master_view
where pipeline_no=3
and employee_no=141
and (appreceivedate >=to_date('2004-02-01','yyyy-mm-dd')
and appreceivedate <=to_date('2004-02-07','yyyy-mm-dd'))
) a
where rownum <= 10
)
where rn >= 1.

It works fine but my problem is that the oracle process
takes up to 99.9% of the CPU every time this query is executed.

Could you please help?


Tom Kyte
July 14, 2004 - 11:36 am UTC

so? you want it to use just 10% or something? what else will you do with the 90% remaining (use it or lose it -- cpu cannot be put into the bank for use later)

is the real problem that the query itself takes too long (that is a different problem). for that, you need to "tune query". interactive queries must return the first rows really fast (end users get bored). tune this query -- whatever the "view" is underneath -- to make it go faster.

JDBC & row estimates

Andy, January 18, 2005 - 3:06 am UTC

Hi Tom,

I'm using the get_est_rows function you show earlier in this thread to get row estimates for paging purposes in my application. I'm binding the variables by using a preparedstatement object to get my data, but when I try to send the parameterized SQL string to get_est_rows, it throws an ORA-00911, presumably because something like "select id from fulltext where az = ?" is not recognisable to the EXPLAIN PLAN command in get_est_rows. So, my next attempt was to get the data via a preparedstatement, then unbind the variables (purely for the purposes of getting the row estimate) and send e.g. "select id from fulltext where az = '1.01.01'" to get_est_rows, which works fine. I have two questions: i) will using unbound variables with EXPLAIN PLAN command be almost as bad as not using binds at all (since most calls to get_est_rows will provide different SQL strings)? ii) is there another way to send a parameterized SELECT statement, with lots of "?"s, to the function in a way that will work?

(I'm on 9.2.0.1.0)

Tom Kyte
January 18, 2005 - 9:58 am UTC

"select id from fulltext where az = ?"

correct -- that is not sql. that is an ODBC'ism that unfortunately got accepted into the "enterprise spec" for JDBC.

You should send to this something like:

"where az = to_number(:x) and ax = to_date(:x) and ay = :y"
^^^^^^^^^ when binding a number
^^^^^^^^ when binding a date
^^^^^^^^ when binding a string



To Simphiwe Tshume

Menon, January 18, 2005 - 11:13 am UTC

Are you using bind variables (example is not.)
Tom, how could you miss it?;)

JDBC & row estimates contd.

Andy, January 19, 2005 - 11:27 am UTC

Thanks for your reply, Tom. I've gone the route of using v$sql_plan instead of EXPLAIN PLAN so I can take advantage of bind peeking, but it's not entirely clear to me why the card values don't always show up in v$sql_plan. For example:

mires@WS2TEST> var x varchar2(10)
mires@WS2TEST> exec :x := 'test'
mires@WS2TEST> select * from (select rownumber from fulltext
where contains(akteinh, :x, 1) > 0) where rownum < 10;

ROWNUMBER
----------
37944
48273
48272
48271
48270
48269
48268
48267
48266

9 Zeilen ausgewõhlt.

Ausf³hrungsplan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=45)
1 0 COUNT (STOPKEY)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'FULLTEXT' (Cost=2 Card=1 Bytes=45)
3 2 DOMAIN INDEX OF 'FTI_AKTEINH' (Cost=0)

mires@WS2TEST> select id, operation, cardinality from v$sql_plan
where (address, child_number) in
(select address, child_number from v$sql where sql_text like '%contains(akteinh%'
and sql_text not like '%sql_text%')
order by id;

ID OPERATION CARDINALITY
---------- --------- -----------
0 SELECT STATEMENT
1 COUNT
2 TABLE ACCESS 1
3 DOMAIN INDEX

Why is there no cardinality value for the SELECT step? (I'm trying to extract the card value to use for row estimates: in get_est_rows that was straightforward, as id = 0 always picked the last step and card values in plan_table seem to be shown for all operations).

why delete & rcommit in get_est_rows?

Vlado, May 19, 2005 - 1:08 pm UTC

In your function get_est_rows you delete from the plan_table based on the statement_id.
However, since the statement_id may not be unique, the function may delete rows that belong to execution plans of other users...

Why not rollback instead of delete & commit? That should guarantee that the appropriate rows will be deleted.
Any disadvanteges of this approach?

Tom Kyte
May 19, 2005 - 1:57 pm UTC

I'm using estimated as the name.

That is MY name. I'm assuming no one else is.


I'll never delete anyone elses, read consistency and multi-versioning won't permit that.


Until we commit, the rows are visible only to us.

Therefore, when we delete - we delete only that which is committed or we created.

If someone else has a "committed" ESTIMATE plan -- they by definition already were done (they need it no longer).

But, there will never be another ESTIMATE plan for we delete them from existence prior to committing so no one else will ever see them to delete them!

nuber of records in cursor

mo, January 09, 2006 - 1:52 pm UTC

Tom:

1. I know you do no recommend couting records in a ref cursor. But let us say I want to do it.

my cusror is built in L_QUERY

how do you I count the records in L_QUERY?

select count(*) from l_query does not work?

2. I see that you mentioned using this
ctx_query.count_hits

How do you use that to get total count of all records in cursor.

Thanks,

Tom Kyte
January 09, 2006 - 1:56 pm UTC

sigh....

do you use google? do you believe they are accurate in their counts?

well, if you really really really really want to make the hardware vendors very very happy...

open l_cursor for
'select a.*, count(*) over () that_silly_count
from ('||l_query||') a';


and sit back and wait.... and watch the little lights light up like christmas on your disks, your cpu monitors....




cursor count

mo, January 09, 2006 - 3:18 pm UTC

TOm:

1. I did this to print the value of the count but it did not work. Is this correct?

open l_cursor for
'select a.*, count(*) over () that_silly_count
from ('||l_query||') a';
LOOP
FETCH l_cursor into l_rec;
exit when l_cursor%notfound;
htp.p(l_rec.that_silly_count);
END LOOP;
Close l_cursor;

2. Why would it be too slow? Is it because the full table scan? Doing a "select count(*) from table" in sql*plus runs very fast.

Tom Kyte
January 09, 2006 - 3:23 pm UTC

"but it did not work"??? what do you mean by that?

you did add an attribute to l_rec right?


Also, the only reason you would count the records would be to tell a user "you are looking at 1 through 10 of 343,214 records". If you are going to FETCH ALL OF THEM, just fetch them - and then you'll know how many you have. It is ONLY when you are not going to fetch them all that you would think about counting them - and in that case - in order to fetch 1..10 of 343,214 - you'll have to wait for ALL 343,214 records to be processed - instead of just the first 10.




Paging

Tanbir Ansari, January 08, 2009 - 4:15 am UTC

what is the right way to paging in orecle if we have 50000 of recordset

Paging query

K Kiran, January 23, 2009 - 11:03 am UTC

Hi Tom,

I was looking at your articles regarding paging and found them very useful. I wanted to know if there is any way to optimize the below query which is related to paging .

I have a table T1 with 100,000 rows , T2 with another 100,000 rows.

T1 table structure -- CustId ,CustName ,Stdate ,Metric1 ,Metric2
T2 table structure -- CustId ,CustName ,Stdate ,Metric3 ,Metric4

CustId is the primary key in both tables.

I have to get this result :
CustId,CustName,Stdt,Metricx,Metricy and page this based on the customer Id.

The query looks like :

SELECT A.CustId,A.CustName,A.Stdt,Metricx,Metricy FROM (Select CustId,CustName,stdt,Metric1,Metric2 from T1 UNION Select Custid,CustName,stdt,Metric3,Metric4 from T2 ) A
WHERE A.CustId IN (SELECT CustId
FROM (SELECT CustId,CustName,ROWNUM RNUM1
FROM ( SELECT DISTINCT CustId,CustName
FROM (Select CustId,CustName,stdt,Metric1,Metric2 from T1 UNION Select Custid,CustName,stdt,Metric3,Metric4 from T2 )
WHERE custid IS NOT NULL
) A
WHERE ROWNUM <= v_END_NUM
) B
WHERE RNUM1 >= v_START_NUM)

Paging by CustId is just making the query perform slower by 10 times. Can you suggest a better solution ?
Tom Kyte
January 23, 2009 - 1:09 pm UTC

did you really really mean to use union? before I look at this further, I want you to answer that. That looks like a bug to me.


A union B = distinct(A+B)

A union all B = (A+B)

but wait, i looked further on. wait, what?? there is no order by on that, that is just garbage - totally none deterministic. We are free to return any data for any page we want to.


Please, don't post a query next time, instead post the DETAILED specifications you worked from - give us the question, not the answer....

Paging query

K Kiran, January 23, 2009 - 1:52 pm UTC

Hi Tom,

It is Union All .. typo . I made change to the query as i didnt want to post the original one .. i made a small mistake there ... the original query does contain Order by CustId.


Tom Kyte
January 23, 2009 - 2:08 pm UTC

give us a well formed example, including create tables so I can the syntax correctly.

And tell us the GOAL here, the question, because I don't get all of those subqueries, they don't seem to do anything

be very very very specific.

Paging query

K Kiran, January 23, 2009 - 2:40 pm UTC

Hi Tom,

Okay .. as you say !
Oracle version : 10.2.0.4
Table T1 :

Create table T1 ( CustId number not null,
CustName varchar2(100) not null,
Stdt date not null,
metric1 varchar2(100),
metric2 varchar2(100),
CONSTRAINT "PK_T1" PRIMARY KEY ("CustId", "Stdt") ENABLE);

T1 contains 100,000 customers.

T1 sample data :
Insert into T1 values (1,'test','01-jan-2008','H',NULL);
Insert into T1 values (1,'test','31-dec-2008','H',NULL);
Insert into T1 values (1,'test','01-jan-2007','H',NULL);
Insert into T1 values (2,'test1','01-jan-2008','H','I');
Insert into T1 values (2,'test1','01-jan-2007',NULL,'I');
Insert into T1 values (7,'test7','01-jan-2007','M',NULL);

Table T2 :

Create table T2 ( CustId number not null,
CustName varchar2(100) not null,
Stdt date not null,
metric1 varchar2(100),
metric2 varchar2(100), CONSTRAINT "PK_T2" PRIMARY KEY ("CustId", "Stdt") ENABLE);

T2 contains 100,000 customers.
T2 sample data:
Insert into T2 values (1,'test','01-jan-2008','K','N');
Insert into T2 values (1,'test','31-dec-2008','S',NULL);
Insert into T2 values (1,'test','01-jan-2007','M','N');
Insert into T2 values (4,'test14','01-jan-2008','H','I');
Insert into T2 values (4,'test14','01-jan-2007',NULL,'I');
Insert into T2 values (5,'test5','01-jan-2007','M',NULL);

Goal : Get the CustId, CustName, Stdt, MetricX , MetricY from T1 and T2 .. page based on the CustId.
Order the page based on CustId.

Result : If page size is 2

1,'test','01-jan-2008','H',NULL
1,'test','31-dec-2008','H',NULL
1,'test','01-jan-2007','H',NULL
1,'test','01-jan-2008','K','N'
1,'test','31-dec-2008','S',NULL
1,'test','01-jan-2007','M','N'
2,'test1','01-jan-2008','H','I'
2,'test1','01-jan-2007',NULL,'I'

If page size is 3 Result:

1,'test','01-jan-2008','H',NULL
1,'test','31-dec-2008','H',NULL
1,'test','01-jan-2007','H',NULL
1,'test','01-jan-2008','K','N'
1,'test','31-dec-2008','S',NULL
1,'test','01-jan-2007','M','N'
2,'test1','01-jan-2008','H','I'
2,'test1','01-jan-2007',NULL,'I'
4,'test14','01-jan-2008','H','I'
4,'test14','01-jan-2007',NULL,'I'

I hope i gave you enough information.
Tom Kyte
January 23, 2009 - 3:46 pm UTC

is there a missing table, you know - the one where cust_id is a primary key, and these two tables have a foreign key to it.

I doubt it, given that there shouldn't be two tables in the first place, but cannot hurt to ask.

without it, this is going to be painful

Paging Query

K Kiran, January 23, 2009 - 4:35 pm UTC

Hi Tom,

Yes there is a parent table PT :

Create table PT ( CustId number not null,
CustName varchar2(100) not null,
DOB date not null,
Salary varchar2(100),
Grade varchar2(100), CONSTRAINT "PK_T2" PRIMARY KEY ("CustId")
ENABLE);

And CustId in T1 and T2 refer to this parent key(CustId).

Paging Query...

K Kiran, January 23, 2009 - 4:36 pm UTC

Ooopsss .. Its PK_PT not PK_T2 for the Primary Key name !
Tom Kyte
January 24, 2009 - 1:04 pm UTC

use parent table
get N custid's from it
join to t1 and t2 using this

done

ops$tkyte%ORA10GR2> Create table PT ( CustId number not null,
  2                        CustName varchar2(100) not null,
  3                        DOB date not null,
  4                        Salary varchar2(100),
  5                        Grade varchar2(100), CONSTRAINT pk_pt PRIMARY KEY (CustId)
  6                                  )
  7  /

Table created.

ops$tkyte%ORA10GR2> exec dbms_stats.set_table_stats( user, 'PT', numrows => 100000, numblks => 10000 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> Create table T1 ( CustId number not null references pt,
  2                        CustName varchar2(100) not null,
  3                        Stdt date not null,
  4                        metric1 varchar2(100),
  5                        metric2 varchar2(100),
  6  CONSTRAINT PK_T1 PRIMARY KEY (CustId, Stdt) ENABLE);

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_stats.set_table_stats( user, 'T1', numrows => 500000, numblks => 50000 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>     Create table T2 ( CustId number not null references pt,
  2                        CustName varchar2(100) not null,
  3                        Stdt date not null,
  4                        metric1 varchar2(100),
  5                        metric2 varchar2(100), CONSTRAINT PK_T2 PRIMARY KEY (CustId, Stdt) ENABLE);

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_stats.set_table_stats( user, 'T2', numrows => 500000, numblks => 50000 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> variable minrnum number
ops$tkyte%ORA10GR2> variable maxrnum number
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> column PLAN_TABLE_OUTPUT format a72 truncate
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> select *
  2    from (
  3  select pt.*, rownum rnum
  4    from (
  5  select /*+ first_rows(5) */ custid, custname
  6    from pt
  7   order by custid
  8         ) pt
  9   where rownum <= :maxrnum
 10         )
 11   where rnum >= :minrnum
 12  /

Execution Plan
----------------------------------------------------------
Plan hash value: 1397970745

------------------------------------------------------------------------
| Id  | Operation                      | Name  | Rows  | Bytes | Cost (%
------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |       |     5 |   390 |     3
|*  1 |  VIEW                          |       |     5 |   390 |     3
|*  2 |   COUNT STOPKEY                |       |       |       |
|   3 |    VIEW                        |       |     5 |   325 |     3
|   4 |     TABLE ACCESS BY INDEX ROWID| PT    |   100K|  6347K|     3
|   5 |      INDEX FULL SCAN           | PK_PT |     5 |       |     2
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RNUM">=TO_NUMBER(:MINRNUM))
   2 - filter(ROWNUM<=TO_NUMBER(:MAXRNUM))

ops$tkyte%ORA10GR2> with cust_ids
  2  as
  3  (
  4  select *
  5    from (
  6  select pt.*, rownum rnum
  7    from (
  8  select /*+ first_rows(5) */ custid, custname
  9    from pt
 10   order by custid
 11         ) pt
 12   where rownum <= :maxrnum
 13         )
 14   where rnum >= :minrnum
 15  )
 16  select *
 17    from (
 18  select /*+ first_rows(20) */ cust_ids.custid, cust_ids.custname, t1.stdt, t1.metric1, t1.metric2 from t1, cust_ids where t1.custid(+) = cust_ids.custid
 19  union all
 20  select /*+ first_rows(20) */ cust_ids.custid, cust_ids.custname, t2.stdt, t2.metric1, t2.metric2 from t2, cust_ids where t2.custid(+) = cust_ids.custid
 21         )
 22   order by custid, stdt
 23  /

Execution Plan
----------------------------------------------------------
Plan hash value: 1287892351

------------------------------------------------------------------------
| Id  | Operation                        | Name                      | R
------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                           |
|   1 |  TEMP TABLE TRANSFORMATION       |                           |
|   2 |   LOAD AS SELECT                 |                           |
|*  3 |    VIEW                          |                           |
|*  4 |     COUNT STOPKEY                |                           |
|   5 |      VIEW                        |                           |
|   6 |       TABLE ACCESS BY INDEX ROWID| PT                        |
|   7 |        INDEX FULL SCAN           | PK_PT                     |
|   8 |   SORT ORDER BY                  |                           |
|   9 |    VIEW                          |                           |
|  10 |     UNION-ALL                    |                           |
|  11 |      NESTED LOOPS OUTER          |                           |
|  12 |       VIEW                       |                           |
|  13 |        TABLE ACCESS FULL         | SYS_TEMP_0FD9D6608_2BD9A3 |
|  14 |       TABLE ACCESS BY INDEX ROWID| T1                        |
|* 15 |        INDEX RANGE SCAN          | PK_T1                     |
|  16 |      NESTED LOOPS OUTER          |                           |
|  17 |       VIEW                       |                           |
|  18 |        TABLE ACCESS FULL         | SYS_TEMP_0FD9D6608_2BD9A3 |
|  19 |       TABLE ACCESS BY INDEX ROWID| T2                        |
|* 20 |        INDEX RANGE SCAN          | PK_T2                     |
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("RNUM">=TO_NUMBER(:MINRNUM))
   4 - filter(ROWNUM<=TO_NUMBER(:MAXRNUM))
  15 - access("T1"."CUSTID"(+)="CUST_IDS"."CUSTID")
  20 - access("T2"."CUSTID"(+)="CUST_IDS"."CUSTID")

ops$tkyte%ORA10GR2> set autotrace off

Paging Query

K Kiran, January 26, 2009 - 1:30 pm UTC

Hi Tom,

I agree with your solution .. but i cannot take CustIds from the parent table .. as i dont know if the customers that i picked from the parent table .. are going to be same customers in Union query. There may be missing customers in T1 or in T2 which are picked up in parent table.
Tom Kyte
January 28, 2009 - 8:01 am UTC

that is why I did an outer join. it works.

anything else is going to be horribly painful.

Paging Query

K Kiran, January 28, 2009 - 9:50 am UTC

Tom,

I see the outer join .. but i want to page based on the customers in the Union query .. not based on the parent table .. I dont see the correct results if i use parent table for paging ... for eg:

Entries in Parent table :

1 A ...
2 B ...
3 C ...
4 D ...
5 E ...
6 F ...

Data in Union query might be like :

2 B ...
4 D ...
5 E ...
6 F ...

When i say get me 2 customers per page .. i should see ..

2 B ...
4 D ...

not

1 A novalue
2 B ...

I suppose the query you gave results in this.
Tom Kyte
January 30, 2009 - 12:08 pm UTC

it does, have you asked to see if that would be acceptable. For you see

to drive the query off of the parent table - easy, get N parent records, join.

to drive the query off of the union all of two otherwise unrelated tables - NOT POSSIBLE IN ANY EFFICIENT MANNER.


Think about it. Think hard and long about it. What if the first table T1 has customers 1..100. And table T2 has 101 through 200.

Say the page size is "10".

You want page 5 (41-50). Well, page "5" from T2 is 141-150, page "5" from T1 is 41-50. Easy enough. Get both, sort, and you could get the first 5.

Now, try to get me page 15 please. Page 15 is 141-150.

Page 15 from T1 is the empty set.
Page 15 from T2 is the empty set.


So, you cannot get "pages" from t1 and t2. You have to take both sets, munge them together and sort them and then get page 15.

In other words: can you do it? sure
Will it be anything useful to you? no, it will kill your performance.


Your data model doesn't really support your needs, this union all thing is a horrible idea, it should never have been two tables in the first place.


I'll stick with my first query and the fact that some customer shows up without metrics - heck, your end users would probably even like that - they might not even realize they would be missing them otherwise

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library