• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

SQL Query formation problem.

 
Ranch Hand
Posts: 223
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am supposed to write a query to extract information from a table.
The table has 30 million records. Now, the requirements are:

1. The entire table has to be ordered first based on the id.
2. Once the table is ordered, I have to write a query as below:



Now, I initially formed the query as:

However, this does not work as this only orders the ResultSet from the query. But I need the entire table to be sorted so that if the query breaks in the middle, I need to be able to resume the query from the last id onwards.

Please help!! This is crazy!
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by v ray:
But I need the entire table to be sorted so that if the query breaks in the middle, I need to be able to resume the query from the last id onwards.


You can't sort the table. It sounds like you are trying to accomplish saving the database work, which you can do another way.

You can create an index on the id column and order that index (when you create the index, you specify it as a sorted index.) Scanning an index is much faster than scanning the tables which can reduce the overall work.
 
v ray
Ranch Hand
Posts: 223
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The table is already indexed, does that help me in any way?
Currently, what I am using is:

select id,xml from xml_hash
where id> startId and rownum < size
order by id;

so if I use 10 threads to execute the query, startId will keep getting incremented which stops at some specified count value...

What do you think of that option?
 
Jeanne Boyarsky
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
That will work. Note that some databases do this parallelism for you.
 
v ray
Ranch Hand
Posts: 223
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well, I am already doing that, but it still needs to be faster, hence the use of threads.

We are currently using:
select id,xml from xml_hash/*+Parallel,4*/
where id> startId and rownum < size
order by id;

We are using a oracle10g database.
Btw, a question. Saying parallel,4 is for the better use of multiple processors right? How do we get the database to use multiple threads, etc to process the above query faster?
The main problem here is, the xml in the above query is a Clob object and is really large!
 
The longest recorded flight time of a chicken is 13 seconds. But that was done without this tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic