Querying Db with huge data
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Querying Db with huge data

  1. #1
    Join Date
    Jun 2013
    Posts
    1

    Question Querying Db with huge data

    [FONT=Century Gothic]Hi Friends,
    I need your help regarding data input to a query.
    For example, I have 1000 records (student_name) in an excel file, I have to query the "students" table for getting the details of these 1000 student records.
    how will I pass these 1000 records in the query as input /FONT]

    [I]select * from students
    where student_id in (' ', ' ',..... ' '_;[/
    I]

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    If I understand your requirement correctly...
    1- you have a large "students" table
    2- you have to retrieve data related to 1,000 of them
    Is that correct?

    Either you...
    1- list the 1,000 student_id's on your predicate, or
    2- you find a different column that brings all of those students like the status of the student being active or something like that, or
    3- you join students table with other tables that allow to filter the 1,000 desired students

    Hard to tell 'cause we have no visibility of your environment.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,003
    It looks to me like he has a table in an Oracle database and he wants to take an external file and query on the database table based on the external file. It seems to me that you want to create an external table in the database to point to your spreadsheet. Then when you query from the students table you can do this:

    Code:
    SELECT * 
      FROM students
     WHERE student_id IN
         ( SELECT student_id
             FROM student_spreadsheet )
     ORDER BY 1;
    this space intentionally left blank

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by gandolf989 View Post
    It looks to me like he has a table in an Oracle database and he wants to take an external file and query on the database table based on the external file. It seems to me that you want to create an external table in the database to point to your spreadsheet. Then when you query from the students table you can do this:

    Code:
    SELECT * 
      FROM students
     WHERE student_id IN
         ( SELECT student_id
             FROM student_spreadsheet )
     ORDER BY 1;
    This is a very smart solution indeed.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,003
    Quote Originally Posted by PAVB View Post
    This is a very smart solution indeed.
    Thanks Yoda!
    this space intentionally left blank

  6. #6
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,478

    Thumbs up

    Quote Originally Posted by PAVB View Post
    This is a very smart solution indeed.
    Indeed it is, I use it all the time...
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width