Hi there,
Looking for a way of returning the Xth to Yth records of an Oracle database
I thought using ROWNUM. Here is the solution I came up with.
SELECT * FROM
(
SELECT
ROWNUM RECID,
t.*,
FROM
table t
WHERE
(ROWNUM BETWEEN 1 AND Y) AND
(whatever condition you want on your table)
)
WHERE
RECID >= X
It work great for me so far, but my tables aren't very big (a few thousands
records).
Is anybody aware of any kind of issues using this query as the table get
bigger and, if yes, is there any way to optimize this. (They are history
tables and will definitely get huge at some point).
I assumed that as this query is processed entirely on the Oracle server the
performance sould be better than if I had queried the top Y records and
filtered out the top X records on the client side.
Thanks for your advice.
JB Fidelia. 2 4082
"news" <jb*******@mult icorpora.ca> wrote in message
news:Vq******** **********@wagn er.videotron.ne t... Hi there,
Looking for a way of returning the Xth to Yth records of an Oracle
database I thought using ROWNUM. Here is the solution I came up with.
SELECT * FROM ( SELECT ROWNUM RECID, t.*, FROM table t WHERE (ROWNUM BETWEEN 1 AND Y) AND (whatever condition you want on your table) ) WHERE RECID >= X
It work great for me so far, but my tables aren't very big (a few
thousands records).
Is anybody aware of any kind of issues using this query as the table get bigger and, if yes, is there any way to optimize this. (They are history tables and will definitely get huge at some point).
I assumed that as this query is processed entirely on the Oracle server
the performance sould be better than if I had queried the top Y records and filtered out the top X records on the client side.
How do you in wich order the will be output since there is no ORDER BY in
your query??
But anyway the point is that if you want to avoid scanning the whole table
(to produce the rownum that you use) you should add column to your table,
say RECID, and assign a unique number to RECID with a sequence. Then if you
create a index on this column RECID, you will get your performance...
SELECT *
FROM table t WHERE (ROWNUM BETWEEN 1 AND Y) AND (whatever condition you want on your table)
Thanks for your advice.
JB Fidelia.
"news" <jb*******@mult icorpora.ca> wrote in message
news:Vq******** **********@wagn er.videotron.ne t... Hi there,
Looking for a way of returning the Xth to Yth records of an Oracle
database I thought using ROWNUM. Here is the solution I came up with.
SELECT * FROM ( SELECT ROWNUM RECID, t.*, FROM table t WHERE (ROWNUM BETWEEN 1 AND Y) AND (whatever condition you want on your table) ) WHERE RECID >= X
It work great for me so far, but my tables aren't very big (a few
thousands records).
Is anybody aware of any kind of issues using this query as the table get bigger and, if yes, is there any way to optimize this. (They are history tables and will definitely get huge at some point).
I assumed that as this query is processed entirely on the Oracle server
the performance sould be better than if I had queried the top Y records and filtered out the top X records on the client side.
Thanks for your advice.
JB Fidelia.
Your above query is not very efficient, ROWNUM is a pseudo column so it
means querying the entire database (at least up to "Y" records) every time
to retrieve a subset. For example, supposing you had 100,000 records in
your table and you wanted to retrieve just the last 5, your query would
require scanning 99,995 records just to get to the first retreivable record.
As the previous poster mentioned, it's better to create a unique, indexed
field and assign it a value from a sequence, and do your queries based on
this field instead of ROWNUM. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Sugapablo |
last post by:
Let's say I create a new record in a table like this:
mysql_query("INSERT INTO table (col1) VALUES ('example')",$conn);
....that had an auto-incrementing, unique identifying column named "ID"
that would be populated with a unique number upon insertion...
How can I get the unique number it assigned to that record returned for
futher use?
|
by: Michael Trosen |
last post by:
Hi Everyone,
I hope someone can help, I'm pretty new to pro*c programming.
I have the following application setup:
a pro*c program calls a stored procedure and recieves a cursor back:
the cursor is defined as: SQL_CURSOR delpt_cursor
|
by: Andreas Fromm |
last post by:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi,
Im building an user database with many tables keeping the data for the
Address, Phone numbers, etc which are referenced by a table where I keep
the single users. My question is, how do I get the "Id"-value of a newly
inserted address to store it in the referencing user table:
|
by: Ben |
last post by:
I have a Tabular form bound to a table. The purpose of this form is to
get times from a timer and record them in a field. The timer dumps the
time automatically through a serial port. When the time is recorded it
goes to the next record. Sometimes a user may have to skip a record and
come back. All of this is no problem and works great.
I have 3 buttons that will set any penalties in a record. The person
just needs to hit a button to...
|
by: klove1209 |
last post by:
Good afternoon,
Can someone please guide me towards how to return records from a table. I currently have a table with about 21 fields, and 21 records. I have one field that is just text and the remaining fields are checkboxes, which allows the user to select true/false for each record in the text field.
I.E.
Field Txt Chkbx1 chkbx2 chkbox3
Eat Apples True False True
Now, how to I loop through this table, for each...
| |
by: littlevikinggirl |
last post by:
Hi,
I posted a badly worded question last week so got no replies and am
still struggling to figure out the problem myself. I have a table
containing two fields, Location and Serial Number. I want to display
some of the serial number values on a form which will have a diagram
overlaid (to map out the "locations").
I have been trying to write an If statement in the controlsource
properties of the text boxes to say "if location=position1...
|
by: vinodkus |
last post by:
I M BEGINNER IN ASP
I WANT TO RETURN TOTAL RECORDS FROM A TABLE.
THERE ARE TWO FORMS CLASS1.ASP AND CLASS2.ASP
THROUGH FIRST FORM I JUST POST THE NAME OF TABLE
SO I M WRITING THE CODE OF CLASS2.ASP
<!--#Include File = "Include/iecon.inc"-->
<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
|
by: jennwilson |
last post by:
Using Access 2000 - I have a query that is suppose to return the records from table within specified time range and find matching data from another table .
Table houses Clinician name, location and rate information
Table houses Clinician name, location and number of units for each type of service rendered on each day. The two tables are connected by field
Please see the code below
SELECT .Clinician, .Location, .Date,...
|
by: news |
last post by:
Hi there,
Looking for a way of returning the Xth to Yth records of an Oracle database
I thought using ROWNUM. Here is the solution I came up with.
SELECT * FROM
(
SELECT
ROWNUM RECID,
t.*,
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |