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!!!!
September 04, 2002 - 3:59 pm UTC
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.
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?
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
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
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
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
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
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.
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.
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?
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)
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?
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,
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.
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
January 08, 2009 - 9:21 am UTC
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 ?
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.
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.
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 !
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.
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.
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