• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 52
  • Last Modified:

When running a Query with an Oracle tool like PL SQL does it automatically search an entire table or just the first ten records to sample. If not how do you force the entire set of rows for each table

Trying to figure out if I can search the entire list of tables here that would allow me to find a record in this database.
Same dilemma same database for those that know me. Spent many many hours trying to find where these records are.
For an example. I have searched the 905 tables below for 15 different records. Only found one...?
My question is when a query is run I noticed that PL SQL only shows a few records like maybe ten rows.
Then it allows you to push the double green arrow seen here to in fact run the entire set of rows for the table.

When you run a small query it shows a few rows....then you can click the green double arrow to see all rows..?
I am curious, if the first" run of ten rows" is all the current query is seeing in actual records...could this explain why I am not seeing many results? See lower code set.
Could that be because the query is only showing the first 10 rows of each table and not seeing the record. If so how do I get the records to look at "Search" the full range of rows in my query. Is that the Bind Variables.

If the code below can be modified to do so then I am open to suggestions. If it is not, I either have to ask another question which I can open or maybe I can include it here. Hard to break these up when you are trying to learn realizing I know so little.  My attempt to get the code to work was the removal of a specific name of a table.
"/*and table_name like 'JOB%'*/   and my thought was that the remaining line of................ "from All_tab_cols     where owner ='JAS' ....... would help me do that? My theory did not work. Still looking myself it is just taking forever when you do not know the terms or understand the structure and function yet. Never giving up or quitting though.


Select * From JAS.ACCOUNTS  ;
Select * From JAS.ADDRESSES ;
/* and 901 other tables in between the line above and line below*/
Select * From JAS.WIP_EXPORT_DETAILS  ;
Select * From JAS.WORK_ORDER_LABOUR_CODES ;
Select * From JAS.XLSX_TEXT ;

select table_name,column_name from (
  select table_name,
    column_name,
    EXTRACTVALUE(
      DBMS_XMLGEN.getxmltype(
        'select count(*) X from ' || table_name || ' where to_char(' || column_name || ') = ''6328'' and rownum=1'
      ),
      '/ROWSET/ROW/X'
    ) count
  from All_tab_cols
  where owner ='JAS' 
  /*and table_name like 'JOB%'*/
  order by table_name, column_name
)
where count=1
/

Open in new window

0
Fletcher Burdine
Asked:
Fletcher Burdine
  • 3
  • 2
3 Solutions
 
sdstuberCommented:
under your preferences you can set "Records per Page"  

you can find the option under Window Types - SQL Window


also note the Maximum result set size - this is helpful if you have limited memory, but usually I recommend setting it to 0 (unlimited) and using the Records limit instead if you need a limit
0
 
Mark GeerlingsDatabase AdministratorCommented:
I see a couple contradictions in your question, apparently because you are new to Oracle.  

First, you said : "When running a Query with an Oracle tool like PL SQL...":.  This is not accurate.  I think you mean: "When running a Query in Oracle...".  Queries in Oracle are SQL, not PL\SQL.  PL\SQL is Oracle's proprietary set of additions to SQL that are used to create stored procedures in Oracle.  Microsoft has a comparable set of additions for SQL Server called T-SQL for creating stored procedures in SQL Server databases.

Second, you included a screen shot from some kind of GUI tool.  That is certainly not PL\SQL, since PL\SQL is not GUI.  Can you tell us which tool or program that actually is?

Oracle SQL queries always query any/all records that meet the criteria that is provided in the "where" clause of the query.  This can be the entire table, or just a portion, depending on which details are provided in the "where" clause.  Oracle SQL by default does *NOT* limit the number of rows that are returned.  Some GUI tools (SQL Developer, TOAD, etc.) provide the ability to execute SQL queries and they can include the ability to limit the number of rows returned.  That will depend on the particular tool you are using and on what settings it may have by default, or what settings you may have changed.
0
 
sdstuberCommented:
The program is "PL/SQL Developer"  it's a common, but unfortunate abbreviation to be called "pl/sql"

The tool itself does do paged fetching, and I've provided the instructions on how to change it in my first response.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Fletcher BurdineTableau Trainer & Consultant Sales Exec.Author Commented:
Good thank you for this information. Do you have any thoughts on maybe what might be a good setting at first to see if the records return a result. I thought about setting it to say 200 rows as another member reminding me that running all those tables over and over which I will do lightly today. I will run more briskly tonight when no one is entering or looking at data? You think setting it to 200 rows would be a good test sampling to try or another number if there are thousands of rows?
0
 
sdstuberCommented:
There is no "right" or "best" answer to that for everyone.

I have mine set to 500.
It works for me for most queries, on occasion I change it to something larger but not often.

If 200 is working for you, there's nothing wrong with it.
0
 
Fletcher BurdineTableau Trainer & Consultant Sales Exec.Author Commented:
Thanks that did it. Very helpful
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now