Last record
AKS, July 05, 2001 - 1:01 am UTC
I think this can be done by using max(rowid) in the where clause.
Fetching Last Record from a table
Rajasekar N, July 05, 2001 - 1:01 am UTC
Tom,
Can't we fetch the last record like this...?
select * from t
minus
select * from t
where rownum < (select count(*) from t)
Fetching Last Record from a table
Rajasekar N, July 05, 2001 - 1:03 am UTC
Tom,
Can't we fetch the last record like this...?
select * from t
minus
select * from t
where rownum < (select count(*) from t)
Use ROWNUM
Dhawal Shah, July 05, 2001 - 2:27 am UTC
This is very much possible with rownum.
The solution which is given here calls for the overhead of
one column.
Though the cost for the following query may be a bit high,
you can reduce the overhead of storing a column.
" select * from emp where rownum = 1 order by rowid desc; "
one more way ?
Vikram, July 05, 2001 - 7:39 am UTC
can't we use this ...
select * from t where rowid in(select max(rowid) from t);
Last row
Danial, July 05, 2001 - 9:03 am UTC
"I think this can be done by using max(rowid) in the where clause" -- would it still work if you do alot of deletes and updates?
Great
Reader, July 05, 2001 - 11:48 am UTC
What a greate clarification for a daily misconcept.
Thanks
Great
Reader, July 05, 2001 - 11:50 am UTC
What a greate clarification for a daily misconcept.
Thanks
Fetching last record from a table
DATTA KUAMR, July 05, 2001 - 12:57 pm UTC
I feel, whatever Mr.Tom has given is right. By using select max(rowid) from table, no doubt gives the result. If we try this soon after inserting a record into the table, we will get the correct result. But, try after inserting a row, select ... from table, order by ..., and again try select max(rowid) from table, I am sure you will not get the desired result.
Fetching of Last Record
Hassan Ayub, July 06, 2001 - 2:07 am UTC
Thanks a lot Tom for a very thorough and well explained answer.. I tried the approaches you suggested and you are certainly right!!!
clearest solution
Daikun, July 27, 2001 - 4:29 am UTC
the securest way to get consistent results! once more you made the theory behind sql and database-concepts clear! This ist more important than using 'dirty' tricks to improve performance...
Fetching last record from a table
Sujoy Hore, February 16, 2005 - 8:04 am UTC
Using the above concept to fetch last record, I wish to fetch the 2nd instance of a record containing one field with common value. Say,in this table there are 5 records against XYZ (name field),ABC has one record,DEF has three records.What I would like to fetch is second record details of XYZ. How do I do that? I can find out the first and last record against XYZ using the procedure earlier explained by you.
February 16, 2005 - 8:40 am UTC
are ABC and DEF in the problem description just to be confusing?
I ask because I see two possible questions here:
a) give me the second (YOU need to provide some definition of what second means here, you have not yet) record by NAME_FIELD (eg: we return the 2cd record by name_field after sorting by SOMETHING YOU NEED TO SPECIFY for XYZ, DEF -- ABC having one record does not have a 2cd)
b) given a name, say XYZ, find the 2cd record after sorting.
for a) you use:
select *
from ( select name_field,
row_number() over (partition by name_field ORDER BY SOMETHING ) rn
from t )
where rn = 1;
for b) you use:
select *
from (select a.*, rownum r
from (select * from t where name_field = :x order by SOMETHING ) a
where rownum <= 2 )
where r = 2;
rowid
KARTHICK, September 30, 2005 - 6:56 am UTC
Can you tell me how the ROWID are generated by oracle? That is suppose I have a table and sequentially I insert 100 rows and after that I delete few and again continue with my insert. Does the ROWID of the rows deleted will be reused ???
September 30, 2005 - 9:39 am UTC
the rowid is not "generated" in as much as "derived"
The rowid is the address of the row on disk.
Since space can and will be reused, rowids can and will be "reused"
A rowid implies NOTHING other than the location of the row physically. It does not imply age, order of insertion, or anything like that.
rowid
KARTHICK, October 03, 2005 - 10:26 am UTC
i insert into a table the following data
rowid value
-------------
AAA 10
BBB 20
CCC 30
as you said rowid is a value derived by oracle and just for example iam having the above specified values.
now i delete the value 20
rowid value
-------------
AAA 10
BBB
CCC 30
now i insert another row with value 40
rowid value
-------------
AAA 10
BBB
CCC 30
DDD 40
here i dint use the row id BBB again. i dint because when i was going through HIGH WATER MARK in your book it said
the free space will not be used untill the table is rebuild or truncated. and it also says, when a full table scan is done the row with rowid BBB will also be scaned.
so now if i rebuild how the records will be organized, will new rowid will be derived for all the rows i.e. new blocks will be assigned to all the rows in a sequential order or ???
( and another thing to be noted is you dont suggest rebuild of table )
October 03, 2005 - 11:24 am UTC
rowids can and will be reused randomly. That you showed a case where it wasn't (a made up case) isn't really meaningful.
That space can and will be reused.
ops$tkyte@ORA9IR2> create table t ( x int, y varchar2(4000), z varchar2(4000) ) tablespace tools;
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 1, null, null );
1 row created.
ops$tkyte@ORA9IR2> insert into t values ( 2, rpad('*',4000,'*'), rpad('*',3000,'*') );
1 row created.
ops$tkyte@ORA9IR2> insert into t values ( 3, null, null );
1 row created.
ops$tkyte@ORA9IR2> commit;
Commit complete.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select x, rowid, dbms_rowid.rowid_block_number(rowid) from t;
X ROWID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
---------- ------------------ ------------------------------------
1 AAAKODAAFAAAC/qAAA 12266
2 AAAKODAAFAAAC/qAAB 12266
3 AAAKODAAFAAAC/qAAC 12266
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> delete from t where x = 2;
1 row deleted.
ops$tkyte@ORA9IR2> commit;
Commit complete.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 4, rpad( '*',1000,'*'), null );
1 row created.
ops$tkyte@ORA9IR2> select x, rowid, dbms_rowid.rowid_block_number(rowid) from t;
X ROWID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
---------- ------------------ ------------------------------------
1 AAAKODAAFAAAC/qAAA 12266
4 AAAKODAAFAAAC/qAAB 12266
3 AAAKODAAFAAAC/qAAC 12266
There is a "demo" of it happening:
manual segment space management
8k block
Rowids are not "sequential" things, they say WHERE a row exists, not when a row was inserted or anything like that.
rowid
karthick, October 05, 2005 - 8:00 am UTC
Tom said...
Another is way is to consider a SEGMENT to be a set of blocks (these blocks are
collected together into extents). Say you create a segment (initially totally
empty). It might look like this:
Oeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee
where O = block used for Overhead (oracle used that block for space management)
e = empty block, block has NEVER contained data.
Now, you insert 1,000 rows into the segment. It now looks like this:
Oddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeee
where d = block with data on it. The set of blocks with d's on them are the set
of blocks under the HWM -- the HWM is the set of blocks that have contained
data. Now I delete 1,000 rows from the table. it will now look like this:
Oddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeee
My Question...
can you tell me what happens if i insert another 100 rows
???????????????????????????????????????????????
if eeeee's are going to change as ddddd's then new row takes new blocks am i right
then they will be given a new address that is rowid am i right. then when the rowid
is going to be reused ( if i dont preform truncate )
if iam wrong please correct me.
Tom said continues...
that is -- it is the same. Just like in a flood -- a HWM can only be advanced.
A HWM never decreases for an object -- you have to in effect recreate the object
to decrease the HWM. So a truncate table would do this:
Oeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee
October 05, 2005 - 11:00 am UTC
when you did the delete - the blocks went onto the freelist (manual segment space management) or the bitmaps used by ASSM were updated to say "empty" and that space will be reused before the HWM of the segment is extended.
the e's would not be used if the 100 rows can fit in the d's.
Primary key ordering
San, May 23, 2006 - 11:47 am UTC
I have a question here. When we create a primary key on a column in a table, (presuming that a unique index would be created on that column), how are the rows ordered ?
Considering table "test_order"
create table test_order (sno number primary key, name varchar2(50));
insert into test_order
(select rownum, object_name
from all_objects where rownum < 100);
commit;
delete from test_order where rownum < 60;
commit;
insert into test_order
(select rownum, object_name
from user_objects
where rownum < 60);
commit;
When we do a "select * from test_order", it wouldn't be sorted by the primary key column which is "sno".
Could you please explain the phenomenon.
Thanks
San
May 23, 2006 - 3:47 pm UTC
the rows are not ordered in a heap table, they just go where they fit.
rows in relational tables don't have an order really.
One Another way to retrieve last record
Mahmood Lebbai, June 26, 2006 - 1:55 am UTC
Consider this query...
select * from (select rownum r,empno,ename,job,sal,deptno from emp) where r=(select max(rownum) from emp)
Tom, any comments...
By the way, On a personal note, I have been trying to ask some questions related to performance on SQL queries...And I get this "Sorry..I have a huge backlog...", all the time. I do not seem to find some time where you are not overwhelmed with questions. I do not plan to ask the question on this review forum after seeing your stern warning not to use this forum for additional questions. Should I hold on to my waiting game?
June 26, 2006 - 7:41 am UTC
that would be a rather inefficient method to get a random record from the table.
select rownum r, ... from emp
will full scan the table, hitting the rows in the order they happen to exist on disk (which has no relation to the order of insertion or anything like that!).
(select max(rownum) from emp)
is an inefficient method to do a count(*) against the table.
and the result would be some random row - not the last inserted row.
ops$tkyte@ORA10GR2> create table t ( x int, y varchar2(4000) ) tablespace mssm;
Table created.
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> insert into t values ( 1, rpad('*',4000,'*') );
1 row created.
ops$tkyte@ORA10GR2> insert into t values ( 2, rpad('*',3000,'*') );
1 row created.
ops$tkyte@ORA10GR2> insert into t values ( 3, rpad('*',3000,'*') );
1 row created.
ops$tkyte@ORA10GR2> commit;
Commit complete.
ops$tkyte@ORA10GR2> delete from t where x = 2;
1 row deleted.
ops$tkyte@ORA10GR2> commit;
Commit complete.
ops$tkyte@ORA10GR2> insert into t values ( 4, null );
1 row created.
ops$tkyte@ORA10GR2> commit;
Commit complete.
ops$tkyte@ORA10GR2> select * from ( select rownum r, x from t ) where r = (select max(rownum) from t );
R X
---------- ----------
3 3
<b>the only way to have a "last record" is for you to
a) define your concept of "last"
b) record attribute values that support your definition of "last"
c) execute a query that retrieves this "last" record based on a and b.</b>
Excellent
Mahmood Lebbai, June 26, 2006 - 4:47 pm UTC
Tom,
Your response was very good. And I did not expect I would get the reply, the same day. Wow!! Great!!!
I tried the same examples on my schema and I have to agree that you were absolutely right. I usually apply my strategy of "By hook or by crook" to solve the problem like these. Sometimes or (all the time, I don't know :-)), this approach compromises on its efficiency and performance. That is not a good thing, I Understand.
I appreciate your prompt rejoinder. Thanks, again.
Is there an exception to this rule?
A reader, August 15, 2006 - 4:52 pm UTC
"Trust me on this one -- there is exactly ONE way to sort of get the last row
inserted. You MUST have some column that you can order by.
Rowid - won't work. Its based on flie/block/slot. We reuse rowids, we can even
change them (partitioned tables, IOTS). You might have extent #1 in file 55 and
extent #2 in file 2. Extent 4 might start on block 555 of file 3, Extent 5
might start on block 2 of file 3. Rowids are NOT sortable."
Let's assume that the task of the application is to:
-loop until no more rows
- get a group of rows where each group can have many rows with the same (repeating) name, e.g.
AAAA .....
AAAA .....
BBBB ....
CCCC ....
CCCC ....
CCCC ...
...
- process this group of rows
-end loop
If this is the case, we can then sort by rowid in the inline view, can't we? Even if the rows change location/rowid in between application runs we will still get correct processing result, as all the application need to do is to process rows in groups of names where each group contains many names and each name can span many rows (rows with identical name column) and to ensure it does not process same row twice. Is that true? Is this an exception to the NO ORDER BY ROWID rule?
August 15, 2006 - 5:01 pm UTC
why would you sort by rowid?
I don't know what you are asking really - the original poster wanted to get things out in order of insertion.
what is your goal?
If you need to process in groups of names, you BETTER be sorting by - well - names don't you thing? sorting by rowid would give you an ordering roughly equivalent to "dbms_random.random" when you talk about the NAME attribute.
The goal
A reader, August 15, 2006 - 5:57 pm UTC
as I said is just to process, i.e. "touch" each row, exactly once, every time application runs, that's all, nothing else. And to do that not row by row, but group by group where each group has many rows with different names and each of these names can be repeated. The order of those rows with the same name is not important. What is important is to process in certain naming order, e.g.
Aaron - DDave
DDave - MMilton
...
MMilton - ZZed
but to process each row exactly once (but group by group)
i.e. in the first group there are many DDaves. All of these DDaves should be processed in that group as name can only be allocated to one and only one group
August 15, 2006 - 6:25 pm UTC
but they you obviously have to sort... by NAME
if you sorted by rowid, the names would be returned, well, randomly.
so, if the goal is to process the data by groups of "X", you best sort by "X" to get all of the "X"'s with the same value together.
last entry
A reader, September 24, 2008 - 11:17 pm UTC
Tom:
What do you think of those designs that add a special derived (Y/N) column to indicate the last record entered and use a trigger to keep this column up-to-date and saying that MAX function created overhead on the DB. It seems very bad design to me and extra code and many updates and you end up with one derived extra column per child table plus the mutating trigger error. is this correct.
ROWNUM=1 vs ROWNUM<=1
Bilal, March 25, 2009 - 10:52 am UTC
Tom, why these queries are different in performance.
for me its very strange, would you please tell me the reason for this.
1) CPU - I/O usage is very high
SELECT inv_number,
inv_type,
inv_sub_type,
carrier_local_flag,
inv_run,
invoice_mode_code
FROM (SELECT insih.inv_number,
insih.inv_type,
insih.inv_sub_type,
insih.carrier_local_flag,
insih.inv_run,
insih.invoice_mode_code
FROM sales_inv_headers insih
WHERE :p_company_code IS NULL
AND:p_system_id IS NULL
AND:p_inv_number IS NULL
AND:p_inv_run IS NULL
AND:p_bulk_suffix IS NULL)
WHERE ROWNUM =1
2) CPU - I/O usage is very low
SELECT inv_number,
inv_type,
inv_sub_type,
carrier_local_flag,
inv_run,
invoice_mode_code
FROM (SELECT insih.inv_number,
insih.inv_type,
insih.inv_sub_type,
insih.carrier_local_flag,
insih.inv_run,
insih.invoice_mode_code
FROM sales_inv_headers insih
WHERE :p_company_code IS NULL
AND:p_system_id IS NULL
AND:p_inv_number IS NULL
AND:p_inv_run IS NULL
AND:p_bulk_suffix IS NULL)
WHERE ROWNUM <=1
March 29, 2009 - 8:22 pm UTC
and you didn't include.....
a tkprof report which would have told us a LOT
so, provide that.
I don't think your analysis is correct, something else was happening. All objects is a very very complex view, however:
select *
from
all_objects where rownum = 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.06 0.07 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 57 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.06 0.08 0 57 0 1
********************************************************************************
select *
from
all_objects where rownum <= 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.07 0.07 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 57 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.07 0.07 0 57 0 1
they are the same.
ROWNUM=1 vs ROWNUM<=1
Bilal, March 30, 2009 - 8:20 am UTC
yes here it is with TKPROF report.
Here :p_rownum = 1.
1) CPU - I/O usage is very high
SELECT inv_number,
inv_type,
inv_sub_type,
carrier_local_flag,
inv_run,
invoice_mode_code
FROM (SELECT insih.inv_number,
insih.inv_type,
insih.inv_sub_type,
insih.carrier_local_flag,
insih.inv_run,
insih.invoice_mode_code
FROM sales_inv_headers insih
WHERE :p_company_code IS NULL
AND:p_system_id IS NULL
AND:p_inv_number IS NULL
AND:p_inv_run IS NULL
AND:p_bulk_suffix IS NULL)
WHERE ROWNUM = :p_rownum
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.03 0.03 0 0 0 0
Execute 3 0.01 0.53 0 0 0 0
Fetch 3 0.36 0.49 4151 12480 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9 0.40 1.06 4151 12480 0 3
2) CPU - I/O usage is very low
SELECT inv_number,
inv_type,
inv_sub_type,
carrier_local_flag,
inv_run,
invoice_mode_code
FROM (SELECT insih.inv_number,
insih.inv_type,
insih.inv_sub_type,
insih.carrier_local_flag,
insih.inv_run,
insih.invoice_mode_code
FROM sales_inv_headers insih
WHERE :p_company_code IS NULL
AND:p_system_id IS NULL
AND:p_inv_number IS NULL
AND:p_inv_run IS NULL
AND:p_bulk_suffix IS NULL)
WHERE ROWNUM <= :p_rownum
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 4 0.01 0.52 0 0 0 0
Fetch 3 0.00 0.00 56 12 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10 0.01 0.54 56 12 0 3
March 30, 2009 - 4:48 pm UTC
oh, you didn't use the number 1, you used a bind variable.
do you have the row source operation there? looks like an explain plan but shows the amount of work PLUS the plan.
This is useful but i want something like this
riteshtiwary, April 24, 2009 - 6:41 am UTC
select feild from demo where date_colum=(date_colum between sysdate-2 and sysdate) and TIMESTAMP_COLUMN = ( select max(timestamp_column) from demo);
April 27, 2009 - 11:58 am UTC
so??? go for it I guess???
guess you better make sure that timestamp column with the max value is in the last two dates.
ROWNUM=1 vs ROWNUM<=1
bilal, June 03, 2009 - 3:47 am UTC
sorry tom due to medical probs i ws on leave.
ys im applying row operation.
so is there any difference if i use parameter or number in ROWNUM criteria?
please define the difference.
Last inserted rows
Meth, September 16, 2010 - 10:30 pm UTC
We can use ORA_ROWSCN and come up with the last rows which have been inserted to a table when we sort it desc.
September 17, 2010 - 7:04 am UTC
only if you rebuild the table from scratch with rowdependencies.
and it would not return the last rows inserted, it would return the last rows MODIFIED - which is very different.
I would not recommend it for this.
last N records for a logging table with only Insert operations
A reader, November 15, 2010 - 2:13 pm UTC
Tom:
I trust and understand your statememt. "You MUST have some column that you can order by"
my situation is a little bit different.
I have a huge table for logging purpose, the only DML operation on it is insert, rows are never updated or deleted.
the first column is a seq_nbr column generated by a sequence, and it is the primary key, it is a regular heap table.
right now , I am using :
SELECT *
FROM
(
SELECT * FROM z_log
ORDER BY seq_nbr desc
)
WHERE ROWNUM<20;
to get the last 20 rows.
check the plan, Oracle will do a "INDEX FULL SCAN DESCENDING INDEX(unique)" first , followed by "Table access by Global Index ROWID".
for this case, if the index is really huge, INDEX FULL SCAN will be expensive. and because this table is only allowing Insert operations, do you think there can be another way to make the query faster by avoiding index full scan?
I am thinking using IOT table might be a better option, but not quite sure the Pros and Cons.
can you clarify?
thanks
November 16, 2010 - 3:35 am UTC
there is also a count stopkey in there isn't there.
that means the plan stops being run when the stopkey is hit.
it will index full scan desc for only 20 entries and then stop. trace it - you'll see that very little IO is done on the index, it is not reading the entire thing.
ROWNUM and stopkey
James Su, November 16, 2010 - 12:37 pm UTC
Hi Tom,
For the original question, is this as good as your answer?
SELECT * FROM (select * from T ORDER BY timestamp_column DESC)
WHERE rownum = 1;
November 17, 2010 - 5:49 am UTC
yes, that would be an equivalent query assuming timestamp_column was NOT NULL.
Followup on "Last N records for a logging table with only Insert operations"
Zack, February 03, 2011 - 3:21 pm UTC
Regarding the query to get the last 20 (19) rows:
SELECT * FROM
(
SELECT * FROM z_log
ORDER BY seq_nbr desc
)
WHERE ROWNUM < 20;
Its cost is 3 (with a similar table of 23,000 rows). However, the cost increases to 21 if we want the last 1000 rows, and 40 for last 2000. A friend of mine suggests I use the following query which has a constant cost of 8.
SELECT * FROM z_log
WHERE
seq_nbr >= (SELECT (MAX(seq_nbr) - 2000) FROM z_log)
Could you please comment on these 2 queries?
Thanks much in advance!
February 03, 2011 - 4:15 pm UTC
I think you need new friends.
So what if the cost goes up - but we do less work?
we could
a) index range scan descending to find N index entries
b) do a table access by index rowid for each one
(and the cost of getting 1000 rows IS MORE than the cost of getting 20 rows and so on, the cost really does go up!)
or we could
a) do an index min/max scan
b) then do an index range scan descending for find about N entries (remember please, SEQUENCES ARE NEVER EVER IN A MILLION BILLION YEARS GAP FREE!!!!!!!!)
c) and then do a table access by index rowid for each one.
I don't know about you - but I'd rather do less work and get the right answer.
Tell me, what is the logic behind choosing a query that has a constant cost? So what if the cost is constant? Why is that "good", why is that better? They are entirely different queries and in fact return different answers.
Stick with your first query please.
last row
A reader, February 04, 2011 - 8:02 am UTC
Followup on "Last N records for a logging table with only Insert operations"
Zack, February 04, 2011 - 3:57 pm UTC
Thanks a ton, Tom.
I hear you - the less work, the better. I wish the query plans would support this but the query plans seem to say the 2nd query is better cost/bytes wise. So I'm a bit confused. I understand there are exceptions that a lower cost might not result in a shorter time. Can you please comment on this a bit more? Please refer to the complete query plans for the 2 queries below.
>> They are entirely different queries and in fact return different answers.
Understood. It's not a problem in our case as we don't have to get exactly N rows back.
>> what is the logic behind choosing a query that has a constant cost? So what if the cost is constant? Why is that "good", why is that better?
Constant or not, what's more important is of curse a cheaper cost which the 2nd query seems to give according to the paln. In our case, the N in "last N" varies based on the user input.
===== EXPLAIN PLAN Outputs =====
SQL> EXPLAIN PLAN FOR select * from (select * from Z_LOG order by seq_nbr desc) f1 where ROWNUM <= 2000;
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2000 | 414K| 40 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 2000 | 414K| 40 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| Z_LOG | 22417 | 2583K| 40 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN DESCENDING| XPK_Z_LOG | 2000 | | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=2000)
SQL> EXPLAIN PLAN FOR SELECT * FROM Z_LOG WHERE seq_nbr >= (SELECT (MAX(seq_nbr) - 2000) FROM Z_LOG);
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1121 | 129K| 8 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | Z_LOG | 1121 | 129K| 6 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | XPK_Z_LOG | 202 | | 2 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 5 | | |
| 4 | INDEX FULL SCAN (MIN/MAX)| XPK_Z_LOG | 22417 | 109K| 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("seq_nbr">= (SELECT MAX("seq_nbr")-2000 FROM "ZACK"."Z_LOG" "Z_LOG"))
February 06, 2011 - 12:00 pm UTC
... Constant or not, what's more important is of curse a cheaper cost which the 2nd
query seems to give according to the paln. In our case, the N in "last N"
varies based on the user input. ...
I've said it a million times, you cannot compare the cost of two different queries. It just doesn't work in real life.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:313416745628 We all have to use our knowledge, our wisdom, our insights into how things work to work them out in our heads. It is obvious to me in looking at the two rather simple plans above - especially after I wrote out in english what they do step by step - which is more efficient.
I can even tell you "why". One of the queries accurately knows "I SHALL RETURN 2,000 RECORDS".
The other query says "I think on average I'll return about 1,121 records"
One of the plans has a dead accurate cost based on dead accurate "guesses" as to the cardinality. Guesses that we KNOW are dead accurate.
One of the plans doesn't. It estimated the cardinality "pretty well", but not as good as the other one. Therefore, any cost associated with it will be impacted by that.
RowID
Ananth, February 08, 2011 - 2:58 pm UTC
Hi Tom,
For ROWID, can we say it this way.
On any day/Time, the ROWID is unique. (if its being derived).
Its only the varying data that resides on that ROWID.
Regards
Ananth
February 10, 2011 - 3:55 pm UTC
the rowid is unique within a table, yes.
the rowid is not unique within a database.
And these days the rowid assigned to a row may change over time, it is not constant as it used to be in the olden days.
rowid
Debasis, February 15, 2011 - 7:18 am UTC
Rowid cannot be used to extract specifically first record or last record inserted, because if one record is deleted in between, then there might be chance that rowid can be assigned to a new record inserted. so in that case max(rowid) or min(rowid) is not working properly to extract the first record and last record.
One more instance like while loading data to a table, suppose there are two processes to load the data into table, then rowid assignment will not be in a sequential order. In this case, min(rowid) and max(rowid) will not be suitable to extract the last record and first record.
Thanks,
Debasis
rowid
Debasis, February 15, 2011 - 7:21 am UTC
In my view, the only way to extract the first record or last record by the use of timestamp.
Thanks,
Debasis
February 15, 2011 - 8:41 am UTC
and even that doesn't "work" if by "first" or "last" you mean in order of commit.
The application, the developer, the data modeler - they must specify the rules that deem something "first" or "last" and the data model must support attributes that allow that rule to be followed.
How to find first and last record from table
Surendra, May 22, 2011 - 1:33 pm UTC
Hi TOM,
I am Surendra want know about findout first and last record from a table.Can you send reply to this query.
Thanks & Regards
Surendra
May 23, 2011 - 11:53 am UTC
tell me what a first and last record would look like?
The concept of first and last do not exist in relational databases - that hasn't changed since I first answered this question (we already stated such) way back when.
to find out first record in a table in oracle
T.venkatesh, May 22, 2011 - 2:32 pm UTC
to find out first record in a table in oracle
T.venkatesh, May 22, 2011 - 2:34 pm UTC
Please send me the answer for it its urgent
Thanks,
T.venkatesh
May 23, 2011 - 12:00 pm UTC
if you would have read this page, you would have realized before posting that...
there is no answer. Please think about it, this isn't dbase or a flat file here, it is a relational database - there is no such thing as a first or last record.
RIGHT ANSWER FOR LAST RECORD IN ORACLE
A reader, June 20, 2011 - 2:00 am UTC
SELECT * FROM (SELECT REGN_DT,ROWNUM R, REGN_NO FROM VT_OWNER) WHERE R >(SELECT MAX(ROWNUM) -4 FROM VT_OWNER);
June 20, 2011 - 1:06 pm UTC
hahahahahahah
Hope you don't have any production code that counts on that. But only because you are wronger than wrong.
There is NO SUCH THING as a "last record", data goes where ever data feels like going.
There are so many things wrong with your approach, so many misunderstandings (I'm being rather blunt here because if you had read this page, you would know that already)...
first of all, you select "-4" in the above. What is -4 for??? getting max(rownum)-4 and getting everything "greater than that" - what does that do?? It looks like you are trying to get the last 4 records - but whatever.
It still doesn't work.
Because rows do not go to the "end" of a table. They go where ever we feel like putting them.
Here is an example for you - using your query. I used an 8k blocksize, 11.2.0.2 database, with ASSM tablespaces (automatic segment space managed)
ops$tkyte%ORA11GR2> create table t ( x int, y varchar2(4000), z varchar2(4000) );
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into t values ( 1, rpad( 'x', 4000, 'x' ), rpad( 'x', 2000, 'x' ) );
1 row created.
ops$tkyte%ORA11GR2> insert into t values ( 2, rpad( 'x', 4000, 'x' ), rpad( 'x', 2000, 'x' ) );
1 row created.
ops$tkyte%ORA11GR2> insert into t values ( 3, rpad( 'x', 4000, 'x' ), rpad( 'x', 2000, 'x' ) );
1 row created.
ops$tkyte%ORA11GR2> insert into t values ( 4, rpad( 'x', 4000, 'x' ), rpad( 'x', 2000, 'x' ) );
1 row created.
ops$tkyte%ORA11GR2> insert into t values ( 5, rpad( 'x', 4000, 'x' ), rpad( 'x', 2000, 'x' ) );
1 row created.
ops$tkyte%ORA11GR2> commit;
Commit complete.
ops$tkyte%ORA11GR2> select x from t;
X
----------
5
1
2
3
4
<b>Now, you might already notice a problem here... The first row is coming out AFTER the fifth row - go figure. We put data where we feel like it...</b>
ops$tkyte%ORA11GR2> delete from t where x = 5;
1 row deleted.
ops$tkyte%ORA11GR2> commit;
Commit complete.
ops$tkyte%ORA11GR2> insert into t values ( 42, rpad( 'x', 4000, 'x' ), rpad( 'x', 2000, 'x' ) );
1 row created.
<b>that is arguably the last row right? I'm in a single user database here, no one else..</b>
ops$tkyte%ORA11GR2> commit;
Commit complete.
ops$tkyte%ORA11GR2> select x from t;
X
----------
42
1
2
3
4
<b>oh, but wait, we decided in this case (this is a direct cut and paste from sqlplus, nothing up my sleeve here) it should be "first" in a full scan...</b>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> SELECT *
2 FROM (SELECT x, ROWNUM R FROM t)
3 WHERE R >(SELECT MAX(ROWNUM) -4 FROM t);
X R
---------- ----------
1 2
2 3
3 4
4 5
Interesting, you returned everything EXCEPT for the last row!!!!
Now, please stop thinking there is a first row, a second row, a last row and so on.
Unless you have some attribute in your data to tell you what is "last", there is going to be no such thing as a last row.
Alexander, June 20, 2011 - 2:59 pm UTC
Why did you include those varchar2(4000) columns? Was that to ensure the row deleted would not be inserted back into the free block and thereby accidentally show ordered results?
June 20, 2011 - 3:18 pm UTC
so I could come up with an easy case knowing I could control the blocks on the freelist very simply.
I just had a row per block in my example. A block either
a) had a row
b) was on the free list and had no rows
and nothing in between
It can happen with ANY size row however. I do not NEED to have the varchar's there, it just made it really trivial for me to demonstrate "it does not work that way, it has never worked that way, it will NEVER work that way, there is no such thing as a 'last row'"
how to identify the rowid which one is max(rowid) in table
charan, June 23, 2011 - 1:16 am UTC
Hi Tom,
i have one doubt on rowid concept.how to idntify which rowid is max in the table?
Actualy i have table
sno rowid name values
--- ----- ---- ------
1 AAA rad 10
1 AAB rad 20
1 AAC rad 5
please give advise on this?
June 23, 2011 - 7:57 am UTC
why? It is not like it implies that is the newest record or anything. The rowid is just an address, it does not convey any other information short of an address.
MAX() works on rowids, so you can find the max rowid in the same fashion you find the max salary.
how to get duplicate records?
charan, June 23, 2011 - 8:04 am UTC
HI Tom,
i write one querry plz explain that querry........
select * from emp where rowid not in (select max(rowid)from emp group by deptno);
above i posted one table that refers to how it works?plz reslove me.
June 23, 2011 - 8:17 am UTC
plz? German postal codes? I'm confused, what do German postal codes have to do with anything?
http://zipcodelookup.me/postal-codes-for-german-towns.php In any case, I'll explain the query.
The subquery "select max(rowid) from emp group by deptno" generates a set of rowids - one for each department found in the emp table. The rowid selected is the "max" rowid for that department - which is not really meaningful, it just ensures at least one and at most one rowid per department will be selected.
Then, the outer query selects all of the data from the emp table where the rowid is NOT IN that set of maximum rowids.
That query returns all of the rows from EMP *except* for one row from each department in EMP - it is a rather "random" row that is dropped out.
It looks like a nonsense query to me, I cannot see how it could ever be useful.
how to delete the duplicate records in table
charan, June 28, 2011 - 12:39 am UTC
hI Tom,
i have a table that table have different duplicate data
how to remove the duplicate records in my table?
how to process please give suggestion
June 28, 2011 - 11:50 am UTC
How to Fix
garys, June 28, 2011 - 12:34 pm UTC
MAX should modified in the next release to give an error if you pass it a rowid. Any answer would be nonsense, anyway.
(I realize that doing this would break a lot of existing code, but it would be a Tough Love Blessing in disguise.)
June 28, 2011 - 12:59 pm UTC
No it isn't nonsense, you can use between, >, <, = and so on. the concepts exist for that (thankfully - we couldn't do "do it yourself parallelism" without them!)
http://asktom.oracle.com/pls/asktom/asktom.search?p_string=%22do+it+yourself+parallelism%22 http://asktom.oracle.com/pls/asktom/asktom.search?p_string=dbms_parallel_execute for a rowid to be greater than another rowid implies it is in a file with a bigger number (or the same), a block with a greater number (or the same) and a slot in the block that is bigger.
it can be used in a sensible fashion:
delete from t where rowid NOT IN (select max(rowid) from t group by SOME,KEY,COLUMN(S) );
that would de-duplicate that table using SOME,KEY,COLUMNS as the unique key - it would keep one record per SOME,KEY,COLUMN value....
Enlightening response
garys, June 28, 2011 - 1:11 pm UTC
Thanks for the response about what it means to order rowids. I read about the rowid structure some time back and now it makes (more) sense.
comparing rowids
Sokrates, June 28, 2011 - 2:31 pm UTC
it seems we even can compare rowids of different tables (though I cannot think of a case where this might be of any use):
sql > CREATE SCHEMA AUTHORIZATION sokrates
2 create table heap1(i int)
3 create table heap2(i int)
4 ;
Schema created.
sql > insert
2 when mod(rownum, 2) = 0 then into heap1 else into heap2
3 select rownum from dba_objects where rownum<=4;
4 rows created.
sql > select
2 a.rowid || ' ' ||
3 case when a.rowid < b.rowid then '<' when a.rowid = b.rowid then '=' when a.rowid > b.rowid then '>' end
4 || ' ' || b.rowid
5 from heap1 a, heap2 b;
A.ROWID||''||CASEWHENA.ROWID<B.ROWIDTHE
---------------------------------------
AAAuoUAAEAAAAi9AAA > AAAuoTAAEAAAAi1AAA
AAAuoUAAEAAAAi9AAA > AAAuoTAAEAAAAi1AAB
AAAuoUAAEAAAAi9AAB > AAAuoTAAEAAAAi1AAA
AAAuoUAAEAAAAi9AAB > AAAuoTAAEAAAAi1AAB
However, comparing the rowid of a heap table with one of an iot seems to fail with ORA-01410: invalid ROWID:
sql > CREATE SCHEMA AUTHORIZATION sokrates
2 create table heap(i int)
3 create table iot( i int primary key ) organization index
4 ;
Schema created.
sql > insert
2 when mod(rownum, 2) = 0 then into heap else into iot
3 select rownum from dba_objects where rownum<=4;
4 rows created.
sql > select
2 a.rowid || ' ' ||
3 case when a.rowid < b.rowid then '<' when a.rowid = b.rowid then '=' when a.rowid > b.rowid then '>' end
4 || ' ' || b.rowid
5 from heap a, iot b;
from heap a, iot b
*
ERROR at line 5:
ORA-01410: invalid ROWID
why ?
June 28, 2011 - 4:10 pm UTC
... (though I cannot think of a case where this might be of any use) ....
clustered tables perhaps - in that situation - two rows can have the same rowid ;) Still not sure what you would use it for though.
And true on the IOT since it really isn't a rowid, it is a logical rowid - the rowid of an IOT contains a rowid hint and the primary key of the row in the IOT.
need help on sql query
Shayam, June 29, 2011 - 11:31 pm UTC
Hi Tom,
CREATE TABLE CMR
(
LOGDATE CHAR(8 BYTE),
LOGTIME CHAR(6 BYTE),
ORDERNO NUMBER(10),
MESSAGE VARCHAR2(1024 BYTE)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE TABLE ABC
(
ORDERNO NUMBER(10) NOT NULL,
FILENAME VARCHAR2(64 BYTE),
JOBDATE CHAR(8 BYTE)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
insert into abc(ORDERNO,FILENAME, JOBDATE) Values(8082,'Maintenance','20110624');
insert into abc(ORDERNO,FILENAME, JOBDATE) Values(8083,'Maintenance','20110623');
insert into abc(ORDERNO,FILENAME, JOBDATE) Values(8084,'Maintenance','20110625');
insert into abc(ORDERNO,FILENAME, JOBDATE) Values(8085,'condition_test','20110622');
insert into abc(ORDERNO,FILENAME, JOBDATE) Values(8086,'condition_test','20110621');
insert into abc(ORDERNO,FILENAME, JOBDATE) Values(8087,'condition_test','20110621');
commit;
Insert into CMR(LOGDATE, LOGTIME,ORDERNO,MESSAGE) Values('20110624', '090031',8082,'HELD BY OPS');
Insert into CMR(LOGDATE, LOGTIME,ORDERNO,MESSAGE) Values('20110623', '130231',8083,'HELD BY OPS');
Insert into CMR(LOGDATE, LOGTIME,ORDERNO,MESSAGE) Values('20110625', '070131',8084,'HELD BY OPS');
Insert into CMR(LOGDATE, LOGTIME,ORDERNO,MESSAGE) Values('20110622', '030031',8085,'HELD BY OPS');
Insert into CMR(LOGDATE, LOGTIME,ORDERNO,MESSAGE) Values('20110621', '180031',8086,'HELD BY OPS');
Insert into CMR(LOGDATE, LOGTIME,ORDERNO,MESSAGE) Values('20110621', '020031',8087,'HELD BY OPS');
commit;
SQL Query:
--------
select b.filename, b.jobDATE, to_char(to_date(logdate || logtime, 'YYYY-MM-DD HH24:MI:SS'),
'DD-MM-YY HH24:MI:SS') Held_Time,
TRUNC (MOD ((SYSDATE - TO_DATE (MAX (logdate) || MAX (logtime),'YYYY-MM-DD HH24:MI:SS')) * 24,24))
HR
from cmr a, abc b
where message = 'HELD BY OPS' and a.orderno=b.orderno
group by b.filename, b.jobDATE,a.logdate, a.logtime
having TRUNC (MOD ((SYSDATE - TO_DATE (MAX (logdate) || MAX (logtime),'YYYY-MM-DD HH24:MI:SS')) *
24,24)) >= 3
Result:-
=======
FILENAME JOBDATE HELD_TIME HR
--------------------------------------------------------
Maintenance 20110623 23-06-11 13:02:31 20
condition_test 20110621 21-06-11 18:00:31 15
condition_test 20110621 21-06-11 02:00:31 7
condition_test 20110622 22-06-11 03:00:31 6
Requirement: how we can get latest held time for particular file name which is held by ops more than 3 hour. here i am getting 3 held time for filename "condition_test" .
Could you please help me on this.
Thanks for effort....
July 01, 2011 - 8:14 am UTC
do you want three records with the latest held time that exceeds 3 hr's associated with each filename?
or did you want the record that has the latest held time that exceeds 3 hr's?
I don't really understand your data - you'll have to explain it. Your query is quite confusing. I don't see a single primary key nor foreign key, I don't know what is supposed to be unique. It is strange to be grouping by something (logtime) that you also MAX() against. And it is really really strange to be concatenating two fields together "MAX (logdate) || MAX (logtime)" to create a date since the logdate and logtime attributes in that case could come from two different rows!!!
and to use mod??? if the time between sysdate and your max function was 24 hours - the mod would be zero - the held time would be 24 hours, but you wouldn't recognize it as such??
I *think* you might want this, but truly - I am not sure at all.
ops$tkyte%ORA11GR2> select * from (
2 select b.filename,
3 b.jobDATE,
4 to_char(to_date(logdate || logtime, 'YYYY-MM-DD HH24:MI:SS'), 'DD-MM-YY HH24:MI:SS') Held_Time,
5 (SYSDATE - TO_DATE (logdate || logtime,'YYYY-MM-DD HH24:MI:SS')) * 24 hr,
6 row_number() over (partition by b.filename order by to_date(logdate || logtime, 'YYYY-MM-DD HH24:MI:SS')) rn
7 from cmr a, abc b
8 where message = 'HELD BY OPS'
9 and a.orderno=b.orderno
10 and (SYSDATE - TO_DATE (logdate || logtime,'YYYY-MM-DD HH24:MI:SS')) * 24 >= 3
11 ) where rn = 1
12 /
FILENAME JOBDATE HELD_TIME HR RN
---------------------------------------------------------------- -------- ----------------- ---------- ----------
Maintenance 20110623 23-06-11 13:02:31 188.188333 1
condition_test 20110621 21-06-11 02:00:31 247.221667 1
need help
Shayam, July 02, 2011 - 2:18 am UTC
Thanks for reply and suggesstion...
i have held and free two times the file 'Maintenance' and 'condition_test' in 2 minute interval.then i run your query..for rn = 1,
it is giving first held time and for rn =2 it is is giving
latest held time for both file.
is there any way to get latest or max rownumber(rn)instead of giving manually 1 and 2 for rn because it can be many no of time hold and free ?
i want no of record with latest held time that exceeds 3 hr's associated with each filename.
July 05, 2011 - 7:44 am UTC
you need to explain this better. I don't understand your logic, your model, your data at all - I said as much above.
Start over, create tables, inserts into table, and provide A LOT of text that explains exactly what is what. Like a specification you would give a programmer to write code from
Shayam, July 05, 2011 - 10:26 am UTC
Take a example of emp table.
Query:
SELECT deptno, ename, rn
FROM (SELECT deptno, job, ename, sal,
ROW_NUMBER () OVER (PARTITION BY deptno, job ORDER BY sal,
ename) AS rn
FROM emp)
WHERE rn >= 1
Result:
DEPTNO ENAME RN
10 MILLER 1
10 CLARK 1
10 KING 1
20 FORD 1
20 SCOTT 2
20 SMITH 1
20 ADAMS 2
20 JONES 1
30 JAMES 1
30 BLAKE 1
30 MARTIN 1
30 WARD 2
30 TURNER 3
30 ALLEN 4
In this scenario , we know rn =4 is the max /latest row number in emp table so we will put WHERE rn >= 4 instead of WHERE rn >= 1 in sql query to get max row number row.
Suppose we donot know how many no of row in the table. And we need to take record only for max rownumber row.So how we can modify above sql query to get only max rownumber row.
July 05, 2011 - 4:00 pm UTC
this confuses me.
do you want the highest row_number from ALL deptnos/jobs
or
do you only want the highest row_number in any deptno/job
these answer that in turn:
ops$tkyte%ORA11GR2> select * from (
2 SELECT deptno, ename, rn, max(rn) over() max_rn
3 FROM (SELECT deptno, job, ename, sal,
4 ROW_NUMBER () OVER (PARTITION BY deptno, job ORDER BY sal,
5 ename) AS rn
6 FROM emp)
7 ) where rn = max_rn
8 /
DEPTNO ENAME RN MAX_RN
---------- ---------- ---------- ----------
30 ALLEN 4 4
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select *
2 FROM (SELECT deptno, job, ename, sal,
3 ROW_NUMBER () OVER (PARTITION BY deptno, job ORDER BY sal DESC nulls last,
4 ename DESC nulls last) AS rn
5 FROM emp)
6 where rn = 1
7 /
DEPTNO JOB ENAME SAL RN
---------- --------- ---------- ---------- ----------
10 CLERK MILLER 1300 1
MANAGER CLARK 2450 1
PRESIDENT KING 5000 1
20 ANALYST SCOTT 3000 1
CLERK ADAMS 1100 1
MANAGER JONES 2975 1
30 CLERK JAMES 950 1
MANAGER BLAKE 2850 1
SALESMAN ALLEN 1600 1
9 rows selected.
Shayam, July 06, 2011 - 8:00 am UTC
Thanks a million
last row of a table based on priority
A reader, August 06, 2011 - 10:06 am UTC
I have a lookup table that has a composite unique key made up of 3 columns (a, b, c). Unique as any of the columns can be null and I need some help the query below;
select *
from ORDERS ord, LOOKUP l
where ( l.a = ord.a and l.b = ord.b and l.c = ord.c
or l.a = ord.a and l.b = ord.b and l.c is null
or l.a = ord.a and l.b is null and l.c = ord.c
or l.a is null and l.b = ord.b and l.c = ord.c
or l.a = ord.a and l.b is null and l.c is null
)
The above query could return up to 5 rows for each distinct ORDER row - what I actually want to return is 1 ORDER row but the one with the highest LOOKUP "priority", and that "priority" is determined by the order of the WHERE clause above, e.g.
lookup row where l.a = ord.a and l.b = ord.b and l.c = ord.c is priority 1 (highest)
lookup row where l.a = ord.a and l.b = ord.b and l.c is null
is priority 2 (2nd highest)
lookup row where l.a = ord.a and l.b is null and l.c = ord.c
is priority 3 (3rd highest), etc
If you can help me with a SQL query first, that would be great.
Secondly, and I might not be able to change this for our project, do you think we can change the table design to make the query easier?
August 06, 2011 - 2:43 pm UTC
Secondly, and I might not be able to change this for our project, do you think we can change the table design to make the query easier?
Yes, I think you should change your data model, this is really sort of whacky - I'd have to understand the "requirement" (you point to data in a really strange manner, I'd have to really understand the motivation here - what real world purpose this solves - I cannot imagine this being useful in business).
how many rows do you expect to return from the outer query? do you use this to return "a row", "10 rows", "all rows" ?
last row of a table based on priority
A reader, August 07, 2011 - 3:52 am UTC
Thanks Tom.
I state "Secondly" as I'm doubtful I'll be able to get the architects to consider changing their design at this stage, so "Firstly" is for me to get the SQL to work with the design we have!
LOOKUP serves as some kind of validation for the business. However the design has been made so that the user does not have to enter every combination of the 3 codes in order for the validation to pass. e.g. if they've entered all 3 codes, then fine, but if the data only matches on 2, then that's fine as well, etc. So they've come up with these 5 different filters (in the WHERE clause).
So for example, the validation check code would be along the lines of;
select *
from ORDERS ord
where exists
(select *
from LOOKUP l
where ( l.a = ord.a and l.b = ord.b and l.c = ord.c
or l.a = ord.a and l.b = ord.b and l.c is null
or l.a = ord.a and l.b is null and l.c = ord.c
or l.a is null and l.b = ord.b and l.c = ord.c
or l.a = ord.a and l.b is null and l.c is null
)
)
Any existence of 5 rows to them passes the VALID check.
However, the client wants to report on ORDERS and also pull in other data from LOOKUP but only returning just 1 row back from LOOKUP, so they've decided that a priority based on the filter test is used (e.g. if l.a = ord.a and l.b = ord.b and l.c = ord.c then that is the row with the highest priority).
I understand the design appears wacky, but we have what we have and the solution I've ended up with so far is outer joining to LOOKUP 5 times in the select statement and a case expression round each report column;
select case when lup1.rowid is not null then lup1.value
when lup2.rowid is not null then lup2.value...
from orders o,
lookup lup1,lookup lup2,lookup lup3...
Not great, hence I've posted my query to see if there's a better way.
A reader, August 11, 2011 - 3:47 pm UTC
I should think that this query should solve for the first question. Sorry about the formatting, and you could do the two inner queries in one if you wanted, but your ROW_NUMBER function would be very hard to read.
select ord.a, ord.b, ord.c FROM (
select ord.a, ord.b, ord.c,
ROW_NUMBER() OVER (PARTITION BY ord.a, ord.b, ord.c ORDER BY DECODE(sort_key,'YYY',1,'YYN',2,'YNY',3,'NYY',4,'YNN',5,6) ASC) AS rowno
FROM
(select ord.a, ord.b, ord.c,
DECODE(l.a,NULL,'N','Y') || DECODE(l.b,NULL,'N','Y') || DECODE(l.c,NULL,'N','Y') AS sort_key
from ORDERS ord, LOOKUP l
where ( l.a = ord.a and l.b = ord.b and l.c = ord.c
or l.a = ord.a and l.b = ord.b and l.c is null
or l.a = ord.a and l.b is null and l.c = ord.c
or l.a is null and l.b = ord.b and l.c = ord.c
or l.a = ord.a and l.b is null and l.c is null
)))
WHERE rownum = 1;
thanks
A reader, August 11, 2011 - 4:20 pm UTC
thank you reader of Arizona, will give that a try
Selecting last record
Jobz, March 07, 2012 - 1:40 am UTC
Hi Tom,
Tried the method that you mentioned in the example. I got the corrct answer, when I ordered it in ASC order.
March 07, 2012 - 6:49 pm UTC
so, if you flip a coin five times and it comes up heads - do you conclude "coins, when flipped, come up heads"?
All I have to do is provide one counter case that demonstrates something isn't true. I don't care how many "positive" cases you have - as long as I have one case, I've shown "it is not true"
how about this,
show your work and I'll tweak it to show that even your test fails.
A Reader, March 26, 2012 - 11:52 pm UTC
Hi Tom,
my requirement is to fetch record of jobs which are running longer than 24 hours.
Description of table tco:
Name Null? Type
----------------------------------------- -------- ----------------------------
JOBNAME NOT NULL VARCHAR2(96)
STRTDATE NOT NULL VARCHAR2(24)
STRTTIME NOT NULL VARCHAR2(18)
ENDDATE NOT NULL VARCHAR2(24)
ENDTIME NOT NULL VARCHAR2(18)
STATUS NOT NULL VARCHAR2(3)
SQL Query used:
select jobname,status,to_char(to_date(max(STRTDATE) || max(STRTTIME), 'YYYY-MM-DD HH24:MI:SS'),'DD-MM-YY HH24:MI:SS') Start_Time,
trunc ((sysdate - to_char(to_date(max(STRTDATE) || max(STRTTIME), 'YYYY-MM-DD HH24:MI:SS'),'DD-MM-YY HH24:MI:SS'))*24,24) hour,
case
when status = 'A'then 'Executing'
else status end "Current_Status",
from tco where status = 'A'
group by jobname,status
having trunc (((sysdate - to_char(to_date(max(STRTDATE) || max(STRTTIME), 'YYYY-MM-DD HH24:MI:SS'),'DD-MM-YY HH24:MI:SS'))*24,24) > 24)
Getting Error:
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got DATE
Can you please help on this.
March 27, 2012 - 7:49 am UTC
which rocket scientist decided this would be a brilliant idea?
STRTDATE NOT NULL VARCHAR2(24)
STRTTIME NOT NULL VARCHAR2(18)
ENDDATE NOT NULL VARCHAR2(24)
ENDTIME NOT NULL VARCHAR2(18)
I'll never understand, not in a million years.
...
else status end "Current_Status",
from tco where status = 'A'
...
that is *not* the query you used, see that "," there - it does not belong and with it we get:
12 /
from tco where status = 'A'
*
ERROR at line 8:
ORA-00936: missing expression
if I take it out I get
12 /
'YYYY-MM-DD HH24:MI:SS'),'DD-MM-YY HH24:MI:SS'))*24,24) > 24)
*
ERROR at line 11:
ORA-00907: missing right parenthesis
so, in short, you didn't bother to give us anything useful to work with :( Just sort of wasted our time.
A test case would have a create table and a select against it that *actually* reproduced the query.
If status = 'A' is active jobs, then I think all you want is a rather "simple" plain query isn't it?
ops$tkyte%ORA11GR2> select *
2 from tco
3 where status = 'A' -- running I presume, A for Active?
4 and to_date( strtdate || strttime, 'yyyy-mm-dd hh24:mi:ss' ) < sysdate-1
5 ;
no rows selected
A reader, March 30, 2012 - 12:26 am UTC
Hi Tom,
Thanks for valuable replying !!!
i have run the SQL query as you suggested below:
SQL query:-
select jobname,status from tco where status ='A' and jobname like 'SAP%' and to_date( strtdate || strttime, 'yyyy-mm-dd hh24:mi:ss' ) >= sysdate-1
Output:-
JOBNAME STA
--------------------------------------------------------------------------
SAP_SRT_METERING_REORG A
SAP_SRT_SEQ_DELETE_BGRFC_QUEUES A
SAP_SRT_SEQ_DELETE_TERM_SEQ A
SAP_SRT_SEQ_DELETE_TERM_SEQ A
SAP_SRT_SEQ_DELETE_TERM_SEQ A
SAP_SRT_SEQ_DELETE_TERM_SEQ A
SAP_SRT_SEQ_DELETE_TERM_SEQ A
7 rows selected.
but from SAPGUI i am getting 12 jobs are cencelled:
FYR:
Job overview from: 29.03.2012 at: 00:00:00
to: 30.03.2012 at: 23:59:59
Selected job names: SAP*
Selected user names: *
Scheduled Released Ready Active Finished Canceled
Event controlled Event ID:
ABAP program Program name :
Job Spool Job Doc Status Start date Start time Duration(sec.) Delay (sec.)
SAP_SRT_METERING_REORG Canceled 30.03.2012 03:00:42 0 42
SAP_SRT_SEQ_DELETE_BGRFC_QUEUES Canceled 29.03.2012 01:04:30 0 0
SAP_SRT_SEQ_DELETE_BGRFC_QUEUES Canceled 29.03.2012 02:04:31 0 1
SAP_SRT_SEQ_DELETE_BGRFC_QUEUES Canceled 30.03.2012 00:04:40 0 0
SAP_SRT_SEQ_DELETE_TERM_SEQ Canceled 29.03.2012 00:04:30 0 0
SAP_SRT_SEQ_DELETE_TERM_SEQ Canceled 29.03.2012 03:04:31 0 0
SAP_SRT_SEQ_DELETE_TERM_SEQ Canceled 29.03.2012 04:04:31 0 0
SAP_SRT_SEQ_DELETE_TERM_SEQ Canceled 29.03.2012 23:04:40 1 0
SAP_SRT_SEQ_DELETE_TERM_SEQ Canceled 30.03.2012 01:04:41 0 0
SAP_SRT_SEQ_DELETE_TERM_SEQ Canceled 30.03.2012 02:04:41 0 0
SAP_SRT_SEQ_DELETE_TERM_SEQ Canceled 30.03.2012 03:04:42 0 0
SAP_SRT_SEQ_DELETE_TERM_SEQ Canceled 30.03.2012 04:04:43 0 0
*Summary 1 43
Could you please help me on this.
March 30, 2012 - 7:10 am UTC
so, you haven't bothered to read anything I wrote above?
I'll show you again:
so, in short, you didn't bother to give us anything useful to work with :( Just sort of wasted our time.
what the heck is "SAPGUI"
why do I care about cancelled jobs
where is a schema to work with
where is test data to play with
where is the query you are trying - one that actually is a query, not the stuff from above
where is the definition of all of the inputs
where is the definition of all of the outputs (logic needed to transform inputs into outputs - a full specification, a description)
come on.
How about order by?
Cornell, May 18, 2012 - 12:45 pm UTC
First, as you'd indicated, one needs to define "last". If it's the most recent entry, timestamp's the way to go. Can't use sequence numbers (as in NEXTVAL), as they're not necessarily in order.
I'm probably not using the right jargon, but when a database is clustered, groups of potential nextval values are handed out to each member of the cluster, which uses them as needed. That is, 1..10 might go to one, and 11..20 might go to another. Then three nextval calls on the first, second and first, would yield a sequence of 1,11,2.
Second, about the only way..
You'd indicated that this is the only way to get the "last" (most recent timestamp_column) row:
select * from t where TIMESTAMP_COLUMN = ( select max(timestamp_column) from T )
and rownum = 1;
That query would require going over the entire table to find the MAX, then going over it again to find those that match, if I'm not mistaken.
Wouldn't order by desc, get one row work as well?
select * from (
select * from t
order by TIMESTAMP_COLUMN desc
)
where rownum = 1;
This query would sort the table, I don't know how expensive that is in comparison to MAX.
Testing in TOAD shows elapsed times that are fairly close, the second usually a tad faster. Unfortunately, my skills with determining efficiency are currently at the level of checking elapsed time.
May 21, 2012 - 8:19 am UTC
That query would require going over the entire table to find the MAX
it would not. Making the reasonable assumption that there was an index on timestamp_column, we'd use an index min/max scan to find the max value (about 3 IO's) and be done with it.
And any semantically equivalent query would suffice - that still fits in with the statement "the only way". You need to get the row having the maximum timestamp. I can come up with an infinite number of queries to "say that", but it is still "the only way".
Your query is technically not semantically equivalent since your query returns at most one row. My query returns all of the "last rows"
and as of the time of writing - it was version 8.0 - which did not have the top-n query optimization
testing in toad for response times from a query that is pretty much trivial (as this is) isn't going to do anything. Toad measures elapsed time, not database time. You are timing the amount of time it takes toad to paint the screen, deal with the data and so on.
And your query would not sort the table, assuming an index on timestamp_column - it would return as quickly as the first query.
Even if it did not have the index, your query would not sort the entire table, it would just have to basically scan the table. Top-N query optimization would make it so we'd read the first row - then we'd read the second row and only keep it if created was larger. And so on. We'd only have one row in memory at any point in time, we do not sort the entire table.
A better approach
Mark, July 13, 2012 - 9:11 am UTC
You absolutely do not have to use "order by" to get the first or last entry in a table. It's all in how you set the table up. If you set the table up with an auto_incrementing primary key, you can use the max of that key to get the lastest entry. Example:
select * from some_table where tables_primary_key = ( select max(table_primary_key) from some_table )
That should give you an almost instant reponse from the database and you won't have to sit there and wait on some "order by" sorting.
July 16, 2012 - 3:44 pm UTC
umm,
guess which will do less work:
select * from (select * from t order by indexed_column desc) where rownum = 1;
versus
select * from some_table where tables_primary_key = ( select
max(table_primary_key) from some_table )
Let's see:
big_table%ORA11GR2> set autotrace traceonly
big_table%ORA11GR2> select *
2 from (select *
3 from big_table
4 order by id desc )
5 where rownum = 1
6 /
Execution Plan
----------------------------------------------------------
Plan hash value: 3437067777
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 141 | 4 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 1 | 141 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| BIG_TABLE | 1000K| 94M| 4 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN DESCENDING| BIG_TABLE_PK | 1 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1345 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
big_table%ORA11GR2> select *
2 from big_table
3 where id = (select max(id) from big_table)
4 /
Execution Plan
----------------------------------------------------------
Plan hash value: 2895345854
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 99 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | BIG_TABLE | 1 | 99 | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | BIG_TABLE_PK | 1 | | 2 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 5 | | |
| 4 | INDEX FULL SCAN (MIN/MAX)| BIG_TABLE_PK | 1 | 5 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"= (SELECT MAX("ID") FROM "BIG_TABLE" "BIG_TABLE"))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1345 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
big_table%ORA11GR2> set autotrace off
Oh, that would be the one that did that "nasty order by" - which doesn't have to sort.
Which of the two above queries do you believe will execute faster?
The first one that
a) reads an index and then
b) access a row in a table?
or the second one that
a) reads an index to get a max value and then
b) reads an index to get a rowid and then
c) reads a table
I'd rather hit the index once - how about you?
You don't have to "sit there and wait on some order by sorting" - the database is a tad bit more intelligent than that.
before you blast something or suggest you have a vastly superior approach - you should really
a) set up a test case
b) give the example
c) show the numbers
It saves time....
(you could have just read the review right above yours too, we had this discussion)
Or you could have read the original answer which included a few ways to do this... one of which was:
select * from t where TIMESTAMP_COLUMN = ( select max(timestamp_column) from T
)
and rownum = 1;
and the statement "it is the only way" referred to the fact you need some column in your table that identifies a row as being "the last", there is no concept of a last row.
solved my problem
Mubashar Ahmad, October 04, 2017 - 8:24 am UTC
i have a table having no pk and no timestamp. this solution helped me a lot. Thanks Tom.
Thanks for clarifying
Huang Jingheng, August 23, 2018 - 9:17 am UTC
I've been trying to use rownum to do that, its good that I now know that's a wrong track to follow.
Doesn't Fetches last record sometimes
Nisha, March 04, 2021 - 5:37 am UTC
select * from t where TIMESTAMP_COLUMN = ( select max(timestamp_column) from T )
and rownum = 1;
This query is not fetching lastest record sometimes. Usually when there is a lot of insert operation happening simultaneously in t table.Please suggestions any other alternative to fetch latest record using insert timestamp
March 04, 2021 - 9:08 am UTC
So how do you know it's not getting the latest row?
Bear in mind it's always possible that two rows have exactly the same insert time. When this happens there's no safe way to find which was last.
If the column has the date data type, this is likely as these are only granular to the second. Timestamps can have fractional seconds. With enough precision, it's less likely but still possible.
Please suggestions any other alternativePlease give more details on
why you want to find the latest row.
Often this is because people want to know what values were inserted, e.g. for auto-generated PK values. In which case the returning clause is the way to go:
insert into ...
returning ...
Chuck Jolley, March 04, 2021 - 9:39 pm UTC
This strikes me as the same problem as with trying to get gapless sequences. You can have one process inserting rows and give have gapless sequence numbers, or you can have multiple processes inserting rows and give up on guaranteed gapless sequences.
In the same way, there really isn't a "latest row" much of the time with multiple processes inserting in the table.
Which one is latest? Last one created in the client, last one inserted, last one committed? It's kind of Schrodinger's row.
Save the timestamp, select where row_number(order by timestamp desc) = 1 and call it the last row. If someone complains, challenge them to prove you are wrong.
March 05, 2021 - 4:41 am UTC
Yup.
The moment you run that query, its potentially out of date.
Fetching latest record
Nisha, March 05, 2021 - 2:23 pm UTC
I face this issue for when, I try to fetch the latest record from one table and insert it to other.
Example:
Table:item ( item_id, name, price, insert_timestamp)
Constraint - item_id is unique here.
Table:item_dtl ( item_id, name, quantity insert_timestamp)
Constraint - item_id is unquie here
Sequence is as follow:
Existing entries in table
Table : Item
Item_id|name|price|insert_timestamp
4|Monitor|10|12|3/22/2012 6:26:17.712300000 AM
3|CPU|20|100|3/22/2012 6:26:15.712300000 AM
2|Monitor|30|200|21/1/2012 5:27:15.712300000 AM
1|CPU|40|200|3/21/2012 5:26:15.712300000 AM
Table: item_ dtl
Item_id| name| quantity| insert_timestamp
4|Monitor|10|3/22/2012 6:27:17.712300000 AM
3|CPU|20|3/22/2012 6:27:15.712300000 AM
2|Monitor|30|21/1/2012 5:29:15.712300000 AM
1|CPU|40|3/21/2012 5:28:15.712300000 AM
Script 1 : Monitor Script
Insert into item values(5, 'Monitor',50, sysdate);
Insert into item_dtl(item_id,name, quantity,insert_timestamp) values (( select item_id from item where name=`Monitor' and insert_timestamp = ( select max( insert_timestamp) from item where name = 'Monitor ' ) and rownum = 1),( select price from item where name=`Monitor ' and insert_timestamp = ( select max( insert_timestamp) from item where name = 'Monitor ' ) and rownum = 1),100, sysdate);
Script 2 : CPU Script
Insert into item values (6, 'CPU', 60,sysdate);
Insert into item_dtl(item_id,name, quantity,insert_timestamp) values (( select item_id from item where name=`CPU' and insert_timestamp = ( select max( insert_timestamp) from item where name = 'CPU ' ) and rownum = 1),( select price from item where name=`CPU ' and insert_timestamp = ( select max( insert_timestamp) from item where name = 'CPU ' ) and rownum = 1),100, sysdate);
Similarly, we have 10 script and these are running simultaneously.
I get unique key constraint error when adding records in item_dtl.
Timestamp shows insert happened in item table before running the insert for item_dtl.
NOTE: the script fails only sometime, 20% failure.
I hope the above details are clear. Please let me know what i am doing wrong here.
March 05, 2021 - 5:18 pm UTC
So it looks like the returning clause is the way to go here!
Insert into item values(5, 'Monitor',50, sysdate)
returning id, price
into l_id, l_price;
Insert into item_dtl
values (l_id, l_price, 100, sysdate);
Similarly, we have 10 script and these are running simultaneously.It's highly likely that the detail insert in one session is picking up an item inserted in another session from time-to-time. Use the returning clause as shown above to avoid this problem.
Doesn't Fetches last record sometimes
Nisha, March 05, 2021 - 5:37 pm UTC
Thanks Tom for the quick update.
Can a delay of 2s between 'insert into item' and 'insert into item_dtls' also work?
As this has been implemented in two different platforms (i.e)
Item table insertion is happening using Java program
Item_dts insertion is happening in shell script.
If delay doesn't work, then I will definitely restructure my current implementation and use returning as suggested.
March 08, 2021 - 2:17 pm UTC
Returning is the only safe way here; adding delays will only make the problem worse!
Thanks Tom!
Carol Benesh, April 09, 2021 - 4:41 pm UTC
You always provide great complete answers. Whenever I google and there is a link 'Ask Tom' I just know this link will explain what I need. Thank you.
April 12, 2021 - 5:17 am UTC
Thanks for the feedback